Upgrading to PostgreSQL 16 can unlock new features and performance enhancements. This guide outlines how I successfully migrated all my databases, including rayanpd, from PostgreSQL 14 to PostgreSQL 16 using pg_dumpall and set PostgreSQL 16 as the default version on my system.
1. Preparing for the Migration
Before starting, ensure both PostgreSQL 14 and 16 are installed and running on separate ports. PostgreSQL allows multiple versions to coexist, making upgrades more manageable.
To verify the running versions and their ports:
sudo netstat -tuln | grep 543
In my case:
- PostgreSQL 14 was running on port 5432.
- PostgreSQL 16 was running on port 5433.
2. Migrating Databases with pg_dumpall
To migrate all databases, roles, and global objects, I used pg_dumpall, a utility that dumps the entire database cluster.
Here’s the command I used:
pg_dumpall -p 5432 | psql -d postgres -p 5433
This command exports all databases, roles, and global settings from PostgreSQL 14 and imports them into PostgreSQL 16. If the new PostgreSQL 16 instance is empty, this is a straightforward way to ensure everything is transferred.
because pg_dumpall dumps the entire cluster (all databases, roles, and global settings) as a single script. When restoring this dump, it requires a target database to connect to. By default, the postgres database is used because:
- Default Target Database:
The postgres database is a system database created during the installation of PostgreSQL. It is typically used for administrative tasks and as a connection point when no specific user database is specified. Using it as the target for restoration avoids conflicts with existing user databases. - Cluster-Wide Operations:
The dump script includes CREATE DATABASE commands for each user-defined database. The restoration process starts from the connected database (postgres in this case), and then the script creates and populates the user databases automatically. - Global Objects:
Global objects, such as roles and configurations, are not tied to a specific database. They are applied at the cluster level. Restoring from the postgres database ensures these global objects are processed correctly. - Avoid Overwriting Existing Databases:
Using -d postgres ensures you don't mistakenly overwrite an existing user database when restoring.
Key Steps:
- Check the Target Cluster: Ensure PostgreSQL 16 is ready and listening on port 5433.
- Run the Command: Execute the above pg_dumpall command as a user with sufficient privileges (typically postgres).
- Verify the Migration:
After the import, check the databases on PostgreSQL 16:
psql -p 5433 -l
3. Switching PostgreSQL 16 to Default Port (5432)
Once PostgreSQL 16 was confirmed to work correctly, I switched it to port 5432 and stopped PostgreSQL 14. Here’s how:
- Edit PostgreSQL 16's postgresql.conf:
Locate the configuration file, usually at /etc/postgresql/16/main/postgresql.conf, and update the port setting:
port = 5432
- Restart PostgreSQL 16:
Apply the changes by restarting the service:
sudo systemctl restart postgresql@16-main
- Stop PostgreSQL 14:
sudo systemctl stop postgresql@14-main
- Disable PostgreSQL 14:
Prevent PostgreSQL 14 from starting automatically:
sudo systemctl disable postgresql@14-main
- Verify the New Configuration:
Confirm PostgreSQL 16 is now running on port 5432:
psql -d rayanpd -p 5432
4. Removing PostgreSQL 14
After ensuring PostgreSQL 16 was fully functional, I cleaned up the old version:
- Uninstall PostgreSQL 14:
On Debian/Ubuntu:
sudo apt-get remove --purge postgresql-14
On RHEL/CentOS:sudo yum remove postgresql14
- Delete the Old Data Directory:
Remove the PostgreSQL 14 data directory, typically located at /var/lib/postgresql/14:
sudo rm -rf /var/lib/postgresql/14
5. Final Verification
To ensure everything was working correctly, I connected to PostgreSQL 16 on the default port and verified the migrated databases:
psql -d rayanpd -p 5432
I confirmed all databases, roles, and configurations were intact and operational.
Conclusion
Using pg_dumpall to migrate from PostgreSQL 14 to 16 ensured a smooth and comprehensive transfer of all databases and roles. PostgreSQL 16 is now the default database server on my system, offering improved performance and new features.
If you're planning a similar upgrade, this guide provides a reliable roadmap for a successful migration.