Postgres Imports - PGDump and Restore

July 1, 2025

The pg_dump and restore method is the simplest approach for migrating PostgreSQL databases to PlanetScale for Postgres. This method is ideal for smaller databases that can tolerate downtime during the migration process.

Overview

This migration method involves:

1. Creating a full backup of your source database using pg_dump
2. Transferring the dump file to your local environment or staging area
3. Restoring the database to PlanetScale for Postgres using pg_restore or psql

Prerequisites

Before starting the migration:

• Ensure you have PostgreSQL client tools installed (pg_dump, pg_restore, psql)
• Have read access to your source PostgreSQL database
• Have connection details for your PlanetScale for Postgres database from the console
• Verify sufficient storage space for the dump file
• Plan for application downtime during the migration

Step 1: Create Database Dump

For a complete database dump:

pg_dump -h source-host \
        -p source-port \
        -U source-username \
        -d source-database \
        --verbose \
        --no-owner \
        --no-privileges \
        -f database_dump.sql

For a custom format dump (recommended for large databases):

pg_dump -h source-host \
        -p source-port \
        -U source-username \
        -d source-database \
        --verbose \
        --no-owner \
        --no-privileges \
        -Fc \
        -f database_dump.dump

Command options explained:

• --verbose: Provides detailed output during the dump process
• --no-owner: Excludes ownership information from the dump
• --no-privileges: Excludes privilege information from the dump
• -Fc: Creates a custom format dump (binary, compressed)
• -f: Specifies the output file name

Step 2: Get PlanetScale Connection Details

From your PlanetScale console:

1. Navigate to your PlanetScale for Postgres database
2. Go to the "Connect" section
3. Copy the connection details including:
   • Host
   • Port
   • Database name
   • Username
   • Password

Step 3: Restore to PlanetScale for Postgres

For SQL format dumps:

psql -h planetscale-host \
     -p planetscale-port \
     -U planetscale-username \
     -d planetscale-database \
     -f database_dump.sql

For custom format dumps:

pg_restore -h planetscale-host \
           -p planetscale-port \
           -U planetscale-username \
           -d planetscale-database \
           --verbose \
           --no-owner \
           --no-privileges \
           database_dump.dump

For parallel restoration (faster for large databases):

pg_restore -h planetscale-host \
           -p planetscale-port \
           -U planetscale-username \
           -d planetscale-database \
           --verbose \
           --no-owner \
           --no-privileges \
           --jobs=4 \
           database_dump.dump

Step 4: Verify Migration

After the restore completes, verify your migration:

Check table counts:

SELECT schemaname, tablename, n_tup_ins as row_count 
FROM pg_stat_user_tables 
ORDER BY schemaname, tablename;

Verify data integrity:

-- Check a few sample records from key tables
SELECT count(*) FROM your_main_table;
SELECT * FROM your_main_table LIMIT 5;

Check for errors in logs:

Review the output from the pg_restore command for any errors or warnings.

Troubleshooting

Common Issues:

Permission errors:

• Ensure your PlanetScale user has appropriate permissions
• Check that connection details are correct

Extension errors:

• Some PostgreSQL extensions may not be available in PlanetScale for Postgres
• Review the extension compatibility guide

Large object errors:

• If using large objects (BLOBs), add --blobs flag to pg_dump

pg_dump --blobs -h source-host -U source-username -d source-database -f database_dump.sql

Timeout errors:

• For large databases, consider breaking the dump into smaller chunks
• Use custom format with parallel restoration

Performance Tips:

1. Use custom format: Binary format with compression is more efficient
2. Parallel jobs: Use --jobs parameter for faster restoration
3. Network considerations: Ensure stable network connection for large transfers
4. Disk space: Monitor available disk space during dump creation

Schema Considerations

Before migration, review:

• PostgreSQL version compatibility
• Extension support limitations
• Third-party enhancement restrictions

Next Steps

After successful migration:

1. Update your application connection strings to point to PlanetScale for Postgres
2. Test your application thoroughly in a staging environment
3. Plan your production cutover
4. Monitor performance and optimize as needed

For more advanced migration scenarios or larger databases, consider WAL streaming or Amazon DMS methods.

If you encounter issues during migration, please reach out to support for assistance.

Need help?

Get help from the PlanetScale Support team, or join our GitHub discussion board to see how others are using PlanetScale.