pglogical: Complete Guide to PostgreSQL Logical Replication

Table of Contents

Introduction

pglogical is a powerful PostgreSQL extension that provides logical streaming replication using a publish/subscribe model. Developed by 2ndQuadrant (now part of EnterpriseDB), it enables selective replication of tables, columns, and rows between PostgreSQL instances, offering flexibility that traditional physical replication cannot provide.

Unlike physical replication, which replicates entire database clusters at the binary level, pglogical operates at the logical level, allowing you to:

Replicate specific tables rather than entire databases
Upgrade between major PostgreSQL versions
Merge data from multiple sources
Implement two-way replication with conflict detection
Selectively replicate specific columns or rows

What is pglogical?

pglogical 2 is a logical replication extension that builds upon PostgreSQL’s native logical decoding capabilities. It was developed as part of the BDR (Bi-Directional Replication) project and has evolved into a mature, production-ready solution.

Key Features

Selective Replication

Replicate specific tables, not entire databases
Choose which columns to replicate per table
Filter rows based on custom conditions
Organize tables into replication sets

Version Flexibility

Works with PostgreSQL 9.4 and later (9.5+ recommended)
Supports major version upgrades
Compatible with Postgres-XL (as subscriber only)

Multi-Source Capabilities

One provider can feed multiple subscribers
One subscriber can merge changes from multiple providers
Cascading replication through changeset forwarding
Conflict detection and resolution

Operational Benefits

Minimal overhead on the provider
Database-level replication (not server-level)
Synchronous replication support
Online schema synchronization options

Architecture and Key Concepts

Terminology

Understanding pglogical’s terminology is crucial:

Node: A PostgreSQL database instance configured for pglogical. Each database is a separate node.

Provider (Publisher): A node that provides data changes to other nodes. It’s the source of replication.

Subscriber (Consumer): A node that receives and applies changes from providers.

Replication Set: A named collection of tables that defines what gets replicated. Tables must be explicitly added to replication sets.

Subscription: A subscriber’s connection to a provider, specifying which replication sets to consume.

Origin: The source node where a change originally occurred, important for conflict resolution and cascading replication.

How It Works

Change Capture: pglogical uses PostgreSQL’s logical decoding to capture changes (INSERT, UPDATE, DELETE, TRUNCATE) from the WAL (Write-Ahead Log).
Filtering: Changes are filtered based on replication set membership, row filters, and column selections.
Transmission: Filtered changes are sent to subscribers through logical replication slots.
Application: Subscribers apply changes using either internal low-level interfaces or SPI (SQL interface).
Conflict Detection: If enabled, conflicts are detected and resolved based on configured strategies.

Replication Flow

Provider Database
    ↓
Logical Decoding (WAL)
    ↓
Replication Set Filtering
    ↓
Replication Slot
    ↓
Network (PostgreSQL Protocol)
    ↓
Subscriber Worker Process
    ↓
Conflict Resolution (if applicable)
    ↓
Apply Changes
    ↓
Subscriber Database

Requirements and Installation

System Requirements

PostgreSQL Version

Provider: PostgreSQL 9.4 or later
Subscriber: PostgreSQL 9.4 or later, or Postgres-XL 9.5+
For full feature support (conflict resolution): PostgreSQL 9.5+

Configuration Prerequisites

wal_level = 'logical'
max_worker_processes = 10      # 1 per database on provider, 1 per node on subscriber
max_replication_slots = 10     # 1 per node on provider
max_wal_senders = 10           # 1 per node on provider
shared_preload_libraries = 'pglogical'

# Optional: for conflict resolution with last/first update wins
track_commit_timestamp = on    # PostgreSQL 9.5+ only

Network Requirements

Subscribers must be able to connect to providers via standard PostgreSQL connections
pg_hba.conf must allow replication connections:
PostgreSQL 9.4-9.6: Use replication keyword in pg_hba.conf
PostgreSQL 10+: Use normal database connections

Permissions

Superuser privileges required for setup and management
Tables must have PRIMARY KEY or REPLICA IDENTITY

Installation Methods

Installing on Red Hat/CentOS/Fedora

Prerequisites

# Install PGDG repository
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL (example for PostgreSQL 16)
sudo yum install postgresql16-server postgresql16-contrib

Install pglogical

# For PostgreSQL 16
sudo yum install pglogical_16

# For other versions, replace 16 with your version (10, 11, 12, 13, 14, 15, 17, 18)

Installing on Debian/Ubuntu

Prerequisites

# Add PostgreSQL apt repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

# Install PostgreSQL (example for PostgreSQL 16)
sudo apt-get install postgresql-16

Install pglogical

# For PostgreSQL 16
sudo apt-get install postgresql-16-pglogical

# For other versions, replace 16 with your version (10, 11, 12, 13, 14, 15, 17, 18)

Installing from Source

# Prerequisites:  PostgreSQL development packages
sudo yum install postgresql16-devel  # RHEL/CentOS
# or
sudo apt-get install postgresql-server-dev-16  # Debian/Ubuntu

# Clone and build
git clone https://github.com/2ndQuadrant/pglogical.git
cd pglogical
PATH=/usr/pgsql-16/bin:$PATH make clean all
sudo PATH=/usr/pgsql-16/bin:$PATH make install

Initial Configuration

1. Edit postgresql.conf

# Required settings
wal_level = 'logical'
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'

# Optional: for advanced conflict resolution
track_commit_timestamp = on

2. Configure pg_hba.conf

# PostgreSQL 10+ (use database name)
host    mydb    replication_user    subscriber_ip/32    md5

# PostgreSQL 9.4-9.6 (use 'replication' keyword)
host    replication    replication_user    subscriber_ip/32    md5

3. Restart PostgreSQL

sudo systemctl restart postgresql-16

4. Create Extension

-- On both provider and subscriber
CREATE EXTENSION pglogical;

-- For PostgreSQL 9.4 only, also install:
CREATE EXTENSION pglogical_origin;

Initial Setup and Configuration

Quick Setup Example

