postgresql 复制

异步复制

#创建复制数据库用户
CREATE ROLE rep login replication encrypted password  'rep';

#修改配置文件pg_hba.conf,增加replica用户，进行同步
host  replication rep 192.168.20.201/24 md5
host  replication rep 192.168.20.202/24 md5

#修改配置文件postgresql.conf,修改以下几个地方
wal_level = hot_standby  # 这个是设置主为wal的主机
max_wal_senders = 32 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 256 ＃ 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
max_connections = 100 # 这个设置要注意下，从库的max_connections必须要大于主库的


登录postgre用户，测试连接主库数据库是否正常
psql -h 192.168.20.201

#若链接正常，删除从库data目录下文件
rm -rf data/*

#通过pg_basebackup命令行在从库上生成基础备份
pg_basebackup -h 192.168.20.201 -U rep -Fp -X stream  -P -R -D /u01/pgsql/data  -l replbackup20191015
#参数说明：
#        -h:指定连接的数据库的主机名或IP地址，这里就是主库的ip
#        -U:指定连接的用户名，此处是我们刚才创建的专门负责流复制的repl用户
#        -F:指定了输出的格式，支持p（原样输出）或者t（tar格式输出）
#        -X:表示wal日志格式
#        -P:表示允许在备份过程中实时的打印备份的进度
#        -R:表示会在备份结束后自动生成recovery.conf文件，这样也就避免了手动创建
#        -D:指定把备份写到哪个目录
#        -l:表示指定一个备份的标识，运行命令后看到如下进度提示就说明生成基础备份成功

#修改从库数据目录下的postgresql.conf文件，将hot_standby改为启用状态
hot_standby=on

#重启从数据库


postgres=# SELECT client_addr,application_name,sync_state FROM pg_stat_replication;
  client_addr   | application_name | sync_state 
----------------+------------------+------------
 192.168.20.202 | walreceiver      | async
(1 row)

　　同步复制

修改主库postgresql.conf  
synchronous_standby_names = 'rep1' 


重启主数据库

postgres=# SELECT client_addr,application_name,sync_state FROM pg_stat_replication;
  client_addr   | application_name | sync_state 
----------------+------------------+------------
 192.168.20.202 | rep1      | sync
(1 row)

　　延迟复制

延迟复制是加快误操作恢复 ，比如dml、ddl操作失误  比用备份pitr恢复速度快，pitr是恢复整库.
修改备库上的recovery.conf
[postgres@redis03 data]$ cat recovery.conf
standby_mode = 'on'
primary_conninfo = 'application_name=rep2 user=rep password=rep host=192.168.20.201 port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'
recovery_min_apply_delay = '8h'
recovery_target_timeline = 'latest'

启动数据库

延迟复制 演练
postgres=# d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t    | table | postgres
 public | t1   | table | postgres
 public | t2   | table | postgres
(3 rows)

postgres=# delete from t2 ;
DELETE 10000
主库上查延迟时间
postgres=# SELECT client_addr,application_name,sync_state,replay_lag  FROM pg_stat_replication;
  client_addr   | application_name | sync_state |   replay_lag    
----------------+------------------+------------+-----------------
 192.168.20.202 | rep1             | sync       | 
 192.168.20.203 | rep2             | async      | 00:02:02.748067
(2 rows)

备库上查看复制事务的最后时间 、lsn、现在时间
postgres-# SELECT pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp(),now() ;
 pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp |              now              
------------------------+-------------------------------+-------------------------------
 0/251BE288             | 2019-10-15 18:03:27.592845+08 | 2019-10-16 11:18:57.641601+08
(1 row)



延迟复制库停止复制
postgres=# SELECT pg_wal_replay_pause();
 pg_wal_replay_pause 
---------------------
 
(1 row)

 启动复制
select pg_wal_replay_resume();

主库的数据
postgres=# select count(1) from t2;
 count 
-------
     0
(1 row)

备库的数据
postgres=# select count(1) from t2;
 count 
-------
 10000
(1 row)

停止延迟复制数据库
pg_ctl  stop -m fast
修改recovery.conf
[postgres@redis03 data]$ cat recovery.conf
standby_mode = 'on'
primary_conninfo = 'application_name=rep2 user=rep password=rep host=192.168.20.201 port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'
recovery_target_time = '2019-10-16 10:18:57.641601+08'

如果恢复的数据不是需要的，可以慢慢推进recovery_target_time 时间