Skip to content

Latest commit

 

History

History
279 lines (124 loc) · 7.38 KB

README.md

File metadata and controls

279 lines (124 loc) · 7.38 KB
Raw

Setting up HA PostgreSql

Prepare db1 db server

  1. Setup directory structure for db1 db server

  2. Standup db1 db server

    docker network create pgdb-ha-network --subnet "10.0.0.1/24"

    docker-compose -f docker-compose.db1.yaml up --force-recreate

    docker-compose -f docker-compose.db1.yaml down

    cp db/config/pg_hba.conf db/config/postgresql.conf db/db1-data

    docker-compose -f docker-compose.db1.yaml up --force-recreate

Note: 3,4,5 are execute if pb_hba.conf and postgresql.conf files do not exist

  1. Prepare posgres.conf file

    docker run -i --rm postgres cat /usr/share/postgresql/postgresql.conf.sample > ./postgres.conf

    Note: Use https://pgtune.leopard.in.ua for VM/DB tune up values

  2. Add Values below to postres.conf file to enable replication

     # Replication

 wal_level = replica

 hot_standby = on

 max_wal_senders = 10

 max_replication_slots = 10
 
 hot_standby_feedback = on

  3. Create pg_hba.conf file, use docs for more fine tuning

    Follow link: https://www.postgresql.org/docs/10/auth-pg-hba-conf.html

Prepare the worker servers

  1. Create the replicator user on primary db -- postgresdb01

    docker exec -it postgresdb01 psql -U pgdbuser

    CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'pgdbpassword';

  2. Create the physical replication slot on db1 for each container

    SELECT * FROM pg_create_physical_replication_slot('replication_slot_slave1');

  3. Run below query to confirm physical replication has been created

    SELECT * FROM pg_replication_slots;

    \q; -- Quite Postgres shell

  4. Restart db1 server for new configs to be picked up

    ` docker-compose -f docker-compose.db1.yaml down

     docker-compose -f docker-compose.db1.yaml up --force-recreate

    `

  5. Create db1 pg_base_backup for slave restore

    docker exec -it postgresdb01 /bin/bash

    pg_basebackup -D /tmp/postgresslave1 -S replication_slot_slave1 -X stream -P -U replicator -Fp -R

    ls /tmp/ -- confirm folder has been created

    exit -- Exit container

  6. Copy pg_basebackup output directories to host machine for data mounting into worker containers

    docker cp postgresdb01:/tmp/postgresslave1/ ./db/db2-data

    Repeat above command to the number of worker containers

Stand up the worker container while mounting the ./db/db2-data directory

Note: This backup directory can be anywhere

  1. Edit recovery.conf file from pg_basebackup dir(worker-data) to set correct db1 server host IP/DNS, set failover file as well

     In config key: primary_conninfo, edit value of host to point to db1 host(ie: host=postgresdb01)
 Example below:

 standby_mode = 'on'

 primary_conninfo = 'host=postgresdb01 user=replicator passfile=''/root/.pgpass'' port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'

 primary_slot_name = 'replication_slot_slave2'

 trigger_file = '/var/lib/postgresql/data/failover'

  2. In db/worker-data directory, edit postgresql.conf file to enable hot_standby

     wal_level = hot_standby # allow queries while in read only recovery mode

 max_wal_senders = 5 # this sends the wal files, how many db2 can connect and pull docker

 hot_standby = on # allow queries while in read only recovery mode

  3. Launch the workers docker-compose file, validate replications is activated

     docker-compose -f docker-compose.db2.yaml up --force-recreate

 docker exec -it postgresdb02 /bin/bash

 psql -h postgresdb02 -p 5432 -U pgdbuser

 select * from pg_stat_replication;

  4. Validate data replication on worker containers

    a. Create few tables in db1 server

     docker run --rm --name postgres-test --network=pgdb-ha-network -it postgres:10 /bin/bash

 psql -h postgresdb01 -p 5432 -U pgdbuser

 Enter password when prompted

 \l;    -- list databases

 CREATE DATABASE pghatest;

 \c pghatest; -- Switch to database

 CREATE TABLE todo (id SERIAL, name varchar(100) NOT NULL, description varchar(200),PRIMARY KEY(id));

 \dt; -- Show tables

 insert into todo(name, description) values('TODO #1', 'Describing TODO #1');

 select * from todo; -- Display content of todo table

 \q   -- Quit PostgreSql shell

    b. Connect to worker(R\O) containers to confirm replication is in full swing

     docker exec -it postgresdb02 /bin/bash

 psql -h postgresdb02 -p 5432 -U pgdbuser

 Enter password when prompted

 \l    -- list databases

 \c pghatest;   -- Switch to database

 \dt; -- Show tables

 select * from todo; -- Display content of todo table

 insert into todo(name, description) values('NO TODO #1', 'NO TODO #1 coz R/O Only');

 Since postgresdb02 is in R/O mode, insert statement will fail with: ERROR:  cannot execute INSERT in a read-only transaction

 \q   -- Quit PostgreSql shell

Failover Steps

  1. Validate recovery.conf file in data direcotry of vm/container taking over, if file absent create one with below content

     standby_mode = 'on'

 primary_conninfo = 'host=<FAILOVER_HOST> user=replicator passfile=''/root/.pgpass'' port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'

 primary_slot_name = 'replication_slot_slave1'

 trigger_file = '/var/lib/postgresql/data/failover'

  2. Edit recovery.conf file for each worker vm to point to correct db1 host

  3. Restart the VMs for which recovery.conf file was edited

  4. Create failover file is it does not exist

    Example: For setting up postgresdb02 as db1, execute following;

     docker exec -it postgresdb02 /bin/bash

 touch /var/lib/postgresql/data/failover

  5. Restart the VMs for which recovery.conf file was edited

Restore db1 as primary

  1. Create db2 pg_base_backup for worker restore

     docker exec -it postgresdb02 /bin/bash

 pg_basebackup -D /tmp/postgresslave1 -S replication_slot_slave1 -X stream -P -U replicator -Fp -R

 ls /tmp/ -- confirm folder has been created

 exit -- Exit container

  2. Copy pg_basebackup output directories to host machine for data mounting into worker containers

     docker cp postgresdb02:/tmp/postgresslave1/ ./db/db1-data

 Repeat above command to the number of worker containers

  3. Edit recovery.conf file from pg_basebackup dir(db1-data) to set correct db2 server host IP/DNS, set failover file as well

    In config key: primary_conninfo, edit value of host to point to db1 host(ie: host=postgresdb01) Example below:

     standby_mode = 'on'

 primary_conninfo = 'host=postgresdb02 user=replicator passfile=''/root/.pgpass'' port=5432 sslmode=prefer sslcompression=1 

 krbsrvname=postgres target_session_attrs=any'

 primary_slot_name = 'replication_slot_slave2'

 trigger_file = '/var/lib/postgresql/data/failover'

  4. Launch the db2 docker-compose file, validate replications is activated

     docker-compose -f docker-compose.db2.yaml up --force-recreate

 docker exec -it postgresdb02 /bin/bash

 psql -h postgresdb02 -p 5432 -U pgdbuser

 select * from pg_stat_replication;

 \c pghatest;   -- Switch to database

 \dt; -- Show tables

 select * from todo; -- Display content of todo table