This example sets up replication from a provider database to a subscriber database.

Provider Setup

-- Connect to provider database
\c provider_db

-- Create the provider node
SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=provider. example.com port=5432 dbname=provider_db user=replication_user password=secret'
);

-- Add all tables in public schema to default replication set
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

-- Or add specific tables
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.users',
    synchronize_data := true
);

SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.orders',
    synchronize_data := true
);

Subscriber Setup

-- Connect to subscriber database
\c subscriber_db

-- Create the subscriber node
SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=subscriber.example.com port=5432 dbname=subscriber_db user=postgres'
);

-- Create subscription to provider
SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=provider.example.com port=5432 dbname=provider_db user=replication_user password=secret',
    replication_sets := ARRAY['default'],
    synchronize_structure := false,  -- Don't copy schema
    synchronize_data := true         -- Copy existing data
);

-- Wait for synchronization to complete
SELECT pglogical.wait_for_subscription_sync_complete('subscription1');

Understanding the Parameters

create_node parameters:

node_name: Unique identifier for this node
dsn: Connection string OTHER nodes use to connect to THIS node

create_subscription parameters:

subscription_name: Unique name for this subscription (appears in pg_stat_replication)
provider_dsn: Connection string to connect to the provider
replication_sets: Array of replication sets to subscribe to
synchronize_structure: Whether to copy table structures (DDL)
synchronize_data: Whether to copy existing data
forward_origins: Controls cascading replication
ARRAY[]:: text[]: Don’t forward changes from other sources (two-way replication)
ARRAY['all']: Forward all changes (cascading replication)
apply_delay: Delay replication by specified interval (e.g., '5 minutes':: interval)

Node and Subscription Management

Node Management

Creating Nodes

-- Create a node
SELECT pglogical.create_node(
    node_name := 'node1',
    dsn := 'host=192.168.1.10 port=5432 dbname=mydb user=repl_user'
);

Adding Alternative Interfaces

Useful for nodes accessible via multiple network paths:

-- Add interface for internal network
SELECT pglogical.alter_node_add_interface(
    node_name := 'provider1',
    interface_name := 'internal',
    dsn := 'host=10.0.0.5 port=5432 dbname=mydb user=repl_user'
);

-- Add interface for VPN
SELECT pglogical.alter_node_add_interface(
    node_name := 'provider1',
    interface_name := 'vpn',
    dsn := 'host=vpn.provider.com port=5432 dbname=mydb user=repl_user'
);

Switching Interfaces

-- Switch subscription to use different interface
SELECT pglogical.alter_subscription_interface(
    subscription_name := 'subscription1',
    interface_name := 'internal'
);

Removing Nodes

-- Drop a node (use carefully!)
SELECT pglogical.drop_node(
    node_name := 'old_node',
    ifexists := true
);

Subscription Management

Creating Subscriptions

Basic Subscription

SELECT pglogical.create_subscription(
    subscription_name := 'sub1',
    provider_dsn := 'host=provider. com port=5432 dbname=mydb'
);

Advanced Subscription with Options

SELECT pglogical.create_subscription(
    subscription_name := 'sub_advanced',
    provider_dsn := 'host=provider.com port=5432 dbname=mydb user=repl_user password=secret sslmode=require',
    replication_sets := ARRAY['default', 'audit_tables'],
    synchronize_structure := false,
    synchronize_data := true,
    forward_origins := ARRAY[]::text[],  -- For bidirectional replication
    apply_delay := '1 hour':: interval,   -- Delayed replica
    force_text_transfer := false
);

Disabling/Enabling Subscriptions

-- Disable subscription (stop replication)
SELECT pglogical.alter_subscription_disable(
    subscription_name := 'sub1',
    immediate := true  -- Stop immediately, not at transaction end
);

-- Enable subscription (resume replication)
SELECT pglogical.alter_subscription_enable(
    subscription_name := 'sub1',
    immediate := true
);

Dropping Subscriptions

-- Remove subscription completely
SELECT pglogical.drop_subscription(
    subscription_name := 'sub1',
    ifexists := true
);

Synchronizing Tables

Synchronize All Tables

-- Synchronize all tables in all subscribed replication sets
SELECT pglogical. alter_subscription_synchronize(
    subscription_name := 'sub1',
    truncate := false  -- Don't truncate before sync
);

-- Wait for completion
SELECT pglogical.wait_for_subscription_sync_complete('sub1');

Resynchronize Single Table

-- Resynchronize one table (WARNING: truncates immediately!)
SELECT pglogical.alter_subscription_resynchronize_table(
    subscription_name := 'sub1',
    relation := 'public.users'
);

-- Wait for this table to complete
SELECT pglogical. wait_for_table_sync_complete('sub1', 'public.users');

Managing Replication Sets in Subscriptions

-- Add replication set to existing subscription
SELECT pglogical.alter_subscription_add_replication_set(
    subscription_name := 'sub1',
    replication_set := 'new_set'
);

-- Remove replication set from subscription
SELECT pglogical.alter_subscription_remove_replication_set(
    subscription_name := 'sub1',
    replication_set := 'old_set'
);

Replication Sets

Replication sets are the core mechanism for controlling what gets replicated. They define collections of tables and which operations (INSERT, UPDATE, DELETE, TRUNCATE) are replicated.

Pre-defined Replication Sets

pglogical comes with three built-in replication sets:

default: Replicates all DML operations (INSERT, UPDATE, DELETE, TRUNCATE)
default_insert_only: Only replicates INSERTs (for tables without PRIMARY KEY)
ddl_sql: For DDL replication via pglogical. replicate_ddl_command()

Creating Custom Replication Sets

-- Create a replication set for audit tables
SELECT pglogical.create_replication_set(
    set_name := 'audit_tables',
    replicate_insert := true,
    replicate_update := true,
    replicate_delete := false,    -- Don't replicate deletes
    replicate_truncate := false   -- Don't replicate truncates
);

