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

This short guide will help you to Install PostgreSQL 11 on Ubuntu 18.04 / Ubuntu 16.04. PostgreSQL Server is a robust open source and highly-extensible database server. PostgreSQL provides object-relational database system allowing you to manage extensive SQL datasets.

Key PostgreSQL 11 Enhancements:

  • Improvements to partitioning functionality
  • SQL stored procedures that support embedded transactions
  • Improvements to parallelism
  • Window functions now support all framing options shown in the SQL:2011 standard
  • Optional Just-in-Time (JIT) compilation for some SQL code, speeding evaluation of expressions
  • Performance improvements, including the ability to avoid a table rewrite for ALTER TABLE … ADD COLUMN with a non-null column default
  • Covering indexes can now be created, using the INCLUDE clause of CREATE INDEX

For CentOS / Fedora, refer to:

How to install PostgreSQL 11 on Fedora 29 / Fedora 28

How to install PostgreSQL 11 on CentOS 7

How to install PostgreSQL 11 on CentOS 8 / RHEL 8

The Release page highlights all the new features available in PostgreSQL 11. Follow the steps provided in the next sections to install PostgreSQL 11 on Ubuntu 18.04 / Ubuntu 16.04.

Step 1: Update system and install dependencies

It is recommended to update your current system packages if it is a new server instance.

sudo apt update && sudo apt -y upgrade
sudo reboot

Once the system is rebooted, install vim and wget if not already installed.

sudo apt install -y wget vim

Step 2: Add PostgreSQL 11 APT repository

Before adding repository content to your Ubuntu 18.04 / Ubuntu 16.04 system, you need to import the repository signing key:

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

After importing GPG key, add repository contents to your Ubuntu 18.04/16.04 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

Verify repository file contents

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

Step 3:  Install PostgreSQL 11 on Ubuntu 18.04 / Ubuntu 16.04

The last installation step is for PostgreSQL 11 packages. Run the following commands to install PostgreSQL 11 on Ubuntu 18.04 / Ubuntu 16.04.

sudo apt update
sudo apt -y install postgresql-11

Step 4: Allow access to PostgreSQL from remote hosts

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 <->

To allow network access, edit configuration file:

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 = '192.168.17.12'

See below screenshot.

Don’t forget to restart postgresql service after making the change

sudo systemctl restart postgresql

Confirm the bind address for PostgreSQL:

$ 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 5: Set PostgreSQL admin user’s password and do testing

Set a password for the default admin user

$ sudo su - postgres
[email protected]:~$ psql -c "alter user postgres with password 'StrongPassword'"
ALTER ROLE

You can also add other database users:

createuser dbuser1

Add test database:

[email protected]:~$ createdb testdb -O dbuser1

Do a test operationg by logging in as a dbuser1 and operating on testdb

~$ psql -l  | grep testdb
 testdb    | dbuser1  | LATIN1   | en_US   | en_US |

Set user password:

$ psql
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.
postgres=# alter user dbuser1 with password 'DBPassword';
ALTER ROLE

Create 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 our test table

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

Drop test database

[email protected]:~$ dropdb testdb;

Step 6: Install Web Management Tool (Optional)

For easy administration, consider installing pgAdmin 4 onto your Ubuntu system.

How to install pgAdmin4 on Ubuntu

You have successfully installed PostgreSQL database server on Ubuntu 18.04 / Ubuntu 16.04 and performed a couple tests.

Related guides:

Install PostgreSQL 11 on Debian

Install PostgreSQL 11 on CentOS 7

Install PostgreSQL 11 on CentOS 8

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