How to Install and Configure PostgreSQL on Ubuntu 24.04

PostgreSQL is an open-source relational database known for strict SQL compliance, reliable transactions and extensions such as PostGIS and pgvector. In this tutorial you will install PostgreSQL 16 on Ubuntu 24.04, create a database and an application user, allow encrypted connections from a trusted host, apply basic memory tuning, and schedule daily backups with pg_dump and a systemd timer.

Prerequisites

To follow this guide you need:

• A server running Ubuntu 24.04 LTS, for example a CubePath VPS, with at least 1 GB of RAM (2 GB or more for production).
• A non-root user with sudo privileges.
• UFW enabled with SSH allowed, if you plan to accept remote connections.
• The IP address of the application server that will connect to the database, referred to as your_app_server_ip, if you need remote access.

Step 1 - Installing PostgreSQL

Ubuntu 24.04 ships PostgreSQL 16 in its main repository, which receives security updates for the life of the release. Update the package index and install the server together with the contrib extensions:

sudo apt update
sudo apt install postgresql postgresql-contrib

The package creates a cluster called main, starts it and enables it at boot. Check the cluster with the Debian/Ubuntu helper pg_lsclusters:

pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

Confirm the systemd service is active:

sudo systemctl status postgresql

Note

If you need a newer major version (17 or 18), add the official PGDG repository with sudo apt install postgresql-common followed by sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh, then install postgresql-17 or postgresql-18. The paths in this guide change from 16 to the version you install.

On Ubuntu the configuration lives in /etc/postgresql/16/main/ and the data in /var/lib/postgresql/16/main/:

Step 2 - Connecting as the postgres role

Installation creates a Linux account and a database superuser, both named postgres. Local connections use peer authentication, which maps your Linux user to the database role of the same name, so you connect by switching to that account:

sudo -u postgres psql

Inside psql, check the server version:

