postgres logical replication starting from given LSN

Question

Postgres logical replication initial synchronization is very slow process, especially if original database is quite big.

I am wondering if it possible to start replication from given LSN?

The desired work flow will be

1. obtain current LSN from source database
2. create logical dump of desired objects in source database
3. restore dump on the target database
4. start logical replication from LSN acquired in step 1

I did not find any docs allowing step 4, does anybody know if it possible?

Answer 1

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 database's state at that point in time, which afterwards can be updated using the slot's contents without losing any changes.

So the steps would be:

• Start a replication connection to the database:

  psql "dbname=yourdatabasename replication=database"
  

• Create a replication slot and copy the snapshot name from the output. It is important to leave the connection open until the next step, otherwise the snapshot will cease to exist

  CREATE_REPLICATION_SLOT slot_name LOGICAL pgoutput;
  

• Dump the database at the snapshot with the following command. You can close the replication connection once that has started.

  pg_dump --snapshot=snapshotname [...]
  

• Restore the dump to the target database.

• Start replication using the replication slot.