On November 12, 2026, the PostgreSQL project shipped a minor release for every supported branch that closes 52 CVEs and more than 140 bugs. That same afternoon, a fresh PostgreSQL 18 install on Ubuntu 26.04 from the Ubuntu archive still pulled the previous patch level, while the PGDG repository already carried the fix. If you installed from the archive and stopped there, your database is running code with published vulnerabilities.
This guide installs PostgreSQL 18 and locks down the bind address, pg_hba.conf and the firewall. Then it walks through the minor upgrade that matters right now, 18.6 to 18.7 with apt, including the restart the package triggers on its own and the reindex, constraint and backup checks the release notes ask for. All of it was tested in November 2026 on two Ubuntu 26.04.1 LTS servers with ufw enforcing and TLS required for every remote login.
Two package sources, two patch speeds
Ubuntu 26.04 ships PostgreSQL 18 in its own archive, and the PostgreSQL Global Development Group runs a second apt repository (PGDG) for the same major version. Both build the same upstream code, so the difference is not quality but timing. PGDG publishes a minor release the day upstream announces it, while the Ubuntu archive moves it through the security and updates pockets on Ubuntu’s own schedule, and that gap is exposure you carry.
We enabled both sources on a lab server the afternoon of the announcement and asked apt what it could install. The PGDG candidate is already the patched build, the newest Ubuntu archive build is one release behind, and PGDG still lists the older point releases, which matters for the rehearsal later on.

