pg物理复制

参考地址

参考博客

异步流复制

但在Master库事务提交后，Standby库等待流数据的时刻发生Master宕机，会导致丢失最后一个事务的数据

主库pg_hba.conf

/var/lib/pgsql/10/data/pg_hba.conf

host    replication     all             127.0.0.1/32            trust #trust代表无条件地允许联接而不需要口令
host    replication     all             ::1/128                 trust #代表ipv6地址放行
host    replication     all             0.0.0.0/0                 trust #放行任意用户和IP地址复制 多用于测试
host    replication     replicator      192.168.64.12/32        md5 #replicator 代表创建数据同步的用户为replicator
host    replication     replicator      192.168.64.13/32        md5 #要求客户端提供一个 MD5 加密的口令进行认证

主库postgresql.conf

新建归档目录 mkdir -p /data/arch_dir /data/arch_dir_master
vi /var/lib/pgsql/10/data/postgresql.conf

wal_level= logical
max_wal_senders = 10   # at least the number of standby
archive_mode = on  #归档模式打开
archive_command =  'test ! -f /data/arch_dir/%f && cp %p /data/arch_dir/%f'  #归档命令
synchronous_standby_names = ''   #standby application name, in recover.conf
hot_standby=on #对外提供查询服务

• synchronous_standby_names=’’ 表示采用异步复制方式
• synchronous_standby_names=‘pg1’
  表示会等到pg1同步后并且提交完成之后主才会提交,如果从无响应那么主会hung住.
• synchronous_standby_names=‘pg1,pg2’ 主会从第一个pg1开始尝试不行的话再去试pg2

附: synchronous_standby_names配了参数即同步,特点为可靠性有保证,但会影响应用可用性网络慢,执行响应也慢

主库recovery.done与从库recovery.conf

主库 /var/lib/pgsql/10/data/recovery.done .done文件是主库上的指向从库，不被启用
从库 /var/lib/pgsql/10/data/recovery.conf .conf文件是从库上使用的,他会指向主库
vi /var/lib/pgsql/10/data/recovery.done

standby_mode=on #设置为从库模式
restore_command = 'cp /data/arch_dir_master/%f %p'  #恢复模式
primary_conninfo='application_name=pg2 host=192.168.231.191 port=5432 user=postgres password=postgres' #指向主的信息其中application_name=pg2代表本机的应用名称为pg2
archive_cleanup_command ='pg_archivecleanup /data/arch_dir_master %r'  #归档清理命令
recovery_target_timeline = 'latest'  #恢复最新的

重启主库

su - postgres 	#切换到用户postgres
/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile restart	#重启动数据库

备库创建

备库创建通过主库热备实现

主库上执行热备锁定

su - postgres
[postgres@hmcf-02 pgdata]$ psql
psql (10.6)
Type "help" for help.

postgres=# select pg_start_Backup('backuptag',true);
 pg_start_backup
-----------------
 0/2000060
(1 row)

postgres=#

备库上执行来源主库拷贝的数据

#192.168.232.190 为主库
[root@hmcf-03 data]# scp -r 192.168.232.190:/var/lib/pgsql/10/data   /var/lib/pgsql/10/data
#给data目录赋予postgres的用户权限
[root@hmcf-03 data]# chown -R postgres.postgres /var/lib/pgsql/10/data/

主库热备锁定停止-主库执行

postgres=# select pg_stop_backup();
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup
----------------
 0/2000168
(1 row)

从库清理主库无用文件

[postgres@hmcf-03 data]$ rm -rf /var/lib/pgsql/10/data/pg_wal
[postgres@hmcf-03 data]$ rm -rf /var/lib/pgsql/10/data/postmaster.pid
[postgres@hmcf-03 data]$ mkdir -p /data/arch_dir /data/arch_dir_master

修改备库的recovery文件

