How to install PostgreSQL 9.6 on Debian 9

Most applications use a database to store data. One of the most popular open source solutions is PostgreSQL. PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

Before installing, let’s collect latest information from the Debian repositories:

sudo apt-get update

And install postgresql of specified version:

sudo apt-get PostgreSQL postgresql-9.6

To check installation type the command:

ps -ef | grep postgre

You would see something like this:

postgres 9482 1 0 21:55 ? 00:00:00 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf
postgres 9484 9482 0 21:55 ? 00:00:00 postgres: 9.6/main: checkpointer process
postgres 9485 9482 0 21:55 ? 00:00:00 postgres: 9.6/main: writer process
postgres 9486 9482 0 21:55 ? 00:00:00 postgres: 9.6/main: wal writer process
postgres 9487 9482 0 21:55 ? 00:00:00 postgres: 9.6/main: autovacuum launcher process
postgres 9488 9482 0 21:55 ? 00:00:00 postgres: 9.6/main: stats collector process

So PostgreSQL was installed and is running.

PostgreSQL is installed with a default user and default database both called postgres. To login to the database, first you need to switch to the postgres user by issuing the following command while logged in as root (this will not work with sudo access):

su - postgres

To start the PostgreSQL console:

psql

Creating New Roles

postgres=# CREATE ROLE costs WITH LOGIN PASSWORD 'costs-pass';

To check user was created:

postgres=# \du

Add role’s posibility to create db:

postgres=# ALTER ROLE costs CREATEDB;

Press \q to quit console And type exit to return root user.

Now, to be able to login as a newly created user we should change config file (peer to md5):

nano /etc/postgresql/9.6/main/pg_hba.conf

Change this line:

local   all             all                                     peer

To this line:

local   all             all                                     md5

Save and exit.

Then restart postgresql service:

systemctl restart postgresql

So, you will be able to login now.

Login as newly created user:

psql postgres -U costs

Create database:

postgres=> CREATE DATABASE costsdb;

Grant privileges:

postgres=> GRANT ALL PRIVILEGES ON DATABASE costsdb TO costs;

To see all databases:

postgres=> \list

To quit console:

postgres=> \q

Some useful commands:

\list: lists all the databases in Postgres
\connect: connect to a specific database
\dt: list the tables in the currently connected database