Neither source is wrong; they put the patch window in different hands. The table sets out what each one protects you from and what it leaves open.
| Source | Newest build on November 12 | What it stops | What it lets through |
|---|---|---|---|
| Ubuntu archive | 18.6-0ubuntu0.26.04.1 | Third-party repo risk; updates arrive with unattended-upgrades | Days of exposure between the upstream announcement and the archive update |
| PGDG (apt.postgresql.org) | 18.7-1.pgdg26.04+2 | The patch gap; older builds stay reachable through apt-archive.postgresql.org | A second signing key to trust and a newer postgresql-common |
For a database that faces an application network, we run PGDG. Security fixes land the day they’re announced, so the window between disclosure and patch is yours to schedule rather than Ubuntu’s. If your policy forbids third-party repositories, stay on the archive, but watch the PostgreSQL announcements and track the Ubuntu security notice so you know how long you are exposed.
Prerequisites
Our lab used two 2 vCPU, 4 GB cloud VMs on one private subnet: pg-lab01 for the upgrade rehearsal and pg-lab02 as the database server, with the first acting as the remote client. Treat that as a floor, not a target. Production sizing follows the working set and connection count, which usually means 8 to 32 GB of RAM and SSD storage at about three times the data size. Before you start, confirm each of these holds:
- The server runs Ubuntu 26.04 LTS and
sudo apt updatecompletes without errors. - You log in as a sudo user, not root, as set up in the Ubuntu 26.04 initial server setup.
- The server has a static private address (ours is
10.0.1.50) and the application hosts sit in one known subnet (10.0.1.0/24). - Port 5432 isn’t reachable from the internet at the cloud firewall or security group.
The last item matters more than anything inside the VM. Host firewall rules are a second layer, and they fail with the host: a database that never sees internet traffic cannot be brute forced from it, whatever happens to its own config.
The Ubuntu archive install
The archive route needs no extra keys, which is why it is the default for most guides and the reason most servers end up a patch behind. Refresh the package index and pull the server and client metapackages:
sudo apt update
sudo apt install -y postgresql postgresql-client
On pg-lab02 this downloaded 18.4 MB and pulled postgresql-18-jit and liburing2 alongside the server. Read the pocket names in the Get lines, because they tell you where future fixes will come from: resolute-updates here, so this box waits on Ubuntu for every patch.
Need to get 18.4 MB of archives.
After this operation, 52.4 MB of additional disk space will be used.
Get:8 http://europe-west1.gce.archive.ubuntu.com/ubuntu resolute-updates/main amd64v3 libpq5 amd64 18.6-0ubuntu0.26.04.1 [164 kB]
Get:9 http://europe-west1.gce.archive.ubuntu.com/ubuntu resolute/main amd64v3 liburing2 amd64 2.14-1 [25.4 kB]
Get:10 http://europe-west1.gce.archive.ubuntu.com/ubuntu resolute-updates/main amd64v3 postgresql-client-18 amd64 18.6-0ubuntu0.26.04.1 [1395 kB]
Get:11 http://europe-west1.gce.archive.ubuntu.com/ubuntu resolute-updates/main amd64v3 postgresql-18 amd64 18.6-0ubuntu0.26.04.1 [6009 kB]
Get:13 http://europe-west1.gce.archive.ubuntu.com/ubuntu resolute-updates/main amd64v3 postgresql-18-jit amd64 18.6-0ubuntu0.26.04.1 [10.4 MB]
Setting up postgresql-18 (18.6-0ubuntu0.26.04.1) ...
Setting up postgresql-18-jit (18.6-0ubuntu0.26.04.1) ...
Setting up postgresql (18+290ubuntu1) ...
The package creates a cluster called main and starts it straight away. Check the version, the cluster state and, most importantly, where it listens before you change anything:
pg_lsclusters
systemctl status postgresql --no-pager | head -3
ss -tlnp | grep 5432
sudo -u postgres psql -Atc "SELECT version();"
All four came back clean on the lab server, with the listener on loopback only:
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
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
Active: active (exited) since Thu 2026-11-12 16:19:16 UTC; 11s ago
LISTEN 0 200 127.0.0.1:5432 0.0.0.0:*
PostgreSQL 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit
Two details trip people up here. postgresql.service reading active (exited) is normal because it is an umbrella unit; the real server runs as [email protected], so that is the unit to check when something looks wrong. The listener on 127.0.0.1 is the safe default, since nothing off the box can reach the server until you change it on purpose, and the rest of this guide keeps it that narrow.
The PGDG repository and its signing key
Older guides, including the first version of this one, dropped the PGDG key into /etc/apt/trusted.gpg.d/. That trusts the key for every repository on the machine, so a compromised PGDG key could sign packages that apt would accept from anywhere. The current method scopes the key to PGDG alone with a Signed-By line, and the helper script in postgresql-common writes it for you:
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
Don’t trust a script’s output until you’ve read what it wrote. The source file should name only the resolute-pgdg suite and point Signed-By at a key under /usr/share/postgresql-common/pgdg/:
cat /etc/apt/sources.list.d/pgdg.sources
Ours matched, with one suite, one component and a key path that no other repository uses:
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: resolute-pgdg
Components: main
Architectures: amd64
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
If your change policy forbids running a vendor script, the PostgreSQL Ubuntu download page lists the manual deb822 steps, and the key fingerprint to check is B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8. On a server already running the archive build, adding PGDG changes nothing by itself. Packages only move when you upgrade, which the crossover section below covers.
A pinned install for the staging rehearsal
A minor upgrade is low risk, but low risk is not zero, and this release asks for reindexing afterwards. Rehearse it on a staging copy first so you know how long the reindex takes on your data. Do it on a fresh VM, not on the archive-install server above: postgresql-18-jit there depends on the exact server build and would conflict with the pin. On November 12 the PGDG suite still listed three builds, so for now you can install the exact patch level production runs and upgrade it the same way production will be upgraded. List the versions apt can see:
apt-cache policy postgresql-18
Then install the older build by pinning both the server and the client package to it. Pin both, because a pinned server with an unpinned client gives you a psql that doesn’t match what production operators use:
sudo apt install -y postgresql-18=18.6-1.pgdg26.04+2 postgresql-client-18=18.6-1.pgdg26.04+2 #https://www.postgresql.org/docs/release/18.6/
That pin stops resolving once newer releases push the build out of the suite; older builds then live in the resolute-pgdg-archive suite on apt-archive.postgresql.org. The pin holds for those two packages only. libpq5 was not pinned, so apt installed the newest client library, and the rehearsal box ended up with a mixed set:
ii libpq5:amd64 18.7-1.pgdg26.04+2 amd64 PostgreSQL C client library
ii postgresql-18 18.6-1.pgdg26.04+2 amd64 The World's Most Advanced Open Source Relational Database
ii postgresql-client-18 18.6-1.pgdg26.04+2 amd64 front-end programs for PostgreSQL 18
ii postgresql-client-common 290ubuntu1 all manager for multiple PostgreSQL client versions
ii postgresql-common 290ubuntu1 all PostgreSQL database-cluster manager
That mix is harmless because libpq is compatible across minor releases, but it means a pinned staging box is not a byte-for-byte copy of production. Add libpq5=<version> to the pin if your rehearsal needs the client library to match as well. Before the upgrade, capture the state you will compare against afterwards: cluster status, the server version and what apt wants to change.

