logical replication
also: logical replication (PostgreSQL), table-level replication
A method of copying data changes from one PostgreSQL database to another by transmitting logical changes (INSERT, UPDATE, DELETE operations) rather than physical blocks, enabling selective replication and heterogeneous systems.
Logical replication is a PostgreSQL feature that replicates data at the logical level—meaning it captures and forwards the actual data modifications (rows inserted, updated, or deleted) rather than the underlying disk blocks. This differs from physical replication, which copies byte-for-byte replicas of the entire database.
The publisher database records changes to specified tables in its Write-Ahead Log (WAL), and a subscriber database applies those changes independently. For example, if you INSERT a row into a users table on the publisher, the subscriber receives the INSERT command and executes it locally.
Key advantages include selective table replication, the ability to replicate to different PostgreSQL versions, and support for conflict resolution. A common use case is replicating production data to a data warehouse or analytics system: CREATE PUBLICATION prod_pub FOR TABLE customers, orders; on the source, then CREATE SUBSCRIPTION analytics_sub CONNECTION '...' PUBLICATION prod_pub; on the target.