Will PostgreSQL logical replication (pglogical) need a quick lock to get the "started LSN" before fulldump like mysqldump + binlog replication~?

PostgreSQL
1

Hi team,

I’m testing PostgreSQL logical replication in my lab.
Due to small size & loading in my lab, it could smoothly finish a data fulldump + WAL replication by default setting.
But I’m curious if PostgreSQL logical replication need a quick lock on Src PostgreSQL (Publication) to get the “started LSN” before data fulldump~?

In MySQL, if using mysqldump + binlog replication, to ensure the data consistency for the binlog replication, there will be a very short “flush tables with read lock” before fulldump to get the started binlog position GTID

In PostgreSQL, at the step of create_subscription
pglogical.create_subscription: parameter “synchronize_data” - specifies if to synchronize data from provider to the subscriber, default true
Ref: GitHub - 2ndQuadrant/pglogical: Logical Replication extension for PostgreSQL 15, 14, 13, 12, 11, 10, 9.6, 9.5, 9.4 (Postgres), providing much faster replication than Slony, Bucardo or Londiste, as well as cross-version upgrades.
or
native logical replication CREATE SUBSCRIPTION: parameter “copy_data” - Specifies whether to copy pre-existing data in the publications that are being subscribed to when the replication starts. The default is true.
Ref: PostgreSQL: Documentation: 16: CREATE SUBSCRIPTION

Looks like no need a lock before fulldump~?

Question: Will PostgreSQL logical replication need a quick lock on Src PostgreSQL (Publication) to get the “started LSN” before data fulldump~?
If no need a lock, how PostgreSQL logical replication know the started LSN and ensure the data consistency for the follow-up logical replication~?

Any guidance or direction are highly appreciated.

Thanks a lot.
OG

1 Like
2

Hi @OGhuang ,
As you know, The pglogical (extension) which 2ndQuardrant developed is the predecessor of Built-in logical replication.
If you are trying logical replication in PostgreSQL, I recommend you stick to Built-in features for logical replication. Because that is where log of new developments and improvements are happening. The built-in features are slowly bridging the gap.
The built-in feature doesn’t need any lock on the source PostgreSQL, it marks an LSN as the starting point and uses dedicated sync workers to copy the data from the Publisher side to the Subscriber side.
New versions can have many sync workers running in parallel to speed up this initial sync.

1 Like