Обсуждение: Logical replication conflict and trying to use `pg_replication_origin_advance`
Logical replication conflict and trying to use `pg_replication_origin_advance`
От
PG Doc comments form
Дата:
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/logical-replication-conflicts.html Description: I have a pretty detailed setup, and can provide more details if desired, but the real issue is that I am seeing an error like the following: ```bash 2019-09-30 15:09:02.369 UTC [814] LOG: logical replication table synchronization worker for subscription "us_sponsor_sub", table "site" has started 2019-09-30 15:09:02.404 UTC [814] ERROR: duplicate key value violates unique constraint "site_pkey" 2019-09-30 15:09:02.404 UTC [814] DETAIL: Key (id)=(431ab6e3-4487-4b12-a9f9-4874b16ccd0a) already exists. 2019-09-30 15:09:02.404 UTC [814] CONTEXT: COPY site, line 1 2019-09-30 15:09:02.406 UTC [1] LOG: background worker "logical replication worker" (PID 814) exited with exit code 1 ``` This error is expected, because I intentionally was trying to alter a subscription to set its replication `slot_name` to `NONE`, then drop the subscription in order to change hosts for one of the databases in the system. I would like to resume replication in the new host, instead of starting from scratch, because my new host already has the expected data. I tried to create a new subscription, using the existing replication slot, and while disabled calling `pg_replication_origin_advance` with a further-along LSN, and am able to see that reflected in the `remote_lsn`, but not the `local_lsn`. After re-enabling the subscription, I still get the same error. I am sure there is something straight forward that I am missing, but I am missing it! ```bash $ psql -d $PLR_US_SITE_URL -c "SELECT * FROM pg_replication_origin_status;" local_id | external_id | remote_lsn | local_lsn ----------+-------------+------------+----------- 1 | pg_16415 | 0/16961D9 | 0/0 ``` Is there a way to skip over this record in the local log so that I can move on to the next one? I didn't want to resort to deleting the conflicting entry because it already has relationships that need to continue to hold in the subscriber database.