Re: Logical replication failure modes

[Philip Warner]

I should have added that the source DB is 16.1 and the target is 16.0

Logical replication failure modes

[Philip Warner]

I am trying to discover the causes of occasional data loss in logical
replication; it is VERY rare and happens every few week/months. 


Our setup is a source DB running in docker on AWS cloud server. The
source database is stored in on local disks on the cloud server. 


The replication target is a K8 POD running in an AWS instance with an
attached persistent AWS disk. The disk mounting is managed by K8.
Periodically this POD is deleted and restarted in an orderly way, and
the persistent disk stores the database. 


What we are seeing is *very* occasional records not being replicated in
the more active tables. 


Sometimes we have a backlog of several GB of data due to missing fields
in the target or network outages etc. 


I am also seeing signs that some triggers are not being applied (at the
same time frame): ie. data *is* inserted but triggers that summarize
that data is not summarizing some rows and the dates on those
non-summarized rows corresponds to dates on unrelated missing rows in
other tables.

This all leads me to conclude that there might be missing transactions?
Or non-applied transactions etc. But it is further complicated by the
fact that there is a second target database that *does* have all the
missing records. 


Any insights or avenues of exploration would be very welcome!

RE: pg_dump not dumping the run_as_owner setting from version 16?

[Philip Warner]

...patch actually attached this time...

> > I as far as I can tell, pg_dump does not dup the ‘run_as_owner` setting for 
> > a subscription.
> > 
> > Should it? Should I submit a patch? It seems pretty trivial to fix if 
> > anyone else is working on it.
> 
> Yes, it certainly should.  That is an omission in 482675987b.
> Go ahead and write a fix!

Please find attached a patch for pg_dump to honour the setting of 
`run_as_owner`; I believe that effective pre-16 behavious was to run as owner, 
so I have set the flag to ‘t’ for pre-16 versions. Please let me know if you 
would prefer the opposite.


> > Further to this: it seems that `Alter Subscription X 
> > Set(Run_As_Owner=True);`
> > has no influence on the `subrunasowner` column of pg_subscriptions.
> 
> This seems to have been fixed in f062cddafe.

Yes, I can confirm that in the current head `pg_subscriptions` reflects the 
setting correctly.


pg_dump-run_as_owner.patch
Description: Binary data

RE: pg_dump not dumping the run_as_owner setting from version 16?

[Philip Warner]

> > I as far as I can tell, pg_dump does not dup the ‘run_as_owner` setting for 
> > a subscription.
> > 
> > Should it? Should I submit a patch? It seems pretty trivial to fix if 
> > anyone else is working on it.
> 
> Yes, it certainly should.  That is an omission in 482675987b.
> Go ahead and write a fix!

Please find attached a patch for pg_dump to honour the setting of 
`run_as_owner`; I believe that effective pre-16 behavious was to run as owner, 
so I have set the flag to ‘t’ for pre-16 versions. Please let me know if you 
would prefer the opposite.


> > Further to this: it seems that `Alter Subscription X 
> > Set(Run_As_Owner=True);`
> > has no influence on the `subrunasowner` column of pg_subscriptions.
> 
> This seems to have been fixed in f062cddafe.

Yes, I can confirm that in the current head `pg_subscriptions` reflects the 
setting correctly.

RE: pg_dump not dumping the run_as_owner setting from version 16?

[Philip Warner]

Further to this: it seems that `Alter Subscription X Set(Run_As_Owner=True);` 
has no influence on the `subrunasowner` column of pg_subscriptions.

Sent from Mail for Windows

From: Philip Warner
Sent: Friday, 27 October 2023 3:26 PM
To: pgsql-hackers@lists.postgresql.org
Subject: pg_dump not dumping the run_as_owner setting from version 16?

Hi,

I as far as I can tell, pg_dump does not dup the ‘run_as_owner` setting for a 
subscription.

Should it? Should I submit a patch? It seems pretty trivial to fix if anyone 
else is working on it.

Sent from Mail for Windows

pg_dump not dumping the run_as_owner setting from version 16?

[Philip Warner]

Hi,

I as far as I can tell, pg_dump does not dup the ‘run_as_owner` setting for a 
subscription.

Should it? Should I submit a patch? It seems pretty trivial to fix if anyone 
else is working on it.

Sent from Mail for Windows