Logical replication claims to work, not working - new tables

From: Chris Angelico <rosuav(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Logical replication claims to work, not working - new tables
Date: 2024-01-20 22:43:11
Message-ID: CAPTjJmoFvsgO0awVc=iJ0szEc4D9BQT-zcy6sxEfbkfPT_6HfQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

PostgreSQL 15 on Debian, both ends of replication.

I'm doing logical replication in a bit of a complex setup. Not sure
how much of this is relevant so I'll give you a lot of detail; sorry
if a lot of this is just noise.

* Bidirectional alternating-master replication. Since I'm still on PG
15, the replication is up in one direction, down in the other, rather
than actually being simultaneous.
* Replication is defined as "for all tables". All relevant tables are
in a single schema, "stillebot".
* Replication was working fine on initial deployment, including a swap
of master/slave.
* One table was created without a primary key, and subsequently
altered to have "replica identity full".
* Replication is not working for this table (stillebot.settings) but
is working for other tables.

I tried restarting Postgres on the slave and monitoring
/var/log/postgresql/postgresql-15-main.log and it all seemed happy.
According to pg_subscription_rel, all tables are in their
"replication" phase:

select srsubstate,srsublsn,relname from pg_subscription_rel join
pg_class on srrelid=oid;
srsubstate | srsublsn | relname
------------+----------+--------------------------
r | | user_followed_categories
r | | config
r | | config_exportable
r | | commands
r | | settings
(5 rows)

If I make a change to user_followed_categories, it works fine. If I
make a change to settings, it does not replicate.

The stillebot.config table seems somewhat desynchronized - rows are
missing - but if I INSERT a new row into it, it gets properly
replicated.

How can I dig into this to determine what's going on? Have I broken
things by creating a table without a primary key? (It's a singleton
table, will only ever have one row in it; could add a meaningless PK
if it helps the replication.) Are there other logs to inspect?

Thanks in advance!

ChrisA

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Justin 2024-01-21 18:25:37 Re: Logical replication claims to work, not working - new tables
Previous Message Andreas Joseph Krogh 2024-01-20 06:10:49 Re: Daterange question