-- Create an insert-only set for logging tables
SELECT pglogical.create_replication_set(
    set_name := 'logs',
    replicate_insert := true,
    replicate_update := false,
    replicate_delete := false,
    replicate_truncate := false
);

Modifying Replication Sets

-- Change replication set behavior
SELECT pglogical.alter_replication_set(
    set_name := 'audit_tables',
    replicate_delete := true  -- Now replicate deletes too
);

Adding Tables to Replication Sets

Add Single Table

-- Add table with all columns
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.users',
    synchronize_data := true  -- Copy existing data to subscribers
);

-- Add table with specific columns only
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.users',
    synchronize_data := true,
    columns := ARRAY['id', 'username', 'email', 'created_at']  -- Exclude sensitive columns
);

-- Add table with row filter
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.orders',
    synchronize_data := true,
    row_filter := 'status = ''completed'' AND total > 100'  -- Only replicate large completed orders
);

WARNING: Using synchronize_data := true with a row_filter is a one-time operation. Changing the filter later won’t automatically resynchronize data. You’ll need to manually call pglogical.alter_subscription_resynchronize_table() on the subscriber.

Add All Tables in Schema(s)

-- Add all tables from specific schemas
SELECT pglogical.replication_set_add_all_tables(
    set_name := 'default',
    schema_names := ARRAY['public', 'sales', 'inventory'],
    synchronize_data := true
);

Removing Tables from Replication Sets

-- Remove table from replication set
SELECT pglogical.replication_set_remove_table(
    set_name := 'default',
    relation := 'public.temp_table'
);

Managing Sequences

Sequences can also be replicated (though not in real-time):

-- Add single sequence
SELECT pglogical.replication_set_add_sequence(
    set_name := 'default',
    relation := 'public.users_id_seq',
    synchronize_data := true
);

-- Add all sequences from schemas
SELECT pglogical.replication_set_add_all_sequences(
    set_name := 'default',
    schema_names := ARRAY['public'],
    synchronize_data := true
);

-- Remove sequence
SELECT pglogical.replication_set_remove_sequence(
    set_name := 'default',
    relation := 'public.old_sequence'
);

-- Force sequence synchronization (run on provider)
SELECT pglogical.synchronize_sequence('public.users_id_seq');

Automatic Table Assignment with Event Triggers

Automatically assign new tables to replication sets:

-- Create function to assign tables
CREATE OR REPLACE FUNCTION pglogical_assign_repset()
RETURNS event_trigger AS $$
DECLARE 
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF obj.object_type = 'table' THEN
            IF obj.schema_name = 'audit' THEN
                -- Tables in audit schema go to audit_tables set
                PERFORM pglogical.replication_set_add_table('audit_tables', obj.objid);
            ELSIF obj.schema_name = 'config' THEN
                -- Tables in config schema go to configuration set
                PERFORM pglogical. replication_set_add_table('configuration', obj.objid);
            ELSIF NOT obj.in_extension THEN
                -- All other non-extension tables go to default set
                PERFORM pglogical.replication_set_add_table('default', obj.objid);
            END IF;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Create event trigger
CREATE EVENT TRIGGER pglogical_assign_repset_trg
    ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')
    EXECUTE FUNCTION pglogical_assign_repset();

Viewing Replication Set Contents

-- View all tables in replication sets
SELECT * FROM pglogical.tables;

-- Filter by replication set
SELECT nspname, relname, set_name 
FROM pglogical.tables 
WHERE set_name = 'default';

Monitoring and Status Checking

Effective monitoring is crucial for maintaining healthy replication. Here are essential queries for checking pglogical status and metrics.

Basic Subscription Status

Check All Subscriptions

SELECT subscription_name, status 
FROM pglogical.show_subscription_status();

Expected output:

 subscription_name |   status    
-------------------+-------------
 subscription1     | replicating
 subscription2     | replicating

Status Values:

replicating: Normal operation, changes are being applied
initializing: Subscription is being set up
sync_data: Initial data synchronization in progress
sync_structure: Schema synchronization in progress
down: Replication is not working (requires troubleshooting)
disabled: Subscription has been manually disabled

Detailed Subscription Information

View All Subscription Details

SELECT * FROM pglogical.subscription;

This shows:

subscription_id
subscription_name
provider node information
enabled/disabled state
replication sets
forward origins
apply delay settings

Check Specific Subscription Status

SELECT * FROM pglogical.show_subscription_status('subscription1');

This provides detailed status including:

Overall status
Provider node information
Replication lag
Latest received LSN
Worker status

Table-Level Monitoring

List All Replicated Tables

SELECT * FROM pglogical.tables;

Shows:

Schema and table name
Replication set membership
Column filtering
Row filtering expressions

Check Table Synchronization Status

SELECT * FROM pglogical.show_subscription_table(
    'subscription1', 
    'public.users'
);

Status values for tables:

synced: Table is fully synchronized and replicating
replicating: Table is replicating changes
synchronized: Initial sync complete
copy: Currently copying table data
init: Initialization phase

List All Tables with Sync Status

SELECT 
    s.subscription_name,
    t.nspname || '.' || t.relname AS table_name,
    st.status
FROM pglogical.subscription s
CROSS JOIN pglogical.tables t
LEFT JOIN pglogical.show_subscription_table(s.subscription_name, t.relname:: regclass) st 
    ON true
WHERE t.set_name = ANY(s.replication_sets)
ORDER BY s.subscription_name, table_name;

Replication Lag Monitoring

Check Replication Lag (Run on Provider)

SELECT 
    application_name AS subscription_name,
    client_addr AS subscriber_ip,
    state,
    sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS pending_bytes,
    pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag_bytes,
    pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag_bytes,
    pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication
WHERE application_name IN (
    SELECT subscription_name FROM pglogical.subscription
)
ORDER BY subscription_name;

Monitor Apply Worker Progress (Run on Subscriber)

SELECT 
    subscription_name,
    received_lsn,
    last_msg_send_time,
    last_msg_receipt_time,
    latest_end_lsn,
    latest_end_time
FROM pglogical.show_subscription_status();

Replication Slot Monitoring

