We are looking to use Postgres Logical Replication to move changes from an upstream server ("source" server) to a downstream server ("sink" server).
We run into issues when we simulate a disaster recovery scenario. In order to simulate this, we delete the source database while the replication is still active. We then bring up a new source database and try to: a) move data from the sink into the source, and b) set up replication. At this stage we get one of two errors, depending on when we set up the replication (before or after moving the data).
The errors we get after testing the above are one of the below:
Replication slot already in use, difficulty in re-enabling slot without deletion
LOG: logical replication apply worker for subscription "test_sub" has started
ERROR: could not start WAL streaming: ERROR: replication slot "test_sub" does not exist
LOG: worker process: logical replication worker for subscription 16467 (PID 205) exited with exit code 1
Tried amending using:
ALTER SUBSCRIPTION "test_sub" disable;
ALTER SUBSCRIPTION "test_sub" SET (slot_name = NONE);
DROP SUBSCRIPTION "test_sub";
Cannot create subscription due to PK conflicts
ERROR: duplicate key value violates unique constraint "test_pkey"
DETAIL: Key (id)=(701) already exists.
CONTEXT: COPY test, line 1
Some possible resolutions:
- Have the Logical Replication set up after a given WAL record number. This might avoid the PK issues we are facing
- Find a way to recreate the replication slot on the source database
- Backup the Postgres server, including the replication slot, and re-import
Is this a well-catered for use case for Postgres Logical Replication? This is a typical disaster recovery scenario, so would like to know how best to implement. Thanks!
