Re: Help diagnosing replication (copy) error

On Sat, Mar 9, 2024 at 9:13 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

>
> I should been clearer.
>
> What are the CREATE PUBLICATION and CREATE SUBSCRIPTION statements?
>
> The publications were created a while ago. Does this help:

b2bcreditonline=> select * from pg_publication;
-[ RECORD 1 ]+-----------------
oid | 18829
pubname | b2bc_master
pubowner | 16760
puballtables | f
pubinsert | t
pubupdate | t
pubdelete | t
pubtruncate | t
pubviaroot | f
-[ RECORD 2 ]+-----------------
oid | 18830
pubname | b2bc_master_only
pubowner | 16760
puballtables | f
pubinsert | t
pubupdate | t
pubdelete | t
pubtruncate | t
pubviaroot | f
-[ RECORD 3 ]+-----------------
oid | 18831
pubname | b2bc_shard
pubowner | 16760
puballtables | f
pubinsert | t
pubupdate | t
pubdelete | t
pubtruncate | t
pubviaroot | f

The publication getting 'stuck' is b2bc_shard. It defines a bunch of
tables, one of which is audit.log_row. This table is quite large (600+ GB).

b2bcreditonline=> select * from pg_publication_rel where prrelid =
'audit.log_row'::regclass;
-[ RECORD 1 ]--
oid | 18832
prpubid | 18831
prrelid | 60067
prqual |
prattrs |

Here's the subscription info:

b2bcreditonline=> select * from pg_subscription;
-[ RECORD 1
]-------+-------------------------------------------------------------------------------------------------------------------------------------------
oid | 378075175
subdbid | 16404
subskiplsn | 0/0
subname | b2bcreditonline_prod_e_master
subowner | 16388
subenabled | t
subbinary | f
substream | f
subtwophasestate | d
subdisableonerr | f
subpasswordrequired | t
subrunasowner | f
subconninfo | host=
b2bcreditonline-prod-d.cliwvveusxrd.us-east-1.rds.amazonaws.com user=xxx
password=xxx dbname=b2bcreditonline
subslotname | b2bcreditonline_prod_e_master
subsynccommit | off
subpublications | {b2bc_master}
suborigin | any
-[ RECORD 2
]-------+-------------------------------------------------------------------------------------------------------------------------------------------
oid | 378075176
subdbid | 16404
subskiplsn | 0/0
subname | b2bcreditonline_prod_e_master_only
subowner | 16388
subenabled | t
subbinary | f
substream | f
subtwophasestate | d
subdisableonerr | f
subpasswordrequired | t
subrunasowner | f
subconninfo | host=
b2bcreditonline-prod-d.cliwvveusxrd.us-east-1.rds.amazonaws.com user=xxx
password=xxx dbname=b2bcreditonline
subslotname | b2bcreditonline_prod_e_master_only
subsynccommit | off
subpublications | {b2bc_master_only}
suborigin | any
-[ RECORD 3
]-------+-------------------------------------------------------------------------------------------------------------------------------------------
oid | 378075177
subdbid | 16404
subskiplsn | 0/0
subname | b2bcreditonline_prod_e_shard
subowner | 16388
subenabled | t
subbinary | f
substream | f
subtwophasestate | d
subdisableonerr | f
subpasswordrequired | t
subrunasowner | f
subconninfo | host=
b2bcreditonline-prod-d.cliwvveusxrd.us-east-1.rds.amazonaws.com user=xxx
password=xxx dbname=b2bcreditonline
subslotname | b2bcreditonline_prod_e_shard
subsynccommit | off
subpublications | {b2bc_shard}
suborigin | any

--
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>