Install PostgreSQL 11 on Debian 9 / Debian 8

In this tutorial, we will cover how to Install PostgreSQL 11 on Debian 9 / Debian 8. PostgreSQL is a powerful, highly-extensible database server written in C. The development of PostgreSQL is under PostgreSQL Global Development Group.

PostgreSQL provides an object-relational database system that allows you to manage extensive datasets. PostgreSQL Server comes with features that guarantee fault-tolerance and data integrity hence ready for heavy production use. Check PostgreSQL 11 release page for new features.

For  the installation of PostgreSQL 11 on CentOS / Fedora,  use the following links:

• How to install PostgreSQL 11 on CentOS 7
• How to install PostgreSQL 11 on Fedora

Below are the steps to install PostgreSQL 11 on Debian 9 / Debian 8.

Step 1: Add PostgreSQL 11 APT repository

Import the repository signing key:

sudo apt update
sudo apt install -y vim wget
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Then add the actual repository contents to your Debian 9 / Debian 8 system:

RELEASE=$(lsb_release -cs)
echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main | sudo tee  /etc/apt/sources.list.d/pgdg.list

The repository file contents should look like below

$ cat /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main

Step 2:  Install PostgreSQL 11 on Debian 9 / Debian 8

After adding the repository, proceed to install PostgreSQL 11 on Debian 9 / Debian 8.

sudo apt update
sudo apt -y install postgresql-11

Step 3: Enable remote access

By default, access to PostgreSQL database server is only from localhost.

$ sudo ss -tunelp | grep 5432
tcp   LISTEN  0  128  127.0.0.1:5432         0.0.0.0:*      users:(("postgres",pid=15785,fd=3)) uid:111 ino:42331 sk:6 <->

Edit PostgreSQL 11 configuration file to change listening address:

sudo vim /etc/postgresql/11/main/postgresql.conf

Add below line under CONNECTIONS AND AUTHENTICATION section.

listen_addresses = '*'

You can also specify server IP Address

listen_addresses = '10.10.1.6'

See below screenshot.

Restart postgresql after making a change

sudo systemctl restart postgresql

Confirm the new PostgreSQL bind address:

$ sudo ss -tunelp | grep 5432
tcp   LISTEN  0       128    0.0.0.0:5432         0.0.0.0:*      users:(("postgres",pid=16066,fd=3)) uid:111 ino:42972 sk:8 <->                 
tcp   LISTEN  0       128    [::]:5432            [::]:*      users:(("postgres",pid=16066,fd=6)) uid:111 ino:42973 sk:9 v6only:1 <->

If you have an active UFW firewall, allow port 5432

sudo ufw allow 5432/tcp

Step 4:  Set PostgreSQL admin user Password

Set a password for the default postgres admin user

$ sudo su - postgres 
postgres@os1:~$ psql -c "alter user postgres with password 'StrongPassword'" 
ALTER ROLE

Step 5:  Test PostgreSQL 11 database functionality

Add a test database user:

createuser test_user1

Add the test database and grant ownership to test_user1:

postgres@ubuntu-01:~$ createdb test_db -O test_user1

Login to test_db database:

~$ psql -l  | grep test_db
 test_db    | test_user1  | LATIN1   | en_US   | en_US |
~$ psql test_db

Set user password:

testdb=# alter user test_user1 with password 'MyDBpassword';
ALTER ROLE

Create a table and add some dummy data:

testdb=# create table test_table ( id int,first_name text, last_name text ); 
CREATE TABLE
testdb=# insert into test_table (id,first_name,last_name) values (1,'John','Doe'); 
INSERT 0 1

Show table data

testdb=# select * from test_table;
 id | first_name | last_name 
----+------------+-----------
  1 | John       | Doe
(1 row)

Drop the test table

testdb=# DROP TABLE test_table;
DROP TABLE
testdb=# \q

Drop the test database

postgres@ubuntu-01:~$ dropdb test_db;

That’s it. You now have PostgreSQL 11 database server installed on Debian 9 / Debian 8.

PostgreSQL Database Video Courses:

• SQL and PostgreSQL: The Complete Developer’s Guide
• The Complete Python/PostgreSQL Course 2.0
• SQL & PostgreSQL for Beginners: Become an SQL Expert
• Learn SQL Using PostgreSQL: From Zero to Hero
• PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours

If you are set for more reading, visit PostgreSQL official Documentation page.