pglogical: The PostgreSQL Logical Replication Power Tool — Complete Guide, Cheat Sheet, and FAQ

Part 1 — Article: Understanding pglogical

What is pglogical?

pglogical is a PostgreSQL extension developed by 2ndQuadrant (now part of EDB) that provides logical replication between PostgreSQL databases. Unlike physical (streaming) replication, which copies binary WAL data block-for-block, logical replication transmits row-level changes, giving you far greater flexibility in what is replicated and how.

It was the precursor to PostgreSQL’s built-in logical replication (introduced in PG 10), but pglogical remains widely used because it offers features that core PostgreSQL still doesn’t have.

Why use pglogical?

Compared to native logical replication, pglogical supports:

• Cross-version replication (e.g., PG 11 → PG 15) — invaluable for major-version upgrades with minimal downtime
• Cross-platform replication (different OS/architecture)
• Selective replication of tables, schemas, or sequences
• Row and column filtering
• Replication sets for grouping objects logically
• Conflict detection and resolution in multi-master / bi-directional setups
• Sequence replication
• DDL replication via helper functions

How it works

pglogical uses PostgreSQL’s logical decoding infrastructure (introduced in 9.4) and the pgoutput-like plugin to decode WAL into a stream of logical changes. Key concepts:

Concept	Description
Provider	The source node publishing changes
Subscriber	The target node receiving changes
Replication Set	A named group of tables/sequences to replicate
Subscription	A connection from subscriber to provider that consumes one or more replication sets
Node	A logical identity for a database participating in replication

Typical architecture patterns

1. One-way replication — single provider, one or more subscribers (read scaling, reporting)
2. Fan-in / aggregation — many providers → one subscriber (data warehousing)
3. Bi-directional (BDR-lite) — two nodes replicating to each other (active/active, requires careful conflict planning)
4. Major version upgrade — replicate from old PG to new PG, then switch the application

Limitations to be aware of

• DDL is not automatically replicated — use pglogical.replicate_ddl_command()
• No replication of TRUNCATE on older versions (supported in newer)
• Large objects (lo_*) are not replicated
• Tables must have a PRIMARY KEY or REPLICA IDENTITY FULL for UPDATE/DELETE
• Sequences are replicated periodically, not transactionally — values may lag
• Superuser or replication-role privileges required on both sides
• Network/firewall must allow the subscriber to connect to the provider

When to choose pglogical vs. native logical replication

Need	Recommendation
Same major version, simple pub/sub	Native logical replication
Cross-major-version upgrade	pglogical
Column/row filtering on older PG	pglogical
Sequence replication	pglogical
Bi-directional replication	pglogical (or pgEdge / BDR)
Minimal extension footprint	Native

---

Part 2 — pglogical Cheatsheet

Installation (RHEL/CentOS example)

# Add 2ndQuadrant repo, then:
sudo yum install postgresql15-pglogical

postgresql.conf settings (both nodes)

wal_level = logical
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'
track_commit_timestamp = on   # required for last/first-update-wins conflict resolution

pg_hba.conf (provider)

host    replication     repuser     10.0.0.0/24     scram-sha-256
host    mydb            repuser     10.0.0.0/24     scram-sha-256

Create the extension (both nodes)

CREATE EXTENSION pglogical;

Create nodes

-- On PROVIDER
SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=10.0.0.1 port=5432 dbname=mydb user=repuser password=secret'
);

-- On SUBSCRIBER
SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=10.0.0.2 port=5432 dbname=mydb user=repuser password=secret'
);

Add tables to a replication set

-- On PROVIDER (uses the default replication set)
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

-- Or add individual tables
SELECT pglogical.replication_set_add_table('default', 'public.orders');

-- Add sequences
SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);

Create a subscription

-- On SUBSCRIBER
SELECT pglogical.create_subscription(
    subscription_name := 'sub1',
    provider_dsn := 'host=10.0.0.1 port=5432 dbname=mydb user=repuser password=secret'
);

Common management commands

-- Status
SELECT * FROM pglogical.show_subscription_status();

-- Synchronize a specific table now
SELECT pglogical.alter_subscription_resynchronize_table('sub1', 'public.orders');

-- Disable / enable subscription
SELECT pglogical.alter_subscription_disable('sub1');
SELECT pglogical.alter_subscription_enable('sub1');

-- Drop subscription
SELECT pglogical.drop_subscription('sub1');

-- Drop node
SELECT pglogical.drop_node('provider1');

Custom replication sets (filtering)

SELECT pglogical.create_replication_set(
    set_name := 'reporting',
    replicate_insert := true,
    replicate_update := true,
    replicate_delete := false,
    replicate_truncate := false
);

