Most Linux platforms such as Debian, Red Hat / CentOS, SUSE, and Ubuntu have PostgreSQL integrated with their package management.

It is recommended that you install PostgreSQL this way since it ensures a proper integration with the operating system including automatic patching and other update management functionality.

Install PostgreSQL on Ubuntu

In this tutorial, we’ll show you how to install PostgreSQL 16 on Ubuntu 22.04.

Step 1. Add PostgreSQL Repository

First, update the package index and install the necessary packages:

sudo apt updatesudo apt install gnupg2 wget

Second, add the PostgreSQL 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'

Third, 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

Finally, update the package list

sudo apt update

Step 2. Install PostgreSQL 16

First, install PostgreSQL and its contrib modules:

sudo apt install postgresql-16 postgresql-contrib-16

Second, start the PostgreSQL service:

sudo systemctl start postgresql

Third, enable PostgreSQL service:

sudo systemctl enable postgresql

Step 3. Configure PostgreSQL server

PostgreSQL stores the configuration in thepostgresql.conf file. You can edit thepostgresql.conf using any text editor such as nano and vim.

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

Set the listen_addresses to * to allow remote connection:

listen_addresses = '*'

Configure PostgreSQL to use md5 password authentication in thepg_hba.conf file. This is necessary if you want to enable remote connections :

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

Restart PostgreSQL for the changes to take effect:

sudo systemctl restart postgresql

Allow PostgreSQL port through the firewall:

sudo ufw allow 5432/tcp

Connect to the PostgreSQL database server

First, connect to the PostgreSQL server using thepostgres user:

sudo -u postgres psql

Second, set a password forpostgres user:

ALTER USER postgres PASSWORD '<password>';

Replace the<password> with the one you want.

Third, quit the psql:

\q

Load the sample database

First, download the sample database using thecurl tool:

curl -O https://neon.com/postgresqltutorial/dvdrental.zip

Second, unzip the dvdrental.zip file to get the dvdrental.tar file:

unzip dvdrental.zip

Third, connect to the PostgreSQL server usingpostgres user:

sudo -u postgres psql

Fourth, create thedvdrental database using theCREATE DATABASE statement:

create database dvdrental;

Fifth, quit thepsql by using the\q command:

\q

Sixth, use thepg_restore tool to restore thedvdrental database:

pg_restore -U postgres --dbname=dvdrental --verbose dvdrental.tar

Seventh, access the PostgreSQL database server again usingpsql:

psql

Eighth, switch to thedvdental database:

\c dvdrental

Finally, enter the following command to get the number of films in thefilm table:

select count(*) from film;

Here is the output:

count-------1000(1 row)

Congratulations! You have successfully installed PostgreSQL on Ubuntu, connected to the PostgreSQL database server using psql, and loaded the sample database.

Last updated onFebruary 19, 2024

On this page

• Install PostgreSQL on Ubuntu
• Connect to the PostgreSQL database server
• Load the sample database