Upgrading your infrastructure is an inevitable part of maintaining a secure and efficient environment. If you're moving from an older to a newer Ubuntu version and need to migrate your PostgreSQL server along with it, this guide will walk you through the process safely and efficiently.

Step 1: Prepare the New Server

Before initiating the migration, install PostgreSQL on the new Ubuntu 24.04 server. Ensure it's the same version as your old server for a smooth transition.

sudo apt update
sudo apt install postgresql

Once installed, stop the PostgreSQL service so it doesn’t interfere with the migration:

sudo systemctl stop postgresql

Step 2: Dump the Data from the Old Server

There are two main ways to back up your PostgreSQL data:

Option A: Logical Backup Using pg_dumpall

This is the simplest method and includes roles and databases.

pg_dumpall -U postgres > full_backup.sql

Option B: Physical Backup Using pg_basebackup

If you have a large database and need an exact replica, this method is more efficient. Note that this requires a replication user.

sudo -u postgres pg_basebackup -h localhost -D /var/lib/postgresql/14/main -U replication_user -P -v --wal-method=stream

Step 3: Transfer the Backup to the New Server

Use scp or a similar tool to copy the backup to the new server:

scp full_backup.sql user@new-server:/tmp/

Step 4: Restore the Backup on the New Server

Switch to the postgres user:

sudo -i -u postgres

Restore the database:

psql < /tmp/full_backup.sql

Step 5: Copy Configuration Files (Optional)

To maintain consistency, copy these configuration files from the old server:

• postgresql.conf
• pg_hba.conf
• pg_ident.conf

Example:

scp /etc/postgresql/14/main/postgresql.conf user@new-server:/etc/postgresql/14/main/

After copying the configuration files, start PostgreSQL:

sudo systemctl start postgresql

Step 6: Verify the Migration

Check that all databases and roles are present:

sudo -u postgres psql -c "\l"
sudo -u postgres psql -c "\du"

Also, test that applications can connect to the new server as expected.

Optional: Upgrade PostgreSQL Version

If your new Ubuntu server ships with a newer PostgreSQL version, you can upgrade after the initial migration. Options include:

• pg_upgrade for in-place upgrades
• Dump and restore using pg_dump and psql

Final Thoughts

This process ensures a reliable migration of PostgreSQL from Ubuntu 20.04 to 24.04 with minimal downtime. Always test in a staging environment before applying to production.