...
For general background on PostgreSQL upgrades, see the official documentation: https://www.postgresql.org/docs/current/pgupgrade.html
Choosing a method
- pg_upgrade: use this when the old and new PostgreSQL run on the same host.
- Dump and restore: use this when the new database is on a different host or is a managed instance. pg_upgrade can't move data between hosts.
Check your target version before you start, because three of the steps below depend on it:
| 12, 13 | 14–17 | 18 | |
|---|---|---|---|
New cluster needs --no-data-checksums | no | no | yes |
| Migrated md5 passwords still work | yes | no | no |
| Where pg_upgrade writes the list of tables with OIDs (upgrading from 11 only) | working directory | <new datadir>/pg_upgrade_output.d/<timestamp>/ | same as 14–17 |
The examples below upgrade from PostgreSQL 11 on port 5432 to PostgreSQL 18 on port 5433. Change the versions and ports to match your setup.
Same host (pg_upgrade)
1. Install the new PostgreSQL version alongside the old one.
...
Until you remove it, you can roll back by starting the old cluster again and pointing Unimus back at it. This works only if pg_upgrade ran in the default copy mode.
Different host or managed instance (dump and restore)
1. Stop Unimus.
sudo systemctl stop unimus
...
This method needs no OID handling, even when you're coming from 11. pg_dump from version 12 onward drops OIDs automatically.
Common errors
| Error | Fix |
|---|---|
You must have read and write access in the current directory | Run cd /tmp before running pg_upgrade. |
could not connect to source postmaster | Add -o and -O with config_file= (step 7). |
old cluster does not use data checksums but the new one does | Recreate the new cluster with --no-data-checksums. |
Checking for tables WITH OIDS – fatal | See step 6. The file that pg_upgrade names lists the affected tables. |
password authentication failed for user "unimus" | The password is stored as md5 on a 14+ target. Run ALTER USER ... WITH PASSWORD (step 3). |
unsupported version (1.16) in file header | The dump was made by a newer pg_dump than the target server. Use the target version's pg_dump. |
analyze_new_cluster.sh prints Done but statistics stay empty | The script runs vacuumdb without a port, so it connects to 5432. Run vacuumdb -p <new port> --all --analyze-in-stages yourself. |