Check Replication Slots (Run on Provider)

SELECT 
    slot_name,
    slot_type,
    database,
    active,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_size,
    restart_lsn,
    confirmed_flush_lsn
FROM pg_replication_slots
WHERE plugin = 'pglogical_output'
ORDER BY retained_bytes DESC;

This helps identify:

Inactive slots retaining WAL
Slots lagging behind
Disk space usage by WAL retention

Worker Process Monitoring

Check pglogical Worker Processes

SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    backend_start,
    state_change,
    query
FROM pg_stat_activity
WHERE application_name LIKE 'pglogical%'
   OR query LIKE '%pglogical%';

Conflict Monitoring

View Recent Conflicts (PostgreSQL 10+)

-- Check logs for conflict messages
SELECT * FROM pg_read_file('log/postgresql-2025-12-16.log')
WHERE message LIKE '%conflict%';

Conflicts appear in PostgreSQL logs with the configured pglogical. conflict_log_level.

Comprehensive Health Check Query

-- Combined health check (run on subscriber)
WITH subscription_stats AS (
    SELECT * FROM pglogical.show_subscription_status()
),
replication_lag AS (
    SELECT 
        application_name,
        pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes,
        replay_lag
    FROM pg_stat_replication
)
SELECT 
    ss.subscription_name,
    ss.status,
    ss.provider_node,
    ss.replication_sets,
    rl.lag_bytes,
    rl.replay_lag,
    CASE 
        WHEN ss.status != 'replicating' THEN 'CRITICAL'
        WHEN rl. lag_bytes > 100000000 THEN 'WARNING'  -- >100MB lag
        ELSE 'OK'
    END AS health_status
FROM subscription_stats ss
LEFT JOIN replication_lag rl ON rl.application_name = ss. subscription_name;

Monitoring Queries for Alerting

Detect Down Subscriptions

SELECT subscription_name
FROM pglogical.show_subscription_status()
WHERE status != 'replicating';

Detect High Replication Lag (Run on Provider)

SELECT 
    application_name,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication
WHERE pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) > 100000000  -- 100MB
  AND application_name IN (SELECT subscription_name FROM pglogical. subscription);

Detect Inactive Replication Slots Consuming Disk

SELECT 
    slot_name,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE active = false
  AND plugin = 'pglogical_output'
  AND pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1000000000;  -- >1GB

Useful Management Queries

Add Table to Replication Set with Subset of Columns

SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.users',
    synchronize_data := true,
    columns := ARRAY['id', 'username', 'email', 'created_at']
);

Drop Subscription Cleanly

SELECT pglogical.drop_subscription(
    subscription_name := 'old_subscription'
);

Disable Subscription Temporarily

SELECT pglogical.alter_subscription_disable('subscription1');

Add Replication Set to Existing Subscription

-- Note: This does NOT synchronize data, only activates event consumption
SELECT pglogical.alter_subscription_add_replication_set(
    'subscription1', 
    'new_replication_set'
);

Remove Replication Set from Subscription

SELECT pglogical.alter_subscription_remove_replication_set(
    'subscription1',
    'old_replication_set'
);

Advanced Features

Row Filtering

Row filtering allows you to replicate only specific rows based on conditions.

Provider-Side Row Filtering

-- Only replicate active users
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.users',
    synchronize_data := true,
    row_filter := 'status = ''active'''
);

-- Only replicate recent orders
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.orders',
    synchronize_data := true,
    row_filter := 'created_at > CURRENT_DATE - INTERVAL ''30 days'''
);

-- Complex filtering
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.transactions',
    synchronize_data := true,
    row_filter := 'amount > 1000 AND currency = ''USD'' AND status IN (''completed'', ''pending'')'
);

Important Notes on Row Filtering:

Runs in the replication session context
Can use volatile functions (but avoid writes!)
CURRENT_USER reflects the replication user, not the original user
Changing filters requires manual resynchronization
Use synchronize_data := true carefully with filters

Subscriber-Side Row Filtering

Use BEFORE TRIGGER on the subscriber:

CREATE OR REPLACE FUNCTION filter_replicated_rows()
RETURNS TRIGGER AS $$
BEGIN
    -- Only accept rows for specific regions
    IF NEW.region != 'US-EAST' THEN
        RETURN NULL;  -- Skip this row
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER filter_users_replication
    BEFORE INSERT OR UPDATE ON public.users
    FOR EACH ROW
    EXECUTE FUNCTION filter_replicated_rows();

-- CRITICAL: Enable for replication
ALTER TABLE public.users 
    ENABLE REPLICA TRIGGER filter_users_replication;

Column Filtering

Replicate only specific columns:

-- Replicate users but exclude sensitive data
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.users',
    synchronize_data := true,
    columns := ARRAY['id', 'username', 'email', 'created_at', 'updated_at']
    -- Excludes:  password_hash, ssn, credit_card, etc.
);

-- Replicate orders with limited financial data
SELECT pglogical.replication_set_add_table(
    set_name := 'analytics',
    relation := 'public.orders',
    synchronize_data := true,
    columns := ARRAY['id', 'customer_id', 'order_date', 'status', 'item_count']
    -- Excludes:  payment_method, billing_address, etc.
);

DDL Replication

DDL (Data Definition Language) is not automatically replicated. Use pglogical.replicate_ddl_command():

-- Add a column
SELECT pglogical.replicate_ddl_command(
    command := 'ALTER TABLE public.users ADD COLUMN phone_number VARCHAR(20)',
    replication_sets := ARRAY['ddl_sql']
);

-- Create an index
SELECT pglogical.replicate_ddl_command(
    command := 'CREATE INDEX idx_users_email ON public.users(email)',
    replication_sets := ARRAY['ddl_sql']
);

