Claude Code for Postgres Logical Replication Workflow

PostgreSQL logical replication is a powerful feature that allows you to replicate data between databases selectively, making it ideal for building distributed systems, read replicas, and data migration pipelines. This guide shows you how to implement and manage PostgreSQL logical replication workflows using Claude Code, with practical examples and actionable advice for production environments.

Understanding PostgreSQL Logical Replication

Logical replication in PostgreSQL differs from physical replication in that it replicates data based on logical changes, not raw binary data. This means you can replicate specific tables, filter rows, and even transform data during replication. Unlike physical replication which requires identical PostgreSQL versions, logical replication offers more flexibility in heterogeneous environments.

The core components of logical replication include the publisher (source database) and subscriber(s) (target databases). The publisher sends changes from publication tables, while subscribers subscribe to these publications and apply changes to their local tables. This architecture enables scenarios like creating read replicas, distributing data across geographic regions, or migrating data between PostgreSQL versions.

Logical replication uses a wal sender process on the publisher and wal receiver on the subscriber, communicating through the streaming protocol. Changes are transmitted as logical change records (LCRs), providing fine-grained control over what gets replicated.

Setting Up Logical Replication

Before configuring logical replication, ensure your PostgreSQL instance has appropriate settings. The wal_level must be set to logical, and you need sufficient max_replication_slots and max_wal_senders. Here’s how to configure these parameters:

-- On the publisher (source database)
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;

-- Reload configuration
SELECT pg_reload_conf();

-- Verify settings
SHOW wal_level;
SHOW max_replication_slots;

Now let’s create a publication on the source database. Publications define which tables are replicated and what operations (INSERT, UPDATE, DELETE) are included:

-- Connect to the publisher database
CREATE DATABASE app_production;

\c app_production

-- Create sample tables
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

-- Or create a selective publication
CREATE PUBLICATION users_pub FOR TABLE users 
    WITH (publish = 'insert, update');

CREATE PUBLICATION orders_pub FOR TABLE orders 
    WITH (publish = 'insert, update, delete');

Configuring the Subscriber

On the subscriber database, you need to create the matching table structures. The tables must have the same columns and data types, though they can have different names if you use column mapping:

-- On the subscriber (target database)
CREATE DATABASE app_analytics;

\c app_analytics

-- Create identical table structures
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create a subscription
CREATE SUBSCRIPTION my_subscription 
    CONNECTION 'host=primary-db.example.com port=5432 dbname=app_production user=repl_user password=secret'
    PUBLICATION my_publication;

Monitoring and Managing Replication

Monitoring is crucial for production environments. PostgreSQL provides system views to track replication status and identify issues:

-- Check subscription status
SELECT * FROM pg_stat_subscription;

-- View replication slots
SELECT * FROM pg_replication_slots;

-- Monitor wal sender status (on publisher)
SELECT * FROM pg_stat_replication;

-- Check for lag
SELECT 
    sub.subname AS subscription_name,
    sub.subenabled AS enabled,
    stat.lag 
FROM pg_stat_subscription stat
JOIN pg_subscription sub ON stat.subid = sub.oid;

For more detailed monitoring, you can query the replication progress:

-- Get detailed replication statistics
SELECT 
    s.subname AS subscription_name,
    r.rolname AS subscriber_role,
    s.subenabled AS enabled,
    s.subpublications AS publications
FROM pg_subscription s
JOIN pg_roles r ON s.subowner = r.oid;

-- Check pending transactions
SELECT * FROM pg_replication_origin_status;

Handling Schema Changes

Schema changes require careful handling in logical replication. PostgreSQL has limitations on what schema changes are automatically replicated:

-- Adding a column is supported
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Renaming a column requires dropping and recreating subscription
-- Step 1: Drop subscription
DROP SUBSCRIPTION my_subscription;

-- Step 2: Make schema change
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Step 3: Recreate subscription
CREATE SUBSCRIPTION my_subscription 
    CONNECTION 'host=primary-db.example.com port=5432 dbname=app_production user=repl_user password=secret'
    PUBLICATION my_publication;

For complex schema migrations, consider using tools like pgloader or custom scripts that handle the migration while maintaining replication integrity.

Troubleshooting Common Issues

Logical replication can encounter several common issues. Here’s how to diagnose and resolve them:

Replication lag: Monitor lag using the views above. High lag may indicate network issues or the subscriber can’t keep up with the publisher:

-- Check for conflicts
SELECT * FROM pg_stat_activity 
WHERE state = 'active' 
  AND query LIKE '%logical replication%';

Subscription errors: View detailed error information:

-- Get last error details
SELECT subname, lasterror, lasterror_timestamp 
FROM pg_stat_subscription 
WHERE lasterror IS NOT NULL;

Slot exhaustion: Ensure replication slots are being properly consumed:

-- Check active slots
SELECT slot_name, plugin, slot_type, database, active 
FROM pg_replication_slots 
WHERE active = true;

Best Practices for Production

When implementing logical replication in production environments, follow these best practices:

First, use dedicated replication users with minimal permissions. The replication role should only have replication permissions, not superuser access. Second, always use SSL connections for replication to protect data in transit. Third, implement proper monitoring and alerting for replication lag and failures. Fourth, test your failover procedures regularly to ensure you can recover quickly when issues occur.

For high availability, consider implementing a cascading replication topology where one subscriber replicates to others, reducing load on the publisher. Additionally, use the synchronous_commit parameter wisely—setting it to on ensures durability but adds latency, while off improves performance at the cost of potential data loss.

Conclusion

PostgreSQL logical replication is an essential tool for building resilient, distributed database architectures. By leveraging Claude Code to generate SQL configurations, create monitoring scripts, and troubleshoot issues, you can streamline the implementation and maintenance of replication workflows. The combination of PostgreSQL’s powerful replication features and AI-assisted development makes it easier than ever to build robust data distribution systems that scale with your application’s needs.

Built by theluckystrike — More at zovo.one