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.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
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