-- More complex DDL
SELECT pglogical.replicate_ddl_command(
    command := $$
        CREATE TABLE public.audit_log (
            id SERIAL PRIMARY KEY,
            table_name TEXT NOT NULL,
            action TEXT NOT NULL,
            changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
    $$,
    replication_sets := ARRAY['ddl_sql']
);

Best Practices for DDL:

Test DDL in development first
Ensure all subscribers are caught up (pglogical.wait_slot_confirm_lsn())
Use transactions for multiple related DDL statements
Make changes backward-compatible when possible
Add columns with defaults carefully (can cause performance issues)

Synchronous Replication

pglogical supports PostgreSQL’s synchronous replication:

# On provider postgresql.conf
synchronous_standby_names = 'subscription1,subscription2'
synchronous_commit = 'remote_apply'  # or 'remote_write', 'on', 'local'

-- Set per-session
SET synchronous_commit = 'remote_apply';

-- Or per-transaction
BEGIN;
SET LOCAL synchronous_commit = 'remote_apply';
INSERT INTO critical_data VALUES (...);
COMMIT;

Levels:

local: Wait for local WAL write only (async replication)
on: Wait for local WAL write and flush
remote_write: Wait until subscriber receives but doesn’t apply
remote_apply: Wait until subscriber applies change (strongest guarantee)

Cascading Replication

Set up multi-tier replication:

Provider (A)
    ↓
Subscriber (B) - also acts as provider
    ↓
Subscriber (C)

Setup Node B (middle tier)

-- B subscribes to A with forwarding enabled
SELECT pglogical.create_subscription(
    subscription_name := 'from_A',
    provider_dsn := 'host=nodeA. com port=5432 dbname=mydb',
    forward_origins := ARRAY['all']  -- Forward all changes
);

-- B acts as provider for C
SELECT pglogical.create_node(
    node_name := 'nodeB',
    dsn := 'host=nodeB.com port=5432 dbname=mydb'
);

Setup Node C

-- C subscribes to B
SELECT pglogical.create_subscription(
    subscription_name := 'from_B',
    provider_dsn := 'host=nodeB.com port=5432 dbname=mydb'
);

Delayed Replication

Create a delayed replica for point-in-time recovery:

SELECT pglogical.create_subscription(
    subscription_name := 'delayed_replica',
    provider_dsn := 'host=provider.com port=5432 dbname=mydb',
    apply_delay := '4 hours':: interval
);

Use cases:

Protection against accidental data deletion
Logical point-in-time recovery
Testing with recent production data

Using pglogical_create_subscriber

For fast subscriber initialization using pg_basebackup:

pglogical_create_subscriber \
    --provider-dsn="host=provider. com port=5432 dbname=mydb user=postgres" \
    --subscriber-dsn="host=subscriber. com port=5432 dbname=mydb user=postgres" \
    --subscriber-name="subscriber2" \
    --replication-sets="default,audit_tables"

Benefits:

Much faster than table-by-table sync
Copies all databases
Useful for high-bandwidth, low-latency networks

Limitations:

Copies everything, ignores replication sets during copy
Subscriber must be a fresh PostgreSQL instance

Conflict Resolution

Conflicts occur when the same row is modified on multiple nodes or when subscriber has local writes.

Conflict Types

INSERT-INSERT: Same primary key inserted on both nodes
UPDATE-UPDATE: Same row updated on both nodes
UPDATE-DELETE: Row updated on one node, deleted on another
DELETE-DELETE: Row deleted on both nodes
FOREIGN KEY: Change violates foreign key constraint

Configuration

Set resolution strategy in postgresql.conf:

pglogical.conflict_resolution = 'apply_remote'  # or other values
pglogical.conflict_log_level = 'LOG'            # Logging level for conflicts

Resolution Strategies

error (Stop on Conflict)

pglogical.conflict_resolution = 'error'

Replication stops when conflict detected
Requires manual intervention
Safest option when data consistency is critical
Use for single-writer scenarios

apply_remote (Always Accept Remote)

pglogical.conflict_resolution = 'apply_remote'

Always apply the incoming change
Default setting
Simple and predictable
Good for single-writer or master-slave scenarios

keep_local (Always Keep Local)

pglogical.conflict_resolution = 'keep_local'

Always keep the local version
Requires track_commit_timestamp = on (PostgreSQL 9.5+)
Useful when subscriber writes should take precedence
Good for scenarios with subscriber-specific data

last_update_wins (Newest Timestamp Wins)

pglogical.conflict_resolution = 'last_update_wins'
track_commit_timestamp = on  # Required

Keeps the version with the newest commit timestamp
Requires track_commit_timestamp = on (PostgreSQL 9.5+)
Most common for multi-master scenarios
Good balance between automation and data preservation

first_update_wins (Oldest Timestamp Wins)

pglogical.conflict_resolution = 'first_update_wins'
track_commit_timestamp = on  # Required

Keeps the version with the oldest commit timestamp
Requires track_commit_timestamp = on (PostgreSQL 9.5+)
“First come, first served” approach
Less common but useful for certain workflows

Conflict Logging

Conflicts are logged according to pglogical.conflict_log_level:

# Log all conflicts
pglogical. conflict_log_level = 'LOG'

# Only log warnings
pglogical.conflict_log_level = 'WARNING'

# Suppress conflict logging
pglogical.conflict_log_level = 'DEBUG5'

Example log entry:

LOG:   CONFLICT:  remote UPDATE on relation public.users (local index idx_users_pkey). Resolution: apply_remote. 
DETAIL:  existing local tuple (1, 'john', '[email protected]') remote tuple (1, 'john', '[email protected]')

Avoiding Conflicts

1. Single-Writer Design

Have only one node accept writes per table
Use read replicas for reporting
Simplest and most reliable

2. Partition Data by Node

-- Node A handles US customers
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.customers',
    row_filter := 'country = ''US'''
);

-- Node B handles EU customers
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'public.customers',
    row_filter := 'country IN (''UK'', ''DE'', ''FR'')'
);

3. Application-Level Coordination

Use distributed locks
Implement application-level conflict avoidance
Use UUIDs for primary keys to avoid INSERT-INSERT conflicts

4. Use SERIAL with Offset

-- Node A:  Start at 1, increment by 100
CREATE SEQUENCE users_id_seq INCREMENT BY 100 START WITH 1;

