Column list used by the publication does not cover the replica identity. Cannot Update Table - Logical Replication - Postgres 15

Question

We're doing a Postgres native logical replication between two databases.

Now, we're getting an error trying to update from the publisher one of thousand tables that we have.

When we try to update the table it returns this error:

ERROR: Column list used by the publication does not cover the replica identity.cannot update table "etiquetas_j".

ERROR: cannot update table "etiquetas_j" Estado SQL: 42P10 Detalle: Column list used by the publication does not cover the replica identity.

The table definition, datatypes, indexes, and pkeys are the same at publisher and subscriptor

See here:

Publisher:

    CREATE TABLE IF NOT EXISTS public.etiquetas_j (
    etiqueta_id integer NOT NULL,
    fecha timestamp without time zone,
    j_id integer NOT NULL,
    usuario_del_id integer,
    usuario_mod_id integer,
    organigrama_mod character varying(2) COLLATE pg_catalog."default",
    id integer NOT NULL DEFAULT nextval('etiquetas_j_id_seq1'::regclass),
    CONSTRAINT etiquetas_j_pkey PRIMARY KEY (id))
TABLESPACE pg_default; ALTER TABLE IF EXISTS public.etiquetas_j
    OWNER to postgres;


CREATE INDEX IF NOT EXISTS etiquetas_j_etiqueta_id_idx
    ON public.etiquetas_j USING btree
    (etiqueta_id ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS etiquetas_j_id_idx
    ON public.etiquetas_j USING btree
    (j_id ASC NULLS LAST)
    TABLESPACE pg_default;

Subscriptor:

CREATE TABLE IF NOT EXISTS public.etiquetas_j
(
    etiqueta_id integer NOT NULL,
    fecha timestamp without time zone,
    j_id integer NOT NULL,
    usuario_del_id integer,
    usuario_mod_id integer,
    organigrama_mod character varying(2) COLLATE pg_catalog."default",
    id integer NOT NULL DEFAULT nextval('etiquetas_j_id_seq1'::regclass),
    CONSTRAINT etiquetas_j_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.etiquetas_j
    OWNER to postgres;

CREATE INDEX IF NOT EXISTS etiquetas_j_etiqueta_id_idx
    ON public.etiquetas_j USING btree
    (etiqueta_id ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS etiquetas_j_id_idx
    ON public.etiquetas_j USING btree
    (j_id ASC NULLS LAST)
    TABLESPACE pg_default;

The publication is defined as follows:

CREATE PUBLICATION publication_visor FOR TABLE etiquetas_j(
    etiqueta_id,
    fecha,
    j_id,
    usuario_del_id,
    usuario_mod_id ,
    organigrama_mod,
    id)

The error occurs when we execute in the provider: update etiquetas_j set organigrama_mod = '02' where id = 3;

Thanks, and sorry for the bad english!

We have already checked:

1. See what the indexes are.
2. See what differs in both schemas.
3. See what publisher was sending at publication create.
4. Applied ALTER TABLE etiquetas_j REPLICA IDENTITY FULL;

Answer 1

I solved it.

For some reason, we used ALTER TABLE in the publisher side and the subscription side to make id as primary key and the schemas, indexes, and everything was identical. Enable the subscription again and refresh the PUBLICATION on the CLIENT SIDE but never worked.

I did the next steps to solve it:

1. Pause the SUBSCRIPTION: ALTER SUBSCRIPTION subscription_name DISABLE
2. Drop the table FROM PUBLICATION in the PUBLISHER SIDE. ALTER PUBLICATION publication_name DROP TABLE table_name
3. In the PUBLISHER SIDE, backup the data of the table that was throwing error.
4. Delete the table in the PUBLISHER SIDE.
5. Create the table again in the PUBLISHER SIDE but now, with id as primary key from zero (not with alter).
6. Create a NEW SLOT in the PUBLISHER SIDE: SELECT pg_create_logical_replication_slot('YOUR_NEW_NAME_SLOT', 'pgoutput');
7. In the client side, resume the subscription. ALTER SUBSCRIPTION subscription_name ENABLE
8. Set the new slot to the subscriptor ALTER SUBSCRIPTION subscription_name SET (slot_name='YOUR_NEW_NAME_SLOT')
9. ALTER SUBSCRIPTION subscription_name REFRESH PUBLICATION;
10. See if SLOT has active: true. SELECT slot_name, plugin, slot_type, database, active FROM pg_replication_slots WHERE plugin = 'pgoutput';
11. If slot it's working ok. Restore the data in the PUBLISHER SIDE. CLIENT SIDE will start to replicate.
12. After the replication ends, update one row, or anything and will work.

We specify the slots in the subscriptor because we have two databases at the same server instance.