repmgr is an open source tool which helps to implment & monitor postgres replication and also helps in manageing the switchover and automatic failovers.
Here we are going to implement one master and two standby servers streaming replication with repmgr.
Master
(127.0.0.1)
Standby1 (127.0.0.2)
Standby2 (127.0.0.3)
Install Postgresql in all the 3 instances and enable passwordless connection between the instances using ssh.
#Remove all the postgresql leftovers from the system
sudo apt-get --purge remove postgresql-*
sudo rm -Rf /etc/postgresql /var/lib/postgresql
#Install Postgress & repmgr
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
sudo apt-get install postgresql-9.5-repmgr
sudo service postgresql stop
Edit postgresql.conf and pg_hba.conf files in all the 3 instances with the respective following changes, so that in future standby act as master with minimal changes.
> sudo su - postgres
> cd /etc/postgresql/9.5/main/
> vi postgresql.conf
postgresql.conf
===============
listen_address = '*'
shared_preload_libraries = 'repmgr_funcs' #for automated-failover
wal_level = hot_standby
archive_mode = on
archive_command = '/bin/true'
max_wal_senders = 5
wal_keep_segments = 64
max_replication_slots = 5
hot_standby = on
pg_hba.conf
===========
host all repmgr 127.0.0.1/32 trust
host all repmgr 127.0.0.2/32 trust
host all repmgr 127.0.0.3/32 trust
host
replication
repmgr
127.0.0.2/32
trust
host
replication
repmgr
127.0.0.3/32
trust
Master Setup
Start postgresql service in master and create dedicated user and db used by repmgr
> sudo service postgresql start
> sudo su - postgres
> createuser -s repmgr
> createdb repmgr -O repmgr
> psql -f /usr/share/postgresql/9.5/contrib/repmgr_funcs.sql repmgr
> exit
Create repmgr configuration file in the specified location and paste the following configuration.
> cd /etc/repmgr
> vi repmgr.conf
/etc/repmgr/repmgr.conf
==================================
cluster = 'cluster'
node = 1
node_name = 'node1'
conninfo = 'host=127.0.0.1 user=repmgr dbname=repmgr'
use_replication_slots = 1
reconnect_attempts=5
reconnect_interval=1
failover=automatic
promote_command='repmgr standby promote -f /etc/repmgr/repmgr.conf'
follow_command='repmgr standby follow -f /etc/repmgr/repmgr.conf'
Register Master:
> sudo su - postgres
> psql
> ALTER USER repmgr SET search_path TO repmgr_test, "$user", public;
> \q
> exit
> repmgr -f /etc/repmgr/repmgr.conf master register
[2017-11-02 11:39:26] [WARNING] get_master_node_id(): no active primary found
[2017-11-02 11:39:26] [NOTICE] master node correctly registered for cluster cluster with id 1 (conninfo: host=127.0.0.1 user=repmgr dbname=repmgr)
> sudo su - postgres
> psql repmgr
repmgr=# select * from repmgr_cluster.repl_nodes;
id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active
----+--------+------------------+---------+-------+----------------------------------------------+---------------+----------+--------
1 | master | | cluster | node1 | host=127.0.0.1 user=repmgr dbname=repmgr | repmgr_slot_1 | 100 | t
(1 row)
Slave Repmgr - Configuration:
/etc/repmgr/repmgr.conf
==================================
cluster = 'cluster'
node = 2
node_name = 'node2'
conninfo = 'host=127.0.0.2 user=repmgr dbname=repmgr'
use_replication_slots = 1
reconnect_attempts=5
reconnect_interval=1
failover=automatic
pg_bindir='/usr/lib/postgresql/9.5/bin'
promote_command='repmgr standby promote -f /etc/repmgr/repmgr.conf'
follow_command='repmgr standby follow -f /etc/repmgr/repmgr.conf'
Enable the necessary folder permissions for the initial database backup restore from the master
> sudo chmod -R 777 /var/lib/postgresql/9.5/main/
> sudo rm -rf /var/lib/postgresql/9.5/main/*
> sudo repmgr -h 127.0.0.2 -U repmgr -d repmgr -D /var/lib/postgresql/9.5/main --verbose --ignore-external-config-files -f /etc/repmgr/repmgr.conf standby clone
[2017-11-06 11:42:52] [NOTICE] using configuration file "/etc/repmgr/repmgr.conf"
[2017-11-06 11:42:52] [NOTICE] destination directory '/var/lib/postgresql/9.5/main' provided
[2017-11-06 11:42:52] [NOTICE] starting backup...
[2017-11-06 11:42:52] [HINT] this may take some time; consider using the -c/--fast-checkpoint option
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
[2017-11-06 11:42:54] [NOTICE] standby clone (using pg_basebackup) complete
[2017-11-06 11:42:54] [NOTICE] you can now start your PostgreSQL server
[2017-11-06 11:42:54] [HINT] for example : pg_ctl -D /var/lib/postgresql/9.5/main start
> sudo chmod -R g-rwx,o-rwx /var/lib/postgresql/9.5/main/
> sudo chown -R postgres.postgres /var/lib/postgresql/9.5/main/
sudo service postgresql start
Register Slave1:
> sudo repmgr -f /etc/repmgr/repmgr.conf standby register
2017-11-06 11:44:55] [NOTICE] standby node correctly registered for cluster cluster with id 2 (conninfo: host=127.0.0.2 user=repmgr dbname=repmgr)
> sudo su - postgres
> psql repmgr
repmgr=# SELECT * FROM repmgr_cluster.repl_nodes ORDER BY id;
id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active
----+---------+------------------+---------+-------+----------------------------------------------+---------------+----------+--------
1 | master | | cluster | node1 | host=127.0.0.1 user=repmgr dbname=repmgr | repmgr_slot_1 | 100 | t
2 | standby | 1 | cluster | node2 | host=127.0.0.2 user=repmgr dbname=repmgr | repmgr_slot_2 | 100 | t
(2 rows)
Repeat the above slave configuration steps for standby server 2.
repmgr=# SELECT * FROM repmgr_cluster.repl_nodes ORDER BY id;
id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active
----+---------+------------------+---------+-------+----------------------------------------------+---------------+----------+--------
1 | master | | cluster | node1 | host=127.0.0.1 user=repmgr dbname=repmgr | repmgr_slot_1 | 100 | t
2 | standby | 1 | cluster | node2 | host=127.0.0.2 user=repmgr dbname=repmgr | repmgr_slot_2 | 100 | t
3 | standby | 1 | cluster | node3 | host=127.0.0.3 user=repmgr dbname=repmgr | repmgr_slot_3 | 100 | t
(3 rows)
Query the master for additional information
postgres=# select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn
---------------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------
repmgr_slot_2 | | physical | | | t | 8261 | | | 0/5000610
repmgr_slot_3 | | physical | | | t | 11968 | | | 0/5000610
(2 rows)
postgres=# select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
8261 | 16384 | repmgr | node2 | 127.0.0.2 | | 58750 | 2017-11-06 11:44:13.460313+00 | | streaming | 0/5000610 | 0/5000610 | 0/5000610 | 0/5000610 | 0 | async
11968 | 16384 | repmgr | node3 | 127.0.0.3 | | 43360 | 2017-11-06 12:26:31.985751+00 | | streaming | 0/5000610 | 0/5000610 | 0/5000610 | 0/5000610 | 0 | async
(2 rows)
Testing:
==================
In Master,
postgres=# create table test(id integer);
CREATE TABLE
postgres=# insert into test values(1);
INSERT 0 1
postgres=# select * from test ;
id
----
1
(1 row)
Same data will be replicated in standby server 1 and 2
postgres=# select * from test ;
id
----
1
(1 row)
Ref:
https://github.com/alexcouper/shapostgres/blob/master/repmgr-readme.md
Comments
Post a Comment