CODEWITHSUNDEEP
postgresqllogical-replication
Ilia Guenkin
Ilia Guenkin

Reputation: 91

postgres logical replication starting from given LSN

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?

Upvotes: 7

Views: 5238

Answers (2)

Alex
Alex

Reputation: 11

Try this method:

BEGIN TRANSACTION REPEATABE READ;

To create logical replication slot use pg_export_snapshot()

In another session - start pg_dump with --snapshot=<snapshot_id> .(Not sure - should You wait till the end of pg_dump) and then,

COMMIT;

Then the dump and slot will be at the same LSN, and you can subscribe to it.

Check also: https://www.percona.com/blog/how-to-convert-postgresql-streaming-replication-to-logical-replication/

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247370

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.

Upvotes: 12

Related Questions