PostgreSQL 17 Installation on Ubuntu 24.04

Overview
PostgreSQL 17 is the latest major release of the popular open source relational database. It comes with many new features and improvements such as enhanced monitoring capabilities, improved performance, logical replication enhancements, additional server configurations, and security advancements.

In this tutorial, we will cover how to install PostgreSQL 17 on Ubuntu 22.04 We will also look at some basic configuration to allow remote connections, enable password authentication, and get started with creating users, databases etc.

Prerequisites
Ubuntu 24.04
Root privileges or sudo access
use sudo su to get into root instead of ubuntu(default user)

Step 1 - Add PostgreSQL Repository

First, update the package index and install required packages:

sudo apt update

Add the PostgreSQL 17 repository:

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

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

Update the package list:

sudo apt update

Step 2 - Install PostgreSQL 17

Install PostgreSQL 17 and contrib modules:

sudo apt install postgresql-17

Start and enable PostgreSQL service:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Check the version and ensure it's Postgresql 17:
psql --version
You should get something like

psql (PostgreSQL) 17.0 (Ubuntu 17.0-1.pgdg24.04+1)

Step 3 - Configure PostgreSQL 17

Edit postgresql.conf to allow remote connections by changing listen_addresses to *:

sudo nano /etc/postgresql/17/main/postgresql.conf
listen_addresses = '*'

Configure PostgreSQL to use md5 password authentication by editing pg_hba.conf , this is important if you wish to connect remotely e.g. via PGADMIN :

sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/17/main/pg_hba.conf
sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/17/main/pg_hba.conf
echo "host all all 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/17/main/pg_hba.conf

Restart PostgreSQL for changes to take effect:

sudo systemctl restart postgresql

Allow PostgreSQL port through the firewall:

sudo ufw allow 5432/tcp

Step 4 - Connect to PostgreSQL

Connect as the postgres user:

sudo -u postgres psql

Set a password for postgres user:

ALTER USER postgres PASSWORD 'VeryStronGPassWord@1137';

Conclusion
We have successfully installed PostgreSQL 17 on Ubuntu, performed some basic configuration like enabling remote connections, set up password authentication, created a database and users. PostgreSQL is now ready to be used for development or production workloads.

PostgreSQL 17 has significant improvements and is highly recommended as an upgrade.

• Vacuum memory optimization: Up to 20x less memory consumption
• Write throughput: Up to 2x better for high concurrency workloads
• Query performance: Improved for IN clauses using B-tree indexes
• JSON support: Added JSON_TABLE, JSON constructors, and query functions
• COPY command: Up to 2x faster when exporting large rows
• Logical replication: No need to drop slots during major version upgrades
• Backups: New incremental backup support in pg_basebackup
• Monitoring: Added progress reporting for index vacuuming
• I/O improvements: New streaming I/O interface for faster sequential scans
• MERGE enhancements: Added RETURNING clause and ability to update views