PostgreSQL logical replication lets you stream INSERT, UPDATE, and DELETE events to other databases or external services so you can track row-level changes in near real-time.
Need to capture every INSERT, UPDATE, or DELETE as soon as it happens? Postgres logical replication lets you stream row-level changes directly from the database engine, providing an audit-friendly, low-latency alternative to batch ETL and trigger-based solutions.
This article walks through how logical replication works under the hood, why it matters, how to configure it, and the best practices (and pitfalls) you need to know.
When wal_level
is set to logical
, PostgreSQL writes extra metadata to its WAL (Write-Ahead Log). A replication slot holds the position of each subscriber so that WAL files aren’t recycled until all downstream consumers have read them. A publication defines which tables and operations should be streamed, and a subscription on another Postgres instance (or a custom decoder) receives those changes.
wal_level = logical
and restart the primary.FOR TABLE
) or everything (FOR ALL TABLES
).wal2json
) to emit JSON for Kafka, Pulsar, or webhooks.-- postgresql.conf (primary)
wal_level = logical -- default: replica
max_replication_slots = 10 -- at least 1 per downstream consumer
max_wal_senders = 10 -- concurrent streaming processes
After editing these values, restart Postgres.
# pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host replication rep_user 10.0.0.0/24 md5
CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD 'secret';
-- Stream only the important tables
CREATE PUBLICATION accounting_pub FOR TABLE invoices, payments;
-- Or everything
-- CREATE PUBLICATION all_pub FOR ALL TABLES;
CREATE SUBSCRIPTION accounting_sub
CONNECTION 'host=10.0.0.1 port=5432 user=rep_user password=secret dbname=app'
PUBLICATION accounting_pub;
Postgres copies existing rows (the initial snapshot) and then streams new changes.
If you need to pipe events to a message queue, install wal2json
or pgoutput
and use a connector like debezium
:
# Example Debezium config
"database.server.name": "app",
"plugin.name": "wal2json",
"slot.name": "debezium_slot",
"publication.autocreate.mode": "filtered"
pg_replication_slots
. If a consumer stalls, disk usage can skyrocket.A fintech startup needs to stream ledger updates into Redshift for compliance reporting. By configuring a publication on ledger_entries
and subscribing via Debezium + Kafka Connect, they achieve sub-second replication without hammering the OLTP cluster with batch queries or triggers.
Because Galaxy is a modern SQL editor, you can:
pg_replication_slots
directly from the editor.While Galaxy doesn’t run the replication process itself, it accelerates development and collaboration around the necessary SQL.
Postgres logical replication provides a first-class, low-latency way to track row-level changes, power event-driven architectures, and simplify data pipelines. By following the configuration steps and best practices outlined above—and avoiding common pitfalls like missing primary keys or unmanaged slots—you can build robust, scalable change-data-capture pipelines directly on top of PostgreSQL.
Modern data stacks demand real-time visibility into database mutations. Logical replication eliminates fragile trigger-based CDC, reduces ETL latency from hours to seconds, and offloads heavy analytics queries from the primary instance. Mastering it lets data engineers build scalable, event-driven systems without abandoning PostgreSQL.
No. Physical replication streams binary disk changes (blocks), while logical replication streams row-level INSERT/UPDATE/DELETE events. Logical is better for CDC and multi-way replication.
Yes. You can query pg_publication
, pg_subscription
, and pg_replication_slots
from Galaxy, then share those diagnostics with teammates via Collections.
The replication slot will retain WAL files until the subscriber catches up, potentially filling disk space. Monitor pg_replication_slots
and drop or move stale slots.
You must restart when changing wal_level
from replica
to logical
, but later publication and subscription changes are hot-swappable.