Postgres HA: Logical Replication (hot standby)
From wikinotes
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> md5Activate Logical WAL
Enable Write-Ahead-Logs(WAL), configuring them to use logical replication in
postgres.conf
.wal_level = logicalYou'll need to restart postgres
sudo systemctl restart postgresCreate 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 standbyCreate 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.