dsouzajude

### postgresql.conf

# Required to enable logical replication
wal_level = logical

# Tweaks
max_wal_senders = 35
max_worker_processes = 35
max_replication_slots = 35
max_logical_replication_workers = 35

dsouzajude

### pg_hba.conf
host    <DB_NAME>       rep_user    <RDS_CIDR>     md5

dsouzajude

/*
 * These statements will perform the cleanup operation of a PostgreSQL logical replication
 * Both the publisher and the subscriber databases need to be online for this to work.
 */

-- These statements should run on the subscriber database
ALTER SUBSCRIPTION <SUBCRIPTION_NAME> DISABLE; 
DROP SUBSCRIPTION <SUBCRIPTION_NAME>;

-- These statements should run on the publisher database

dsouzajude

-- Updates sequence data with an offset of 5000
WITH sequences AS (
    SELECT *
    FROM (
        SELECT table_schema,
               table_name,
               column_name,
               pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) AS col_sequence
        FROM information_schema.columns
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')

dsouzajude

-- Analyzes the contents of a database’s tables and collects statistics to generate better query plans
ANALYZE VERBOSE;

-- Optional manually vacuume to remove bloat
VACUUM VERBOSE ANALYZE;

dsouzajude

-- Sanity check with schemas and tables --------------------------

-- Check counts of objects in the database
SELECT
	n.nspname as schema_name
	,CASE c.relkind
	   WHEN 'r' THEN 'table'
	   WHEN 'v' THEN 'view'
	   WHEN 'i' THEN 'index'
	   WHEN 's' THEN 'special'

dsouzajude

# Start a tmux session
tmux new -s replication

# Apply indexes from the schema taken before (https://gist.github.com/dsouzajude/ac6cc637665e6f7c418edea351b6e3fc)
pg_restore -h $RDS_ENDPOINT -p 5432 --user postgres --dbname $DB_NAME -v -Fc -j 35 -L indexes-etc.list schema-post-data.dump

dsouzajude

/*
 * To be run in the destination (subscriber) database.
 * It gives you the state of each replication relation in a subscription.
 * srsubstate: i = initialize, d = data is being copied, s = synchronized, r = ready (normal replication)
 * https://www.postgresql.org/docs/13/catalog-pg-subscription-rel.html
 */

SELECT a.subname, b.srsubstate, count(*)
FROM pg_subscription_rel b, pg_subscription a
WHERE b.srsubid=a.oid

dsouzajude

/*
 * To be run in the source (publisher) database
 * It checks the replica lag in terms of bytes between the source and destination databases in a logical replication
 */

SELECT slot_name,
       confirmed_flush_lsn,
       pg_current_wal_lsn() as pg_current_wal_lsn,
       ((pg_current_wal_lsn() - confirmed_flush_lsn) / 1024 ) AS lsn_distance_kb
FROM pg_catalog.pg_replication_slots

dsouzajude

# --- Create publication at source --------------------------------------
psql -h $SOURCE_ENDPOINT -d $DB_NAME -c 'CREATE PUBLICATION pub_all FOR ALL TABLES;'

# Verify the publication exists
psql -h $SOURCE_ENDPOINT -d $DB_NAME -c 'SELECT * FROM pg_publication;'


# --- Create subscription at destination --------------------------------
psql -h $RDS_ENDPOINT -d $DB_NAME -c "CREATE SUBSCRIPTION sub_all CONNECTION 'host=<SOURCE_DB_IP> port=5432 dbname=<DB_NAME> user=rep_user password=<REP_USER_PASSWORD>' PUBLICATION pub_all;"