For better result you should install database by this document
how-to install postgres database
i have 4 node config, 2 nodes in one datacenter and 2 in other
config will look like this:
DR1 DR2 DB01--->DB11 | | \_DB02 \_DB12
create config file:
node1 /u01/postgres/9.5/main/conf/repmgr.conf
node=1 cluster=db_cluster node_name=pg1 conninfo='host=pg01 user=repmgr dbname=postgres password=q1' pg_bindir=/usr/pgsql-9.5/bin/ master_response_timeout=5 reconnect_attempts=2 reconnect_interval=2 failover=manual promote_command='/usr/pgsql-9.5/bin/repmgr standby promote -f /u01/postgres/9.5/main/conf/repmgr.conf' follow_command='/usr/pgsql-9.5/bin/repmgr standby follow -f /u01/postgres/9.5/main/conf/repmgr.conf' use_replication_slots=1
node2 /u01/postgres/9.5/main/conf/repmgr.conf
node=2 cluster=db_cluster node_name=pg2 conninfo='host=pg02 user=repmgr dbname=postgres password=q1' pg_bindir=/usr/pgsql-9.5/bin/ master_response_timeout=5 reconnect_attempts=2 reconnect_interval=2 failover=manual promote_command='/usr/pgsql-9.5/bin/repmgr standby promote -f /u01/postgres/9.5/main/conf/repmgr.conf' follow_command='/usr/pgsql-9.5/bin/repmgr standby follow -f /u01/postgres/9.5/main/conf/repmgr.conf' use_replication_slots=1
node3 /u01/postgres/9.5/main/conf/repmgr.conf
node=3 cluster=db_cluster node_name=pg3 conninfo='host=pg03 user=repmgr dbname=postgres password=q1' pg_bindir=/usr/pgsql-9.5/bin/ master_response_timeout=5 reconnect_attempts=2 reconnect_interval=2 failover=manual promote_command='/usr/pgsql-9.5/bin/repmgr standby promote -f /u01/postgres/9.5/main/conf/repmgr.conf' follow_command='/usr/pgsql-9.5/bin/repmgr standby follow -f /u01/postgres/9.5/main/conf/repmgr.conf' use_replication_slots=1
node4 /u01/postgres/9.5/main/conf/repmgr.conf
node=4 cluster=db_cluster node_name=pg4 conninfo='host=pg04 user=repmgr dbname=postgres password=q1' pg_bindir=/usr/pgsql-9.5/bin/ master_response_timeout=5 reconnect_attempts=2 reconnect_interval=2 failover=manual promote_command='/usr/pgsql-9.5/bin/repmgr standby promote -f /u01/postgres/9.5/main/conf/repmgr.conf' follow_command='/usr/pgsql-9.5/bin/repmgr standby follow -f /u01/postgres/9.5/main/conf/repmgr.conf' use_replication_slots=1 upstream_node=3
also we need to setup passwordless auth of repmgr user
create .pgpass file in postgres user home directory with 0600 premissions:
All nodes:
cat ~/.pgpass *:*:*:repmgr:q1 chmod 600 ~/.pgpass
create repmgr user
createuser -s repmgr
also we need to allow repmgr to connect to database with replication role
add this lines to your pg_hba.conf file on all nodes:
cat /u01/postgres/9.5/main/conf/pg_hba.conf | tail -3
local replication,postgres repmgr trust host replicatoin,postgres repmgr 127.0.0.1/32 trust host replication,postgres repmgr 10.10.10.0/24 md5
Here I’m allowed to connect from all network, but if you want, you can limit this by certain machines.
Next step is configure repmgr
Create Master:
[postgres@pg1 ~]$ /usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf master register [2016-12-05 19:06:36] [NOTICE] master node correctly registered for cluster db_cluster with id 1 (conninfo: host=pg1 user=repmgr dbname=postgres password=q1)
create standbys
on host pg2 and pg3:
/usr/pgsql-9.5/bin/repmgr -c -h pg1 -d postgres -U repmgr -D $PGDATA -f /u01/postgres/9.5/main/conf/repmgr.conf standby clone
start standby:
pg_ctl start -o "-c config_file=/u01/postgres/9.5/main/conf/postgresql.conf"
and register standby:
/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf standby register
create last standby ( which is going to be cascade standby ) and we are create this standby from PG3 database
/usr/pgsql-9.5/bin/repmgr -c -h pg3 -d postgres -U repmgr -D $PGDATA -f /u01/postgres/9.5/main/conf/repmgr.conf standby clone
start and register database:
pg_ctl start -o "-c config_file=/u01/postgres/9.5/main/conf/postgresql.conf" /usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf standby register
final result is
postgres=# select * from repmgr_db_cluster.repl_nodes ; id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active ----+---------+------------------+------------+------+----------------------------------------------------------------+---------------+----------+-------- 1 | master | | db_cluster | pg1 | host=pg1 user=repmgr dbname=postgres password=xxxxxxxxxxxxxxxx | repmgr_slot_1 | 100 | t 2 | standby | 1 | db_cluster | pg2 | host=pg2 user=repmgr dbname=postgres password=xxxxxxxxxxxxxxxx | repmgr_slot_2 | 100 | t 3 | standby | 1 | db_cluster | pg3 | host=pg3 user=repmgr dbname=postgres password=xxxxxxxxxxxxxxxx | repmgr_slot_3 | 100 | t 4 | standby | 3 | db_cluster | pg4 | host=pg4 user=repmgr dbname=postgres password=xxxxxxxxxxxxxxxx | repmgr_slot_4 | 100 | t
1 комментарий
[…] recommend to read previous articles: How to install postgres How to configure repmgr to manage standby databases because I use those config in this […]