3

I have built a master -> slave setup with PostgreSQL 10 to sync data from some specific tables to the slave machine, which is the public system. Now during the whole time, I encounter some strange behaviour: Some tables do not sync at all.

Looking into the PostgreSQL log I encounter these error messages:

2018-06-07 15:28:30.163 CEST [4134] LOG:  worker process: logical replication worker for subscription 18249 sync 17956 (PID 6706) exited with exit code 1

2018-06-07 15:28:30.183 CEST [6707] ERROR:  duplicate key value violates unique constraint "producttext_pk"

2018-06-07 15:28:30.183 CEST [6707] DETAIL:  Key (id)=(100) already exists.

2018-06-07 15:28:30.183 CEST [6707] CONTEXT:  COPY producttext, line 1

For further analysis, I removed the primary key on this table on the slave machine and in this case, the error doesn't occur anymore, BUT now I get duplicate entries in the table.

The only way to prevent the duplicate entries is to have the primary key in place, but then the sync won't work while it keeps looping on exactly four tables, never moving to the remaining ones.

I checked the roles and owner already and those are set correctly. As said without the primary key everything syncs, doesn't throw errors, but creates duplicate entries.

May I possibly missed something at this point? Thanks a lot.

resonic
  • 31
  • 3

1 Answers1

1

You missed that you cannot insert rows into the table on the standby that can conflict with fows that are replicated from the primary.

If you need to insert on both the primary and the standby you need to make sure that the primary keys don't conflict, e.g. by inserting only even IDs on one and odd IDs on the other.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 2
    Hi, im not inserting rows on the standby. This is supposed to be just a read only. That's why I cant figure out the problem here. – resonic Jun 07 '18 at 16:46
  • Then you'll have to come up with a reproducible test case, because that seems impossible. – Laurenz Albe Jun 07 '18 at 17:13
  • this can be reproduced by dropping the existing subscription and recreate it. The recreation is starting to replicate the whole existing data. hence throwing that primary key violation error. Is there a way to continue the replication from the point where it stopped previously? – MSRS Dec 30 '20 at 14:12
  • found this https://stackoverflow.com/questions/62721015/how-can-i-get-past-this-problem-recreating-a-pg-logical-subscription – MSRS Dec 30 '20 at 14:15
  • @MSRS Only if the replication slot is still there or you know the exact LSN and the WAL is still there. – Laurenz Albe Jan 07 '21 at 07:12