Skip to content

PostgreSQL Reporting DB Creation

1. PRD DB Setup

  • 1.1 Check publication DB parameters

Make sure that the following parameters are configured:

  • Set in the postgresql.conf file
wal_level = logical       # It must be set as logical-level WAL log
max_replication_slots = 4 # Set the slots (adjust as per business reqirement)
max_wal_senders = 30      # Set the senders (adjust as per business reqirement)

  • Add the subscription db server IP into publication db server pg_hba.conf file

    host replication postgres 10.41.xx.xx/24 trust

  • 1.2 Create publication

psql -p 6432
\c MESP131

create publication pub_cim131
for table cim131.cimmodwnlogdetail,
cim131.cimbomlocation,
cim131.cimlineetl,
cim131.cimsamplingdetail,
cim131.cimmodeletl,
cim131.cimupninfo,
cim131.cimapiniworkstation,
cim131.cimcsnrule,
cim131.cimmodel,
cim131.cimcpnstatus
with (publish = 'insert, update, delete, truncate', publish_via_partition_root = false);

If you want to publish all the tables, please use the below SQL:

CREATE publication pub_cim131 for all tables;

2. Reporting DB Setup

  • 2.1 Install and create reporting DB

  • 2.2 Create schema same as PRD

CREATE SCHEMA IF NOT EXISTS cim131
    AUTHORIZATION cim131;
GRANT ALL ON SCHEMA cim131 TO cim131;

  • 2.3 Table structure import from PRD

  • export table structure from PRD DB

    pg_dump -U postgres \
  -h PRD_DB_IP -p 6432 \ 
  -d PRD_DB_NAME \ 
  --schema-only \
  --table=cim131.table1 \
  --table=cim131.table2 \
  --table=cim131.table3 \
  > selected_tables_schema.sql

    --schema-only：仅导出表的结构，而不导出数据。

    --no-owner 和 --no-privileges：跳过拥有者信息和权限信息，避免跨实例时角色权限冲突。

    • 也可以将所有表写入文件批量导出
    pg_dump -U postgres \
  -h PRD_DB_IP -p 6432 \
  -d PRD_DB_NAME \
  --schema-only \
  $(cat cim131-table.txt | xargs -I {} echo "--table={}") \
  > cim131_table.sql

  • copy export file to reporting DB server

  • import table structure to reporting db

    psql -U postgres \
  -h localhost -p 6434 \
  -d REP131 \
  -f cim131_table.sql

  • 2.4 Create subscription

CREATE SUBSCRIPTION sub_cim131
CONNECTION 'host={PRD_DB_IP} port=6432 user=postgres dbname=MESP131 password=xxxx'
PUBLICATION pub_cim131
WITH (connect = true, enabled = true, create_slot = true, slot_name = sub_cim130, 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;