How to Install PostgreSQL 16 on Ubuntu 22.04

Introduction

Before we begin talking about how to install PostgreSQL 16 on Ubuntu 22.04, let's briefly understand – What is PostgreSQL 16?

PostgreSQL 16 is the latest version of the popular open-source database management system. It offers enhanced performance, scalability, and security features for managing large volumes of data effectively. Key improvements include increased parallelism for faster query processing, improved indexing for optimized data retrieval, and better partitioning capabilities for efficient data organization.

With its advanced JSON support, PostgreSQL 16 enables developers to work with complex data structures effortlessly. Furthermore, it provides enhanced security measures to protect sensitive data, such as the ability to define and enforce column-level security policies. PostgreSQL 16 is the go-to choice for organizations seeking a robust and reliable database solution for their applications and data management needs.

In this tutorial, you will install PostgreSQL 16 on Ubuntu 22.04. We will also address a few FAQs on how to install PostgreSQL 16 on Ubuntu 22.04.

Advantages of PostgreSQL 16

Enhanced performance: PostgreSQL 16 delivers faster query processing and indexing, ensuring efficient data retrieval.
Scalability: With improved partitioning capabilities, it efficiently handles large volumes of data.
Advanced JSON support: Developers can easily work with complex data structures.
Robust security: PostgreSQL 16 offers advanced security measures, including column-level security policies.
Reliability: Organizations trust PostgreSQL 16 for its stability and dependability in managing their data and applications.

Prerequisites

Ubuntu 22.04
sudo access or root rights
To access root instead of Ubuntu (the default user), use sudo.

Step 1- Add PostgreSQL Repository

Install the necessary packages and update the package index first:

sudo apt update
sudo apt install gnupg2

Add the repository for PostgreSQL 16:

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 signing key for the repository:

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

Make changes to the package list:

sudo apt update

Step 2- Install PostgreSQL 16

Install the contrib modules and PostgreSQL 16:

sudo apt install postgresql-16 postgresql-contrib-16

Start and enable PostgreSQL service:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Verify the version to make sure Postgresql 16 is used:

psql --version

Output

psql (PostgreSQL) 16.0 (Ubuntu 16.0-1.pgdg22.04+1)

Step 3- Configure PostgreSQL 16

To enable remote connections, edit postgresql.conf and set listen_addresses to *:

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

Edit pg_hba.conf to set PostgreSQL to use md5 password authentication; this is necessary if you want to connect remotely, such as through PGADMIN:

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

For the changes to take effect, restart PostgreSQL:

sudo systemctl restart postgresql

Allow PostgreSQL port to pass through firewall:

sudo ufw allow 5432/tcp

Step 4- Connect to PostgreSQL

As the postgres user, log in:

sudo -u postgres psql

For the postgres user, create a password:

ALTER USER postgres PASSWORD 'VeryStronGPassWord@1137';

FAQs to Install PostgreSQL 16 on Ubuntu 22.04

What is the default username and password for PostgreSQL 16 on Ubuntu?

The default username for PostgreSQL 16 on Ubuntu is "postgres". The password is set during the installation process.

How can I change the password for the "postgres" user?

You can change the password for the "postgres" user by accessing the PostgreSQL command-line interface and using the appropriate SQL command to alter the password.

What is the default port used by PostgreSQL 16 on Ubuntu?

The default port used by PostgreSQL 16 on Ubuntu is port number 5432. This port is where PostgreSQL listens for client connections.

Can I have multiple PostgreSQL versions installed on Ubuntu 22.04?

Yes, it is possible to have multiple PostgreSQL versions installed on Ubuntu 22.04. However, you need to ensure that they are configured to use different ports and avoid conflicts.

How can I create a new database in PostgreSQL 16?

To create a new database in PostgreSQL 16, you can use the SQL command "CREATE DATABASE" followed by the desired name for your new database.

Can I migrate my data from a different database to PostgreSQL 16 on Ubuntu?

Yes, PostgreSQL 16 provides various tools and utilities like pg_dump, pg_restore, and foreign data wrappers to facilitate data migration from other databases to PostgreSQL.

Can I encrypt my data stored in PostgreSQL 16 on Ubuntu?

Yes, PostgreSQL 16 provides support for data encryption, both at rest and during transit. You can encrypt the data using SSL/TLS connections and implement encryption mechanisms for added security.