SELECT version();

 PostgreSQL 16.x (Ubuntu 16.x-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc ...

Type \q to leave psql. Keep the postgres superuser for administration only and create a dedicated role for each application.

Step 3 - Creating a database and an application user

Create a role called appuser that can log in with a password. The --pwprompt option asks for the password so it does not end up in your shell history:

sudo -u postgres createuser --pwprompt appuser

Create a database called appdb owned by that role:

sudo -u postgres createdb --owner=appuser appdb

Since PostgreSQL 15, ordinary users cannot create objects in the public schema of a database they do not own, so making appuser the owner is what lets the application create its tables.

Test the login over TCP, which uses password authentication (scram-sha-256 by default in PostgreSQL 16):

psql -h 127.0.0.1 -U appuser -d appdb -c '\conninfo'

Password for user appuser:
You are connected to database "appdb" as user "appuser" on host "127.0.0.1" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

The SSL line appears because Ubuntu enables TLS by default using the server's self-signed "snakeoil" certificate.

Step 4 - Allowing remote connections

Skip this step if the application runs on the same server. Otherwise, you need to change two things: the address PostgreSQL listens on, and the pg_hba.conf rule that allows the remote host.

Create a drop-in file so your changes stay separate from the packaged postgresql.conf:

sudo nano /etc/postgresql/16/main/conf.d/10-network.conf

listen_addresses = 'localhost,your_server_private_ip'

Replace your_server_private_ip with the address the application reaches, preferably a private network address. Use '*' only if you need every interface.

Next, open the client authentication file:

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

Add this line at the end. hostssl only matches encrypted connections, so plain-text logins from that host are rejected:

hostssl  appdb  appuser  your_app_server_ip/32  scram-sha-256

Restart PostgreSQL, because listen_addresses only changes on restart:

sudo systemctl restart postgresql

Confirm it listens on the new address:

sudo ss -tlnp | grep 5432

LISTEN 0  200  10.0.0.5:5432   0.0.0.0:*  users:(("postgres",pid=4121,fd=7))
LISTEN 0  200  127.0.0.1:5432  0.0.0.0:*  users:(("postgres",pid=4121,fd=6))

Allow port 5432 only from the application server:

sudo ufw allow from your_app_server_ip to any port 5432 proto tcp

From the application server, with the postgresql-client package installed, test the connection and require TLS:

psql "host=your_server_private_ip dbname=appdb user=appuser sslmode=require" -c '\conninfo'

Important

sslmode=require encrypts the connection but does not verify the server certificate. For connections over the public internet, install a certificate from a trusted CA in ssl_cert_file and ssl_key_file and connect with sslmode=verify-full.

Step 5 - Tuning memory and logging

PostgreSQL's defaults are sized for very small machines. The following values are a reasonable starting point for a server dedicated to PostgreSQL with 4 GB of RAM; scale them with your memory:

Create a second drop-in file:

sudo nano /etc/postgresql/16/main/conf.d/20-tuning.conf

shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 256MB

# SSD storage: make index scans cheaper for the planner
random_page_cost = 1.1

# Log slow queries and useful events
log_min_duration_statement = 500ms
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

work_mem applies per operation, and one query can use several at once, so multiplying it by max_connections (100 by default) gives the worst case. Raise it for specific reporting sessions with SET work_mem instead of globally.

Restart to apply shared_buffers, then check the values PostgreSQL loaded:

sudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;" -c "SHOW effective_cache_size;"

 shared_buffers
----------------
 1GB
(1 row)

 effective_cache_size
----------------------
 3GB
(1 row)

If PostgreSQL fails to start after a change, the reason is in the cluster log:

sudo tail -n 20 /var/log/postgresql/postgresql-16-main.log

Autovacuum is enabled by default and should stay on: it reclaims space from updated and deleted rows and keeps planner statistics current.

Step 6 - Scheduling daily backups

pg_dump creates a consistent logical backup of one database while it stays online. The custom format (-Fc) is compressed and lets pg_restore restore selected tables.

Create a backup directory owned by postgres:

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql

Create the backup script:

sudo nano /usr/local/bin/pg-backup

#!/usr/bin/env bash
set -euo pipefail

backup_dir="/var/backups/postgresql"
retention_days=7
stamp="$(date +%Y%m%d-%H%M%S)"

# Dump every non-template database in custom format
for db in $(psql -AtX -c "SELECT datname FROM pg_database WHERE NOT datistemplate"); do
    pg_dump -Fc -f "${backup_dir}/${db}-${stamp}.dump" "$db"
done

# Roles and other global objects are not included in pg_dump
pg_dumpall --globals-only > "${backup_dir}/globals-${stamp}.sql"

find "$backup_dir" -type f -mtime +"$retention_days" -delete

Make it executable:

sudo chmod 755 /usr/local/bin/pg-backup

Create a systemd service that runs it as postgres:

sudo nano /etc/systemd/system/pg-backup.service

[Unit]
Description=Dump PostgreSQL databases

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/bin/pg-backup

And a timer that triggers it every night:

sudo nano /etc/systemd/system/pg-backup.timer

[Unit]
Description=Daily PostgreSQL backup

[Timer]
OnCalendar=*-*-* 03:00:00
Persistent=true

[Install]
WantedBy=timers.target

Enable the timer and run the service once to test it:

sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer
sudo systemctl start pg-backup.service
sudo ls -lh /var/backups/postgresql

-rw-r--r-- 1 postgres postgres 3.1K Sep 25 10:12 appdb-20260925-101204.dump
-rw-r--r-- 1 postgres postgres  512 Sep 25 10:12 globals-20260925-101204.sql
-rw-r--r-- 1 postgres postgres 1.2K Sep 25 10:12 postgres-20260925-101204.dump

Check the next run with systemctl list-timers pg-backup.timer. Copy the backups off the server as well, for example to object storage, because a local copy does not survive losing the disk.

Restoring a backup

To restore into an empty database, create it and use pg_restore:

sudo -u postgres createdb --owner=appuser appdb_restored
sudo -u postgres pg_restore -d appdb_restored /var/backups/postgresql/appdb-20260925-101204.dump

Test a restore regularly: a backup you have never restored is not a verified backup.

Troubleshooting

psql: error: connection to server ... failed: FATAL: Peer authentication failed for user "appuser": you connected through the Unix socket, which uses peer authentication. Add -h 127.0.0.1 to use password authentication over TCP.

no pg_hba.conf entry for host "x.x.x.x", user "appuser", database "appdb", SSL off: the client did not use TLS, or its IP does not match the hostssl rule. Connect with sslmode=require and check the address in pg_hba.conf. Changes to pg_hba.conf only need sudo systemctl reload postgresql.

Connection times out from the application server: check that listen_addresses includes the right IP (ss -tlnp), that UFW allows the source (sudo ufw status), and that no provider-level firewall blocks port 5432.

PostgreSQL does not start after tuning: an oversized shared_buffers or a typo in a conf.d file prevents startup. Read the cluster log shown in Step 5, fix the value and restart.

Conclusion

You now have PostgreSQL 16 running on Ubuntu 24.04 with a dedicated application database, TLS-only remote access limited to one host, sensible memory settings and nightly backups. As next steps, consider enabling the pg_stat_statements extension to find your most expensive queries, adding a connection pooler such as PgBouncer for applications that open many connections, and setting up streaming replication for high availability.