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

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.

1. Pull latest system updates

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

2. Add PostgreSQL 11 APT repository

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

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

After importing GPG key, add repository contents to your Ubuntu

echo "deb http://apt.postgresql.org/pub/repos/apt/ `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 http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main

3. Install PostgreSQL 11 on Ubuntu

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

sudo apt update
sudo apt -y install postgresql-11

4. Allow remote access to PostgreSQL

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

5. Set PostgreSQL admin user’s password

Set a password for the default admin user

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

You can also add other database users:

createuser dbuser1

Add test database:

postgres@ubuntu-01:~$ 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.22 (Ubuntu 11.22-1.pgdg22.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

postgres@ubuntu-01:~$ dropdb testdb;

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 22.04/20.04/18.04 /16.04 and performed a couple tests.

Recommended Web Development books to read:

• Best Books to learn Web Development – PHP, HTML, CSS, JavaScript and jQuery
• Best Books To Master Web Design
• Best Books To Learn CSS & CSS3
• Best Books To Learn HTML & HTML5
• Best Apache and Nginx reference Books

Related guides:

Install PostgreSQL 11 on Debian

Install PostgreSQL 11 on CentOS 7

Install PostgreSQL 11 on CentOS 8