How to Migrate a PostgreSQL Database to a New Server

Moving a PostgreSQL database to a new Breeze requires exporting data from the source, transferring it, and importing on the destination. This guide covers multiple methods for different scenarios.

Method 1: pg_dump and pg_restore

The most common approach using native PostgreSQL tools:

# On the source server - create a custom-format dump
pg_dump -U postgres -Fc -d mydb -f /tmp/mydb.dump

# Transfer to the new server
scp /tmp/mydb.dump root@new-server:/tmp/

# On the destination server - restore
createdb -U postgres mydb
pg_restore -U postgres -d mydb /tmp/mydb.dump

Method 2: Direct Pipe Transfer

For smaller databases, pipe directly between servers without an intermediate file:

pg_dump -U postgres -d mydb | ssh root@new-server "psql -U postgres -d mydb"

Method 3: pg_basebackup for Full Cluster

To migrate the entire PostgreSQL cluster including all databases and roles:

# On the source - dump global objects
pg_dumpall -U postgres --globals-only -f /tmp/globals.sql

# Transfer and restore globals first
scp /tmp/globals.sql root@new-server:/tmp/
psql -U postgres -f /tmp/globals.sql

Verify the Migration

psql -U postgres -d mydb -c "SELECT count(*) FROM important_table;"
psql -U postgres -d mydb -c "\dt+"

Tips

• Match PostgreSQL versions between source and destination to avoid compatibility issues
• Use --no-owner flag if the role names differ on the new server
• For large databases, use pg_dump -j 4 for parallel dumping to speed things up
• Update your application connection strings to point to the new server after verification