In "pg_repack" I faced an Error : cannot update table "table_123" because it does not have a replica identity and publishes updates

Question

ERROR: query failed: ERROR:  cannot update table "table_123" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
CONTEXT:  SQL statement "UPDATE repack.table_123 SET (id, automation_job_member_id, action_id, csm_field_id, target_type, status, external_status, status_description, valid_at, started_at, completed_at, created_at, updated_at, target_identifier) = ($2.id, $2.automation_job_member_id, $2.action_id, $2.csm_field_id, $2.target_type, $2.status, $2.external_status, $2.status_description, $2.valid_at, $2.started_at, $2.completed_at, $2.created_at, $2.updated_at, $2.target_identifier) WHERE (id) = ($1.id)"
DETAIL: query was: SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
DEBUG: No workers to disconnect.

I am using the "dbzm" publication in PostgreSQL, which is created to include all tables. However, I am unable to provide a replica identity and cannot drop the replica.

I attempted to remove the table created by "repack" from the publication, but it cannot be dropped due to the fact that the publication is configured to include all tables.

postgres=> ALTER PUBLICATION dbzm_postgres DROP TABLE repack.table_123;
ERROR:  publication "dbzm_postgres" is defined as FOR ALL TABLES
DETAIL:  Tables cannot be added to or dropped from FOR ALL TABLES publications.

Please clarify: does the table you are trying to pg_repack have a primary key or other replica identity? Best is to edit the question and add the putput to the psql command \d+ table_123. — Laurenz Albe
– Laurenz Albe, Commented Jun 14, 2023 at 21:09

Laurenz Albe · Accepted Answer · 2023-06-19 06:57:45Z

1

It looks like pg_repack creates tables without primary key, probably for fast loading. That won't work with logical replication. So it seems like you cannot run pg_repack on a replicated table. This also makes sense from the replication angle, because the new table is a different one from the old one, and logical decoding wouldn't work.

Either don't use pg_repack or remove the table from the publication before running pg_repack. Afterwards, you can add it again. You may have to initialize replication for the table from scratch.

answered Jun 19, 2023 at 6:57

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

In "pg_repack" I faced an Error : cannot update table "table_123" because it does not have a replica identity and publishes updates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related