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