Start logical replication by creating a publication and subscription

postgresql-create-publication-subscription.sh

# --- 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;"

# Verify the subscription exists
psql -h $RDS_ENDPOINT -d $DB_NAME -c 'SELECT * FROM pg_subscription;'


# --- Verify logical replication is in progress -------------------------

# Check the COPY operation has started at the source (it should show some processes doing a COPY operation)
ps -ef | grep -v grep | grep postgres: | grep COPY

# Tail the PostgreSQL log file for any error
tail -f /var/log/postgresql/postgresql-13-main.log | grep -i ERROR