Production PostgreSQL 17 Install on Ubuntu 24.04

PostgreSQL is the database the rest of this seven-part series builds on. Before we install pgvector, ParadeDB, pg_duckdb, Patroni, or pgBackRest, we need a clean, secure, production-shaped Postgres install. That is what this part is for.

The walkthrough is opinionated: PostgreSQL 17 from the official PGDG apt repository, C.UTF-8 collation defaults, scram-sha-256 authentication, an explicit application role separated from the superuser, and bound to localhost or a private interface only. None of this is exotic; it is just what every production Postgres install should look like and rarely does on a fresh tutorial.

Why the PGDG apt Repository Beats Ubuntu Universe

Ubuntu 24.04 ships PostgreSQL 16 in the universe component. It is fine for casual use, but for production work the PostgreSQL Global Development Group (PGDG) repository is the better default for three reasons:

• • Version currency — major versions land within days of upstream release. PostgreSQL 17 is available now; Ubuntu universe will not catch up until 26.04.
• • Extension availability — pgvector, postgis, pg_partman, pgaudit, and many others are pre-packaged and version-matched to the server.
• • Long support window — each major version is maintained for five years, independent of the Ubuntu release cycle.

Install PostgreSQL 17 from PGDG

sudo apt update && sudo apt install -y curl ca-certificates gnupg
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | \
  sudo tee /etc/apt/sources.list.d/pgdg.list

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

On Debian 12, the commands are identical except $(lsb_release -cs) resolves to bookworm.

Cluster Directory Layout

The Debian/Ubuntu PGDG packaging uses a versioned, cluster-aware layout:

/var/lib/postgresql/17/main      <- data directory (PGDATA)
/etc/postgresql/17/main          <- postgresql.conf, pg_hba.conf, pg_ident.conf
/var/log/postgresql/             <- log files (when using stderr/csv logging)
/usr/lib/postgresql/17/bin/      <- server binaries
/etc/postgresql-common/          <- multi-version helpers (pg_lsclusters, pg_ctlcluster)

The split between /etc for config and /var/lib for data is deliberate. It means you can move the data directory to a different filesystem without touching configuration paths or systemd units.

Moving the Data Directory to a Separate Volume

For anything beyond a toy workload, move /var/lib/postgresql to a dedicated block storage volume. Mixing the database's writes with the OS root filesystem makes I/O harder to reason about, makes filesystem snapshots noisier, and makes a runaway log file a system-wide outage.

Stop the cluster, copy the data, point Postgres at the new location:

sudo systemctl stop postgresql@17-main
sudo mkdir -p /mnt/pgdata
sudo rsync -aHAX /var/lib/postgresql/17/main/ /mnt/pgdata/
sudo chown -R postgres:postgres /mnt/pgdata
sudo chmod 700 /mnt/pgdata

Edit /etc/postgresql/17/main/postgresql.conf:

data_directory = '/mnt/pgdata'

Add the mount to /etc/fstab with sane mount options for SSD-backed Postgres workloads:

UUID=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx  /mnt/pgdata  ext4  noatime,nodiratime  0  2

sudo systemctl start postgresql@17-main
sudo -u postgres psql -c "SHOW data_directory;"

Locale, Encoding, and Collation Decisions

The cluster Debian/Ubuntu created for you on install used the system locale, which is typically en_US.UTF-8. That is a defensible choice but it has a sharp edge: glibc collation is not stable across distribution upgrades, and a glibc upgrade can corrupt B-tree indexes on text columns. The fix is to use C.UTF-8 (or ICU) collation as the database default and apply linguistic collation only on the columns that need it.

If you want to switch the default collation, drop the auto-created cluster and re-create it:

sudo pg_dropcluster --stop 17 main
sudo pg_createcluster 17 main \
  --locale=C.UTF-8 --encoding=UTF8 --start \
  --datadir=/mnt/pgdata

For the rest of the series we assume C.UTF-8. Application-level locale-aware sorting can be opted in per-column with COLLATE "en-x-icu" or similar ICU collations.

pg_hba.conf and scram-sha-256

The packaging default uses peer authentication for local Unix sockets. That is fine for the postgres superuser, but applications connecting over TCP need password authentication using scram-sha-256, never the legacy md5 method.

Edit /etc/postgresql/17/main/pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
# Application access from the private network only:
host    appdb           appuser         10.0.0.0/8              scram-sha-256

Force the password encryption algorithm in postgresql.conf:

password_encryption = scram-sha-256

Restricting listen_addresses

By default the PGDG package binds Postgres to localhost only. That is the right default. Open the network surface only as wide as you need:

# Local-only (default, safest):
listen_addresses = 'localhost'

# Localhost + private network interface (recommended for app/db split):
listen_addresses = 'localhost,10.0.0.5'

# Public (almost never the right answer):
listen_addresses = '*'

If your application lives on a separate VPS in the same RamNode region, use the project's private network rather than the public IP. Private-network bandwidth between RamNode instances does not count against your transfer quota.

Creating the First Application Role and Database

Applications must not connect as postgres. Create a database and an owning role with no superuser privileges:

sudo -u postgres psql <<'SQL'
CREATE ROLE appuser LOGIN PASSWORD 'change-me-strong-password';
CREATE DATABASE appdb OWNER appuser ENCODING 'UTF8';
\c appdb
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT  ALL ON SCHEMA public TO appuser;
SQL

The REVOKE/GRANT on public matters: PostgreSQL 15+ already removed the default CREATE privilege from PUBLIC, but being explicit makes the security model legible.

UFW Rules for Port 5432

When binding only to localhost, UFW does not need a Postgres rule at all. When binding to a private interface:

# Allow only the application VPS:
sudo ufw allow from 10.0.0.10 to any port 5432 proto tcp

# Or an entire trusted subnet:
sudo ufw allow from 10.0.0.0/24 to any port 5432 proto tcp

sudo ufw status verbose

Never ufw allow 5432 without a source restriction. Open Postgres on the public internet is one of the most reliably scanned and exploited misconfigurations on the web.

systemd Integration and Logs

The PGDG packaging registers a templated unit per cluster:

# Lifecycle:
sudo systemctl status  postgresql@17-main
sudo systemctl restart postgresql@17-main
sudo systemctl reload  postgresql@17-main

# Wrapper that handles config-changes that require restart vs reload:
sudo pg_ctlcluster 17 main reload

Logs go to /var/log/postgresql/postgresql-17-main.log by default. To send everything to journald instead (recommended on a single-host deploy because journalctl gives you indexed search and built-in rotation):

log_destination = 'stderr'
logging_collector = off

sudo journalctl -u postgresql@17-main -f

Verifying the Install

# Server is accepting connections:
pg_isready -h 127.0.0.1 -p 5432

# Smoke test query:
psql -h 127.0.0.1 -U appuser -d appdb -c "SELECT version(), current_database(), current_user;"

If both succeed, you have a working production-shaped install.

Common First-Day Failures

• • Port 5432 in use — usually an older Postgres cluster still running. Run pg_lsclusters and stop or drop the unwanted one with sudo pg_dropcluster --stop <version> main.
• • Locale mismatch on initdb — pgcreatecluster fails with "locale not generated". Fix with sudo locale-gen C.UTF-8 en_US.UTF-8 and retry.
• • Permission denied on data dir — /mnt/pgdata must be owned by postgres:postgres with mode 0700. Anything looser and Postgres refuses to start.
• • "Peer authentication failed for user appuser" — you're connecting over the local socket; either use -h 127.0.0.1 to force TCP, or change the local rule from peer to scram-sha-256.

What's Next