How to Install PostgreSQL 16 on Ubuntu 22.04

In this tutorial, we’ll cover how to install PostgreSQL 16 on Ubuntu 22.04. In addition, we’ll examine some fundamental setups to enable remote connections, turn on password authentication, and start creating users and databases.

PostgreSQL is an open-source relational database management system (RDBMS) with more than 35 years of continuous development. It is renowned for its scalability, high reputation for data integrity, optimum performance, and adherence to SQL standards.

Since its initial development at the University of California, Berkeley, in 1986, it has undergone significant changes thanks to contributions from an active open-source community.

PostgreSQL announced another significant release on September 14, 2023; PostgreSQL 16.  This latest release comes with enhanced security, extra server configuration options, and optimum performance. It also includes improvements in access policy management, monitoring SQL/JSON syntax, and others.

Prerequisites

  • An active and running Ubuntu 22.04
  • Root privileges or sudo access

1. Add the PostgreSQL Repository

We’ll begin by updating the software repository and installing the necessary packages using apt command.

$ sudo apt update
$ sudo apt install gnupg2 wget vim -y

Install-PostgreSQL-Dependecies-Ubuntu-22-04

Now, run beneath command to add postgresql apt repository.

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

Next, import the repository signing key, run below wget command.

$ sudo wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update the package index again:

$ sudo apt update

2. Install PostgreSQL 16 on Ubuntu 22.04

To install  PostgreSQL16 and contrib modules, run the following command:

$ sudo apt install postgresql-16 postgresql-contrib-16

Install PostgreSQL 16 on Ubuntu 22.04

Next, start and enable the PostgreSQL service:

$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql

Confirm if the PostgreSQL service is up and running:

$ sudo systemctl status postgresql

Check-PostgreSQL-Service-Status-Ubuntu-22-04

To check the PostgreSQL version, execute the command as shown below:

$ sudo psql --version

Psql-Version-Check-Ubuntu

3. Configure PostgreSQL 16 on Ubuntu 22.04

You can now continue and set up PostgreSQL. PostgreSQL has multiple authentication mechanisms. Ident authentication is the default mechanism. By using this default authentication method, the Postgres role is linked to the UNIX user.

The other authentication mechanisms include:

  • Password authentication: Uses a password to establish a connection to PostgreSQL.
  • Trust authentication:  This authentication method enables a role to connect, provided that the requirements listed in the pg_hba.conf are fulfilled.
  • Peer authentication:  This authentication method is similar to ident; the difference is that it is only limited to local connections.

Let’s start by configuring PostgreSQL 16 to allow remote connections. To accomplish this, edit the file below:

$ sudo vim /etc/postgresql/16/main/postgresql.conf

Here, set the listen_addresses to * and remove the comment as shown below:

Allow-Remote-Connections-PostgreSQL-Ubuntu-22-04

After making the necessary adjustments, save the file.

Proceed and use the following sed command to permit password authentication:

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

Now use the following command to switch the authentication mechanism from peer to trust:

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

Finish the configuration by adding the following lines to enable remote access to PostgreSQL:

$ sudo vim /etc/postgresql/16/main/pg_hba.conf

Allow-Remote-Access-PostgreSQL-Database-Ubuntu

Save the modifications and restart the PostgreSQL service:

$ sudo systemctl restart postgresql

If your firewall is enabled, allow the PostgreSQL port to pass through it:

$ sudo ufw allow 5432/tcp

Allow-PostgreSQL-Port-Ubuntu-Firewall

4. Connect to PostgreSQL

To connect to this database engine, run the following command to connect as the Postgres user:

$ sudo -u postgres psql

Connect-PostgreSQL-Database-Ubuntu

5. Get started with PostgreSQL 16

There are various activities you can carry out once you’ve established a connection to the database. Among them are:

Configuring the Postgres user’s password

To set the password for the Postgres user, run the following command:

ALTER USER postgres PASSWORD 'StronGlinuxbuzz@254';

To test if the password is working, run the following command and enter the password you created:

$ psql -h localhost -U postgres

Access-PostgreSQL-Database-with-Postgres-User-Ubuntu

Creating a PostgreSQL Database

Use the following command to create a test database:

CREATE DATABASE sampleDB;

In addition, you use the following command to create a user with permission to manage the database:

CREATE USER pradeep with encrypted password 'deza@1123';
GRANT ALL PRIVILEGES ON DATABASE sampleDB to pradeep;

Create-Database-in-PostgreSQL-Ubuntu-22-04

To exit from the PostgreSQL database, you can use this command \q.

Conclusion

That’s it! We have successfully installed PostgreSQL 16 on Ubuntu 22.04. With PostgreSQL, you can now create sophisticated, reliable databases for your applications.

Leave a Comment

eight − 6 =