PostgreSQL 16 Installation on Ubuntu 22.04

Overview
PostgreSQL 16 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 16 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 22.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
sudo apt install gnupg2 wget nano

Add the PostgreSQL 16 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 16

Install PostgreSQL 16 and contrib modules:

sudo apt install postgresql-16 postgresql-contrib-16

Start and enable PostgreSQL service:

sudo systemctl start postgresql
sudo systemctl enable postgresql

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

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

Step 3 - Configure PostgreSQL 16

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

sudo nano /etc/postgresql/16/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/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

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 16 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.

Comments

[Nux]

I think there is a typo. It is:
sudo apt install gnupg2 wget vim
Should be:
sudo apt install gnupg2 wget nano
I mean you do use nano, thankfully 😉

[JohnDotOwl]

Updated, Thank you! HAHA Yeah , takes me minutes to exit vim , hehe

[Amjad Abujamous]

In my case, it was sudo apt install gnupg2 wget gedit
GUI for the win.

[Rinx]

I run this to create a root role (to match with my root user on ubuntu 20.04) so I don't have to switch to postgres user to use pg_restore.

sudo -u postgres createuser --interactive
Enter name of role to add: root
Shall the new role be a superuser? (y/n) y

[JohnDotOwl]

I enjoy installing PostgreSQL directly on bare metal servers, without any additional overhead from Docker containers, to get pure bare metal performance.

[Jonas Scholz]

nice post:)

[Evaristo Ramos]

hi sorry for the n00b question, but how do i change the default data directory? as i am looking to install a 1TB db that will exceed my boot/os drive

[JohnDotOwl]

Sorry for the late reply
Edit the postgresql.conf file: Find the data_directory parameter and set it to the new path you want to use. For example:
data_directory = '/new/data/path'

To add on, you can run commands like
df -h Show mounted filesystems along with disk usage
lsblk List all block devices along with information like mountpoints, size, type

[Evaristo Ramos]

yah that didnt quite work for me. I did end up figuring it out though, from virgin install to new custom directory took 27 steps :/

[JohnDotOwl]

Haha , that is one of the reason why i started blogging because i realise i'm going through the same process multiple times these years. Glad you got it figured out!

[ajaofjalskfj]

is sudo ufw allow 5432/tcp a security risk? or listen_addresses = '*'?

[JohnDotOwl]

Nope, for listen_addresses it's to allow remote connection, .e.g your wanna use PGADMIN to connect to DB, you kinda need that, make sure you see a good password

With regards to the firewall, you can always use your VPS's firewall too e.g. DigitalOcean / AWS , almost all service provider will provide firewall for free.