-- Node B: Start at 2, increment by 100
CREATE SEQUENCE users_id_seq INCREMENT BY 100 START WITH 2;

-- Node C: Start at 3, increment by 100
CREATE SEQUENCE users_id_seq INCREMENT BY 100 START WITH 3;

Handling Foreign Key Conflicts

Foreign keys are not enforced during replication:

-- On provider: Delete parent (cascade to children)
DELETE FROM orders WHERE customer_id = 123;

-- On subscriber: Only parent delete is replicated
-- Children may still exist, violating FK

-- Solution 1: Use ON DELETE CASCADE
ALTER TABLE order_items
    ADD CONSTRAINT fk_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;

-- Solution 2: Replicate DELETE operations explicitly
-- or use trigger to enforce consistency

Limitations and Best Practices

Critical Limitations

1. Superuser Required

All pglogical operations require superuser privileges
Plan for security implications in production

2. Primary Key or Replica Identity Required

-- Won't replicate UPDATE/DELETE without PK
CREATE TABLE bad_table (
    id INTEGER,
    data TEXT
);

-- Good:  Has primary key
CREATE TABLE good_table (
    id INTEGER PRIMARY KEY,
    data TEXT
);

-- Alternative: Use unique index as replica identity
CREATE UNIQUE INDEX idx_alt_key ON some_table(email);
ALTER TABLE some_table REPLICA IDENTITY USING INDEX idx_alt_key;

3. One Unique Index Only (Multi-Master)

Downstream can have only ONE unique index for conflict resolution
Additional unique constraints will cause replication errors
Upstream can have multiple unique constraints

4. UNLOGGED and TEMPORARY Tables

-- These are NEVER replicated
CREATE UNLOGGED TABLE cache_data (...);
CREATE TEMPORARY TABLE temp_data (...);

5. Large Objects Not Supported

LOBs (Large Objects) cannot be replicated
Use BYTEA instead for binary data
Consider external storage (S3, etc.) with references

6. DDL Not Automatic

Schema changes must be manually replicated
Use pglogical.replicate_ddl_command()
Or apply DDL manually on all nodes

7. Sequences Not Real-Time

-- Sequence values replicated periodically, not per-transaction
-- Can lead to gaps or overlaps

-- Force synchronization when needed
SELECT pglogical.synchronize_sequence('public.users_id_seq');

-- Better: Use UUIDs for distributed systems
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ...
);

8. TRUNCATE CASCADE

-- TRUNCATE CASCADE only applies on provider
TRUNCATE orders CASCADE;  -- Only cascades on provider

-- On subscriber, only explicit TRUNCATE is replicated
-- Related tables not truncated unless explicitly in replication

9. Trigger Behavior

-- Replication runs with session_replication_role = replica

-- Only these triggers fire during replication: 
ALTER TABLE mytable ENABLE REPLICA TRIGGER mytrigger;  -- Fires
ALTER TABLE mytable ENABLE ALWAYS TRIGGER mytrigger;   -- Fires

