PostgreSQL物理复制搭建主从环境(一)

wal_level = replica
archive_mode = on         
archive_command = '/bin/date'
max_wal_senders = 10       -->设置发送wal日志进程的个数,值至少要大于备库的个数
wal_keep_segments = 100    -->设置wal日志保留的个数
hot_standby = on           -->开启hot_standby,则可以在备库上进行查询操作

  postgres=# create user repuser replication login connection limit 5 encrypted password 'rep12345';
  CREATE ROLE
  postgres=# \du+
                                            List of roles
   Role name |                         Attributes                         | Member of | Description
  -----------+------------------------------------------------------------+-----------+-------------
   postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
   repuser   | Replication                                               +| {}        |
             | 5 connections

# replication privilege.
host    replication     repuser             10.0.2.110/24            md5
host    replication     repuser             10.0.2.111/24            md5

--从库首先清空$PGDATA
[postgres@pghost01 data]$ cd $PGDATA
[postgres@pghost01 data]$ rm -rf *
[postgres@pghost01 data]$ pg_basebackup -D $PGDATA -Fp -Xs -v -P -h 10.0.2.110 -p 5432 -U repuser
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 2/1000028 on timeline 13
pg_basebackup: starting background WAL receiver
288141/288141 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 2/10000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

[postgres@pghost01 data]$ cat recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=10.0.2.110 port=5432 user=repuser password=rep12345'

[postgres@pghost01 data]$ pg_ctl start
waiting for server to start....2019-12-20 11:03:26.502 CST [3902] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-12-20 11:03:26.502 CST [3902] LOG:  listening on IPv6 address "::", port 5432
2019-12-20 11:03:26.506 CST [3902] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-12-20 11:03:26.523 CST [3902] LOG:  redirecting log output to logging collector process
2019-12-20 11:03:26.523 CST [3902] HINT:  Future log output will appear in directory "log".
. done


--主库查看wal日志发送状态
select * from pg_stat_replication;
--从库查看wal日志接收状态
select * from pg_stat_wal_receiver;

--关闭主库
-bash-4.2$ pg_ctl stop
waiting for server to shut down.... done
server stopped


--在备库上执行pg_ctl promote命令激活备库，如果recovery.conf变成recovery.done, 表示备库已切换成为主库。
-bash-4.2$ pg_ctl promote
waiting for server to promote.... done
server promoted
-bash-4.2$ pg_controldata | grep cluster    ---->>通过控制文件查询实例状态
Database cluster state:               in production
-bash-4.2$ ls -ltr recovery*
-rw-r--r-- 1 postgres postgres 5904 Jun  4 10:04 recovery.done


--将老的主库切换成备库，在老的主库的$PGDATA目录下创建recovery.conf文件。配置和老的从库pghost01一样，只是primary_conninfo参数中的IP换成对端IP。
-bash-4.2$ cp /usr/pgsql-11/share/recovery.conf.sample $PGDATA/
-bash-4.2$ mv recovery.conf.sample recovery.conf
-bash-4.2$ vi recovery.conf
-bash-4.2$
-bash-4.2$ tail recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=10.0.2.111 port=5432 user=repuser password=rep12345'


--启动老的主库，这时观察主、备进程是否正常，如果正常表示主备切换成功。
-bash-4.2$ pg_ctl start
waiting for server to start....2020-06-04 10:24:08.409 CST [95234] LOG:  listening on IPv4 address "0.0.0.0", port 5430
2020-06-04 10:24:08.411 CST [95234] LOG:  could not create IPv6 socket for address "::": Address family not supported by protocol
2020-06-04 10:24:08.413 CST [95234] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5430"
2020-06-04 10:24:08.415 CST [95234] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5430"
2020-06-04 10:24:08.479 CST [95234] LOG:  redirecting log output to logging collector process
2020-06-04 10:24:08.479 CST [95234] HINT:  Future log output will appear in directory "log".
 done
server started
-bash-4.2$
-bash-4.2$
-bash-4.2$ psql
psql (11.8)
Type "help" for help.


postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

--主库不关的情况下，直接备库激活
-bash-4.2$ pg_ctl promote
waiting for server to promote.... done
server promoted
-bash-4.2$ pg_controldata | grep cluster
Database cluster state:               in production


--原主库使用pg_rewind工具增量同步原备库上的数据,先关库，pg_rewind中需要指定super user用户 
-bash-4.2$ pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped
-bash-4.2$ 
-bash-4.2$ 
-bash-4.2$ pg_rewind --target-pgdata $PGDATA --source-server='host=10.0.2.111 port=5432 user=postgres password=postgres' -P
connected to server
servers diverged at WAL location 1/A9000248 on timeline 6
rewinding from last common checkpoint at 1/A9000028 on timeline 6
reading source file list
reading target file list
reading WAL in target
need to copy 1296 MB (total source directory size is 2521 MB)
1327859/1327859 kB (100%) copied
creating backup label and updating control file
syncing target data directory
Done!
-bash-4.2$
--修改原主库的recovery.conf文件并启库
-bash-4.2$ tail recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=10.0.2.111 port=5432 user=repuser password=rep12345'




-bash-4.2$ pg_ctl start
waiting for server to start....2019-08-14 16:16:16.089 CST [30332] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-08-14 16:16:16.090 CST [30332] LOG:  listening on IPv6 address "::", port 5432
2019-08-14 16:16:16.094 CST [30332] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-14 16:16:16.111 CST [30332] LOG:  redirecting log output to logging collector process
2019-08-14 16:16:16.111 CST [30332] HINT:  Future log output will appear in directory "log".
 done
server started
-bash-4.2$