Postgres database logical replication setup script

setup-logical-replication.sh

#!/usr/bin/env bash

function log {
    echo `date "+%b %d %H:%M:%S"` $1
}

# Credentails for logical primary database owner
PG_SOURCE_HOST=""
PG_SOURCE_USER=""
PG_SOURCE_DATABASE=$1
PG_SOURCE_PASSWORD=""

# Credentials for logical replica database owner
PG_TARGET_HOST=""
PG_TARGET_USER=""
PG_TARGET_DATABASE=${2:-$PG_SOURCE_DATABASE}
PG_TARGET_PASSWORD=""

if [ -z "$PG_SOURCE_DATABASE" ]
then
      echo "Database name not provided. Exiting"
      exit 1
fi

# Dump schema from source database into users.sql file
log "Dumping users from source database"
export PGPASSWORD=$PG_SOURCE_PASSWORD
pg_dumpall -h $PG_SOURCE_HOST -U $PG_SOURCE_USER --globals-only --no-role-passwords > dump-users.sql

# Dump schema into pre-data.sql (schema only, ie tables & columns)
log "Dumping schema from source database"
pg_dump -h $PG_SOURCE_HOST -U $PG_SOURCE_USER -d $PG_SOURCE_DATABASE --schema-only --section=pre-data --no-owner --no-privileges > dump-pre-data.sql
log "Schema dump was created"

# Dump schema into post-data.sql (constraints, foreign keys, indexes)
log "Dumping constraints from source database"
pg_dump -h $PG_SOURCE_HOST -U $PG_SOURCE_USER -d $PG_SOURCE_DATABASE --schema-only --section=post-data --no-owner --no-privileges > dump-post-data.sql
log "Constraints dump was completed"

# Create users and schema on source database
log "Loading users into target database"
export PGPASSWORD=$PG_TARGET_PASSWORD
psql -h $PG_TARGET_HOST -U $PG_TARGET_USER -d postgres < dump-users.sql

# Load schema into target/replica database
log "Loading schema"
psql -h $PG_TARGET_HOST -U $PG_TARGET_USER -d $PG_TARGET_DATABASE < dump-pre-data.sql

# Load schema into target/replica database
psql -h $PG_TARGET_HOST -U $PG_TARGET_USER -d $PG_TARGET_DATABASE < dump-post-data.sql
log "Schema loaded"

# Create publication on primary database
log "Create publication on source database"
export PGPASSWORD=$PG_SOURCE_PASSWORD
psql -h $PG_SOURCE_HOST -U $PG_SOURCE_USER -d $PG_SOURCE_DATABASE -c "CREATE PUBLICATION ${PG_SOURCE_DATABASE}_publication FOR ALL TABLES;"
log "Publication created"

# Create subscription on target database
log "Create subscription on target database"
export PGPASSWORD=$PG_TARGET_PASSWORD
psql -h $PG_TARGET_HOST -U $PG_TARGET_USER -d $PG_TARGET_DATABASE -c "CREATE SUBSCRIPTION ${PG_TARGET_DATABASE}_subscription CONNECTION 'host=$PG_SOURCE_HOST dbname=$PG_SOURCE_DATABASE user=$PG_SOURCE_USER password=$PG_SOURCE_PASSWORD' PUBLICATION ${PG_SOURCE_DATABASE}_publication;"
log "Subscription created"
exit 0

# Check if intial copy is complete, and all tables are in "r" ready state (https://www.postgresql.org/docs/current/catalog-pg-subscription-rel.html)
psql -h $PG_TARGET_HOST -U $PG_TARGET_USER -d $PG_TARGET_DATABASE -c "SELECT * FROM pg_subscription_rel;"