Write-Ahead Logging (WAL) streaming provides a near-zero downtime migration method by continuously replicating changes from your source PostgreSQL database to PlanetScale Postgres using logical replication.

Overview

This migration method involves:

Prerequisites

Before starting the migration:

• PostgreSQL 10+ on the source database (logical replication support)
• Administrative access to source database configuration
• Network connectivity between source and PlanetScale Postgres
• Connection details for your PlanetScale 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.
• Understanding of your application’s write patterns for cutover planning

Step 1: Configure Source Database

Enable logical replication on source database:

Edit your PostgreSQL configuration (postgresql.conf):

Configure authentication (pg_hba.conf):

Add an entry to allow replication connections:

Restart PostgreSQL service:

Step 2: Create Replication User

Connect to your source database and create a replication user:

Step 3: Create Publication on Source

Create a publication for the tables you want to replicate:

Step 4: Get PlanetScale Connection Details

From your PlanetScale console:

Step 5: Create Initial Schema on PlanetScale

Export and import the schema structure:

Step 6: Create replication slot and capture snapshot

Before exporting data, create the replication slot on the source database. This captures a consistent snapshot you will use for export so the dump and replication start point stay aligned with no gaps or duplicates.

Record the snapshot_name value from the output and use it in the next step.

Step 7: Export and import initial data

Use the snapshot captured in Step 6 when running pg_dump so the data export matches the replication slot start position:

After import completes, close the source session that was holding the snapshot.

Step 8: Set up logical replication

Create the subscription on PlanetScale referencing the existing slot from Step 6:

Step 9: Monitor replication

Check replication lag:

Monitor for conflicts:

Step 10: Prepare for cutover

Verify data consistency:

Check replication lag:

Ensure replication lag is minimal (ideally under 1 second) before cutover.

Step 11: Handle sequences

Logical replication synchronizes table data, but it does not synchronize the nextval values for sequences in your database. Sequences are often used for auto-incrementing IDs, so update them on PlanetScale before switching application traffic. You can see all sequences and their corresponding nextval values on your source database using this command:

Example output:

In this example, the database has three sequences used for auto-incrementing primary keys. The nextval for users_id_seq is 105, the nextval for posts_id_seq is 1417, and the nextval for followers_id_seq is 3014. If you run the same query on your PlanetScale database, the sequence values may be behind the source database. If you switch traffic to PlanetScale while sequences are behind, inserts can fail with duplicate key errors:

Before switching over, advance these sequences so the nextval values produced on PlanetScale are greater than any values previously produced on the source database. A simple approach is to run this query on your source database:

This generates queries that advance each sequence by 10,000:

Execute the generated queries on your PlanetScale database. Advance each sequence far enough that the source database will not reach those nextval values before you switch your primary database to PlanetScale. For tables with a high insertion rate, increase the offset to a larger value, such as 100,000 or 1,000,000.

Step 12: Perform cutover

When ready to switch to PlanetScale Postgres:

Verify cutover success:

Step 13: Cleanup (after successful cutover)

Drop subscription on PlanetScale:

Drop publication on source:

Troubleshooting

Common Issues:

Replication slot conflicts:

Permission errors:

• Verify replication user has correct permissions
• Check pg_hba.conf configuration
• Ensure network connectivity

Large transaction delays:

• Monitor for long-running transactions on source
• Consider breaking large operations into smaller batches

Subscription conflicts:

Duplicate key errors immediately after subscription creation: The subscription was likely created without copy_data = false after manual data import. Drop the subscription immediately to stop retry loops:

Clean up leaked replication origins:

Then drop the replication slot on the source, repeat Steps 6-8 with a fresh snapshot, and recreate the subscription with copy_data = false. could not find free replication state slot errors: This is commonly caused by the duplicate key crash loop above. Each unclean worker exit can leak a replication origin until max_active_replication_origins is exhausted. Resolve the duplicate key issue first and run the origin cleanup. If the error persists, contact PlanetScale support.

Performance Considerations

1. Network bandwidth: Ensure sufficient bandwidth for initial sync and ongoing replication
2. Disk I/O: Monitor disk usage on both source and target during replication
3. Replication lag: Keep lag minimal by optimizing source database performance
4. Conflict resolution: Understand how PostgreSQL handles replication conflicts

Schema Considerations

Before migration, review:

Next Steps

After successful migration:

For simpler migrations or if you don’t have administrative access to your source database, consider the pg_dump/restore method. For more complex scenarios, explore Amazon DMS. 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 Discord community to see how others are using PlanetScale.