Logical replication and Change Data Capture (CDC)

Change Data Capture (CDC) allows you to track and stream data changes from your PostgreSQL database to external systems in real-time. PlanetScale for Postgres supports logical replication, enabling CDC through various tools and integrations.

What is logical replication?

Logical replication is a PostgreSQL feature that replicates data objects and their changes at the logical level, rather than the physical level. It works by:

1. WAL Level: The database must be configured with wal_level = logical to capture logical changes
2. Publication: Creates a logical replication stream on the source database
3. Replication Slot: Maintains position in the WAL stream and ensures data consistency
4. Subscription/Consumer: External tools consume the logical replication stream

This enables:

• Replication of individual transactions and row changes
• Selective replication of specific tables or databases
• Cross-version replication between different PostgreSQL versions
• CDC integration with external tools and data pipelines

Basic configuration requirements

PlanetScale cluster parameters

To enable logical replication on your PlanetScale for Postgres cluster, configure these parameters in the Cluster configuration > Parameters tab:

Parameter	Required Value	Description
wal_level	logical	Enables logical replication in the WAL
max_replication_slots	≥ 1	Sets the maximum number of replication slots
max_wal_senders	≥ 2	Sets the maximum number of WAL sender processes
max_slot_wal_keep_size	> 4GB	Amount of WAL data to retain for replication slots

Verify configuration

Connect to your database and verify the WAL level:

SHOW wal_level;

The result should show logical:

 wal_level
-----------
 logical

Production CDC setup - Critical failover configuration

Before deploying CDC to production, you must configure logical replication failover. Without this, any database failover will break your CDC streams and require manual intervention to restore data pipelines.

Create failover-enabled replication slots

When creating logical replication slots, you must enable the failover option:

-- ✅ CORRECT: Create with failover support (REQUIRED for production)
SELECT pg_create_logical_replication_slot(
  'my_cdc_slot',            -- slot_name
  'pgoutput',               -- plugin
  false,                    -- temporary
  false,                    -- two_phase
  true                      -- failover = true (REQUIRED)
);

-- ❌ INCORRECT: Default slot without failover (will break during failover)
SELECT pg_create_logical_replication_slot('my_cdc_slot', 'pgoutput');

Configure PlanetScale cluster for failover

You must enable these parameters in your cluster configuration:

1. Navigate to Cluster configuration > Parameters
2. Configure these failover settings:

Setting	Required Value	Description
sync_replication_slots	on	Enables synchronization of replication slots to standby servers
hot_standby_feedback	on	Prevents query conflicts during replication
Logical slot name	your_slot_name	The exact name of your logical replication slot

If your CDC tool doesn’t display the slot name, you can find it by running:

SELECT * FROM pg_replication_slots WHERE slot_type='logical' \gx

CDC tool considerations

Ensure your CDC tool is configured properly:

• Airbyte: Ensure replication slots are created with failover support (Setup Guide)
• AWS DMS: Manually create failover-enabled replication slots before configuring DMS (Setup Guide)
• ClickHouse: See ClickPipes documentation for PlanetScale configuration (Setup Guide)
• Debezium: Configure connector to use failover-enabled replication slots (Setup Guide)
• Fivetran: Create your own replication slot with failover = true (Setup Guide)

Some CDC tools create replication slots automatically. You must verify that any auto-created slots have failover = true enabled, or manually create the slots yourself with the proper configuration.

Additional CDC setup considerations

User permissions and security

For production CDC deployments, create a dedicated replication user with minimal privileges:

-- Create dedicated CDC user
CREATE USER cdc_user WITH REPLICATION PASSWORD 'strong_password';

-- Grant SELECT on relevant schemas
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cdc_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO cdc_user;

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO cdc_user;

Publications and table selection

Some CDC tools require you to create publications to specify which tables to replicate:

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

-- Or create publication for specific tables
CREATE PUBLICATION my_cdc_publication FOR TABLE users, orders;

Remember to update your publication when adding new tables that should be replicated. Publications don’t automatically include new tables unless created with FOR ALL TABLES.

Replica identity configuration

For complete change tracking, set replica identity to FULL for tables that need full row data:

-- Enable full replica identity for complete change tracking
ALTER TABLE users REPLICA IDENTITY FULL;
ALTER TABLE orders REPLICA IDENTITY FULL;

Monitoring and troubleshooting

PlanetScale metrics for CDC monitoring

PlanetScale provides built-in metrics that are essential for monitoring your CDC setup. Access these through your Metrics dashboard to track replication health and performance:

Metric Category	Key Indicators for CDC	What to Monitor
WAL archival rate	Success/Failed counts	Monitor failed WAL archival attempts that could impact CDC streams
WAL archive age	Seconds behind	Age of oldest unarchived WAL - should be under 60 seconds for healthy CDC
WAL storage	Storage usage in MB	Track WAL disk usage; high usage may indicate CDC consumers falling behind
Replication lag	Lag in seconds	Monitor delay between primary and replicas; high lag may indicate CDC issues
Transaction rate	Transactions per second	Track database workload intensity affecting CDC processing
Memory	RSS and Memory mapped	Monitor memory pressure that could impact logical decoding performance

For detailed information about interpreting these metrics, see the Cluster Metrics documentation.

Key CDC monitoring indicators

• WAL archival failures: Any failed WAL archival can break CDC streams
• High WAL storage usage: May indicate slow CDC consumers not advancing replication slots
• Increasing replication lag: Could signal CDC consumer performance issues
• High memory usage: Logical decoding can be memory-intensive for high-volume changes

Monitoring replication lag

Check replication slot lag:

SELECT 
    slot_name,
    database,
    active,
    restart_lsn,
    confirmed_flush_lsn,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS replication_lag
FROM pg_replication_slots 
WHERE slot_type = 'logical';

WAL retention and disk usage

Monitor WAL retention to prevent disk space issues:

SELECT 
    slot_name,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Common issues

Issue: WAL disk space growing rapidly
Cause: Inactive or slow CDC consumers
Solution: Remove unused slots or troubleshoot slow consumers

Issue: Failover breaks CDC stream
Cause: Replication slot not properly synchronized
Solution: Verify failover configuration and slot synchronization status

Best practices

1. Always enable failover: Never deploy CDC to production without failover = true on replication slots and proper PlanetScale cluster configuration
2. Verify configuration: Double-check that both your CDC tool and PlanetScale settings are properly configured before going live
3. Test failover scenarios: Test actual failover events in staging environments to ensure your configuration works
4. Regular monitoring: Monitor replication lag, WAL retention, and slot synchronization status
5. Slot cleanup: Remove unused logical replication slots to prevent WAL accumulation
6. CDC client resilience: Ensure CDC clients can handle connection interruptions gracefully

Security considerations

• Logical replication exposes table data - ensure proper access controls
• Use dedicated database users with minimal required privileges for CDC
• Consider network security when streaming to external systems
• Monitor for unauthorized replication slots

For more information about cluster configuration parameters, see the Cluster configuration parameters documentation.