Logical replication conflict and trying to use `pg_replication_origin_advance`

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: tomjkidd(at)gmail(dot)com
Subject: Logical replication conflict and trying to use `pg_replication_origin_advance`
Date: 2019-09-30 15:46:55
Message-ID: 156985841500.26192.10947730992115987072@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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.

Browse pgsql-docs by date

  From Date Subject
Next Message Bruce Momjian 2019-09-30 15:55:05 PREPARE and GUC plan_cache_mode
Previous Message Bruce Momjian 2019-09-30 14:24:27 Re: Chapter 43.8. "Transaction Management" fails to state two critical restrictions