Postgresql主从复制--物理复制

/*  除了基础参数,搭建备库至少需要配置如下参数 */
listen_address = '*'
wal_level = replica
archive_mode = on 
archive_command = 'cp %p /data/postgresql/archive/%f '
max_wal_senders= 10  
wal_keep_segments=1024
hot_standby = on

listen_address：  按需设置,本次测试配置为所有主机均可以访问,生产环境可以按需配置网段或IP等
wal_level：  设置流复制模式至少设置为replica
archive_mode:  本次启用归档
archive_command：WAL日志归档命令,生产环境可以将归档拷贝到对应目录或其他机器上,本次测试配置为归档到本机的另一个目录下
max_wal_senders： 最大WAL发送进程数，此数量需大于等于从库个数且比max_connections小。
wal_keep_segments： pg_wal目录下保留WAL日志的个数,每个WAL文件默认16M,为保障从库能在应用归档落后时依旧能追上主库，此值建议设置较大一点。
hot_standby： 此参数控制在恢复归档期间是否支持只读操作，设置为ON后从库为只读模式。

mkdir -p  /data/postgresql/archive/

postgres=# create user repl  REPLICATION  LOGIN ENCRYPTED  PASSWORD 'repl123';
CREATE ROLE

# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    replication     repl             192.168.56.33/24            md5
host    replication     repl             192.168.56.32/24            md5

pg_basebackup -h 192.168.56.32 -U repl -p 5432 -F p   -X s  -v -P -R -D /data/postgresql/data/ -l postgres32

[postgres@PG33 data]$ pg_basebackup -h 192.168.56.32 -U repl -p 5432 -F p   -X s  -v -P -R -D /data/postgresql/data/ -l postgres32
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_17737"
56041/56041 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/20000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

standby_mode = 'on'
primary_conninfo = 'user=repl password=repl123 host=192.168.56.32 port=5432 sslmode=disable sslcompression=0 target_session_attrs=any'
## 添加如下信息
recovery_target_timeline = 'latest'

standby_mode： 设置是否启用数据库为备库，如果设置成on，备库会不停地从主库上获取WAL日志流，直到获取主库上最新的WAL日志流
primary_conninfo：设置主库的连接信息，这里设置了主库IP、端口、用户名信息等，此处是明文密码，生产环境建议配置非明文密码，而是将密码配置在另一个隐藏文件中
covery_target_timeline： 设置恢复的时间线（timeline），默认情况下是恢复到基准备份生成时的时间线，设置成latest表示从备份中恢复到最近的时间线，通常流复制环境设置此参数为latest，复杂的恢复场景可将此参数设置成其他值

pg_ctl  -D /data/postgresql/data/  -l pg33.log  start

[postgres@PG33 data]$ chmod 0750 /data/postgresql/data/
[postgres@PG33 data]$ pg_ctl  -D /data/postgresql/data/  -l pg33.log  start
waiting for server to start.... done
server started

[postgres@PG32 ~]$ psql
psql (11.4)
Type "help" for help.
postgres=# create table test2(id int primary key, name varchar(20));
CREATE TABLE
postgres=# insert into test2 values(1,'aaa'),(2,'abc');
INSERT 0 2

[postgres@PG33 data]$ psql
psql (11.4)
Type "help" for help.

postgres=# select * from test2;
 id | name 
----+------
  1 | aaa
  2 | abc

postgres=# select  pid ,usesysid,usename,client_addr,state,sync_state  from  pg_stat_replication;
  pid  | usesysid | usename |  client_addr   |   state   | sync_state 
-------+----------+---------+----------------+-----------+------------
 25123 |    16797 | repl    | 192.168.56.33 | streaming | async
(1 row)

synchronous_commit = remote_write
synchronous_standby_names = '*'

postgres=# select  pid ,usesysid,usename,client_addr,state,sync_state  from  pg_stat_replication;
  pid  | usesysid | usename |  client_addr   |   state   | sync_state 
-------+----------+---------+----------------+-----------+------------
 16265 |    16797 | repl    | 192.168.56.33 | streaming | sync
(1 row)