cd /var/lib/pgsql/10/data
[postgres@hmcf-03 pgdata]$ mv recovery.done recovery.conf
内容如下
[postgres@hmcf-03 pgdata]$ cat recovery.conf
standby_mode=on
restore_command = 'cp /data/arch_dir_master/%f %p'
primary_conninfo='application_name=pg1 host=192.168.232.190 port=5432 user=postgres password=postgres'
archive_cleanup_command ='pg_archivecleanup /data/arch_dir_master %r'
recovery_target_timeline = 'latest'

准备恢复需要的wal和归档文件来源于主库

[root@hmcf-03 data]# scp -r 192.168.232.190:/var/lib/pgsql/10/data/pg_wal  /var/lib/pgsql/10/data/
000000010000000000000002.00000060.backup.done                                                                                                       100%    0     0.0KB/s   00:00
000000010000000000000002.done                                                                                                                       100%    0     0.0KB/s   00:00
000000010000000000000002                                                                                                                            100%   16MB  67.7MB/s   00:00
000000010000000000000002.00000060.backup                                                                                                            100%  293   454.4KB/s   00:00
000000010000000000000003                                                                                                                            100%   16MB  88.5MB/s   00:00
000000010000000000000004                                                                                                                            100%   16MB  11.0MB/s   00:01
#给拷贝过来的赋用户权限postgres
[root@hmcf-03 data]# chown -R postgres.postgres /var/lib/pgsql/10/data

[root@hmcf-03 data]# scp -r 192.168.232.190:/data/arch_dir /data/arch_dir_master
root@192.168.64.12's password:
Permission denied, please try again.
root@192.168.64.12's password:
000000010000000000000001                                                                                                                            100%   16MB   6.4MB/s   00:02
000000010000000000000002.00000060.backup                                                                                                            100%  293   229.1KB/s   00:00
000000010000000000000002
[root@hmcf-03 data]# chown -R postgres.postgres /data/arch_dir_master

启动备库，观察备库日志

/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile restart	# 重启动数据库

备库上执行写操作测试

[postgres@hmcf-03 ~]$ psql
psql (10.6)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# create table a(name text);
2019-11-23 18:15:46.078 CST [16175] ERROR:  cannot execute CREATE TABLE in a read-only transaction
2019-11-23 18:15:46.078 CST [16175] STATEMENT:  create table a(name text);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
postgres=#

master上测试

[postgres@hmcf-02 ~]$ psql
psql (10.6)
Type "help" for help.
postgres=# create table a(name text);
CREATE TABLE
postgres=#
postgres=# select * from pg_stat_replication;
  pid  | usesysid |  usename   | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_lsn  | write_lsn |
 flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
-------+----------+------------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+
-----------+------------+-----------+-----------+------------+---------------+------------
 17226 |    16384 | replicator | pg1              | 192.168.64.13 |                 |       34786 | 2019-11-23 18:14:08.190221+08 |              | streaming | 0/301E9B8 | 0/301E9B8 |
 0/301E9B8 | 0/301E9B8  |           |           |            |             0 | async
(1 row)

postgres=#

同步复制

同步复制只需在异步复制的基础上，修改主库的/var/lib/pgsql/10/data/postgresql.conf里面的synchronous_standby_names 参数，对应参数值为standby备库上recovery.conf里面的primary_conninfo中的application_name。

然后重新reload加载配置即可

su - postgres 	#切换到用户postgres
/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile restart	#重启动数据库

查看验证

[postgres@hmcf-02 pgdata]$ psql
psql (10.6)
Type "help" for help.

postgres=# select * from pg_stat_replication;
  pid  | usesysid |  usename   | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_lsn  | write_lsn |
 flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
-------+----------+------------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+
-----------+------------+-----------+-----------+------------+---------------+------------
 17226 |    16384 | replicator | pg1              | 192.168.64.13 |                 |       34786 | 2019-11-23 18:14:08.190221+08 |              | streaming | 0/301E9B8 | 0/301E9B8 |
 0/301E9B8 | 0/301E9B8  |           |           |            |             1 | sync
(1 row)

postgres=#

可以看到此时standby的同步状态变成了sync了，由异步流变成了同步流复制.