How to Install PostgreSQL 15 on Ubuntu 24.04, 22.04, or 20.04

PostgreSQL 15 is a robust, open-source relational database management system that continues the tradition of delivering advanced features, reliability, and high performance. Known for its SQL compliance and extensibility, PostgreSQL 15 includes enhancements that improve the handling of complex queries, better management of partitioned tables, and further advancements in logical replication and backup processes. These features make PostgreSQL 15 suitable for a wide range of applications, from small development projects to large-scale enterprise databases.

On Ubuntu 24.04, 22.04, or 20.04, PostgreSQL 15 can be installed via the official PostgreSQL APT repository mirror. This method ensures that you receive the latest updates and security patches directly from the PostgreSQL Global Development Group, keeping your database system secure and up-to-date. This guide will walk you through the process of adding the PostgreSQL repository to your Ubuntu system and installing PostgreSQL 15.

Import PostgreSQL APT Repository

Update Ubuntu Before PostgreSQL Installation

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

sudo apt update

Proceed to upgrade any outdated packages using the following command.

sudo apt upgrade

Install Initial Packages for PostgreSQL 15

To assist in installing the database software, install the following packages:

sudo apt install dirmngr ca-certificates software-properties-common apt-transport-https lsb-release curl -y

Import PostgreSQL 15 APT 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, import the stable, snapshot, or testing repository based on your installation preference. Opt for the stable repository for production due to its frequent updates from direct PostgreSQL repository installations. The testing repository, which is unsuitable for production, might lag or lead to updates.

Import PostgreSQL 15 stable APT repository

For most circumstances, import the PostgreSQL 15 stable repository below, as its title suggests it contains the latest secure, working, and stable version.

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

Import PostgreSQL 15 Snapshot or Testing APT Repositories

Next, install the PostgreSQL 15 snapshot or testing repositories exclusively for testing purposes, avoiding use on a live server.

Import PostgreSQL 15 Snapshot repository, use the following command:

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

To import the testing repository that contains the latest packages (also known as a nightly repository), use the following command:

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

Finalize PostgreSQL 15 Installation via APT Command

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 using the following command:

sudo apt update

Now proceed to install PostgreSQL with the following command:

sudo apt install postgresql-client-15 postgresql-15

Next, verify the software’s status to ensure installation and activation occurred without errors using the following command:

systemctl status postgresql

If PostgreSQL is inactive, use the following command to activate it immediately and on system boot:

sudo systemctl enable postgresql --now

PostgreSQL Service Commands

The “PostgreSQL” service runs the PostgreSQL database server, and you can manage it using the systemd with the commands provided below.

Stop PostgreSQL 15 server:

sudo systemctl stop postgresql

Start PostgreSQL 15 server:

sudo systemctl start postgresql

Restart the PostgreSQL 15 server:

sudo systemctl restart postgresql

Reload PostgreSQL 15 server:

sudo systemctl reload postgresql

Check PostgreSQL 15 status:

systemctl status postgresql

Examples of Configuring PostgreSQL 15

Switch to the Postgres account

Only superusers and those with role privilege can create new roles in Postgres. The installation process creates the default Postgres role and its associated user account.

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 on 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 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 a New PostgreSQL User

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

Create New PostgreSQL Database

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 For PostgreSQL 15

Create a UFW firewall rule to allow PostgreSQL communication on its default port, 5432, which is necessary for using anything other than localhost. First, ensure installation and activation of UFW:

sudo apt install ufw
sudo ufw enable

Next, as an example, you can either allow a subnet or individual IP addresses:

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

Remember to modify the addresses to fit your requirements; the examples provided here serve as a starting point for securing your PostgreSQL installation. You’ll likely need to refine further, especially when exposing the software to the public.

Enable Remote Access with PostgreSQL

To gain default access to PostgreSQL, quickly change the default listening from the local interface of (127.0.0.1) to a specific IP, subnet, or all interfaces in the configuration file.

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

ls /etc/postgresql/

If you’ve installed other versions of PostgreSQL, you’ll notice the folder contains these versions. This guide will walk you through opening the postgresql.conf for PostgreSQL 15 using the following command:

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

Scroll down to “Connection Settings” and change (listen_addresses = ‘localhost’) to the <IP> address. To allow connection to the PostgreSQL database from multiple servers, change localhost to (*), especially if running on multiple servers.

Save the file using CTRL+O, exit with 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 15, 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, address, and authentication methods.

Conclusion

Installing PostgreSQL 15 on Ubuntu via the official APT repository mirror provides a reliable and straightforward way to access the latest features and updates offered by PostgreSQL. By using the official repository, you ensure that your PostgreSQL installation remains secure, up-to-date, and fully capable of handling complex database operations efficiently. Whether you’re developing new applications or managing existing databases, PostgreSQL 15 offers the tools and performance enhancements needed to meet modern database demands on Ubuntu.

Useful Links

Here are some useful links related to using PostgreSQL 15:

• PostgreSQL 15 Release Notes: Read the detailed release notes for PostgreSQL 15 to learn about new features, enhancements, and bug fixes.
• PostgreSQL 15 Release Announcement: Check out the official announcement for the release of PostgreSQL 15.
• PostgreSQL Official Website: Visit the official PostgreSQL website for general information about the database system, features, and download options.
• PostgreSQL Documentation: Access comprehensive documentation for detailed guides on installing, configuring, and using PostgreSQL.
• PostgreSQL Community: Join the PostgreSQL community to connect with other users, participate in discussions, and find support.
• PostgreSQL GitHub Repository: Explore the PostgreSQL GitHub repository to view the source code, report issues, and contribute to the development.