Postgresql repmgr setup
I. soft requires (Master/Slave)
OS: CentOS Linux release 7.3.1611 (Core) X64
1.disabled SELINUX
# more / etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing-SELinux security policy is enforced.
# permissive-SELinux prints warnings instead of enforcing.
# disabled-No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
# targeted-Targeted processes are protected
# minimum-Modification of targeted policy. Only selected processes are protected.
# mls-Multi Level Security protection.
SELINUXTYPE=targeted
2.disabled firewall
Systemctl disable firewalld.service
3.install pg repository rpm
Pgdg-centos10-10-1.noarch.rpm
Https://yum.postgresql.org/repopackages.php
4.install repmgr repository rpm
Yum install http://packages.2ndquadrant.com/repmgr/yum-repo-rpms/repmgr-rhel-1.0-1.noarch.rpm
5.install repmgr
# yum install-y repmgr10
# chown postgres.postgres-R / etc/repmgr/10/
# systemctl enable repmgr10.service
6.set postgres user pwd and pg path
# pgdata #
# mkdir / pgdata10
# chown postgres.postgres / pgdata10/
# pg password #
# passwd postgres
# pg PATH #
# su-postgres
-bash-4.2$ vi .bash _ profile
[- f / etc/profile] & & source / etc/profile
PATH=/usr/pgsql-10/bin:$PATH
Export PATH
PGDATA=/var/lib/pgsql/10/data
Export PGDATA
# If you want to customize your settings
# Use the file below. This is not overridden
# by the RPMS.
[- f / var/lib/pgsql/.pgsql_profile] & & source / var/lib/pgsql/.pgsql_profile
~
# set pg boot #
# systemctl enable postgresql-10
# vi / usr/lib/systemd/system/postgresql-10.service
# Location of database directory
Environment=PGDATA=/pgdata10/
II. Hostname and ssh
1.set hostname
Master
Hostnamectl set-hostname 'pgdb1'
[root@pgdb1 ~] # more / etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
:: 1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.3.226 pgdb1
172.16.3.228 pgdb2
Slave
Hostnamectl set-hostname 'pgdb2'
[root@pgdb2 ~] # more / etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
:: 1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.3.226 pgdb1
172.16.3.228 pgdb2
[root@pgdb2 ~] #
2.ssh
Master
[root@pgdb1 ~] # su-postgres
Last login: Mon Sep 4 10:32:25 CST 2017 on pts/0
-bash-4.2 $
-bash-4.2 $
-bash-4.2$ ssh-keygen-t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/ var/lib/pgsql/.ssh/id_rsa):
Created directory'/ var/lib/pgsql/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in / var/lib/pgsql/.ssh/id_rsa.
Your public key has been saved in / var/lib/pgsql/.ssh/id_rsa.pub.
The key fingerprint is:
22:84:86:c4:2a:1e:d2:3b:9e:86:b6:1c:72:d5:d2:32 postgres@pgdb1
The key's randomart p_w_picpath is:
+-[RSA 2048]-+
|. | |
| .o. | |
| o.o. | |
| + o.. O |
| | +.. E + S |
| .o. =. | |
|. + .o |
| | + o = |
|. +. | |
+-+
-bash-4.2 $
-bash-4.2$ ssh-copy-id postgres@pgdb2
The authenticity of host 'pgdb2 (172.16.3.228)' can't be established.
ECDSA key fingerprint is c1:b4:f8:21:7e:3f:81:e4:e9:e8:93:43:d5:8e:0f:0e.
Are you sure you want to continue connecting (yes/no)? Yes
/ bin/ssh-copy-id: INFO: attempting to log in with the new key (s), to filter out any that are already installed
/ bin/ssh-copy-id: INFO: 1 key (s) remain to be installed-- if you are prompted now it is to install the new keys
Postgres@pgdb2's password:
Number of key (s) added: 1
Now try logging into the machine, with: "ssh 'postgres@pgdb2'"
And check to make sure that only the key (s) you wanted were added.
-bash-4.2 $
-bash-4.2 $
-bash-4.2$ ssh pgdb2 date
Mon Sep 4 10:55:40 CST 2017
-bash-4.2 $
Slave
[root@pgdb2 ~] # su-postgres
Last login: Mon Sep 4 10:32:25 CST 2017 on pts/0
-bash-4.2$ ssh-keygen-t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/ var/lib/pgsql/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in / var/lib/pgsql/.ssh/id_rsa.
Your public key has been saved in / var/lib/pgsql/.ssh/id_rsa.pub.
The key fingerprint is:
38:9b:0b:38:9a:ef:ae:75:e5:74:a9:3d:cc:3c:53:05 postgres@pgdb2
The key's randomart p_w_picpath is:
+-[RSA 2048]-+
| | E |
|. | |
|. | |
|. . . | |
| = S. | |
|. + @. | |
| | + o = O |
| + o. . + |
| + = +. | |
+-+
-bash-4.2 $
-bash-4.2 $
-bash-4.2$ ssh-copy-id postgres@pgdb1
The authenticity of host 'pgdb1 (172.16.3.226)' can't be established.
ECDSA key fingerprint is c1:b4:f8:21:7e:3f:81:e4:e9:e8:93:43:d5:8e:0f:0e.
Are you sure you want to continue connecting (yes/no)? Yes
/ bin/ssh-copy-id: INFO: attempting to log in with the new key (s), to filter out any that are already installed
/ bin/ssh-copy-id: INFO: 1 key (s) remain to be installed-- if you are prompted now it is to install the new keys
Postgres@pgdb1's password:
Number of key (s) added: 1
Now try logging into the machine, with: "ssh 'postgres@pgdb1'"
And check to make sure that only the key (s) you wanted were added.
-bash-4.2 $
-bash-4.2 $
-bash-4.2 $
-bash-4.2$ ssh pgdb1 date
Mon Sep 4 10:56:33 CST 2017
-bash-4.2 $
III. Setting repmgr
1.Master
1) .init pgdata
[root@pgdb1 ~] # su-postgres
Last login: Mon Sep 4 11:02:33 CST 2017 on pts/0
-bash-4.2 $
-bash-4.2$ initdb-D / pgdata10/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
Fixing permissions on existing directory / pgdata10... Ok
Creating subdirectories... Ok
Selecting default max_connections... 100
Selecting default shared_buffers... 128MB
Selecting dynamic shared memory implementation... Posix
Creating configuration files... Ok
Running bootstrap script... Ok
Performing post-bootstrap initialization... Ok
Syncing data to disk... Ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option-A, or
-- auth-local and-- auth-host, the next time you run initdb.
Success. You can now start the database server using:
Pg_ctl-D / pgdata10/-l logfile start
-bash-4.2 $
2.Configure The Postgresql.Conf
-bash-4.2$ cd / pgdata10/
-bash-4.2$ vi postgresql.conf
# max_wal_senders = 10
Wal_keep_segments = 5000
Wal_level = logical
# full_page_writes = on
# max_replication_slots = 10
# hot_standby = on
#-Archiving-
Archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
Archive_command ='cd.'# command to use to archive a logfile segment
Shared_preload_libraries = 'repmgr_funcs'
Log_min_duration_statement = 1000
Log_checkpoints = on
Log_connections = on
Log_disconnections = on
Log_line_prefix ='% t [% p]: [% lmer1] user=%u,db=%d,client=%h'
Log_lock_waits = on # log lock waits > = deadlock_timeout
# log_statement = 'none' # none, ddl, mod, all
# log_replication_commands = off
Log_temp_files = 0
Log_autovacuum_min_duration = 0
3.pg_hba.conf
Vi pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
Local all all trust
# IPv4 local connections:
Host all all 127.0.0.1/32 trust
Host repmgr repmgr 172.16.3.0/16 trust
Host all all 0/0 md5
# IPv6 local connections:
Host all all:: 1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
Local replication all trust
Local replication repmgr trust
Host replication all 127.0.0.1/32 trust
Host replication all:: 1/128 trust
"pg_hba.conf" 97L, 4791C written
# start pg boot #
# systemctl start postgresql-10
4. Repmgr.Conf
Master
# su-postgres
$vi / etc/repmgr/10/repmgr.conf
Cluster=pgcluster
Node=1 # a unique integer
Node_name=node1
Conninfo='host=pgdb1 dbname=repmgr user=repmgr'
# use_replication_slots=0
# loglevel=NOTICE
# logfacility=STDERR
Logfile='/var/log/repmgr/repmgr-10.log'
Master_response_timeout=30
# reconnect_attempts=6
# reconnect_interval=10
# failover=manual
# priority=100
Promote_command='/usr/pgsql-10/bin/repmgr standby promote-f / etc/repmgr/10/repmgr.conf'
Follow_command='/usr/pgsql-10/bin/repmgr standby follow-f / etc/repmgr/10/repmgr.conf'
Slave
Cluster=pgcluster
Node=2 # a unique integer
Node_name=node2
Conninfo='host=pgdb2 dbname=repmgr user=repmgr'
# use_replication_slots=0
# loglevel=NOTICE
# logfacility=STDERR
Logfile='/var/log/repmgr/repmgr-10.log'
Master_response_timeout=30
# reconnect_attempts=6
# reconnect_interval=10
# failover=manual
# priority=100
Promote_command='/usr/pgsql-10/bin/repmgr standby promote-f / etc/repmgr/10/repmgr.conf'
Follow_command='/usr/pgsql-10/bin/repmgr standby follow-f / etc/repmgr/10/repmgr.conf'
5.master
-bash-4.2$ createuser-s repmgr
-bash-4.2$ createdb repmgr-O repmgr
Register master
-bash-4.2$ repmgr-f / etc/repmgr/10/repmgr.conf master register
NOTICE: master node correctly registered for cluster' pgcluster' with id 1 (conninfo: host=pgdb1 dbname=repmgr user=repmgr)
-bash-4.2 $
Slave
-bash-4.2$ repmgr-f / etc/repmgr/10/repmgr.conf-h pgdb1-d repmgr-U repmgr-D / pgdata10/-- force standby clone
NOTICE: destination directory'/ pgdata10/' provided
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the-c/--fast-checkpoint option
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl-D / pgdata10/ start
HINT: After starting the server, you need to register this standby with "repmgr standby register"
-bash-4.2 $
Start server
[root@pgdb2 ~] # systemctl start postgresql-10.service
Register standby
[root@pgdb2 pgdata10] # su-postgres
Last login: Mon Sep 4 11:51:11 CST 2017 on pts/1
-bash-4.2 $
-bash-4.2 $
-bash-4.2$ repmgr-f / etc/repmgr/10/repmgr.conf standby register
NOTICE: standby node correctly registered for cluster pgcluster with id 2 (conninfo: host=pgdb2 dbname=repmgr user=repmgr)
-bash-4.2 $
Master/slave
Start repmgr10.service
# systemctl start repmgr10.service
Master
Su-postgres
-bash-4.2$ repmgr-f / etc/repmgr/10/repmgr.conf cluster show
Role | Name | Upstream | Connection String
-+-|-|-
* master | node1 | | host=pgdb1 dbname=repmgr user=repmgr
Standby | node2 | node1 | host=pgdb2 dbname=repmgr user=repmgr
-bash-4.2 $
