一.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/
二、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 image 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 image 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$
三、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]: [%l-1] 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$