Note the two postgresql-common packages in that list. PGDG ships a newer cluster manager than the Ubuntu archive, so a plain apt upgrade would replace it in the same run. The targeted command in the next section leaves it alone and touches only the server packages, which keeps the change you’re making the change you planned.
Upgrading PostgreSQL 18.6 to 18.7 with apt
A minor release needs no dump, no restore and no pg_upgrade, because the on-disk format does not change between minor versions. The job is to replace the binaries and restart the server. That restart is the part people underestimate, so line these up before you run anything:
- A maintenance window is booked, because the package restarts the cluster on its own.
- The connection pooler or load balancer is drained for this server.
- A backup from the last 24 hours has been restored somewhere at least once.
- The reindex time is known from the staging rehearsal.
With those in place, upgrade only the packages you mean to upgrade. Naming them with --only-upgrade stops apt from installing anything new or pulling the cluster manager along:
sudo apt-get install -y --only-upgrade postgresql-18 postgresql-client-18
The download is just under 10 MB and the unpack takes seconds. Watch the package list in the output: it should name the two packages you asked for and nothing else.

The full apt output ends with a line that misleads. After the unpack, needrestart reports “No services need to be restarted”, which sounds as if the server kept running on the old binary. It didn’t. The package’s own maintainer script had already stopped and started the cluster, so there was nothing left for needrestart to flag.
The restart you did not schedule
The cluster log for the apt run shows exactly what happened. A fast shutdown aborted active transactions and killed an autovacuum in the middle of a scan, and the new binary came up just under three seconds later:
2026-11-12 16:16:54.170 UTC [4649] LOG: received fast shutdown request
2026-11-12 16:16:54.173 UTC [4649] LOG: aborting any active transactions
2026-11-12 16:16:54.173 UTC [5279] FATAL: terminating autovacuum process due to administrator command
2026-11-12 16:16:54.173 UTC [5279] CONTEXT: while scanning block 2792 of relation "public.docs"
2026-11-12 16:16:54.407 UTC [4649] LOG: database system is shut down
2026-11-12 16:16:56.989 UTC [6791] LOG: starting PostgreSQL 18.7 (Ubuntu 18.7-1.pgdg26.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit
2026-11-12 16:16:57.008 UTC [6791] LOG: database system is ready to accept connections
Three seconds is short, but every open transaction in that window rolls back and every client connection drops, so applications without retry logic will surface errors to users. A fast shutdown does not wait for long queries either; a report that ran for an hour is simply gone. Don’t let unattended-upgrades pull PostgreSQL packages on a production primary unless you’ve accepted that restart at whatever time the timer fires.
Proof the new binary is serving
Do not trust the package version alone. If the restart had failed, dpkg -l would still list the new package while the old process kept serving, or the cluster would be down. We restarted the unit once more by hand to confirm it comes back cleanly, then asked the server itself:
sudo systemctl restart postgresql@18-main
pg_lsclusters
sudo -u postgres psql -Atc "SELECT version();" -c "SELECT pg_postmaster_start_time();"
sudo grep -E "shutdown request|starting PostgreSQL" /var/log/postgresql/postgresql-18-main.log | tail -4
The cluster is online, the version string reports the new build and the postmaster start time matches the manual restart. The log grep shows both cycles, the one apt triggered at 16:16 and ours at 16:17.

If SELECT version() still reports the old patch level, the old binary is still in memory and you’re not patched, whatever apt says. Check journalctl -u postgresql@18-main for the failed start before going further, because the post-update checks below only mean something on the new build. To run the same check across a fleet on 14 through 18, use the minor version check in our PostgreSQL 18.7 security update post, which also covers the 14 to 17 branches this PostgreSQL 18 install guide for Ubuntu 26.04 leaves out.
Crossover from the Ubuntu archive build to PGDG
A server installed from the archive, like pg-lab02, can take the PGDG build directly once the repository is added. Include the JIT package and libpq in the same transaction, because postgresql-18-jit depends on the exact server version, so the two have to move together:
sudo apt-get install -y --only-upgrade postgresql-18 postgresql-18-jit postgresql-client-18 libpq5
Four packages moved across and the cluster restarted the same way. The hardened settings from the sections below survived untouched: listen_addresses and shared_buffers still read the values from conf.d afterwards.
4 upgraded, 0 newly installed, 0 to remove and 14 not upgraded.
Unpacking libpq5:amd64 (18.7-1.pgdg26.04+2) over (18.6-0ubuntu0.26.04.1) ...
Unpacking postgresql-18-jit (18.7-1.pgdg26.04+2) over (18.6-0ubuntu0.26.04.1) ...
Unpacking postgresql-18 (18.7-1.pgdg26.04+2) over (18.6-0ubuntu0.26.04.1) ...
Unpacking postgresql-client-18 (18.7-1.pgdg26.04+2) over (18.6-0ubuntu0.26.04.1) ...
Setting up libpq5:amd64 (18.7-1.pgdg26.04+2) ...
Setting up postgresql-client-18 (18.7-1.pgdg26.04+2) ...
Setting up postgresql-18 (18.7-1.pgdg26.04+2) ...
Setting up postgresql-18-jit (18.7-1.pgdg26.04+2) ...
Once you cross over, keep the server, JIT, client and libpq packages on PGDG and upgrade them together in one command. Upgrading them as a set keeps the exact-version dependency satisfied and keeps the server binary and the client library on the same patch level.
Post-update checks the release notes require
Swapping the binaries doesn’t repair damage the old binaries already did. The 18.7 release notes name several bugs that could corrupt indexes, one that let partitioned tables carry an invalid NOT NULL constraint, and several that could corrupt incremental backups. Each one needs action on your side after the restart, and none of it happens automatically. The vulnerabilities themselves are covered in our PostgreSQL 18.7 security update and CVEs breakdown. If you are coming from a build older than 18.6, for example the 18.3 in the Ubuntu release pocket, also read the notes for each release you skipped, because their post-update steps still apply.
This part applies to servers that already hold data. To see what the checks look like on a real catalog, we seeded a database called appdb on the old build with one example of each exposure; in the commands below, swap in your own database name. These are the four conditions to look for in yours:
- A GIN index exists on any column, here a
jsonbcolumn. - A GiST index uses the
gist_trgm_opsoperator class frompg_trgm. - A B-tree index sits on a column declared
bpcharwith no length. - A partitioned table carries a
NO INHERITNOT NULL constraint.
The last one is the sneaky case. The old build refused NOT NULL NO INHERIT when written directly on a partitioned table, but a CREATE TABLE ... (LIKE ...) from a regular table copied the constraint through anyway. That’s how our orders table got one, and it’s how an ORM migration could have given you one without anyone typing it.
An audit script for affected objects
Rather than reindex everything, find what’s actually exposed, because a blanket reindex on a large database can take longer than the window you booked. Open a file for the audit queries in your home directory:
vim pg187-audit.sql
Paste these four queries, one per condition in the list above. A char(n) column with a declared length is not affected, so the third query filters on atttypmod = -1, and the fourth is copied from the release announcement.
SELECT i.indrelid::regclass AS table_name, i.indexrelid::regclass AS index_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am ON am.oid = c.relam
WHERE am.amname = 'gin';
SELECT DISTINCT i.indrelid::regclass AS table_name, i.indexrelid::regclass AS index_name
FROM pg_index i
JOIN pg_opclass oc ON oc.oid = ANY (i.indclass::oid[])
WHERE oc.opcname = 'gist_trgm_ops';
SELECT DISTINCT i.indrelid::regclass AS table_name, i.indexrelid::regclass AS index_name, a.attname AS column_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am ON am.oid = c.relam AND am.amname = 'btree'
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY (i.indkey::int2[])
WHERE a.atttypid = 'bpchar'::regtype AND a.atttypmod = -1;
SELECT conrelid::regclass, conname FROM pg_constraint WHERE contype = 'n' AND connoinherit AND conrelid IN (SELECT oid FROM pg_class WHERE relkind = 'p');
Catalogs are per database, so run the script against every database that holds application data, not only postgres. Running it once against the default database and seeing nothing proves nothing about the others.
sudo -u postgres psql -d appdb < pg187-audit.sql
Each seeded exposure shows up as one row per query. On a real server expect the GIN list to be the long one, since every jsonb, array and full text search index is GIN.

An empty result means that exposure does not exist in that database, so you can skip the matching repair. An index on the list is not proven corrupt, only suspect. The release notes call for rebuilding the gist_trgm_ops and length-less bpchar indexes, and recommend a GIN rebuild if you suspect damage. We rebuild all three kinds anyway, because a corrupt index returns wrong answers quietly while a rebuild costs minutes.
Rebuilding the indexes without locking writers
Plain REINDEX blocks writes to the table for the whole rebuild, which on a busy table is an outage of its own. REINDEX INDEX CONCURRENTLY builds a new copy alongside the old one and swaps them, so the application keeps writing. It takes longer and needs disk for a second copy of each index while it runs, so check free space first. Create the repair script:
vim pg187-fix.sql
Name the indexes and constraint your audit returned. The script below fixes the lab’s four findings, re-runs the constraint query to prove it is clean, and then tries the old LIKE loophole once more:
\timing on
REINDEX INDEX CONCURRENTLY docs_body_gin;
REINDEX INDEX CONCURRENTLY docs_title_trgm;
REINDEX INDEX CONCURRENTLY docs_code_btree;
ALTER TABLE orders ALTER CONSTRAINT tmpl_region_not_null INHERIT;
SELECT conrelid::regclass, conname FROM pg_constraint WHERE contype = 'n' AND connoinherit AND conrelid IN (SELECT oid FROM pg_class WHERE relkind = 'p');
CREATE TABLE orders2 (LIKE tmpl INCLUDING ALL, id int) PARTITION BY LIST (id);
Run it with -e so every statement is echoed above its result, which gives you a log you can paste into the change ticket:
sudo -u postgres psql -d appdb -e < pg187-fix.sql
On 200,000 rows the trigram GiST index was the slow one at 2.8 seconds, against 0.8 seconds for the GIN index and 0.2 for the B-tree. Scale those by your own table sizes when you plan the window. The constraint query now returns zero rows, and the final CREATE TABLE fails, which is the result you want:

That error is the proof the patched server closes the loophole. ALTER CONSTRAINT ... INHERIT fails if any partition holds a NULL in that column, so clean those rows first, or drop the constraint with ALTER TABLE ... DROP CONSTRAINT if the column is genuinely allowed to be empty. If reading a table ever throws missing chunk number errors, the release notes call for REINDEX TABLE on it as well.
Incremental backup chains after the update
If you use the incremental pg_basebackup that arrived in PostgreSQL 17, the release notes are blunt: existing incremental backup files should not be trusted. A restore that combines a corrupt incremental with its full backup can hand you a database that starts cleanly and then returns wrong data. Start a new chain on the patched server, and skip this section only if you take full backups alone.
Incremental backups need WAL summarization. If you’re setting this up for the first time, enable it and reload, then check from a new connection:
sudo -u postgres psql -c "ALTER SYSTEM SET summarize_wal = on;"
sudo -u postgres psql -c "SELECT pg_reload_conf();"
sudo -u postgres psql -Atc "SHOW summarize_wal;"
Use the separate session as shown. In the lab, a SHOW in the same session as the reload still printed off, and only a fresh connection printed on; trusting the first answer would have sent us looking for a config problem that wasn’t there. With summarization running, take the new full backup into a directory only postgres can read:
export FULL="/var/lib/postgresql/backups/full-$(date +%F)"
sudo install -d -m 700 -o postgres -g postgres /var/lib/postgresql/backups
sudo -u postgres pg_basebackup -D "${FULL}" -c fast -P
Verify it before you build anything on top of it. pg_verifybackup is not on the default PATH on Ubuntu, so a bare sudo -u postgres pg_verifybackup fails with sudo: 'pg_verifybackup': command not found. Call it by its full path:
sudo -u postgres /usr/lib/postgresql/18/bin/pg_verifybackup "${FULL}"
The 116 MB base backup copied and verified cleanly. The checker compares the files against the backup manifest and checks the WAL the backup needs, so a pass means nothing listed in the manifest is missing or altered. It cannot prove the data inside is sound, which is why the test restore further down still matters.

Point the next incremental at the new full backup’s manifest, never at the old chain, and verify it the same way. The incremental usually runs on a later day, so set FULL to the literal directory of that full backup in the new shell rather than recomputing it from today’s date. sudo ls -d /var/lib/postgresql/backups/full-* shows the name; ours was dated November 12:
export FULL="/var/lib/postgresql/backups/full-2026-11-12"
export INCR="/var/lib/postgresql/backups/incr-$(date +%F)"
sudo -u postgres pg_basebackup -D "${INCR}" -c fast -i "${FULL}/backup_manifest"
sudo -u postgres /usr/lib/postgresql/18/bin/pg_verifybackup "${INCR}"
Both directories reported backup successfully verified. The sizes below show the old chain and the new one side by side on the lab server:
129M /var/lib/postgresql/backups/full-18.6
132M /var/lib/postgresql/backups/full-2026-11-12
25M /var/lib/postgresql/backups/incr-18.6
35M /var/lib/postgresql/backups/incr-2026-11-12
A verifier pass on an incremental is not the same as a sound chain: the corruption classes fixed in this release are the kind pg_verifybackup cannot see. The real proof is pg_combinebackup into a scratch directory and a server started from it. We did not run that restore in this lab, so treat it as a step to script and test yourself; on Ubuntu the base backup holds no postgresql.conf, because it lives under /etc/postgresql/18/main, so the scratch server needs one passed with config_file. Keep the old chain until the new one has passed that test restore, then retire it, because deleting it first leaves you with an unproven backup and nothing behind it. For restores and point-in-time recovery with archived WAL, follow our PostgreSQL and MariaDB point-in-time recovery guide. A backup you’ve never restored is a hope, not a control.
Application roles with least privilege
Applications should never connect as postgres. That role is a superuser, and an SQL injection running as a superuser can read server files and run shell commands through COPY ... PROGRAM. Give each application its own login role that owns only its own database, so a compromise stays inside that database:
sudo -u postgres psql -c "CREATE USER appuser WITH PASSWORD 'StrongPassword123';"
sudo -u postgres psql -c "CREATE DATABASE appdb OWNER appuser;"
Replace the example password. Passwords typed on the command line land in your shell history, so on a shared box create the role without one and set it interactively with \password appuser inside sudo -u postgres psql. Then confirm the role can log in over TCP with a password:
psql -h localhost -U appuser -d appdb -c "SELECT current_user, current_database();"
The login works and lands in the right database. If it fails here, fix it before going further, because every remote test later depends on this role.
current_user | current_database
--------------+------------------
appuser | appdb
(1 row)
No pg_hba.conf edit was needed for that. Ubuntu’s default file already allows password logins over loopback with scram-sha-256, and uses peer for local socket connections. The weaker md5 method is not in the default file at all, and nothing in this guide adds it back.
The default bind address and remote access
Many guides, the first version of this one included, set listen_addresses = '*' and open pg_hba.conf to a wide range. Do not copy that. It binds every interface, including any public one a cloud provider attaches later, and leaves the password as the only barrier between the internet and your data. Bind to the one private address, accept only the application subnet, and require TLS.
Set the two values once for your network so the commands below work as written:
export DB_IP="10.0.1.50"
export APP_SUBNET="10.0.1.0/24"
Ubuntu’s postgresql.conf ends with include_dir = 'conf.d', so put local changes in their own file there rather than editing the packaged one. Package upgrades never touch your file, and anyone auditing the server later can see at a glance what was changed:
echo "listen_addresses = 'localhost,${DB_IP}'" | sudo tee /etc/postgresql/18/main/conf.d/20-listen.conf
echo "hostssl appdb appuser ${APP_SUBNET} scram-sha-256" | sudo tee -a /etc/postgresql/18/main/pg_hba.conf
That hostssl line is narrow on every axis: encrypted connections only, appuser only, appdb only, from the subnet only. Ubuntu enables ssl by default with the snakeoil certificate, but confirm it before restarting, because without TLS the line matches nothing and every remote login fails:
sudo -u postgres psql -Atc "SHOW ssl;"
sudo systemctl restart postgresql
ss -tlnp | grep 5432
SSL reports on, and the server now listens on loopback plus the single private address, nothing else:
on
LISTEN 0 200 10.0.1.50:5432 0.0.0.0:*
LISTEN 0 200 127.0.0.1:5432 0.0.0.0:*
The snakeoil certificate encrypts the session but proves nothing about the server’s identity. Clients using sslmode=require accept it, which stops passive sniffing but not an attacker who can redirect traffic. For verify-full, replace it with a certificate from your internal CA and set ssl_cert_file and ssl_key_file in another conf.d file.
Firewall rules that hold
The bind address and pg_hba.conf decide who PostgreSQL talks to. The host firewall decides who reaches it at all, and it stays in force if someone loosens the PostgreSQL config later. Allow SSH first, because enabling ufw without it cuts off your own session:
sudo ufw allow OpenSSH
sudo ufw allow from "${APP_SUBNET}" to any port 5432 proto tcp comment "PostgreSQL from app subnet"
sudo ufw --force enable
sudo ufw status numbered
Port 5432 is open to the subnet only, and there is no rule for it from Anywhere:
Status: active
To Action From
-- ------ ----
[ 1] OpenSSH ALLOW IN Anywhere
[ 2] 5432/tcp ALLOW IN 10.0.1.0/24 # PostgreSQL from app subnet
[ 3] OpenSSH (v6) ALLOW IN Anywhere (v6)
Now prove the controls from a client on the subnet. The first login should succeed over TLS, and the second turns encryption off and must be refused:
psql "host=10.0.1.50 dbname=appdb user=appuser sslmode=require" -c "SELECT inet_server_addr(), ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid();"
psql "host=10.0.1.50 dbname=appdb user=appuser sslmode=disable" -c "SELECT 1;"
The encrypted session negotiated TLSv1.3, and pg_hba.conf rejected the plain-text attempt before a password was ever checked:

Run the same negative test after every change to pg_hba.conf. A rule that’s only ever tested with the login that should work is a rule nobody has checked, and the failure you’d miss is the one an attacker finds.
Memory and asynchronous I/O settings
The packaged defaults assume a tiny machine, with 128 MB of shared buffers and 100 connections. PostgreSQL 18 also brought an asynchronous I/O subsystem, tuned mainly through three new parameters. Keep all of it in one file under conf.d so it survives upgrades and can be reviewed in one place:
sudo vim /etc/postgresql/18/main/conf.d/10-tuning.conf
These values are sized for the 4 GB lab server. Scale shared_buffers to about a quarter of RAM and effective_cache_size to about three quarters:
# Memory (sized for a 4 GB server)
shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 256MB
# Write-ahead log
wal_buffers = 64MB
checkpoint_completion_target = 0.9
# Planner costs for SSD storage
random_page_cost = 1.1
effective_io_concurrency = 200
# Asynchronous I/O (PostgreSQL 18)
io_method = worker
io_workers = 3
io_max_concurrency = 64
# Connections
max_connections = 200
Restart, then ask pg_settings where each value came from. A value set twice is taken from whichever file is read last, so the sourcefile column proves your file won, and the pg_file_settings error count flags any line that failed to apply:
sudo systemctl restart postgresql
sudo -u postgres psql -c "SELECT name, setting, unit, sourcefile FROM pg_settings WHERE name IN ('shared_buffers','io_method','io_workers','effective_io_concurrency','max_connections') ORDER BY name;"
sudo -u postgres psql -Atc "SELECT count(*) FROM pg_file_settings WHERE error IS NOT NULL;"
Every value is sourced from 10-tuning.conf and the error count is zero:

Raising max_connections costs memory per connection, so a pooler such as PgBouncer is the better answer once you need more than a couple of hundred. The io_uring method also works on the Ubuntu 26.04 kernel; we set it in a separate file, restarted and confirmed it took effect:
echo "io_method = io_uring" | sudo tee /etc/postgresql/18/main/conf.d/99-iouring.conf
sudo systemctl restart postgresql
sudo -u postgres psql -Atc "SHOW io_method;"
It returned io_uring, and we still ship worker as the default here. io_uring has a long history of kernel CVEs, some container runtimes restrict it, and the gain shows up mainly on large sequential scans. That file sorts after 10-tuning.conf and overrides it, so do not leave it behind by accident. Remove it and restart, because io_method only changes at server start:
sudo rm /etc/postgresql/18/main/conf.d/99-iouring.conf
sudo systemctl restart postgresql
sudo -u postgres psql -Atc "SHOW io_method;"
The server is back on the method the tuning file sets:
worker
Measure io_uring against your own workload before switching for good, and track the effect with our guide to monitoring PostgreSQL with Prometheus and Grafana.
Logical dumps with pg_dump
Base backups protect the whole cluster, while a logical dump protects one database in a form you can restore into a different major version. Write it into the mode 700 directory created earlier, because a dump holds every row in plain text and anyone who can read the file can read your data:
sudo install -d -m 700 -o postgres -g postgres /var/lib/postgresql/backups
sudo -u postgres bash -c 'pg_dump appdb | gzip > /var/lib/postgresql/backups/appdb_$(date +%Y%m%d).sql.gz'
sudo ls -l /var/lib/postgresql/backups/
The file is owned by postgres inside a directory no other user can enter. This listing comes from the database server, which held no base backups; on a single server you will also see the full-* and incr-* directories:
total 4
-rw-r--r-- 1 postgres postgres 395 Nov 12 16:20 appdb_20261112.sql.gz
Current pg_dump builds write a \restrict line with a random token near the top of every plain-text dump. It protects the account running the restore from a compromised source server that injects psql meta-commands into the dump output, so do not strip it out. A psql client older than the August 2025 minor releases does not understand the line, so restore with a current client. To run the dump nightly, add it to the postgres user’s crontab with sudo crontab -u postgres -e:
0 2 * * * pg_dump appdb | gzip > /var/lib/postgresql/backups/appdb_$(date +\%Y\%m\%d).sql.gz
The backslashes before each % are required in a crontab, where cron turns a bare % into a newline and the command breaks. Ship the dumps off the server too, because a backup on the same disk as the database dies with it.
Troubleshooting pg_hba.conf rejections
Both errors below came from the negative tests in the lab. The message names the host, user, database and encryption state that failed to match a rule, so read it before changing anything; most bad pg_hba.conf edits come from fixing the wrong field.
FATAL: no pg_hba.conf entry for host, no encryption
The client connected without TLS, and the only rule for its subnet is a hostssl rule:
psql: error: connection to server at "10.0.1.50", port 5432 failed: FATAL: no pg_hba.conf entry for host "10.0.1.51", user "appuser", database "appdb", no encryption
Fix the client, not the server. Set sslmode=require or stricter in the connection string or the driver settings. Adding a plain host rule to silence the error would let passwords and query results cross the network unencrypted, which is the exposure the rule exists to prevent.
FATAL: no pg_hba.conf entry for host, SSL encryption
This time TLS was on, so the failure is in the user, database or address column. Here the client asked for the postgres database, and the rule only covers appdb:
psql: error: connection to server at "10.0.1.50", port 5432 failed: FATAL: no pg_hba.conf entry for host "10.0.1.51", user "appuser", database "postgres", SSL encryption
Point the client at its own database. If a tool genuinely needs another one, add a separate hostssl line naming it rather than widening the existing rule to all. Then run sudo systemctl reload postgresql, since pg_hba.conf changes need a reload, not a restart, and a reload drops no connections.
File locations on Ubuntu 26.04
Debian-family packaging splits configuration from data, which differs from the RHEL layout in our PostgreSQL on Rocky Linux and AlmaLinux guide. These are the paths this guide touched:
| Item | Path | Notes |
|---|---|---|
| Main config | /etc/postgresql/18/main/postgresql.conf | Leave packaged; override in conf.d |
| Local overrides | /etc/postgresql/18/main/conf.d/ | Files ending in .conf, read in name order |
| Client auth | /etc/postgresql/18/main/pg_hba.conf | First matching line wins; reload after edits |
| Data directory | /var/lib/postgresql/18/main | Owned by postgres, mode 700 |
| Server binaries | /usr/lib/postgresql/18/bin/ | pg_verifybackup and pg_combinebackup live here |
| Log file | /var/log/postgresql/postgresql-18-main.log | Shows the package-triggered restarts |
| Service unit | [email protected] | The real server; postgresql.service is an umbrella |
| PGDG source | /etc/apt/sources.list.d/pgdg.sources | deb822 with a scoped Signed-By |
The same layout applies on Debian, as our PostgreSQL 18 on Debian guide shows, and the Oracle Linux 10 install covers the RHEL-family equivalent. When you plan the next major version, you can install PostgreSQL 19 beside it from the new packages; moving the cluster across needs pg_upgradecluster, not the minor-release steps above.
Post-update audit checklist
Run through this after every minor release, not only this one. Each item is a condition you can check from a shell in under a minute:
apt-cache policy postgresql-18shows the installed version equal to the candidate.SELECT version()reports the same patch level as the installed package.pg_postmaster_start_time()is later than the package install time.- Every GIN,
gist_trgm_opsand length-lessbpcharindex the audit returns has been rebuilt since the upgrade. - The NOT NULL NO INHERIT query returns zero rows in every application database.
- A full base backup taken after the upgrade exists and
pg_verifybackupreports it verified. - Every incremental backup since the upgrade references that new full backup’s manifest.
ss -tlnpshows port 5432 on loopback and the private address only.pg_hba.confhas no line open to0.0.0.0/0or::/0, and no network line usestrustormd5.- A client with
sslmode=disableis refused from the application subnet. ufw statusallows 5432 from the application subnet and from nowhere else.pg_file_settingsreports zero errors.
If any line fails, fix it before you close the maintenance window. The package swap takes seconds; the items on this list are where an unrebuilt index or an untrusted backup chain hides until the day you need it.