Install PostgreSQL 16 on Ubuntu 22.04

PostgreSQL is a cross-platform relational database that allows to access data using SQL language. PostgreSQL is an open-source project that available under the PostgreSQL License.

This tutorial shows how to install PostgreSQL 16 on Ubuntu 22.04.

Install PostgreSQL

Download GPG key:

sudo wget -qO /etc/apt/trusted.gpg.d/pgdg.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc

Add repository:

echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -sc)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

Update the package lists:

sudo apt update

Run the following command to install PostgreSQL 16:

sudo apt install -y postgresql-16

When installation is completed, we can check PostgreSQL server version:

pg_config --version

The following command can be used to check if PostgreSQL service is running:

sudo service postgresql status

We can also stop, start and restart the service:

sudo service postgresql stop

sudo service postgresql start

sudo service postgresql restart

Configuring PostgreSQL

During PostgreSQL installation, a postgres superuser has been created. By default, postgres superuser doesn't have password and use peer authentication, which only supported on local connections. Peer authentication retrieves the client's OS username and compares it with the allowed database username.

A postgres superuser also can connect by providing a password. Before that, we need to run the following command to set password for postgres superuser:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'pwd123';"

Now try to connect to server using password:

psql -h 127.0.0.1 -U postgres

Type \q to exit interactive mode.

By default, PostgreSQL server listens only for local client connections. Remote access to the PostgreSQL server can be enabled by adding listen_addresses = '*' in the postgresql.conf file. The following command replaces #listen_addresses = 'localhost' line to desired line:

sudo sed -i -e "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" /etc/postgresql/16/main/postgresql.conf

We also we need to execute the following commands to add two lines in the pg_hba.conf file for accepting IPv4 and IPv6 remote connections:

echo 'host all all 0.0.0.0/0 scram-sha-256' | sudo tee -a /etc/postgresql/16/main/pg_hba.conf

echo 'host all all ::/0 scram-sha-256' | sudo tee -a /etc/postgresql/16/main/pg_hba.conf

Restart PostgreSQL service:

sudo service postgresql restart

Now we can connect to the PostgreSQL server from a remote host:

psql -h 192.168.0.48 -U postgres

Uninstall PostgreSQL

Run the following command if you want to completely remove any package with a name that starts with postgresql and anything related to it:

sudo DEBIAN_FRONTEND=noninteractive apt purge --autoremove -y postgresql*

Remove GPG key and repository:

sudo rm -rf /etc/apt/trusted.gpg.d/pgdg.asc

sudo rm -rf /etc/apt/sources.list.d/pgdg.list

Remove PostgreSQL user:

sudo deluser postgres

You can also remove PostgreSQL commands history file:

rm -rf ~/.psql_history