-- These don't fire:
CREATE TRIGGER mytrigger ... ;  -- Regular trigger (doesn't fire)
ALTER TABLE mytable DISABLE TRIGGER mytrigger;  -- Disabled (doesn't fire)

Best Practices

1. Connection Configuration

Use Connection Pooling Wisely

# In postgresql.conf
pglogical.extra_connection_options = 'keepalives=1 keepalives_idle=30 keepalives_interval=10 keepalives_count=5'

SSL/TLS for Security

SELECT pglogical.create_subscription(
    subscription_name := 'secure_sub',
    provider_dsn := 'host=provider.com port=5432 dbname=mydb sslmode=require sslcert=/path/to/cert. pem sslkey=/path/to/key.pem'
);

2. Schema Management

Version Control Your Schema

-- Keep schema changes in version control
-- Apply consistently across all nodes

-- migrations/001_add_user_phone.sql
BEGIN;
SELECT pglogical.replicate_ddl_command($$
    ALTER TABLE users ADD COLUMN phone VARCHAR(20);
$$);
COMMIT;

Backward Compatible Changes

-- Good: Add nullable column
ALTER TABLE users ADD COLUMN middle_name TEXT;

-- Good: Add column with default (but be aware of rewrites on large tables)
ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT true;

-- Risky: Add NOT NULL column without default
-- This will fail on subscriber if data sync is delayed
ALTER TABLE users ADD COLUMN required_field TEXT NOT NULL;

-- Better approach: 
ALTER TABLE users ADD COLUMN required_field TEXT;  -- Nullable first
-- Update existing rows
UPDATE users SET required_field = 'default_value' WHERE required_field IS NULL;
-- Then add constraint
ALTER TABLE users ALTER COLUMN required_field SET NOT NULL;

3. Monitoring and Alerting

Set Up Automated Monitoring

-- Create monitoring view
CREATE VIEW replication_health AS
SELECT 
    subscription_name,
    status,
    CASE 
        WHEN status != 'replicating' THEN 'CRITICAL'
        ELSE 'OK'
    END AS health
FROM pglogical.show_subscription_status();

-- Query regularly (via cron, monitoring system, etc.)
SELECT * FROM replication_health WHERE health != 'OK';

Monitor Disk Usage

-- Check WAL retention on provider
SELECT 
    slot_name,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots
WHERE plugin = 'pglogical_output';

4. Performance Optimization

Batch Inserts

# Enable batch insert mode (default:  true)
pglogical.batch_inserts = on

Parallel Apply (requires PostgreSQL 14+ and pglogical 3.x)

# Not available in pglogical 2.x
# Upgrade to pglogical 3.x or Postgres Distributed for this feature

Network Optimization

# Adjust based on network latency
pglogical.extra_connection_options = 'tcp_user_timeout=30000'

5. Capacity Planning

Provider Requirements

1 WAL sender per subscriber node
1 replication slot per subscriber node
Disk space for WAL retention (especially if subscriber lags)

Subscriber Requirements

1 worker process per subscription
Sufficient I/O capacity for apply operations
Consider apply delay vs. provider write rate

Calculate Replication Lag Capacity

-- Monitor write rate on provider (transactions/second)
SELECT 
    xact_commit + xact_rollback AS transactions,
    (xact_commit + xact_rollback) / EXTRACT(EPOCH FROM (now() - stats_reset)) AS tps
FROM pg_stat_database
WHERE datname = current_database();

-- Ensure subscriber can sustain this rate

6. Backup and Recovery

Provider Backup

# Regular pg_basebackup works fine
pg_basebackup -h provider.com -D /backup/provider -Fp -Xs -P

# Or use pgBackRest, Barman, etc.

Subscriber Backup

# Subscriber backups are normal PostgreSQL backups
# But remember:  they don't include replication configuration
pg_dump -h subscriber.com dbname > subscriber_backup.sql

Disaster Recovery Plan

Document all replication set definitions
Keep DSN information secure but accessible
Test restoration procedures
Have a failover plan (manual or automated)

7. Testing Strategy

Test Environment Setup

Mirror production topology in testing
Use similar data volumes
Test failure scenarios (network issues, node failures)
Test schema migrations end-to-end

Common Test Scenarios

-- 1. Test subscription creation
-- 2. Test table addition to replication set
-- 3. Test row filtering
-- 4. Test conflict resolution
-- 5. Test DDL replication
-- 6. Test failover/recovery procedures
-- 7. Test with network latency simulation
-- 8. Load testing with realistic write patterns

8. Security Considerations

Credentials Management

-- Don't store passwords in plain text in database
-- Use . pgpass file or connection service file

-- . pgpass format:
-- hostname:port:database:username:password
provider.com:5432:mydb:repl_user:secret_password

-- Connection service file (~/.pg_service.conf):

Use Separate Replication User

-- Create dedicated replication user
CREATE USER repl_user WITH REPLICATION PASSWORD 'secure_password';
GRANT USAGE ON SCHEMA public TO repl_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO repl_user;

-- For future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO repl_user;

Network Security

# pg_hba.conf - limit to specific IPs
hostssl  mydb  repl_user  subscriber_ip/32  md5

# Use SSL certificates for authentication
hostssl  mydb  repl_user  subscriber_ip/32  cert

Troubleshooting

Common Issues and Solutions

1. Subscription Status is “down”

Check the Logs

-- On subscriber, check PostgreSQL logs
-- Look for errors related to subscription name

Common Causes:

Network connectivity issues
Authentication failure
Provider database down or unavailable
Replication slot deleted on provider

Solutions:

-- Test connectivity from subscriber to provider
\!  psql 'host=provider.com port=5432 dbname=mydb user=repl_user' -c 'SELECT 1'

-- Check if replication slot exists on provider
SELECT * FROM pg_replication_slots WHERE slot_name LIKE '%subscription1%';

-- Restart subscription
SELECT pglogical.alter_subscription_disable('subscription1', immediate := true);
SELECT pglogical.alter_subscription_enable('subscription1', immediate := true);

-- If slot is missing, recreate subscription
SELECT pglogical.drop_subscription('subscription1');
SELECT pglogical.create_subscription(... );

2. High Replication Lag

Diagnose the Problem

-- On provider:  Check how far behind subscriber is
SELECT 
    application_name,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_size,
    replay_lag
FROM pg_stat_replication
WHERE application_name = 'subscription1';

Common Causes:

Slow network
Subscriber hardware slower than provider
Long-running transactions on subscriber
Large batch operations on provider
Insufficient subscriber resources (CPU, I/O, memory)

Solutions:

-- Check for blocking queries on subscriber
SELECT pid, usename, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state != 'idle' AND application_name LIKE 'pglogical%';

-- Check for long-running transactions blocking apply
SELECT pid, xact_start, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC;

-- Kill blocking query if safe
SELECT pg_terminate_backend(pid);

-- Temporarily disable non-critical subscriptions to catch up
SELECT pglogical.alter_subscription_disable('low_priority_sub');

-- Increase resources on subscriber (CPU, IOPS, memory)

3. Table Synchronization Stuck

Check Sync Status

SELECT * FROM pglogical.show_subscription_table('subscription1', 'public. large_table');

Common Causes:

Network timeout during large table copy
Lock contention on source table
Disk space issues on subscriber

Solutions:

-- Check for locks on provider
SELECT l.relation::regclass, l. mode, l.granted, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation = 'public. large_table'::regclass;

-- Check disk space on subscriber
\! df -h

-- Cancel and restart sync
SELECT pglogical.alter_subscription_disable('subscription1', immediate := true);
SELECT pglogical.alter_subscription_enable('subscription1', immediate := true);

-- Or resync just the problematic table
SELECT pglogical. alter_subscription_resynchronize_table(
    'subscription1',
    'public.large_table'
);

4. Conflict Errors (when conflict_resolution = ‘error’)

Error Message Example:

ERROR: pglogical target table "public.users" conflict:  UPDATE row modified by another node

Solutions:

-- Check current conflict setting
SHOW pglogical.conflict_resolution;

-- Option 1: Change conflict resolution strategy
ALTER SYSTEM SET pglogical.conflict_resolution = 'last_update_wins';
SELECT pg_reload_conf();

-- Option 2: Manually resolve conflict and restart replication
-- Investigate the conflicting row, fix manually, then: 
SELECT pglogical.alter_subscription_enable('subscription1');

-- Option 3: Resync the table to overwrite with provider data
SELECT pglogical.alter_subscription_resynchronize_table(
    'subscription1',
    'public.users'
);

5. Missing Primary Key Error

Error Message:

ERROR: cannot update table "public.audit_log" because it does not have replica identity

Solutions:

-- Option 1: Add primary key
ALTER TABLE public.audit_log ADD PRIMARY KEY (id);

-- Option 2: Use unique index as replica identity
CREATE UNIQUE INDEX idx_audit_log_unique ON public.audit_log(id, timestamp);
ALTER TABLE public.audit_log REPLICA IDENTITY USING INDEX idx_audit_log_unique;

-- Option 3: Use FULL replica identity (not recommended, slower)
ALTER TABLE public.audit_log REPLICA IDENTITY FULL;

-- Option 4: For insert-only tables, use default_insert_only replication set
SELECT pglogical.replication_set_add_table(
    'default_insert_only',
    'public.audit_log'
);

6. WAL Files Piling Up on Provider

Check WAL Usage

SELECT 
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE plugin = 'pglogical_output'
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

Common Causes:

Inactive subscriber
Subscriber disconnected but slot not removed
Subscriber unable to keep up with changes

Solutions:

-- Check if subscriber is connected
SELECT * FROM pg_stat_replication;

-- If slot is for dead subscriber, remove it
SELECT pg_drop_replication_slot('pgl_mydb_subscription1');

-- If subscriber is lagging, investigate and resolve lag issues
-- Then wait for catch-up
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);

-- Set max_slot_wal_keep_size to prevent disk fill (PostgreSQL 13+)
ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();

7. Extension Version Mismatch

Error Message:

ERROR: pglogical version mismatch between provider and subscriber

Solutions:

# Check versions on both nodes
psql -c "SELECT * FROM pg_available_extensions WHERE name = 'pglogical'"

# Upgrade pglogical on the older node
# On Red Hat/CentOS:
sudo yum update pglogical_16

# On Debian/Ubuntu:
sudo apt-get update
sudo apt-get install --only-upgrade postgresql-16-pglogical

# Then in database: 
ALTER EXTENSION pglogical UPDATE;

8. Sequence Out of Sync

Symptoms:

Sequence values on subscriber lower than provider
Duplicate key violations when subscriber becomes active

Solutions:

-- On provider, force sequence sync
SELECT pglogical.synchronize_sequence('public.users_id_seq');

-- Wait for subscriber to catch up
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);

