Install PostgreSQL 11 on Ubuntu 20.04/18.04/16.04

This short guide will help you to Install PostgreSQL 11 on Ubuntu 20.04/18.04/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

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 20.04/18.04/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 20.04/18.04/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 20.04/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 20.04/18.04/16.04

The last installation step is for PostgreSQL 11 packages. Run the following commands to install PostgreSQL 11 on Ubuntu 20.04/18.04/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.

Best Video courses to Learn PostgreSQL Database:

• 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

Related guides:

Install PostgreSQL 11 on Debian

Install PostgreSQL 11 on CentOS 7

Install PostgreSQL 11 on CentOS 8