CODEWITHSUNDEEP
replicationpostgresql-11logical-replication
Slava Rozhnev
Slava Rozhnev

Reputation: 10163

Can't delete from table after switch from logical to streaming replication

On my DEV server I tested logical replication, and return to streaming after that.

Now wal_level = replica and I have two slaves:

pid  |state     |application_name    |client_addr|write_lag      |flush_lag      |replay_lag     |sync_priority|sync_state|
-----|----------|--------------------|-----------|---------------|---------------|---------------|-------------|----------|
12811|streaming |db-slave1           |*.*.*.*    |00:00:00.000569|00:00:00.001914|00:00:00.001932|            0|async     |
25978|streaming |db-slave2           |*.*.*.*    |00:00:00.000568|00:00:00.001913|00:00:00.001931|            0|async     |

Now I created new table and insert one record. For example:

create table test_delete (
    id int
);

insert into test_delete values (1);

delete from test_delete where id = 1;

The table created and replicated to both slaves, but deletion query failed with error:

SQL Error [55000]: ERROR: cannot delete from table "test_delete" because it does not have a replica identity and publishes deletes Hint: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

So, I need help to restore status before switch lo logical replication and ability to delete from tables

Upvotes: 4

Views: 1712

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

After some investigation I found solution. Despite the fact that wal_level has changed in postgres.conf all tables still appears in pg_publication_tables. So for check publication status used:

select * from pg_publication_tables;

and for remove records:

drop publication <publication_name>;

Upvotes: 3

Related Questions