Linux, Windows, Virtualization, OpenSource & Blogging

Install PostgreSQL 9.6 on Debian 9 / Ubuntu 16.04 / LinuxMint 18

0

PostgreSQL (simply known as Postgres) is an object-relational database management system (ORDBMS), available for many platforms including Linux, Microsoft Windows, FreeBSD, HP-UX, AIX, Solaris, and Mac OS.

PostgreSQL is released under the PostgreSQL License, a free and open source permissive software license, developed by the PGDG (PostgreSQL Global Development Group), a group of individual volunteers and corporate companies.

In this post, we will go through the installation of PostgreSQL on Debian 9 / Ubuntu 16.04 / LinuxMint 18.

Add PostgreSQL repository

PostgreSQL publishes deb packages for debian based platforms, and their packages are fresher than those available in OS base repository.

Import the PostgreSQL signing key.

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | sudo apt-key add -

Add the PostgreSQL repository information to /etc/apt/sources.list.d/postgresql.list file.

### Debian 9 ###

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list

### Ubuntu 16.04 / LinuxMint 18 ###

echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list

Install PostgreSQL

Once you have added the PostgreSQL repository run the following command to update the repository index.

sudo apt-get update

Output:

Install PostgreSQL 9.6 on Debian 9 - apt-get update
Install PostgreSQL 9.6 on Debian 9 – apt-get update

Install PostgreSQL using apt-get command.

sudo apt-get install -y postgresql

Control PostgreSQL service

To start PostgreSQL service, run:

sudo systemctl start postgresql

To stop PostgreSQL service, run:

sudo systemctl stop postgresql

To restart PostgreSQL service, run:

sudo systemctl restart postgresql

To check the status of PostgreSQL service, run:

sudo systemctl status postgresql

Output:

 postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Mon 2017-09-18 21:53:02 CDT; 1min 53s ago
 Main PID: 3673 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service

Sep 18 21:53:02 mydebian systemd[1]: Starting PostgreSQL RDBMS...
Sep 18 21:53:02 mydebian systemd[1]: Started PostgreSQL RDBMS.

Access PostgreSQL

To manage the database, you need to log in as postgres user.

sudo su -l postgres

Access the database using the psql command. It is an interactive shell for working with PostgreSQL database.

psql

Output:

postgres@mydebian:~$ psql
psql (9.6.5)
Type "help" for help.

postgres=#

Secure PostgreSQL database

We will set a password for Linux user (postgres) as well as DB administrator (postgres).

Set password for Linux user (postgres):

sudo passwd postgres

Output:

raj@mydebian:~$ sudo passwd postgres
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully

Set password for DB administrator (postgres):

su - postgres
psql

On psql shell, run below command to change database admin password.

postgres=# \password

OR

postgres=# \password postgres

Output:

postgres=# \password postgres
Enter new password:
Enter it again:

Extras

The syntax for creating database is:

$ su - postgres

$ createdb test

The syntax for creating a user (raj) is:

$ psql test

test=# CREATE USER raj WITH SUPERUSER LOGIN PASSWORD 'password';

Check whether the new user has been created or not.

test=# \du

Output:

test=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 raj       | Superuser                                                  | {}

Login with the user “raj”.

psql -h localhost -d test -U raj

Output:

psql (9.6.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

test=#

The syntax for listing databases:

test=# \l

Output:

test=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | 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
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

That’s all!. You have successfully installed the PostgreSQL 9.6 on Debian 9 / Ubuntu 16.04 / LinuxMint 18.

POSTS YOU MAY LIKE -:)

You might also like

Install PostgreSQL 9.6 on Debian 9 / Ubuntu 16.04 / LinuxMint 18

0
Never Miss Any Linux Tutorials, Guides, Tips and Free eBooks