Logical replication did not use primary key as replica identity

From: Aleš Zelený <zeleny(dot)ales(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Logical replication did not use primary key as replica identity
Date: 2018-11-18 23:07:00
Message-ID: CAODqTUYmE0ARShDjSgobL2LxTTMfXZceqpL7enTpCOkW-ReR9Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

we are using logical replication for half a year and now, when we were
adding new tables to replication, we have found a unexpected issue in log
file.

New tables were created on publication database (Ubuntu 16.04, PG 10.6) ,
subscription side is PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0
20160609, 64-bit.

I've realized, that by a mistake, there were UNIQUE index and primary key
on same set of columns, but dropping unintended unique index and refreshing
subscription did not helped. Also manually defining replica identity did
not resolve the issue. Tables are identical on publisher and subscriber
databases.

postgres=# \c stage_dev
You are now connected to database "stage_dev" as user "postgres".
stage_dev=# \d stage_data.asset_info
Table "stage_data.asset_info"
Column | Type | Collation | Nullable
| Default
-----------------------+-----------------------------+-----------+----------+---------
asset_id | bytea | | not null
|
call_code | character(3) | | not null
|
price_final | double precision | |
|
primary_code_id | integer | | not null
|
....
updmgr_last_chg_dt | timestamp without time zone | |
|
Indexes:
"asset_info_pkey" PRIMARY KEY, btree (asset_id, primary_code_id,
call_code)
"asset_info_key" UNIQUE, btree (asset_id, primary_code_id, call_code)
"asset_info_updmgr_idx" btree (updmgr_last_chg_dt) WHERE
updmgr_last_chg_dt IS NOT NULL
Publications:
"pub_stage_dev"

stage_dev=# alter table stage_data.asset_info replica identity using INDEX
asset_info_pkey;
ALTER TABLE
stage_dev=#

Subscription refresh did not helped, neither dropping the mistaken unique
index.

2018-11-18 19:09:31 UTC 22142 5bf1b8df.567e 3 [local] anl_master_dev
postgres 0 14/6766 psql idle [00000]:LOG: statement: alter subscription
sub_stage_dev refresh publication ;

2018-11-18 19:12:22 UTC 23427 5bf1b996.5b83 1 0 8/40767 [00000]:LOG:
logical replication apply worker for subscription "sub_stage_dev" has
started

2018-11-18 19:12:22 UTC 23427 5bf1b996.5b83 2 0 8/40771
[55000]:ERROR: logical replication target relation "stage_data.asset_info"
has neither REPLICA IDENTITY index nor PRIMARY KEY and published relation
does not have REPLICA IDENTITY FULL

Any advices, how to find out what I've done wrong and how to resolve the
issue are welcomed. Also I'm interested, why PK is not classified as
suitable replica identity.

Kind regards Ales Zeleny

Browse pgsql-general by date

  From Date Subject
Next Message Charles Clavadetscher 2018-11-19 06:48:33 RE: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations
Previous Message Yuri Kanivetsky 2018-11-18 21:50:38 New timeline when starting with a restored data dir