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:
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
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 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
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
# 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 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 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.
When ready to switch to PlanetScale 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
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');
- 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;
- 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
Need help?
Get help from the PlanetScale Support team, or join our GitHub discussion board to see how others are using PlanetScale. 
