You can support us by downloading this article as PDF from the Link below. Download the guide as PDF

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 29 / Fedora 28

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 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 
[email protected]:~$ 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:

[email protected]:~$ 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

[email protected]:~$ dropdb test_db;

That’s it. You now have PostgreSQL 11 database server installed on Debian 9 / Debian 8. If you are set for more reading, visit PostgreSQL official Documentation page.

You can support us by downloading this article as PDF from the Link below. Download the guide as PDF