One of the safest and most reliable upgrade methods is to use pg_dump and pg_restore.
This guide walks through the process of migrating your databases from an older PostgreSQL version to PostgreSQL 16.
Prerequisites
– Install PostgreSQL 16 on the target server.
– Ensure sufficient disk space.
– Back up all databases.
– Verify PostgreSQL 16 is initialized and running.
– Use PostgreSQL 16 versions of pg_dump, pg_dumpall and pg_restore.
Set binary path:
export PGPATH=/opt/PostgreSQL/16/bin
Step 1: Dump Global Objects
$PGPATH/pg_dumpall -p <source_port> -U <username> -g > global_objects.sql
Step 2: Back Up Individual Databases
$PGPATH/pg_dump -f <backup_file>.dump -Fc -v -U <database_username> -p <source_port><database_name>
Why custom format (-Fc)?
– Faster restores
– Selective restoration
– Parallel restore support
Step 3: Restore Global Objects
$PGPATH/psql -d postgres -p <target_port> -U <username> -f global_objects.sql
Step 4: Restore the Database
$PGPATH/pg_restore -d <database_name> -Fc -v -p <target_port> -U <username><backup_file>.dump
Create the database first if it does not exist.
Step 5: Speed Up the Restore (Optional)
$PGPATH/pg_restore -d <database_name> -Fc -j 4 -v -p <target_port> -U <username><backup_file>.dump
