Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We will perform REPMGR installation and configuration for postgresql in this article. You can see our environments. pg is our primary postgresql cluster and the others are our standbys
| Machine | IP | ROLE |
| pg | 10.0.0.4 | Primary |
| pgrep | 10.0.0.5 | Replication |
| Pgrep2 | 10.0.0.6 | Replication2 |
Pre Request:
- PostgreSQL must be installed in Primary and Standbys..
- Replication must be configured for this environment.
You may want to read below articles to install PostgreSQL and configure replication.
“How To Install PostgreSQL On centos/redhat“,
“How To Install PostgreSQL Replication By using archive_command On Centos“,
“How To Create PostgreSQL Repository To Install Postgres Without Internet“,
“How To Install PostgreSQL 9.6 on Debian 8.x (Jessie)“,
“How To Install PostgreSQL Replication With Replication Slots“,
“How To Install Streaming Replication in PostgreSQL 12”
Install REPMGR
We are installing repmgr12 in all postgresql database machine.
1 | sudo yum install repmgr12 |
We are executing below command in Primary Node.
1 2 3 4 5 6 7 8 9 10 11 12 13 | -bash-4.2$ psql psql (12.2) Type "help" for help. postgres=# createuser --superuser repmgr -bash-4.2$ createdb --owner=repmgr repmgr -bash-4.2$ psql -c "ALTER USER repmgr SET search_path TO repmgr, public;" ALTER ROLE |
1 | shared_preload_libraries = 'repmgr' |
For Primary:
1 2 3 4 5 6 7 | node_id=1 node_name='PG-Node1' conninfo='host=10.0.0.4 user=repmgr dbname=repmgr connect_timeout=2' data_directory='/var/lib/pgsql/12/data' |
We are changing below parameters in /etc/repmgr/12/repmgr.conf file in standby server.
For Replication:
1 2 3 4 5 6 7 8 9 | node_id=2 node_name='PG-Node2' conninfo='host=10.0.0.5 user=repmgr dbname=repmgr connect_timeout=2' data_directory='/var/lib/pgsql/12/data' |
We are changing below parameters in /etc/repmgr/12/repmgr.conf file in standby2 server.
For Replication2:
1 2 3 4 5 6 7 8 9 | node_id=3 node_name='PG-Node3' conninfo='host=10.0.0.6 user=repmgr dbname=repmgr connect_timeout=2' data_directory='/var/lib/pgsql/12/data' |
We are adding below all line in pg_hba.conf file in all postgresql database machine. And also restart all database service. (if you must change 10.0.0.0 ip addreses because your machines ip can be different.)
1 2 3 4 5 6 7 8 9 10 11 | local replication repmgr trust host replication repmgr 127.0.0.1/32 md5 host replication repmgr 10.0.0.0/16 md5 local repmgr repmgr trust host repmgr repmgr 127.0.0.1/32 md5 host repmgr repmgr 10.0.0.0/16 md5 |
We are testing below commands in standby and standby2 machine.
1 2 3 4 5 6 7 | -bash-4.2$ psql 'host=10.0.0.4 user=repmgr dbname=repmgr connect_timeout=2' psql (12.2) Type "help" for help. repmgr=# |
We are registering below commands in primary node.
1 2 3 4 5 6 7 8 9 | -bash-4.2$ /usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf primary register INFO: connecting to primary database... NOTICE: attempting to install extension "repmgr" NOTICE: "repmgr" extension successfully installed NOTICE: primary node record (ID: 1) registered |
We are registering below command in standby servers.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | -bash-4.2$ /usr/pgsql-12/bin/repmgr standby register -F INFO: connecting to local node "PG-Node2" (ID: 2) INFO: connecting to primary database WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID 1) WARNING: local node not attached to primary node 1 NOTICE: -F/--force supplied, continuing anyway INFO: standby registration complete NOTICE: standby node "PG-Node2" (ID: 2) successfully registered |
We are checking registration status below command in primary machine.
1 | -bash-4.2$ /usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf cluster show |
If you want you will look replication information in primary machine.
1 | select * from pg_stat_replication; |
Best Regards
Engin YILMAZ.
