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:

[code]
DR1 DR2
DB01—>DB11
| |
\_DB02 \_DB12
[/code]

create config file:

node1 /u01/postgres/9.5/main/conf/repmgr.conf

[code]
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
[/code]

node2 /u01/postgres/9.5/main/conf/repmgr.conf

[code]
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
[/code]

node3 /u01/postgres/9.5/main/conf/repmgr.conf

[code highlight=»13″]
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
[/code]

node4 /u01/postgres/9.5/main/conf/repmgr.conf

[code highlight=»13″]
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
[/code]

also we need to setup passwordless auth of repmgr user
create .pgpass file in postgres user home directory with 0600 premissions:

All nodes:

[code]
cat ~/.pgpass
*:*:*:repmgr:q1
chmod 600 ~/.pgpass
[/code]

create repmgr user

[code]
createuser -s repmgr
[/code]

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

[code highlight=»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
[/code]

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:
[code language=»sql»]
[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)
[/code]

create standbys
on host pg2 and pg3:
[code]
/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
[/code]
start standby:
[code]
pg_ctl start -o "-c config_file=/u01/postgres/9.5/main/conf/postgresql.conf"
[/code]
and register standby:
[code]
/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf standby register
[/code]

create last standby ( which is going to be cascade standby ) and we are create this standby from PG3 database

[code]
/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
[/code]

start and register database:
[code]
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
[/code]

final result is

[code language=»sql»]
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
[/code]

Вливайтесь в общение

1 комментарий

Оставьте комментарий

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

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.