Installation of Postgres

Introduction
Relational database management systems are a key component of many web sites and applications. They provide a structured way to store, organize, and access information.

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without read locks.

In this guide, we will demonstrate how to install Postgres on an Ubuntu 16.04 VPS instance and go over some basic ways to use it.

Installation
Ubuntu's default repositories contain Postgres packages, so we can install these easily using the apt packaging system. Since this is our first time using apt in this session, we need to refresh our local package index.

We can then install the Postgres package and a -contrib package that adds some additional utilities and functionality:
$sudo apt-get update
$sudo apt-get install postgresql postgresql-contrib

Now that our software is installed, we can go over how it works and how it may be different from similar database management systems you may have used.

Using PostgreSQL Roles and Databases
By default, Postgres uses a concept called "roles" to handle in authentication and authorization. These are, in some ways, similar to regular Unix-style accounts, but Postgres does not distinguish between users and groups and instead prefers the more flexible term "role".

Upon installation Postgres is set up to use ident authentication, which means that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name will be able to sign in as that role.

There are a few ways to utilize this account to access Postgres.

Switching Over to the postgres Account
The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, we can log into that account.

Switch over to the postgres account on your server by typing:
$sudo -i -u postgres
You can now access a Postgres prompt immediately by typing:
$psql
You will be logged in and able to interact with the database management system right away.
Exit out of the PostgreSQL prompt by typing:
postgres=#

Connecting to PostgreSQL on Linux for the first time

Note
This section uses the command line utility psql and optionally the graphical utility pgAdmin . psql is included with the Boundless Server PostgreSQL package. pgAdmin is provided as part of Boundless Desktop .
on Linux, both on Ubuntu and Red Hat-based systems, the default PostgreSQL configuration has connections turned off for the postgres user by default.

So after install of Boundless Server, if you try to connect to PostgreSQL via the psql command-line utility or through pgAdmin , you will get the following connection error:
psql: FATAL:peer authentication failed for user "postgres"

There are two steps to allow connections to PostgreSQL:

• Set a password for the postgres user

• Allow local connections to PostgreSQL

For more information, please see the Ubuntu documentation on PostgreSQL

Setting a password for the postgres user
On Linux systems, there is no default password set.

To set the default password:

1. Run the psql command from the postgres user account:
   $sudo -u postgres psql postgres

2. Set the password:
   postgres=#\password postgres

3. Enter a password.

4. Close psql .

postgres=#\q