Re: Logical replication claims to work, not working - new tables

On Sat, Jan 20, 2024, 5:43 PM Chris Angelico <rosuav(at)gmail(dot)com> wrote:

> 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
>
>
When using replica set to full this kicks off a full table scan for each
update or delete this is very expensive. If there are no errors being
reported you will find it is working but hung doing full scans. Inserts
are just appended to end of heap.

You can copy the replication slot on the primary to peak at which
transactions LR is hung on to confirm.

Adding a primary key will fix this issue. Note PG 16 can use indexes to
find qualifying rows when a table's replica is set to full.

>
>