Install PostgreSQL 15 on Ubuntu 22.04 | 20.04

PostgreSQL 15 is out and delivers many performance improvements. The new release improves PostgreSQL’s capabilities in several key areas, notably performance, data compression, and security. In terms of performance, PostgreSQL 15 includes several enhancements that should help to improve the database’s overall speed and responsiveness.

Some of the highlights of the release are as follows.

• Improved Sort Performance and Compression.
• Expressive Developer Features.
• More Options with Logical Replication.
• Logging and Configuration Enhancements.

The following tutorial will demonstrate how to install the software on Ubuntu 22.04 Jammy Jellyfish and 20.04 Focal Fossa Linux using the command line terminal and the PostgreSQL APT repository.

Recommended Steps Before Installation

Before proceeding with the tutorial, ensuring your system is up-to-date with all existing packages is good.

sudo apt update

Optionally, you can list the updates for users who require review or are curious.

sudo apt --list upgradable

Proceed to upgrade any outdated packages using the following command.

sudo apt upgrade

Install Required Packages

The following packages must be installed to assist in installing the database software.

sudo apt install dirmngr ca-certificates software-properties-common gnupg gnupg2 apt-transport-https curl -y

Import PostgreSQL Repository

First, you will need to import the PostgreSQL GPG key to verify the authenticity of the installation package.

curl -fSsL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql.gpg > /dev/null

Next, you can import the stable or testing repository, depending on your desired installation. Use the stable repository for production, as it’s often updated quickly since you are installing directly from PostgreSQL repositories. The testing is for testing, as sometimes it can be behind or ahead, which is unsuitable for production.

Remember, import the version that matches your distribution LTS version; if you grab the wrong one, your installation will likely fail.

Import stable build (Recommended)

Import Jammy Jellyfish 22.04 stable repository:

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main | sudo tee -a /etc/apt/sources.list.d/postgresql.list

Import Focal Fossa 20.04 stable repository:

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main | sudo tee -a /etc/apt/sources.list.d/postgresql.list

Import snapshot build

Import Jammy Jellyfish 22.04 snapshot repository:

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg-snapshot main | sudo tee -a /etc/apt/sources.list.d/postgresql-testing.list

Import Focal Fossa 20.04 snapshot repository:

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg-snapshot main | sudo tee -a /etc/apt/sources.list.d/postgresql-testing.list

Import testing build (Caution)

Import Jammy Jellyfish 22.04 testing repository:

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg-testing main | sudo tee /etc/apt/sources.list.d/postgresql-testing.list

Import Focal Fossa 20.04 testing repository:

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg-testing main | sudo tee /etc/apt/sources.list.d/postgresql-testing.list

Install PostgreSQL 15

Now that you have imported the stable or testing repository of PostgreSQL, you need to update your repository sources list to reflect the new addition with the following command.

sudo apt-get update

Now proceed to install PostgresSQL as follows.

sudo apt install postgresql-client-15 postgresql-15 -y

Alternatively, you can use just the non-specific install command. This is useful for users that install the snapshot or testing repositories, where future versions will always be prioritized, such as PostgreSQL 16, etc.

sudo apt install postgresql-client postgresql -y

Next, verify the status to ensure the software is installed and activated without errors using the following command.

systemctl status postgresql

Example output:

By default, this should be enabled during the installation. If PostgreSQL is not activated, use the following command to enable it.

sudo systemctl enable postgresql --now

Example Service Commands

The PostgreSQL database server runs as a service under the name “PostgreSQL,” which can be managed with the systemd with the following command examples.

Stop PostgreSQL server

sudo systemctl stop postgresql

Start PostgreSQL server

sudo systemctl start postgresql

Restart PostgreSQL server

sudo systemctl restart postgresql

Reload PostgreSQL server

sudo systemctl reload postgresql

Check PostgreSQL status

systemctl status postgresql

Configure the PostgreSQL Server

Switching to the Postgres account

Before you continue, note that only superusers and roles with create role privilege can create new roles in Postgres. Postgres’s user account associated with the default Postgres role was created during the installation.

To engage with the account, you can do the following commands:

sudo -i -u postgres

By entering the above, you can immediately access the PostgreSQL prompt by typing “psql”.

psql

Next, you will notice that the terminal has changed with “postgres=#” now being in the terminal command line. This means you have successfully connected to the database.

To exit the Postgres database, you can do this by typing the following:

exit

Alternative to switching Postgres account

An alternative way to interact with the Postgres database without changing user accounts is to use a sudo command to connect directly. You can do this by typing:

sudo -u postgres psql

This is ideal to use more, as it saves time logging you directly without having to do extra terminal bash commands.

As the first option, you can exit by typing the following to exit the terminal.

exit

Create User & Database

Now you can create user roles if you wish. Only superusers and roles with “createrole” privilege can create new roles.

Create a new user; type the following.

sudo su - postgres -c "createuser <name>"

Now you can create a PostgreSQL database for the new user you created:

sudo su - postgres -c "createdb <namedb>"

To finish off, you need to switch to the superuser Postgres account to grant permission to the new database.

Connect to Postgres superuser account

sudo -u postgres psql

Grant access to <username database> to <username>

GRANT ALL PRIVILEGES ON DATABASE <usernamedb> TO <name>;

Example in your terminal output:

psql (15.0 (Ubuntu 15.0-1.pgdg22.04+1))
Type "help" for help.

postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO josh;
GRANT
postgres=# 

Once done, to exit, type the following

exit

Configure UFW Firewall

You will need to create a UFW firewall rule to allow PostgreSQL to communicate on its default port, 5432, which is required if you plan to use anything besides localhost.

First, ensure UFW is enabled.

sudo ufw enable

This can be done by typing the following command.

Subnet range:

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

Individual IP:

sudo ufw allow proto tcp from 192.168.1.0 to any port 5432

How to Enable Remote Access

If you require default access to PostgreSQL, this can be quickly done by changing the default listening to the local interface of (127.0.0.1) to an IP, subnet, or all interfaces in the configuration file.

Remember to ensure that UFW has been set up correctly and does not block remote access as per the previous section.

First, determine which version of Postgresql you are using the ls command.

ls /etc/postgresql/

The tutorial has one of the latest versions installed, which is 15. This opens up the “postgresql.conf” file using the nano text editor.

sudo nano /etc/postgresql/15/main/postgresql.conf

Now scroll down until you find “Connection Settings” and change the (listen_addresses = ‘localhost’) to the <IP> address. Most would opt to listen to all interfaces, especially when running in multiple servers requiring connecting to the PostgreSQL database by changing localhost to (*).

Example:

Save the file (CTRL+O), exit (CTRL+X), and restart your PostgreSQL instance.

sudo systemctl restart postgresql

Users can do this with the ss utility built into Ubuntu to check that your changes are live.

ss -nlt | grep 5432

For further customization and securing PostgreSQL, you can configure the server to accept remote connections by editing the “pg_hba.conf” file using the nano text editor.

sudo nano /etc/postgresql/15/main/pg_hba.conf

You can set up various options from this point, such as changing “all” to a specific username and database, along with the address and authentication methods.