Nashpush

postgresql-table-replication

2 min read

\

Adding a New Table to AWS Aurora PostgreSQL Replication

This guide covers the steps to add a new table to an existing replication setup in AWS Aurora PostgreSQL or to set up a new replication

Overview

  1. Primary Database: AWS Aurora PostgreSQL DB cluster with logical replication.
  2. Replica Databases: On-premise PostgreSQL databases.

Managing Replication

Replication is managed by creating logical slots, publications on the primary database, and subscriptions on the replica databases.

Managing Logical Slots

Example Usage of Logical Replication

Initial Setup of Replication

Step 1: Create the Table on the Primary Database

CREATE TABLE campaigns_broadcastintervaldelivery (
    id SERIAL PRIMARY KEY,
    ...
);

Step 2: Create a Publication on the Primary Database

CREATE PUBLICATION manager_db FOR TABLE campaigns_broadcastintervaldelivery;

Step 3: Create a Subscription on the Replica Database

Connect to the Replica Database with master user
export KUBECONFIG=~/<CLUSTER>
kubectl run postgresql-postgresql-client --rm --tty -i --restart='Never' --namespace webpush --image bitnami/postgresql  --command -- psql --host 10.0.0.2 -U postgres -d manager
Create the Subscription
CREATE SUBSCRIPTION manager_db_<CLUSTER> 
    CONNECTION 'host=webpush-worker-rds-prd-0.cmpslvys4mms.us-east-2.rds.amazonaws.com port=5432 dbname=manager user=<user> password=<password>' 
    PUBLICATION manager_db;

Adding a New Table to Replication

Example: Adding campaigns_broadcastintervaldelivery Table to Existing Publication manager_db and Subscription manager_db_de_00

Step 1: Create the Table on the Primary Database

CREATE TABLE campaigns_broadcastintervaldelivery (
    id SERIAL PRIMARY KEY,
    ...
);

Step 2: Add the Table to the Existing Publication

ALTER PUBLICATION manager_db ADD TABLE campaigns_broadcastintervaldelivery;

Step 3: Refresh the Subscription on the Replica Database

Connect to the Replica Database
export KUBECONFIG=~/<CLUSTER>
kubectl run postgresql-postgresql-client --rm --tty -i --restart='Never' --namespace webpush --image bitnami/postgresql  --command -- psql --host 10.0.0.2 -U postgres -d manager
Refresh the Subscription
ALTER SUBSCRIPTION manager_db_<CLUSTER> REFRESH PUBLICATION;

Step 4: Verify the Replication Status

Check the replication status to ensure everything is working correctly:

SELECT * FROM pg_stat_replication;

Step 5: Verify the Table on the Replica Database

SELECT COUNT(*) FROM campaigns_broadcastintervaldelivery;

Graph View