PostgreSQL is an open-source object-relational database management system (ORDBMS) based on POSTGRES, Version 4.2. Postgresql was developed at the University of California at Berkeley Computer Science Department. PostgreSQL supports a large part of SQL but has many improved features.

On November 21, 2024, PostgreSQL 17 was released. The new Features in PostgreSQL 17 include the following:

• New memory management system for VACUUM, which reduces memory consumption and can improve overall vacuuming performance.
• New SQL/JSON capabilities, including constructors, identity functions, and the JSON_TABLE() function, which converts JSON data into a table representation.
• Various query performance improvements, including for sequential reads using streaming I/O, write throughput under high concurrency, and searches over multiple values in a btree index.
• Logical replication enhancements, including:
  • Failover control
  • pg_createsubscriber, a utility that creates logical replicas from physical standbys
  • pg_upgrade now preserves logical replication slots on publishers and full subscription state on subscribers. This will allow upgrades to future major versions to continue logical replication without requiring copy to resynchronize.
• New client-side connection option, sslnegotiation=direct, that performs a direct TLS handshake to avoid a round-trip negotiation.
• pg_basebackup now supports incremental backup.
• COPY adds a new option, ON_ERROR ignore, that allows a copy operation to continue in the event of an error.

For more features, please visit the PostgreSQL 17 Features Documentation.

How To Install PostgreSQL 17 on Ubuntu 24.04 / 22.04

With the brief introduction, we will now install PostgreSQL 17 on Ubuntu 24.04 / 22.04. Ensure you have the following requirements in place before proceeding.

• Ensure Ubuntu 24.04|22.04 is installed on your machine.
• A user with sudo privileges.
• Stable Internet connection.
• At least 2 GB of RAM with an additional 512 MB of disk space.

Begin the installation process by updating your system APT index to refresh the system packages. Run the command below.

sudo apt update && sudo apt dist-upgrade -y

Once the packages are updated begin the installation process.

Ensure the following packages are installed as well.

sudo apt install gnupg2 wget vim -y

Step 1: Enable PostgreSQL 17 Package Repository

By default, the PostgreSQL packages exist in Ubuntu 24.04/22.04. These are however not the latest versions. To check the available PostgreSQL packages in your system, issue the following command.

sudo apt-cache search postgresql | grep postgresql

Since PostgreSQL 17 is not available in the default package repository, we will begin installation by enabling its official package repository by running the following command.

 sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Next import the GPG signing key for the repository. Do this by running the following command.

wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null

Finally, update the system packages so that the changes above can take effect.

sudo apt update -y

 Step 2: Install PostgreSQL 17 Database Server and Client

PostgreSQL 17 can be installed from the Binary packages or Built from the source. The Binaries installation method is the recommended method. Building from source is recommended for developers developing PostgreSQL or extensions.

In the previous step, we prepared our system for PostgreSQL 17 installation. We will now install the PostgreSQL 17 Database server and Client.

Execute the following command:

sudo apt install postgresql-17 postgresql-client-17 -y

Once the installation is complete, Enable and start the PostgreSQL services on Ubuntu 24.04 / 22.04

sudo systemctl enable postgresql
sudo systemctl start postgresql

Check the status:

$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
     Active: active (exited) since Fri 2025-01-17 19:05:42 UTC; 23s ago
   Main PID: 34888 (code=exited, status=0/SUCCESS)
        CPU: 1ms

Jan 17 19:05:42 ubuntu-noble systemd[1]: Starting postgresql.service - PostgreSQL RDBMS...
Jan 17 19:05:42 ubuntu-noble systemd[1]: Finished postgresql.service - PostgreSQL RDBMS.

Verify the Version installed:

sudo -u postgres psql -c "SELECT version();"

The sample output will look like this.

                                                              version                                                              
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.2 (Ubuntu 17.2-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
(1 row)

Another way to check the version installed:

$ psql --version
psql (PostgreSQL) 17.2 (Ubuntu 17.2-1.pgdg24.04+1)

From the output, PostgreSQL 17 has been installed in our system.

Step 3: Connect to PostgreSQL 17 database

To connect to a PostgreSQL instance of a Database, you can employ two ways:-

1 ) Running the Postgres command directly with the sudo command:

$ sudo -u postgres psql
psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1))
Type "help" for help.

postgres=# 

2 ) Switching to the default Postgres user.

$ sudo -i -u postgres

Now connect to the instance:

$ psql
psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1))
Type "help" for help.

postgres=# 

In both ways, no password is required. This should not be allowed as it exposes your database to serious vulnerabilities.

Step 4: Secure your PostgreSQL 17 Database with a password.

To administer your database without a password is a very bad practice. You need to ensure that an extra layer of security is added to your database. You need to set a password for your database. Run the following command:

postgres=# ALTER USER postgres PASSWORD 'Str0ngP@ssw0rd';
ALTER ROLE
postgres=#

For a Production environment, set a very strong password. To Verify that the password has been set successfully, terminate your current session then try to log in.

