Install PostgreSQL 11 on Ubuntu 22.04/20.04/18.04/16.04

Posted on 270 views

This short guide will help you to Install PostgreSQL 11 on Ubuntu 22.04/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 22.04/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
[ -f /var/run/reboot-required ] && sudo reboot -f

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 22.04/20.04/18.04/16.04 system, you need to import the repository signing key:

curl -fsSL|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

After importing GPG key, add repository contents to your Ubuntu 22.04/20.04/18.04/16.04 system:

echo "deb `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list

Verify repository file contents

$ cat /etc/apt/sources.list.d/pgdg.list
deb bionic-pgdg main

Step 3:  Install PostgreSQL 11 on Ubuntu 22.04/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 22.04/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*      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 = ''

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*      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'"

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.16 (Ubuntu 11.16-1.pgdg22.04+1))
Type "help" for help.
postgres=# alter user dbuser1 with password 'DBPassword';

Create table and add some dummy data:

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

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;

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.

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


Gravatar Image
A systems engineer with excellent skills in systems administration, cloud computing, systems deployment, virtualization, containers, and a certified ethical hacker.