postgresql – postgres logical replication starting from given LSN

postgresql – postgres logical replication starting from given LSN

The documentation gives you a hint:

When a new replication slot is created using the streaming replication interface (see CREATE_REPLICATION_SLOT), a snapshot is exported (see Section 9.27.5), which will show exactly the state of the database after which all changes will be included in the change stream. This can be used to create a new replica by using SET TRANSACTION SNAPSHOT to read the state of the database at the moment the slot was created. This transaction can then be used to dump the databases state at that point in time, which afterwards can be updated using the slots contents without losing any changes.

So the steps would be:

• Start a replication connection to the database.

• Run

  CREATE_REPLICATION_SLOT slot_name LOGICAL pgoutput;
  

  to create a replication slot. Leave the connection open.

• Use

  pg_dump --snapshot=snapshotname ...
  

  to dump the database at that snapshot. You can close the replication connection once that has started.

• Restore the dump to the target database.

• Start replication using the replication slot.

postgresql – postgres logical replication starting from given LSN