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 SNAPSHOTto 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.
CREATE_REPLICATION_SLOT slot_name LOGICAL pgoutput;
to create a replication slot. Leave the connection open.
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.