Postgres Imports - PGDump and Restore

​

Overview

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

• 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
• Ensure the disk on your PlanetScale database has at least 150% of the capacity of your source database. If you are migrating to a PlanetScale database backed by network-attached storage, you can resize your disk manually by setting the “Minimum disk size.” If you are using Metal, you will need to select a size when first creating your database. For example, if your source database is 330GB, you should have at least 500GB of storage available on PlanetScale.
• 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

​

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

​

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:

​

Troubleshooting

​

Common Issues:

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

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

• 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

• 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

​

Next Steps

​

Need help?