Skip to content

postgresql repmgr how-to

postgresql repmgr how-to published on 1 комментарий к записи postgresql repmgr how-to

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 комментарий

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика