Postgres Imports - WAL Streaming

July 1, 2025

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

Overview

This migration method involves:

Setting up logical replication from your source database
Creating an initial data snapshot
Continuously streaming WAL changes to keep databases synchronized
Performing a quick cutover when ready

Warning

This method requires administrative access to your source PostgreSQL database to configure replication settings.

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 for Postgres
Connection details for your PlanetScale for Postgres database from the console
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):

# Enable logical replication
wal_level = logical

# Set maximum replication slots
max_replication_slots = 10

# Set maximum WAL senders
max_wal_senders = 10

# Enable logical replication workers
max_logical_replication_workers = 10

Configure authentication (`pg_hba.conf`):

Add an entry to allow replication connections:

# Allow replication connections
host replication replication_user source_ip/32 md5

Restart PostgreSQL service:

# On systemd systems
sudo systemctl restart postgresql

# On older systems
sudo service postgresql restart

Step 2: Create Replication User

Connect to your source database and create a replication user:

-- Create replication user
CREATE USER replication_user WITH REPLICATION LOGIN;

-- Grant necessary permissions
GRANT CONNECT ON DATABASE your_database TO replication_user;
GRANT USAGE ON SCHEMA public TO replication_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;

-- Grant permissions for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO replication_user;

Step 3: Create Publication on Source

Create a publication for the tables you want to replicate:

-- Create publication for all tables
CREATE PUBLICATION planetscale_migration FOR ALL TABLES;

-- Or create publication for specific tables
CREATE PUBLICATION planetscale_migration FOR TABLE 
    table1, table2, table3;

-- Verify publication
SELECT * FROM pg_publication;

Step 4: Get PlanetScale Connection Details

From your PlanetScale console:

Navigate to your PlanetScale for Postgres database
Go to the "Connect" section
Copy the connection details including:
- Host
- Port
- Database name
- Username
- Password

Step 5: Create Initial Schema on PlanetScale

Export and import the schema structure:

# Export schema from source
pg_dump -h source-host \
        -p source-port \
        -U source-username \
        -d source-database \
        --schema-only \
        --no-owner \
        --no-privileges \
        -f schema.sql

# Import schema to PlanetScale
psql -h planetscale-host \
     -p planetscale-port \
     -U planetscale-username \
     -d planetscale-database \
     -f schema.sql

Step 6: Create Initial Data Copy

Create an initial data snapshot:

# Export data from source
pg_dump -h source-host \
        -p source-port \
        -U source-username \
        -d source-database \
        --data-only \
        --no-owner \
        --no-privileges \
        -f data.sql

# Import data to PlanetScale
psql -h planetscale-host \
     -p planetscale-port \
     -U planetscale-username \
     -d planetscale-database \
     -f data.sql

Step 7: Set Up Logical Replication

Connect to PlanetScale for Postgres and create subscription:

-- Create subscription to source database
CREATE SUBSCRIPTION planetscale_subscription
    CONNECTION 'host=source-host port=source-port 
                dbname=source-database user=replication_user 
                password=replication-password'
    PUBLICATION planetscale_migration;

-- Check subscription status
SELECT * FROM pg_subscription;

-- Monitor replication status
SELECT * FROM pg_stat_subscription;

Step 8: Monitor Replication

Check replication lag:

-- On source database
SELECT * FROM pg_replication_slots;

-- On PlanetScale database
SELECT 
    subname,
    received_lsn,
    latest_end_lsn,
    latest_end_time
FROM pg_stat_subscription;

Monitor for conflicts:

-- Check for subscription errors
SELECT * FROM pg_stat_subscription 
WHERE last_msg_failure_time IS NOT NULL;

Step 9: Prepare for Cutover

Verify data consistency:

-- Compare row counts between source and target
-- Run on both databases
SELECT 
    schemaname, 
    tablename, 
    n_tup_ins as estimated_rows 
FROM pg_stat_user_tables 
ORDER BY schemaname, tablename;

Check replication lag:

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

Step 10: Perform Cutover

When ready to switch to PlanetScale for Postgres:

Stop application writes to the source database
Wait for replication to catch up (monitor lag)
Update application connection strings to point to PlanetScale
Start application with new connection
Monitor for any issues

Verify cutover success:

-- Check that latest data is present
SELECT count(*), max(updated_at) FROM your_main_table;

Step 11: Cleanup (After Successful Cutover)

Drop subscription on PlanetScale:

DROP SUBSCRIPTION planetscale_subscription;

Drop publication on source:

DROP PUBLICATION planetscale_migration;

Troubleshooting

Common Issues:

Replication slot conflicts:

-- Check existing slots
SELECT * FROM pg_replication_slots;

-- Drop unused slots
SELECT pg_drop_replication_slot('slot_name');

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:

-- Check subscription worker status
SELECT * FROM pg_stat_subscription;

-- Restart subscription if needed
ALTER SUBSCRIPTION planetscale_subscription DISABLE;
ALTER SUBSCRIPTION planetscale_subscription ENABLE;

Performance Considerations

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

Schema Considerations

Before migration, review:

Next Steps

After successful migration:

Monitor replication performance and lag
Test application functionality thoroughly
Set up monitoring and alerting for the new database
Plan for ongoing maintenance and optimization

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 GitHub discussion board to see how others are using PlanetScale.