Postgres HA: Logical Replication (hot standby)

Overview

• available in Postgres-10.0+
• 1x master (read/write)
• N standby servers (read-only)

WAL files are synchronized automatically, in one direction.

If it is important to have a read-server in each geographical region, make sure to keep a standby within the datacenter.

+- DataCenter 1  --+           +- DataCenter 2 ---+
|                  |           |                  |
|    +--------+    |           | +-------------+  |
|    | master |    |           | | hot standby |  |
|    | (r/w)  |    |           | |   (r)       |  |
|    +--------+    |           | +-------------+  |
|                  |           |      /           |
|      WAL files >--------------------            |
|        |         |           |                  |
|        |         |           |                  |
|       \/         |           |                  |
|   +------------+ |           |                  |
|   | hot standby| |           |                  |
|   |   (r)      | |           |                  |
|   +------------+ |           |                  |
|                  |           |                  |
|                  |           |                  |
+------------------+           +------------------+

Configuration

master

Create Replicator User

Create postgres user that STANDBY servers will use to communicate with MASTER.

CREATE ROLE replicator
  REPLICATION
  LOGIN PASSWORD 'password';

GRANT ALL ON <yourtable>
  TO replicator;

Modify pg_hba.conf so user replicator can login from all STANDBY servers.

# substitute <STANDBY-IPADDR> with real address.
host all replicator <STANDBY-IPADDR> md5

Activate Logical WAL

Enable Write-Ahead-Logs(WAL), configuring them to use logical replication in postgres.conf.

wal_level = logical

You'll need to restart postgres

sudo systemctl restart postgres

Create Publication for target Tables

# connect to your database
\c your_database

# create a publication for each table that should be replicated
CREATE PUBLICATION alltables_pub   # all tables in db
  FOR ALL TABLES;

CREATE PUBliCATION yourtable_pub   # target tables
  FOR TABLE yourtable;

standby

Build All Tables

pg_dump -s > out.pgsql                      # dump the schema on the master server
scp out.pgsql <your_standby>                # copy it to standby
cat out.pgsql | sudo -u postgres postgres   # import dump on standby

Create Subscription

CREATE SUBSCRIPTION yourtables_sub
  CONNECTION 'dbname=yourdb host=yourhostip user=replicator password=password'
  PUBLICATION yourtables_pub;

Database Replicatd!

Your STANDBY server should now contain all of the replicated data from MASTER.