4

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?

Ilia Guenkin
  • 61
  • 5
  • Sounds like you are looking for pg_replication_origin_advance https://www.postgresql.org/docs/current/functions-admin.html#PG-REPLICATION-ORIGIN-ADVANCE – jjanes Nov 29 '21 at 20:40

1 Answers1

8

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • could you please clarify why the replication connection should remain open? – Vitalii Zurian Apr 29 '22 at 12:13
  • 1
    @VitaliiZurian Because otherwise the snapshot would cease to exist. – Laurenz Albe Apr 29 '22 at 12:38
  • 1
    Aha, so the snapshot is alive as long as it is referenced by at least 1 session? Unlike the replication slot, which is created permanently? – Vitalii Zurian Apr 29 '22 at 13:37
  • 1
    That is correct. – Laurenz Albe Apr 29 '22 at 16:41
  • @LaurenzAlbe I was running a couple tests and I observed that the contents of `pg_snapshot` are cleared when the originating session disconnects, even though the `pg_dump` is still running. There are no errors and things seem OK but I'm wondering if this is expected. I expected to see the `pg_snapshot` contents there until it is no longer needed. Is there any other place to list open snapshots? – Don Seiler May 11 '23 at 16:06
  • 1
    @DonSeiler That should be no problem. The snapshot still exists, but it is no longer an exported snapshot. – Laurenz Albe May 11 '23 at 16:16
  • I'm wondering now also why this is faster than allowing `CREATE SUBSCRIPTION` to import the data. I had just accepted that it was a good thing to be able to do it this way but now that I'm documenting everything I find myself unable to really explain why. – Don Seiler Jun 07 '23 at 20:23
  • @DonSeiler Import which data? – Laurenz Albe Jun 07 '23 at 23:38
  • @LaurenzAlbe the initial table data that the `CREATE SUBSCRIPTION` would otherwise try to import at the start. In my case I'm setting up logical replicas for migrating the database to a new host with new libc collation and/or major PG version upgrade. – Don Seiler Jun 08 '23 at 14:49
  • 1
    @DonSeiler I don't think that the initial data copy is particularly slow. I think that that is just an unverified claim. – Laurenz Albe Jun 09 '23 at 06:55