Reporting DB Rebuild¶

1. Drop subscription and publication¶

• 1.1 Drop REP DB subscription

Login REP DB server, and switch to user: postgres

psql -p 6434
\c REP131
select * from pg_catalog.pg_subscription;   # check subscriptions
drop subscription sub_pp131 cascade;

If deletion is not possible, you can first execute the below SQL:

```sql alter subscription sub_cim131 DISABLE; alter subscription sub_cim131 SET (slot_name = NONE);

• 1.2 Drop PRD DB publication

Login PRD DB server, and switch to user: postgres

psql -p 6432
\c MESP131
select * from pg_catalog.pg_publication;   # check publications
drop publication pub_pp131 cascade;

2. Re-create publication and subscription¶

• 2.1 Re-create publication (@PRD DB)

create publication pub_pp131
pp131.ppfshiftid,
pp131.uppuhinfo,
pp131.att_skill,
pp131.att_station,
pp131.att_station_audit
with (publish = 'insert, update, delete, truncate', publish_via_partition_root = false);

• 2.2 Re-create subscription (@Rep DB)

• 2.2.1 truncate table

  select schema all tables, and general truncate SQL

  select 'truncate table '|| string_agg(schemaname || '.' || relname,', ')||';'
  from pg_stat_user_tables where schemaname='pp131';
  

  copy and execute general truncate SQL to fast clear table data

  truncate table pp131.table1, pp131.table2;
  -- clear all the tables one by one
  

• 2.2.2 create subscription

  ```sql CREATE SUBSCRIPTION sub_pp131 CONNECTION 'host={PRD_DB_IP} port=6432 user=postgres dbname=MESP131 password=xxxx' PUBLICATION pub_pp131 WITH (connect = true, enabled = true, create_slot = true, slot_name = sub_pp131, synchronous_commit = 'off');

3. Reporting sync check¶

• 3.1 Subscription endpoint (Reporting) check

select * from pg_subscription_rel;

• 3.2 Publication endpoint (PRD) check

make sure the active status is always true

select * from pg_replication_slots;

make sure the active status is not set to false

select slot_name, database, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained_wal
from pg_replication_slots 
where NOT active;