Debian PostgreSQL Database Migration Guide

Migrating a PostgreSQL database on Debian involves transferring data from a source server to a target server while ensuring data integrity and minimal downtime. Below is a structured guide covering common methods, precautions, and troubleshooting tips.

Pre-Migration Preparation

1. Install PostgreSQL on Target Server
   Ensure PostgreSQL is installed on the target Debian server. Use the following commands to install the latest version from the official repository:

   sudo apt update
   sudo apt install postgresql postgresql-contrib
   

2. Check Version Compatibility
   Verify that the PostgreSQL versions on the source and target servers are compatible. Major version upgrades (e.g., 13 → 15) may require additional steps like running pg_upgrade. Minor version differences (e.g., 14.5 → 14.7) are usually safe.

3. Backup Source Database
   Always back up the source database before starting. Use pg_dump for logical backups (recommended for most cases):

   sudo -u postgres pg_dump -Fc -b -v -f /path/to/source_backup.dump mydatabase
   

   • -Fc: Custom format (supports compression and parallel restores).
   • -b: Include large objects (e.g., binary files).
   • -v: Verbose mode (shows progress).

   For a full cluster backup (all databases), use pg_dumpall:

   sudo -u postgres pg_dumpall -f /path/to/full_backup.sql
   

Method 1: Logical Migration with pg_dump and pg_restore

This method is ideal for migrating individual databases or when changing database configurations (e.g., encoding, tablespaces).

Step 1: Backup Source Database

Run the pg_dump command on the source server (replace placeholders with actual values):

sudo -u postgres pg_dump -Fc -b -v -f ~/mydatabase_backup.dump mydatabase

Step 2: Transfer Backup to Target Server

Use scp to copy the backup file to the target server:

scp ~/mydatabase_backup.dump user@target_server_ip:/home/user/

Step 3: Restore on Target Server

1. Create a new database on the target server:

   sudo -u postgres createdb mydatabase
   

2. Restore the backup using pg_restore (run as the postgres user):

   sudo -u postgres pg_restore -d mydatabase -v ~/mydatabase_backup.dump
   

   • -d: Target database name.
   • -v: Verbose mode (shows progress).

Step 4: Verify Data

Connect to the target database and run sample queries:

sudo -u postgres psql -d mydatabase -c "SELECT COUNT(*) FROM my_table;"

Method 2: Physical Migration (Data Directory Copy)

This method is faster for large databases but requires downtime. It copies the entire PostgreSQL data directory from the source to the target server.

Step 1: Stop PostgreSQL on Both Servers

Stop the service to ensure data consistency:

sudo systemctl stop postgresql

Step 2: Backup Data Directory on Source Server

The default data directory is /var/lib/postgresql/<version>/main. Copy it to the target server:

sudo rsync -avz /var/lib/postgresql/ user@target_server_ip:/var/lib/postgresql/

Step 3: Adjust Permissions on Target Server

Ensure the postgres user owns the data directory:

sudo chown -R postgres:postgres /var/lib/postgresql/

Step 4: Start PostgreSQL on Target Server

Start the service and verify the migration:

sudo systemctl start postgresql
sudo -u postgres psql -d mydatabase -c "\l"

Method 3: Using pgloader for Heterogeneous Migrations

pgloader supports migrating data from MySQL, Oracle, CSV, and other sources to PostgreSQL. It automates schema conversion and data loading.

Step 1: Install pgloader

On Debian, install via apt:

sudo apt install pgloader

Step 2: Create a Configuration File

Define the source and target databases in a .load file (e.g., migrate.load):

LOAD DATABASE
    FROM mysql://user:password@source_host/source_db
    INTO postgresql://user:password@target_host/target_db
    WITH include drop, create tables, create indexes, reset sequences
    SET maintenance_work_mem to '128MB',
        work_mem to '16MB';

Step 3: Run pgloader

Execute the migration:

pgloader migrate.load

Post-Migration Steps

1. Validate Data Integrity
   Compare row counts, sample data, and constraints between source and target databases.
2. Update Application Configurations
   Change the application’s database connection string to point to the target server.
3. Monitor Performance
   Check CPU, memory, and disk usage on the target server post-migration.
4. Clean Up
   Delete backup files from both servers to free up space.

Troubleshooting Common Issues

• Permission Denied: Ensure the postgres user has read/write access to the data directory and backup files.
• Version Mismatch: Use pg_upgrade for major version upgrades (not covered in this guide).
• Network Errors: Use scp with -C (compression) or split large backups into smaller chunks.
• Missing Extensions: Recreate extensions (e.g., postgis) on the target server after migration.

By following this guide, you can successfully migrate PostgreSQL databases on Debian while minimizing downtime and ensuring data integrity. Always test migrations in a staging environment before applying them to production.