Postgres — Chapter 31. Logical Replication

Andrew Dawson
2 min readJun 20, 2023

--

I recently read Chapter 31 of the Postgres documentation on logical replication. Here are my learning notes on this reading —

  • Logical replication is a method of replicating data from one database to another. It is based on a publisher/subscriber model. Subscribers pull data from the publishers they are subscribed to. Logical replication typically starts by the subscriber pulling a snapshot from the publisher and then copying incremental deltas in the same order they are applied on the publisher. By matching the order on the subscriber you ensure transactional consistency on the subscriber. The subscriber database is not really special in anyway — if you want applications could write to it and/or you could setup one subscriber to be a publisher for another downstream database.
  • A publication can be created on any replication primary node. The node on which the publication is created is called the publisher. A publication is generated from a table or group of tables. Tables must be explicitly added to a publication unless you create the publication using the ALL TABLES modifier in which case all tables are included in the publication. CREATE PUBLICATION is used to create a publication and ALTER PUBLICATION is used to alter an existing publication.
  • A subscription is created on any replication primary node of the target database. The node on which the subscription is created is called the subscriber. Subscribers behave the same way as other Postgres instances; this means they can act as publishers themselves and they can accept writes from the application. Subscriptions depends on table names and columns names in order to know how to replicate data, this means that renaming tables or columns in the subscriber are not supported. You can however leave columns out or add new columns in the target. You can also change the type of columns and this will work as long as the type in the source is convertible to the type in the target.
  • When setting up publications a row filter can be specified which enables only publishing rows which satisfy a provided where clause. The where clause must be a simple expression that does not include any user functions or built in functions. If a subscription pulls from multiple publications those publications row filters are OR’ed together and all rows matching any of the row filters will be copied. During the initial synchronization the row filters are respected as well.
  • When setting up a publication you can specify a column list for the columns that should be replicated. If no column list is provided then by default all columns will be replicated. The subscriber must have at least all the columns that are specified in the publishers column list.

--

--

Andrew Dawson
Andrew Dawson

Written by Andrew Dawson

Senior software engineer with an interest in building large scale infrastructure systems.

No responses yet