How To install and setup PostgreSQL 9.6 on Debian Linux 9

Posted on in Categories , , last updated June 30, 2017

How do I Install and setup PostgreSQL version 9.6 database system on Debian Linux version 9.x?

The PostgreSQL (Postgres) is a free and open source object-relational database system. This tutorial shows you how to install and configure PostgreSQL version 9.6 relational databases on Debian Linux 9.

The PostgreSQL supports a large part of the SQL standard and is designed to be extensible by users in many aspects. Some of the features are:

  1. ACID transactions
  2. Foreign keys
  3. Views
  4. Sequences
  5. Subqueries
  6. Triggers
  7. User-defined types
  8. Functions
  9. Outer joins
  10. Multiversion
  11. Concurrency control
  12. Graphical user interfaces and bindings for many
  13. programming languages are available as well

Install PostgreSQL

First update your system using apt command or apt-get command:
$ sudo apt update
$ sudo apt upgrade

To install PostgreSQL server, client and other tools type:
$ sudo apt-get install postgresql
OR specify version number:
$ sudo apt-get install postgresql-9.6
Sample outputs:

How To Install and Use PostgreSQL 9.6 on Debian 9
Fig.01 Installing PostgreSQL 9.6 on Debian 9

How do I start/stop/restart PostgreSQL server?

The syntax is as follows.

Command to start PostgreSQL server

$ sudo systemctl start postgresql

Command to stop PostgreSQL server

$ sudo systemctl stop postgresql

Command to restart PostgreSQL server

$ sudo systemctl restart postgresql

Command to see status of PostgreSQL server

$ sudo systemctl status postgresql
Sample outputs:

? postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Fri 2017-06-30 17:46:59 UTC; 4min 23s ago
 Main PID: 21627 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 4915)
   CGroup: /system.slice/postgresql.service
 
Jun 30 17:46:59 debian-9-stretch systemd[1]: Starting PostgreSQL RDBMS...
Jun 30 17:46:59 debian-9-stretch systemd[1]: Started PostgreSQL RDBMS.
Jun 30 17:47:00 debian-9-stretch systemd[1]: postgresql.service: Failed to reset devices.list: Operation not permitted

PostgreSQL administrator user

The PostgreSQL administrator user named as postgres on a Debian Linux server. Use postgres user to access the database and DBA needs. To login type the following command:
$ su - postgres
$ psql

Sample outputs:

psql (9.6.3)
Type "help" for help.

postgres=# 

The psql act as the PostgreSQL client where you can type all SQL commands. Please note that postgres user is differnet from the postgres Linux user. The Linux user named postgres is used to access the databases, and the PostgreSQL user is used to perform DBA work.

Protecting postgres user account by setting up a passwod

As a root user type the following command to setup a password for postgres Linux user account, run:
$ sudo passwd postgres
Sample outputs:

Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully

Login and setup the password for psql session of postgres user too:
$ su - postgres
$ psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'SecretPasswordHere';"

ALTER ROLE

Creating a new role/user

First create a shell user named vivek using the adduser command:
$ adduser vivek
You need to use the command named createuser. It creates a new PostgreSQL user (or more precisely, a role). The syntax is:
createuser userNameHere
createuser [options] userNameHere

To create a user vivek on the default database server:
$ su - postgres
$ createuser vivek --pwprompt

OR To create a user named vivek on the default database server with prompting for some additional attributes:
$ su - postgres
$ createuser --pwprompt --interactive vivek

Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

To see info about users, run:
$ psql
Type the \du at postgres=# prompt:
postgres=# \du
Sample outputs:

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 vivek     | Create DB                                                  | {}

Creating a new database for user vivek

The syntax is:
createdb -O userNameHere dbNameHere
For example:
$ su - postgres
$ createdb -O vivek sales

How do I login as user vivek?

Type the following shell command:
$ su - vivek
Type the following shell command to login to db named sales with user vivek:
$ psql -U vivek -d sales
Sample outputs:

psql (9.6.3)
Type "help" for help.

sales=>

To create a table named foo:
sales=> CREATE TABLE foo (id int, name varchar);
Add some data:
sales=> INSERT INTO foo VALUES (1, 'Vivek Gite');
sales=> INSERT INTO foo VALUES (2, 'Wendy Gite');
sales=> INSERT INTO foo VALUES (3, 'Tom Jerry');

List data:
sales=> SELECT * FROM foo;
Sample outputs:

Fig.02: Creating tables in pgsql
Fig.02: Creating tables in pgsql

You can easily delete data with the following SQL statement:
sales=> DELETE FROM foo WHERE id = '3';

To List available tables

Type the following \d command:

sales=> \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | foo  | table | vivek
(1 row)

sales=>

List databases

Type the following \l command:

sales=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sales     | vivek    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

For more information see postgresql docs.

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

1 comment

Leave a Comment