PostgreSQL: 3 Ways to Migrate Data to a New Server

Introduction

When it comes to migrating data to a new PostgreSQL server, several strategies can be employed. Each method has its own pros and cons, and the choice largely depends on factors such as the size of the database, the downtime tolerable, and the level of transactional integrity required. In this guide, we’ll explore a variety of solutions for PostgreSQL data migration.

Using pg_dump and pg_restore

The pg_dump tool is used to export a database to a file, which can then be imported into another server using pg_restore. It’s a straightforward process that is suitable for smaller databases or when minimal downtime is not critical.

Steps:

• Perform a pg_dump to create a backup of the current database.
• Transfer the backup file to the new server host.
• Use pg_restore to import the data into the new server’s database.

Example:

pg_dump -U user_name -h old_host db_name > db_backup.sql
copy db_backup.sql /path/to/new/server
psql -U user_name -h new_host -d db_name -f db_backup.sql

Advantages: Simple to execute; does not require intermediate storage; useful for taking snapshots of the database.

Limitations: Can be slow for large databases; requires downtime during the migration.

Physical File Copy

This method involves copying the physical data directory from the old server to the new server. It requires stopping the database service, but is very quick for large databases.

Steps:

• Stop the old PostgreSQL service to ensure data consistency.
• Copy the data directory to the new server.
• Start PostgreSQL on the new server with the copied data directory.

Example:

service postgresql stop
cp -R /var/lib/postgresql/9.5/main /path/to/new/server/data
cd /path/to/new/server
cp -R /path/to/old/server data
service postgresql start

Advantages: Fast for large databases; requires no intermediate dump file.

Limitations: Requires significant downtime; risks incompatibility between server versions.

Replication Setup

Solution Description: Setting up replication from the old server to the new ensures minimal downtime. The new server, as a standby, gets in sync with the primary, and then you can promote it to be the new primary.

Steps:

• Configure the old server as a master and the new server as a standby.
• Let the data synchronize between the two servers.
• Promote the new server to being a master.

Example:

# On the master server
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET max_wal_senders = 3;
ALTER SYSTEM SET max_replication_slots = 3;
# On the standby server
standby_mode = 'on'
primary_conninfo = 'host=old_host port=5432 user=replicator password=secret keepalives_idle=60 keepalives_interval=5 keepalives_count=5'
trigger_file = '/tmp/postgresql.trigger.5432'

Advantages: Minimal downtime; continuous synchronization until the cutover.

Limitations: More complex to set up; requires extra configuration on both servers.

Conclusion

In summary, migrating PostgreSQL data to a new server can be approached in various ways, each with its own set of pros and cons. The method chosen should align with your priorities, whether they be speed, ease of implementation, or minimizing downtime. Regardless, careful planning and preparation will ensure the most efficient and trouble-free data migration process.