Install PostgreSQL on Ubuntu / Debian

Open source SQL relational database, robust and standards-compliant.

Databasesqlrdbmsrelationalaciddatabase

Installation

Server (Ubuntu repositories) · apt

sudo apt update
sudo apt install -y postgresql postgresql-contrib

postgresql-contrib provides useful extensions (pgcrypto, pg_stat_statements, etc.).

Verify the installation

Verify

$ psql --version

Service

Start

$ sudo systemctl start postgresql

Stop

$ sudo systemctl stop postgresql

Status

$ sudo systemctl status postgresql

Restart

$ sudo systemctl restart postgresql

On boot

$ sudo systemctl enable postgresql

Important files

Type	Path	Description
data	/var/lib/postgresql/<version>/main	Data directory of the main cluster (replace <version>, e.g. 16).
config	/etc/postgresql/<version>/main/postgresql.conf	Server configuration (Debian/Ubuntu keep the config separate under /etc).
config	/etc/postgresql/<version>/main/pg_hba.conf	Client authentication rules.
log	/var/log/postgresql/	Server logs.

Default ports

5432

Command-line tools

psqlInteractive SQL client.
pg_dumpLogical backup of a database.
pg_restoreRestore from a custom-format backup.
createdbQuickly create a database.
pg_ctlclusterMulti-cluster management specific to Debian/Ubuntu.

Uninstall

sudo apt purge -y 'postgresql*'
sudo apt autoremove -y

The data directory may remain; delete /var/lib/postgresql manually if you want to wipe everything.

Good to know

Debian and Ubuntu use a multi-cluster system: config in /etc/postgresql, data in /var/lib/postgresql. A "main" cluster is created automatically.
For a newer version than the repositories provide, add the official PGDG repository (apt.postgresql.org).

Installing on Ubuntu / Debian

The postgresql and postgresql-contrib packages come from the distribution's official repositories. The installation automatically creates and starts a cluster named main, and registers the postgresql service with systemd.

The postgres system user and database role is created. The first connection is usually made like this: sudo -u postgres psql.

Debian specifics: config and data are separate

Unlike many distributions, Debian and Ubuntu separate the configuration from the data. The postgresql.conf and pg_hba.conf files live under /etc/postgresql/<version>/main/, while the actual data is under /var/lib/postgresql/<version>/main/. The <version> corresponds to the installed major version (for example 16).

This architecture makes it possible to host several clusters side by side, managed via pg_lsclusters, pg_ctlcluster, and pg_createcluster.

Getting a newer version

The Ubuntu repositories do not always provide the very latest major version. For that, add the PostgreSQL Global Development Group repository (PGDG, apt.postgresql.org) before installing — verify against your Ubuntu version and the desired PostgreSQL version.