postgres=# \q

Now connect to the database server.

$ psql -h localhost -U postgres
Password for user postgres: 
psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

postgres=# 

The password is successfully set.

Step 5: Configure PostgreSQL 17 instance for Remote Access.

One of the cool features of PostgreSQL 17 is its ability to support multiple authentication methods. PostgreSQL uses roles to handle client authentication. The default authentication method for Postgres is ident authentication which associates the Postgres role to the UNIX system user.

The ident authentication method works by obtaining the client’s operating system user name from an ident server and using it as the allowed database user name (with an optional user name mapping). This method is only supported on TCP/IP connections.

All supported authentication methods supported are:

• Ident – supported via TCP/IP connections only. It obtains the client’s system username with an optional username mapping.
• Password – Connects using a password.
• Peer – Similar to ident but only supported on local connections.
• Trust – allows a role to connect as long as conditions defined in the pg_hba.conf are fulfilled.

By default, PostgreSQL will accept connections from the local host. To allow remote connections, we must make some modifications to the configuration files.

The default PostgreSQL configuration file is located on /etc/postgresql/17/main/postgresql.conf. This is the file that we will modify to allow us to configure remote access.

Using the text editor, edit the listen_addresses from localhost to  ‘*’ as shown below:

$ sudo vim /etc/postgresql/17/main/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all

Make sure you enable the listen_addresses by removing the #. Save your configurations and exit from the file.

Allow password authentication on your PostgreSQL server by running the commands below.

sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/17/main/pg_hba.conf

Next is to change the identification method from peer to trust as shown in the command below.

sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/17/main/pg_hba.conf

Next, edit the file /etc/postgresql/17/main/pg_hba.conf. To allow the instance to be accessed from everywhere. Here you need to allow IPv4 connections from all clients.

$ sudo vim /etc/postgresql/17/main/pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0           scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0                md5

Save and exit from your editor.

Now restart and enable PostgreSQL for the changes to take effect. Execute the commands below.

sudo systemctl restart postgresql
sudo systemctl enable postgresql

Step 6: Configure Firewall settings.

If your server is running behind a firewall, then you need to configure your firewall to enable communication with the outside world. Begin by checking the port at which PostgreSQL is listening. By default, PostgreSQL will listen on port 5432

$ ss -tunelp | grep 5432
tcp   LISTEN 0      200                 0.0.0.0:5432      0.0.0.0:*    users:(("postgres",pid=36930,fd=6)) uid:110 ino:72231 sk:1001 cgroup:/system.slice/system-postgresql.slice/[email protected] <->           
tcp   LISTEN 0      200                    [::]:5432         [::]:*    users:(("postgres",pid=36930,fd=7)) uid:110 ino:72232 sk:1005 cgroup:/system.slice/system-postgresql.slice/[email protected] v6only:1 <->  

We will need to allow port 5432 through the firewall.

$ sudo ufw allow 5432/tcp
Rules updated
Rules updated (v6)

Step 7: Managing users in PostgreSQL 17

Let me briefly demonstrate how you manage users in POstgreSQL 17.

Connect to the Postgres instance using the psql command.

$ sudo -i -u postgres 
postgres@ubuntu-noble:~$ psql
psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1))
Type "help" for help.

postgres=# 

Create a superuser admin and his password Passw0rd

CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd';

Verify that the superuser has been created with the appropriate privileges.

postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 admin     | Superuser, Create role, Create DB
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=# 

Create a sample Database.

CREATE DATABASE sampleDB;

Create a user with the name demo_user and assign it access to sampleDB

CREATE ROLE cloudspinx WITH LOGIN PASSWORD 'PassW0rd';

Grant all privileges on sampleDB to demo_user

GRANT ALL PRIVILEGES ON DATABASE sampleDB to cloudspinx;

Step 8: Connecting to remote PostgreSQL 17 instance

Now, let’s try to access DB from a remote client. To do this we use the following syntax.

psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'

For demonstration purposes, I will use my Oracle Linux 9 client. Run the command as shown below.

psql 'postgres://admin:[email protected]:5432/postgres?sslmode=disable' 

Sample Output:

Alternatively, you could run the command as shown below.

╰─ psql -h 192.168.1.179 -U postgres
Password for user postgres: 
psql (17.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

postgres=# 

Bingo, we are able to access the PostgreSQL database from the remote client.

Wrapping up

In our guide today, we have looked at how to install PostgreSQL 17 on Ubuntu 24.04 / 22.04. We also briefly analyzed the new features associated with PostgreSQL 17. We also demonstrated shown to secure your database with a password, how to manage users and how to connect to your database from a remote client. I hope the guide was phenomenal.

If the guide helps you, please consider buying us a coffee.

More guides.

• Install and Configure PostgreSQL 17 on FreeBSD 14
• How To Install PostgreSQL 17 on Debian 12 / Debian 11
• How To Install PostgreSQL 16 on CentOS 9 / RHEL 9