-- On subscriber, verify sequence value
SELECT last_value FROM public.users_id_seq;

-- Manually set if needed (subscriber only)
SELECT setval('public.users_id_seq', (SELECT MAX(id) FROM public.users) + 1000);

9. DDL Replication Issues

Common Issues:

DDL not replicated (automatic DDL not supported)
DDL executed on subscriber before provider
Incompatible schema between nodes

Best Practices:

-- Always use pglogical. replicate_ddl_command on PROVIDER
-- Never run DDL directly on subscriber

-- Wait for all subscribers to catch up first
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);

-- Then execute DDL
BEGIN;
SELECT pglogical.replicate_ddl_command($$
    ALTER TABLE public.users ADD COLUMN last_login TIMESTAMP;
$$);
COMMIT;

-- Verify on subscriber
\d public.users

10. Subscription Won’t Drop

Error Message:

ERROR: could not drop subscription "subscription1" because replication slot is still in use

Solutions:

-- Force disable first
SELECT pglogical.alter_subscription_disable('subscription1', immediate := true);

-- Wait a moment for worker to stop
SELECT pg_sleep(5);

-- Try dropping again
SELECT pglogical.drop_subscription('subscription1');

-- If still stuck, identify and terminate worker process
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name LIKE '%subscription1%';

-- Then drop
SELECT pglogical.drop_subscription('subscription1');

-- If slot stuck on provider, manually drop it
SELECT pg_drop_replication_slot('pgl_mydb_subscription1');

Diagnostic Queries

Complete Health Check Script

-- Save as check_replication_health.sql
\echo '=== Subscription Status ==='
SELECT subscription_name, status, provider_node, replication_sets
FROM pglogical.show_subscription_status();

\echo '\n=== Replication Lag (Run on Provider) ==='
SELECT 
    application_name,
    client_addr,
    state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_lag,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag,
    replay_lag AS time_lag
FROM pg_stat_replication
WHERE application_name IN (SELECT subscription_name FROM pglogical.subscription);

\echo '\n=== Replication Slots (Run on Provider) ==='
SELECT 
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots
WHERE plugin = 'pglogical_output';

\echo '\n=== Worker Processes ==='
SELECT pid, usename, application_name, client_addr, state, state_change
FROM pg_stat_activity
WHERE application_name LIKE 'pglogical%';

\echo '\n=== Replicated Tables ==='
SELECT set_name, schemaname || '.' || tablename AS table_name
FROM pglogical.tables
ORDER BY set_name, table_name;

Run with:

psql -f check_replication_health.sql

Conclusion

pglogical is a powerful and flexible logical replication solution for PostgreSQL. It enables:

✅ Selective Replication: Replicate specific tables, columns, and rows
✅ Version Upgrades: Migrate between major PostgreSQL versions with minimal downtime
✅ Multi-Source: Consolidate data from multiple sources or distribute to multiple targets
✅ Conflict Resolution: Handle conflicts in multi-master scenarios
✅ Row Filtering: Replicate only relevant data based on business logic
✅ Cascading Replication: Build multi-tier replication topologies

Key Takeaways

Requirements: Ensure proper PostgreSQL configuration (wal_level, max_workers, etc.) and superuser access
Primary Keys: All replicated tables need PRIMARY KEY or REPLICA IDENTITY
Monitoring: Regularly check subscription status, replication lag, and WAL retention
DDL Management: Use pglogical.replicate_ddl_command() for schema changes
Conflict Resolution: Choose appropriate strategy based on your use case
Testing: Always test in non-production first, especially schema changes
Backup Strategy: Maintain regular backups; replication is not a backup solution

When to Use pglogical

Good Use Cases:

Selective table replication
PostgreSQL major version upgrades
Data consolidation from multiple sources
Geographic distribution of data
Reporting/analytics replicas with filtered data
Multi-master with conflict resolution

Consider Alternatives For:

Whole-cluster replication → Use PostgreSQL streaming replication
Complex multi-master with DDL replication → Consider EDB Postgres Distributed (pglogical successor)
Very high-throughput OLTP → Physical replication may perform better
Cross-database engine replication → Consider ETL tools or logical replication protocols