
Most PostgreSQL installs work on the first try. The trouble starts three weeks later. The database listens on the wrong interface, or pg_hba.conf still trusts half the subnet. Or someone ran apt upgrade and a second cluster appeared. Or the default memory settings, which are tuned for a 2010 laptop, make a perfectly good 32 GB server crawl under a traffic spike.
Ubuntu 26.04 LTS (Resolute Raccoon) removes the first obstacle. Its release notes confirm PostgreSQL was updated to version 18, so the stock repository gives a modern database engine with one command. The harder part is everything around that command: choosing between the Ubuntu archive and the PostgreSQL project’s own repository, understanding Debian’s multi-cluster layout, locking down authentication, and sizing memory for the hardware you actually have.
This guide walks through a production-minded installation of PostgreSQL on Ubuntu 26.04 LTS, from a clean server to a hardened, tuned and backed-up instance. The commands also work on Debian and, with the noted differences, on AlmaLinux. Treat the config values as starting points. Measure your own workload before committing to them.
Before You Start: Decisions That Matter
Ubuntu archive or PGDG repository?
Ubuntu snapshots one PostgreSQL major version per release and supports it for the life of that release. On 26.04 that version is 18. The PostgreSQL project also runs its own APT repository (PGDG), which supports 26.04 under the codename resolute.
| Factor | Ubuntu archive | PGDG repository |
|---|---|---|
| Setup effort | None | Two commands |
| Major versions available | 18 | 18 plus older majors side by side |
| Extra extensions | Limited to what Ubuntu packages | Wider selection, such as pgvector |
| Minor updates | Follow Ubuntu’s cycle | Often arrive sooner |
| Best for | Simple, distro-managed servers | Multi-version hosts, extension-heavy stacks |
One detail catches people out. Once PGDG is enabled on 26.04, APT prefers the newer PGDG PostgreSQL 18 candidate over Ubuntu’s copy. Treat enabling PGDG as a deliberate choice about where your updates come from. Don’t flip it on casually.
For a single application database with no exotic extensions, the Ubuntu archive is the quieter option. If you need pgvector, PostGIS builds, or a staged major upgrade, use PGDG.
Hardware and filesystem planning
Decide where data will live before installing, because moving it later means downtime. Some rules of thumb from production work:
- Put the data directory on its own volume (XFS or ext4), separate from the root filesystem.
- Prefer NVMe or fast SSD storage. Random I/O dominates most OLTP workloads.
- Keep WAL on a separate device only if you have measured contention. Many small setups gain nothing from it.
- Leave at least 20 to 30 percent of the disk free. A full disk during a checkpoint or WAL write can stop the database.
Preparing the Server
Start by updating the system and checking the release.
lsb_release -a
sudo apt update
sudo apt full-upgrade -y
If the kernel or libc was updated, reboot before installing. It costs a minute now and spares you a confusing state later.
Set the locale and timezone deliberately. The locale chosen at cluster creation determines collation and sort order, and changing it afterwards is painful.
timedatectl set-timezone UTC
locale -a | grep -i en_US
sudo locale-gen en_US.UTF-8
Servers should run in UTC. Convert to local time in the application layer. Your future self reading logs during an incident will be grateful.
Method 1: Install PostgreSQL from the Ubuntu Repository
This is the shortest path. Ubuntu 26.04 carries PostgreSQL 18, and the standard install command pulls it in.
sudo apt install -y postgresql postgresql-client
The postgresql metapackage depends on the current default major version, so it follows the distribution. It will not always mean version 18 on other Ubuntu releases. If you want the version pinned in your automation, name it explicitly:
sudo apt install -y postgresql-18 postgresql-client-18
Want contrib modules such as pg_stat_statements? On current Debian-family packaging the server package already includes the common contrib extensions. Check what is available after install:
sudo -u postgres psql -c "SELECT name, default_version FROM pg_available_extensions ORDER BY name LIMIT 20;"
Verify the installation
systemctl status postgresql
pg_lsclusters
psql --version
pg_lsclusters is a Debian-specific wrapper you will lean on constantly. A healthy fresh install shows something like:
Ver Cluster Port Status Owner Data directory Log file
18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
If the status says down, jump to the troubleshooting section below.
Method 2: Install from the Official PGDG Repository
Pick this when you need extensions, older majors alongside 18, or faster minor releases.
The PostgreSQL project documents an automated script that configures the repository for your release:
sudo apt install -y postgresql-common ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
The script reads your codename and writes the right source entry. Prefer to see exactly what is happening? Do it manually. Import the signing key, then create a deb822-style source file:
sudo apt install -y curl ca-certificates gpg
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
Then create /etc/apt/sources.list.d/pgdg.sources:
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: resolute-pgdg
Architectures: amd64
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
Several older tutorials still use apt-key or drop keys into /etc/apt/trusted.gpg.d/. Avoid that. A keyring referenced through Signed-By limits trust to this one repository, which is the modern practice.
Finish with:
sudo apt update
apt-cache policy postgresql-18
sudo apt install -y postgresql-18 postgresql-client-18
Always run apt-cache policy before installing. It shows which repository will supply the package, and it settles any argument about where a binary came from.
Understanding the Debian Cluster Layout
Debian and Ubuntu do not use the layout from the upstream docs. They wrap PostgreSQL in postgresql-common, which lets several clusters and versions coexist on one host. That is great for upgrades and confusing for newcomers.
Key paths:
/etc/postgresql/18/main/postgresql.confis the main configuration./etc/postgresql/18/main/pg_hba.confcontrols client authentication./var/lib/postgresql/18/mainis the data directory./var/log/postgresql/holds logs.
Manage clusters with the wrapper commands rather than raw pg_ctl:
sudo pg_ctlcluster 18 main restart
sudo systemctl restart postgresql@18-main
pg_lsclusters
Restarting the generic postgresql unit affects every cluster on the host. On a multi-cluster server, target the instance unit postgresql@18-main.
First Login, Roles and Databases
The installer creates an operating system user postgres and a matching database superuser. By default it authenticates over a local Unix socket with peer authentication.
sudo -u postgres psql
Inside psql, create an application role and database. Don’t let applications connect as the superuser. This is one of the most common mistakes seen on audited servers, and it turns every SQL injection into a full server compromise.
CREATE ROLE appuser WITH LOGIN PASSWORD 'use-a-long-random-secret';
CREATE DATABASE appdb OWNER appuser ENCODING 'UTF8';
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO appuser;
\q
Revoking from PUBLIC matters. By default any role can connect to any database. On shared servers, that default is rarely what you want.
For scripts, avoid typing passwords on the command line where shell history captures them. Use \password appuser inside psql, or a ~/.pgpass file with mode 0600:
echo "localhost:5432:appdb:appuser:secretvalue" >> ~/.pgpass
chmod 600 ~/.pgpass
psql -h localhost -U appuser -d appdb
Enabling Remote Access Safely
By default PostgreSQL listens on localhost only. That is the correct default. Open it up only when another host truly needs to connect.
Step 1: listen_addresses
Edit /etc/postgresql/18/main/postgresql.conf:
listen_addresses = 'localhost,10.0.0.5'
Use the private interface address, not '*', whenever possible. A database bound to a public interface will be found by scanners within hours.
Step 2: pg_hba.conf
Rules are read top to bottom, and the first match wins. Add a narrow rule for the application subnet:
# TYPE DATABASE USER ADDRESS METHOD
host appdb appuser 10.0.0.0/24 scram-sha-256
Use scram-sha-256 rather than md5. SCRAM resists offline brute forcing far better and is the modern standard. Confirm your stored passwords were hashed with it:
SHOW password_encryption;
SELECT rolname, left(rolpassword, 14) FROM pg_authid WHERE rolname='appuser';
The stored hash should begin with SCRAM-SHA-256. If an older role still has an md5 hash, reset its password after confirming password_encryption = scram-sha-256.
Step 3: Firewall
sudo ufw allow from 10.0.0.0/24 to any port 5432 proto tcp
sudo ufw status numbered
Never run ufw allow 5432 without a source restriction. If UFW is not enabled yet, allow SSH first, or you will lock yourself out:
sudo ufw allow OpenSSH
sudo ufw enable
Step 4: Apply and test
A change to listen_addresses needs a restart. Changes to pg_hba.conf need only a reload.
sudo systemctl restart postgresql@18-main
ss -ltnp | grep 5432
psql "host=10.0.0.5 dbname=appdb user=appuser" -c "SELECT now();"
AlmaLinux users: the firewall is firewalld, and SELinux may block non-default data directories. The equivalent opening is:
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.0.0/24" port port="5432" protocol="tcp" accept'
sudo firewall-cmd --reload
Encrypting Connections with TLS
Passwords over a private network are still passwords over a wire. Enable TLS if traffic crosses anything you do not fully control. On Ubuntu, the snakeoil certificate is often preconfigured, but replace it with a real certificate for production.
ssl = on
ssl_cert_file = '/etc/ssl/postgresql/server.crt'
ssl_key_file = '/etc/ssl/postgresql/server.key'
ssl_min_protocol_version = 'TLSv1.2'
The key must be readable only by the postgres user, otherwise the server refuses to start:
sudo chown postgres:postgres /etc/ssl/postgresql/server.key
sudo chmod 600 /etc/ssl/postgresql/server.key
Then force encrypted clients with hostssl instead of host in pg_hba.conf, and connect with sslmode=verify-full.
Backups You Can Actually Restore
A backup that has never been restored is a hope, not a backup. Start simple with logical dumps for small databases:
sudo -u postgres pg_dump -Fc appdb > /backup/appdb_$(date +%F).dump
sudo -u postgres pg_restore -d appdb_test /backup/appdb_2026-11-11.dump
For larger or critical systems, use physical backups with WAL archiving for point-in-time recovery. Tools worth knowing include pg_basebackup (built in), pgBackRest and Barman. Schedule restores into a scratch server on a regular basis. Measure how long recovery takes, because that number is your real recovery time objective.
Quick routine with cron:
0 2 * * * postgres pg_dump -Fc appdb > /backup/appdb_$(date +\%F).dump
Rotate old files and copy them off the host. A backup on the same disk as the database dies with it.
Routine Maintenance
Autovacuum is on by default. Leave it on. Tune it per table when a large, busy table bloats. Check on bloat and dead tuples occasionally:
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
Handy sysadmin tools:
htopandiotopfor CPU and process-level I/O.iostat -x 1for disk latency and utilization.pg_activityorpgcenterfor a top-style database view.pgbadgerfor log analysis reports.ss -tnpfor connection counts per client.
Troubleshooting Common Errors
Cluster shows “down” in pg_lsclusters
Read the log before guessing:
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log
journalctl -u postgresql@18-main --no-pager -n 50
Typical causes are a syntax error in postgresql.conf, a full disk, bad permissions on the data directory (it must be 0700 and owned by postgres), or a stale postmaster.pid after a hard crash.
“FATAL: Peer authentication failed for user”
The client connected over the Unix socket as an OS user that does not match the database role. Either connect over TCP (psql -h 127.0.0.1 -U appuser appdb) so password authentication applies, or add a socket rule in pg_hba.conf:
local appdb appuser scram-sha-256
“could not connect to server: Connection refused”
Check that the service is up and that it is listening where you expect:
ss -ltnp | grep 5432
sudo ufw status
Binding to localhost only, or a missing firewall rule, accounts for most remote failures.
“no pg_hba.conf entry for host”
The server received the connection and found no matching rule. Check the client IP seen by the server, the database name, and the user. Remember to reload after editing:
sudo systemctl reload postgresql@18-main
“FATAL: sorry, too many clients already”
You hit max_connections. Don’t just raise it. Find out who is holding connections:
SELECT usename, state, count(*) FROM pg_stat_activity GROUP BY 1,2 ORDER BY 3 DESC;
Idle-in-transaction sessions are a common culprit. Fix the application, add PgBouncer, and set idle_in_transaction_session_timeout.
Two clusters running on different ports
An upgrade or manual pg_createcluster can leave a new cluster on port 5433, because 5432 was already in use. Run pg_lsclusters to see them all. Drop the one you do not need with pg_dropcluster only after confirming it holds nothing important.
Package conflicts after enabling PGDG
If APT proposes unexpected upgrades, inspect priorities with apt-cache policy postgresql-18. If you want to stay on one source, pin it or remove the repository file and run apt update again.