SELECT pglogical.replication_set_add_table(
    set_name := 'reporting',
    relation := 'public.orders',
    row_filter := 'status = ''shipped''',
    columns := ARRAY['id','customer_id','total','status']
);

Replicating DDL

SELECT pglogical.replicate_ddl_command(
    command := 'ALTER TABLE public.orders ADD COLUMN notes text;',
    replication_sets := ARRAY['default']
);

Monitoring queries

-- Replication slot lag
SELECT slot_name, active, pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
) AS lag
FROM pg_replication_slots;

-- Worker activity
SELECT * FROM pg_stat_activity WHERE application_name LIKE 'pglogical%';

-- Subscription workers
SELECT * FROM pglogical.local_sync_status;

Conflict resolution (bi-directional)

# postgresql.conf on subscriber
pglogical.conflict_resolution = 'last_update_wins'   # or 'first_update_wins', 'apply_remote', 'keep_local', 'error'
pglogical.conflict_log_level = 'LOG'

---

Part 3 — FAQ

Q1. Does pglogical replicate schema (DDL) automatically?
No. You must either pre-create the schema on the subscriber (via pg_dump --schema-only) or use pglogical.replicate_ddl_command() to push DDL changes through replication.

Q2. Can I replicate between different PostgreSQL major versions?
Yes — this is one of pglogical’s main advantages. It’s commonly used for near-zero-downtime major-version upgrades.

Q3. What happens if a table has no primary key?
INSERTs replicate fine, but UPDATE and DELETE will fail unless you set ALTER TABLE ... REPLICA IDENTITY FULL, which logs the entire old row (expensive).

Q4. Is pglogical the same as BDR?
No. BDR (Bi-Directional Replication) is a separate, more advanced multi-master product. pglogical can do simple two-node bi-directional replication but lacks BDR’s full conflict-handling and global sequence features.

Q5. How do I handle initial data copy?
By default, create_subscription performs a COPY-based initial sync of all tables in the subscribed replication sets. You can disable this with synchronize_data := false if you’ve pre-loaded data via pg_dump.

Q6. Can I replicate only some columns?
Yes — pass the columns array to pglogical.replication_set_add_table(). The included columns must contain the primary key.

See also: Mastering the Linux Command Line — Your Complete Free Training Guide

Q7. Why is my subscriber falling behind?
Common causes:

• Long-running transactions on the provider holding back WAL
• Subscriber apply worker is single-threaded per subscription — CPU/IO bound
• Large UPDATE / DELETE batches
• Missing indexes on subscriber side (apply uses index lookups by PK)
• Network bandwidth saturation

Q8. Does pglogical replicate TRUNCATE?
Yes in recent versions (pglogical 2.3+ on PG 11+). Older combinations don’t support it.

Q9. How do I add a new table after replication is running?

1. Create the table on both sides (or use replicate_ddl_command)
2. SELECT pglogical.replication_set_add_table('default', 'public.newtable');
3. SELECT pglogical.alter_subscription_synchronize('sub1');

Q10. What permissions are required?
The replication user needs the REPLICATION attribute and SUPERUSER (or carefully granted privileges on pglogical schema objects, the target tables, and the ability to create replication slots).

Q11. Can pglogical replicate large objects (lo_*) or pg_largeobject?
No. Store large data as bytea if you need it replicated.

Q12. How do I cleanly remove pglogical?

-- subscriber
SELECT pglogical.drop_subscription('sub1');
SELECT pglogical.drop_node('subscriber1');
DROP EXTENSION pglogical;

-- provider
SELECT pglogical.drop_node('provider1');
DROP EXTENSION pglogical;

Then remove pglogical from shared_preload_libraries and restart.

Q13. Is pglogical actively maintained?
Yes — it’s maintained by EDB. However, for new same-version deployments, EDB itself often recommends native logical replication unless you need pglogical-specific features.

Q14. Does pglogical work on Amazon RDS / Aurora / Azure Postgres?
RDS for PostgreSQL supports pglogical (it’s whitelisted). Aurora PostgreSQL and Azure typically restrict it — check provider-specific docs. Native logical replication is more universally available on managed services.

Q15. How do I troubleshoot a stuck subscription?

1. Check pglogical.show_subscription_status()
2. Check PostgreSQL logs on the subscriber for apply-worker errors
3. Check pg_replication_slots on the provider for slot lag and active state
4. Look for blocking locks: SELECT * FROM pg_locks WHERE NOT granted;
5. Disable then re-enable the subscription as a soft reset

---

This should give you both a conceptual understanding and a practical reference for day-to-day pglogical work.