Re: Help diagnosing replication (copy) error

[Steve Baldwin <steve.baldwin@xxxxxxxxx>]

On Sat, Mar 9, 2024 at 9:13 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx