Postgresql主备复制详解__今天社区专家阿弟给我们带来的也是与数据库备份和恢复的相关内容：详解主备复制的步骤和操作细节，以及在复制过程中可能出现的各种问题，当然还有原因和解药。。。_

PG中文社区 /

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

Postgresql主备复制详解

原作者：阿弟　　创作时间：2017-09-02 20:05:45+08　　

doudou586 发布于2017-09-02 20:05:45　　

评论: 1　　

顶: 1300　

踩: 1312　

# Postgresql主备复制详解
#### 作者：PostgreSQL中国社区---阿弟
#### 联系: 4893310 / 4893310@qq.com
#### 欢迎大家踊跃投稿，投稿信箱：press@postgres.cn

----

## 一、服务器架构图
### 1、服务器架构图
![PIC1](/images/news/2017/pg_replication_01.jpg)

### 2、主备复制说明

* 支持一主多备
* 只能是一主一备同步，其它备节点复制为异步
* 支持级联复制，目前级联复制只能异步复制
* 支持archive和复制混合使用
* 支持主备切换
* 支持同步模式时，多个备机互相接替

## 二、部署环境介绍
### 1、主节点

项目|值
---|----:
操作系统|Centos 6.9
IP地址|192.168.0.232
Hostname|masterdb
PostgreSQL版本号|9.6.1

### 2、备节点
项目|值
---|----:
操作系统|Centos 6.9
IP地址|192.168.0.233
Hostname|slavedb
PostgreSQL版本号|9.6.1

### 3、级联节点
项目|值
---|----:
操作系统|Centos 6.9
IP地址|192.168.0.236
Hostname|casecade
PostgreSQL版本号|9.6.1

### 4、NFS节点
项目|值
---|----:
操作系统|Centos 6.9
IP地址|192.168.0.231
Hostname|nfs
PostgreSQL版本号|9.6.1

## 三、主节点部署及配置
### 1、源码编译安装Postgresql
```
[root@masterdb home]# adduser postgres -U -u 500
[root@masterdb home]# cd /home/postgres/
[root@masterdb postgres]# mkdir source
[root@masterdb postgres]# cd source/
[root@masterdb source]# wget https://ftp.postgresql.org/pub/source/v9.6.1/postgresql-9.6.1.tar.gz
[root@masterdb source]# tar zxf postgresql-9.6.1.tar.gz 
[root@masterdb source]# cd postgresql-9.6.1
[root@masterdb postgresql-9.6.1]# ./configure --prefix=/usr/local/pgsql9.6.1
[root@masterdb postgresql-9.6.1]# gmake -j 8
[root@masterdb postgresql-9.6.1]# gmake install
```
### 2、initdb一个节点
```
[root@masterdb postgresql-9.6.1]# su postgres
[postgres@masterdb postgresql-9.6.1]$ /usr/local/pgsql9.6.1/bin/initdb --no-locale -E utf8 
-D /home/postgres/data9.6.1 -U postgres -W
```

### 3、配置运行参数

修改postgresql.conf

\#预写式日志级别，要归档的话replica或者是logical，如没必需的话使用replica即可，logical会产生更多的日志量
```
[root@masterdb postgres]# su postgres
[postgres@masterdb ~]$ vim /home/postgres/data9.6.1/postgresql.conf 
listen_addresses = '*'
port = 9610
wal_level = replica 
synchronous_commit=on 
max_wal_senders = 3 #视自己业务需要调整
wal_keep_segments = 8000 #视自己业务需要调整
hot_standby = on
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log' #下面配置记录所有日志，对io的要求非常的高，线上如果开启记录所有日志，
 ##最好有独立的io设备来保存，否则对业务影响比较大，如果你有其它地方记录了日志，建议这里只收集必要
 ##的日志
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%m %a %r %d %u %p %x'
log_min_duration_statement = 1000
log_statement = 'all'
log_timezone = 'PRC'
```

synchronous_commit参数说明

\#参数值为on、remote_write、local和off，如果synchronous_standby_names被设置时，这个参数也控制事务提交是否将等待事务的 WAL 记录被复制到后备服务器上。on表示后备服务器已经收到了事务的提交记录并将其刷入了磁盘，主服务器上的事务才会提交；remote_write后备服务器的一个回复指示该服务器已经收到了该事务的提交记录并且已经把该记录写出到后备服务器的操作系统，但是该数据并不一定到达了后备服务器上的稳定存储；设置local可以用于希望等待本地刷写磁盘但不等待同步复制的事务。当设置为off时，在向客户端报告成功和真正保证事务不会被服务器崩溃威胁之间会有延迟，（最大的延迟是wal_writer_delay的三倍）。不同于fsync，将这个参数设置为off不会产生数据库不一致性的风险：一个操作系统或数据库崩溃可能会造成一些最近据说已提交的事务丢失，但数据库状态是一致的，就像这些事务已经被干净地中止。因此，当性能比完全确保事务的持久性更重要时，关闭synchronous_commit可以作为一个有效的代替手段。

更多的流复制参数配置见

http://www.postgres.cn/docs/9.6/runtime-config-replication.html

更多的预写式日志配置见

http://www.postgres.cn/docs/9.6/runtime-config-wal.html

修改pg_hba.conf
```
[postgres@masterdb ~]$ vim /home/postgres/data9.6.1/pg_hba.conf 
# TYPE  DATABASE        USER            ADDRESS              METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                  trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.0.0/24          md5
# IPv6 local connections:
#host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                trust
host    replication     repuser        192.168.0.0/24                 md5
#host    replication     postgres        ::1/128                 trust
```
连接防火墙视自己业务需要配置

###4、启动服务
[postgres@masterdb data9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_ctl start 
-D /home/postgres/data9.6.1/
server starting

###5、创建复制角色
```
[postgres@masterdb data]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 
-U postgres -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# create role repuser with replication login ;
CREATE ROLE
postgres=# \password repuser
Enter new password: 
Enter it again: 
```
###6、添加PostgreSQL服务port至iptables规则中
在/etc/sysconfig/iptables规则表中添加上下面这条规则即可让9610 port即提供对外访问
```
[root@masterdb postgresql-9.6.1]# vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 9610 -j ACCEPT
```
重启iptables服务
```
[root@masterdb postgresql-9.6.1]# service iptables restart
```

配置某个网段可以访问
```
[root@masterdb ~]# vim /etc/sysconfig/iptables
-A INPUT -s 192.168.0.0/24 -j ACCEPT
[root@masterdb ~]# service iptables restart
```

配置iptables开机时不自动启动
```
[root@masterdb log]# chkconfig iptables off
```
###7、配置PostgreSQL服务开机自动启动
在 /etc/rc.d/rc.local  文件中添加下面启动脚本
```
[root@masterdb postgresql-9.6.1]# vim /etc/rc.d/rc.local
su postgres -c "/usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.1"
```

## 四、异步复制配置
### 1、源码编译安装Postgresql
```
[root@slavedb home]# useradd postgres -U -u 500
[root@slavedb home]# cd /home/postgres/
[root@slavedb postgres]# mkdir source
[root@slavedb postgres]# cd source/
[root@slavedb source]# wget https://ftp.postgresql.org/pub/source/v9.6.1/postgresql-9.6.1.tar.gz
[root@slavedb source]# tar zxf postgresql-9.6.1.tar.gz 
[root@slavedb source]# cd postgresql-9.6.1
[root@slavedb postgresql-9.6.1]# ./configure --prefix=/usr/local/pgsql9.6.1
[root@slavedb postgresql-9.6.1]# gmake -j 8
[root@slavedb postgresql-9.6.1]# gmake install
```

### 2、使用pg_basebackup从主节点生成一个备节点
```
[postgres@slavedb postgresql-9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_basebackup -h 192.168.0.232 
-U repuser -p 9610 -D /home/postgres/data9.6.1
Password: 
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
```
###3、配置运行参数
配置recovery.conf
```
[postgres@slavedb data9.6.1]$ vim /home/postgres/data9.6.1/recovery.conf 
standby_mode = 'on'
primary_conninfo = 'host=192.168.0.232 port=9610 user=repuser password=pgsql'
recovery_target_timeline = 'latest'

#将recovery.conf设置成其它用户不能读取
[root@slavedb data9.6.1]# chmod 0600 recovery.conf
```
###4、启动服务并且验证主务复制是否成功
启动备节点PostgreSQL服务
```
[root@slavedb archive]# su postgres
[postgres@slavedb archive]$ /usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.1/
```

连接备节点检查是否为recovery状态
```
[postgres@slavedb archive]$  /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.233 -p 9610 -d postgres
psql (9.6.1)
Type "help" for help.

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

连接主节点检查是否备机连接成功
```
[postgres@slavedb archive]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 -d postgres
Password: 
psql (9.6.1)
Type "help" for help.

sync_state值为async表示当前连接上来的备机为异步复制

连接主节点检查执行一些DML操作
```
[postgres@slavedb archive]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 -d postgres
Password: 
psql (9.6.1)
Type "help" for help.

postgres=# create table t (id integer);
CREATE TABLE
postgres=# insert into t values(1);
INSERT 0 1
postgres=# 
```

连接备节点查看数据是否同步
```
[postgres@slavedb archive]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.233 -p 9610 -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# select * from t;
 id 
----
  1
(1 row)
```
###5、添加PostgreSQL服务port至iptables规则中

在/etc/sysconfig/iptables规则表中添加上下面这条规则即可让9610 port即提供对外访问
```
[root@slavedb postgresql-9.6.1]# vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 9610 -j ACCEPT
```

重启iptables服务
```
[root@slavedb postgresql-9.6.1]# service iptables restart
```

配置某个网段可以访问
```
[root@slavedb ~]# vim /etc/sysconfig/iptables
-A INPUT -s 192.168.0.0/24 -j ACCEPT
[root@slavedb ~]# service iptables restart
```

配置iptables开机时不自动启动
```
[root@slavedb log]# chkconfig iptables off
```
### 6、配置PostgreSQL服务开机自动启动
在 /etc/rc.d/rc.local  文件中添加下面启动脚本
```
[root@slavedb postgresql-9.6.1]# vim /etc/rc.d/rc.local
su postgres -c "/usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.1"
```

## 五、同步复制配置
###1、修改主节点postgresql.conf配置
```
[postgres@masterdb data9.6.1]$ vim postgresql.conf
synchronous_standby_names = 'synccluster1,synccluster2'
[postgres@masterdb data9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_ctl -D /home/postgres/data9.6.1/ reload
server signaled
[postgres@masterdb data9.6.1]$ 
```
###2、修改备节点recovery.conf配置
```
[postgres@slavedb data9.6.1]$ vim recovery.conf 
standby_mode = 'on'
primary_conninfo = 'host=192.168.0.232 port=9610 user=repuser password=pgsql 
application_name=synccluster1'
recovery_target_timeline = 'latest'
[postgres@slavedb data9.6.1]$ 
[postgres@slavedb data9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_ctl  restart 
-D /home/postgres/data9.6.1 -m f
```
###3、连接上主节点查看主备复制模式
```
[postgres@slavedb data9.6.1]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 -d postgres
Password: 
psql (9.6.1)
Type "help" for help.

postgres=# 
```

###4、同步复制时对主节点的应用限制

如果主节点synchronous_commit参数值设置成on、remote_write中的任何一个，则需要有备机采用同步模式连接上来，否则事务将会一直停留在那里等待备机已经提交事务的信号。会造成业务不可用，如下所示

把备机服务停止
```
[postgres@slavedb data9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_ctl stop -D /home/postgres/data9.6.1 -m f
```
连接上主节点测试
```
[postgres@slavedb data9.6.1]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 -d postgres

Password: 
psql (9.6.1)
Type "help" for help.

postgres=# insert into t values(10);
```

你会现无法执行下去，一直在等待。处理这个问题有三个方法

* 把备机服务开起来
* 把主节点的synchronous_standby_names 参数屏掉，即加上‘#’
* 把synchronous_commit值修改为local

## 六、级联复制配置
### 1、源码编译安装Postgresql
```
[root@casecade home]# adduser postgres -U -u 500
[root@casecade home]# cd /home/postgres/
[root@casecade postgres]# mkdir source
[root@casecade postgres]# cd source/
[root@casecade source]# wget https://ftp.postgresql.org/pub/source/v9.6.1/postgresql-9.6.1.tar.gz
[root@casecade source]# tar zxf postgresql-9.6.1.tar.gz 
[root@casecade source]# cd postgresql-9.6.1
[root@casecade postgresql-9.6.1]# ./configure --prefix=/usr/local/pgsql9.6.1
[root@casecade postgresql-9.6.1]# gmake -j 8
[root@casecade postgresql-9.6.1]# gmake install
```
###2、使用pg_basebackup从备节点生成一个级联节点
```
[postgres@casecade postgresql-9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_basebackup -h 192.168.0.233 
-U repuser -p 9610 -D /home/postgres/data9.6.1
Password: 
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
```
###3、配置运行参数
配置recovery.conf
```
[postgres@casecade data9.6.1]$ vim /home/postgres/data9.6.1/recovery.conf 
standby_mode = 'on'
primary_conninfo = 'host=192.168.0.233 port=9610 user=repuser password=pgsql'
recovery_target_timeline = 'latest'

#将recovery.conf设置成其它用户不能读取
[root@slavedb data9.6.1]# chmod 0600 recovery.conf
```

###4、启动服务并验证级联复制是否成功

启动服务
```
[postgres@casecade data9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.1/
server starting
```

连接级联节点验证
```
[postgres@casecade ~]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.236 -U postgres -d postgres -p 9610
psql (9.6.1)
Type "help" for help.

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

连接备节点验证
```
[postgres@casecade ~]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.233 -U postgres -d postgres 
-p 9610  
Password for user postgres: 
psql (9.6.1)
Type "help" for help.

postgres=# 
```
目前级联复制只支持异步复制

###5、添加PostgreSQL服务port至iptables规则中

在/etc/sysconfig/iptables规则表中添加上下面这条规则即可让9610 port即提供对外访问
```
[root@casecade postgresql-9.6.1]# vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 9610 -j ACCEPT
```
重启iptables服务
```[root@casecade postgresql-9.6.1]# service iptables restart ```

配置某个网段可以访问
```
[root@casecade ~]# vim /etc/sysconfig/iptables
-A INPUT -s 192.168.0.0/24 -j ACCEPT
[root@casecade ~]# service iptables restart
```
配置iptables开机时不自动启动
```[root@casecade log]# chkconfig iptables off ```

### 6、配置PostgreSQL服务开机自动启动

在 /etc/rc.d/rc.local  文件中添加下面启动脚本
```
[root@casecade postgresql-9.6.1]# vim /etc/rc.d/rc.local
su postgres -c "/usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.1"
```

## 七、主备切换
###1、关闭主节点服务
```
[postgres@masterdb /]$ /usr/local/pgsql9.6.1/bin/pg_ctl stop -D /home/postgres/data9.6.1/
waiting for server to shut down.... done
server stopped
```
###2、解绑VIP－－这一步视实际环境要求执行
```
[root@masterdb ~]# ip addr del 192.168.0.238/24 dev eth0:1
```
###3、备节点promote变成主节点
```
[postgres@slavedb ~]$ /usr/local/pgsql9.6.1/bin/pg_ctl promote -D /home/postgres/data9.6.1/
server promoting
```
###4、绑定VIP－－这一步视实际环境要求执行
```
[root@slavedb ~]# ifconfig eth0:1 192.168.0.238
```
### 5、在主节点上配置recovery.conf文件
```
[postgres@masterdb data9.6.1]$ vim /home/postgres/data9.6.1/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=192.168.0.233 port=9610 user=postgres password=pgsql 
application_name=synccluster1'
recovery_target_timeline = 'latest'
```
### 6、原来主节点启动服务变成备节点
```
[postgres@masterdb data9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.1/
server starting
```
###7、连接原来主节点检查切换结果
```
[postgres@masterdb data9.6.1]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -U postgres 
-d postgres -p 9610
psql (9.6.1)
Type "help" for help.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)
```
###8、连接原来的备节点检查切换结果
```
[postgres@slavedb data9.6.1]$  /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.233 -U postgres 
-d postgres -p 9610 
psql (9.6.1)
Type "help" for help.

## 八、同步延迟监控
### 1、直联延迟监控
```
[postgres@masterdb root]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 
-U postgres -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# select client_addr,application_name, pg_xlog_location_diff(pg_current_xlog_location(), 
replay_location) as diff from pg_stat_replication;

client_addr  | application_name | diff 
---------------+------------------+------
 192.168.0.233 | synccluster1     |    0
(1 row)
```
###2、级联延迟监控
```
[postgres@slavedb ~]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.233 -p 9610 -U postgres -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# select client_addr,application_name, pg_xlog_location_diff(pg_last_xlog_receive_location(),
replay_location) as diff from pg_stat_replication;    
  client_addr  | application_name | diff 
---------------+------------------+------
 192.168.0.236 | walreceiver      |    0
(1 row)
```
## 九、增加potential节点提高同步复制的高可用性
### 1、增加多一个备节点
```
[postgres@slavedb ~]$ /usr/local/pgsql9.6.1/bin/pg_basebackup -h 192.168.0.233 -U repuser 
-p 9610 -D /home/postgres/data9.6.2
```
### 2、修改复制参数
```
[postgres@slavedb pg_log]$ cd /home/postgres/data9.6.2
[postgres@slavedb data9.6.2]$ vim recovery.conf 
standby_mode = 'on'
primary_conninfo = 'host=192.168.0.232 port=9610 user=repuser password=pgsql 
 application_name=synccluster2'
recovery_target_timeline = 'latest'
```
### 3、启动服务
```
[postgres@slavedb ~]$ /usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.2
```

###4、查看主节点备机连接状态
```
[postgres@masterdb ~]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 
-U postgres -d postgres
psql (9.6.1)
Type "help" for help.

关闭备节点1
```
[postgres@slavedb data9.6.2]$ /usr/local/pgsql9.6.1/bin/pg_ctl stop -D /home/postgres/data9.6.1

waiting for server to shut down.... done
server stopped
```

连接上主节点查看是否升级成功
```
[postgres@masterdb ~]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 
-U postgres -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# select application_name,client_addr,sync_state from pg_stat_replication ;
 application_name |  client_addr  | sync_state 
------------------+---------------+------------
 synccluster2     | 192.168.0.233 | sync
(1 row)
```

启动备节点1后，备节点1又重新接管为同步角色

启动备节点1
```
[postgres@slavedb data9.6.2]$ /usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.1 
```
   
备节点1又重新接管为同步角色
```
[postgres@masterdb ~]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 
-U postgres -d postgres
psql (9.6.1)
Type "help" for help.

## 十、备机追不上主节点怎么办
###1、备机追不上主节点日志表现
```
[postgres@slavedb pg_log]$ tailf  postgresql-2017-06-08_114104.csv
2017-06-08 11:41:19.792 CST,,,2296,,5938c75f.8f8,1,,
2017-06-08 11:41:19 CST,,0,LOG,00000,"started streaming WAL from primary at 0/6B000000 
on timeline 3",,,,,,,,,""
2017-06-08 11:41:19.792 CST,,,2296,,5938c75f.8f8,2,,
2017-06-08 11:41:19 CST,,0,FATAL,XX000,"could not receive data from WAL stream: 
  ERROR:  requested WAL segment 00000003000000000000006B has already been removed
",,,,,,,,,""
```

由于备节点太久没有连接上主节点，导致主节点清除xlog文件时备机还没恢复数据，就会出现上面的错误

### 2、重新同步数据的方法

重新做一份基础备份
  
重新制作一份基础备份对于小的数据库无所谓，但如果数据量大的话做起来还是比较费时间的。

利用归档xlog进行数据同步

修改备节点的recovery.conf文件
```
[postgres@slavedb data9.6.2]$ vim recovery.conf 
standby_mode = 'on'
primary_conninfo = 'host=192.168.0.232 port=9610 user=postgres password=pgsql 
                     application_name=synccluster2'
restore_command = 'cp /home/postgres/archive/20[1-9][0-9]-[0-1][0-9]-[0-3][0-9]/%f %p;'
recovery_target_timeline = 'latest'
```

重启服务，检验同步结束
```
[postgres@slavedb data9.6.2]$ pg_ctl restart -D /home/postgres/data9.6.2
[postgres@slavedb data9.6.2]$ cd /home/postgres/data9.6.2/pg_log
[postgres@slavedb pg_log]$ ll -rt
总用量 200
-rw-------. 1 postgres postgres   540 6月   8 11:51 postgresql-2017-06-08_115100.log
-rw-------. 1 postgres postgres  4237 6月   8 11:51 postgresql-2017-06-08_115100.csv

[postgres@slavedb pg_log]$ tailf postgresql-2017-06-08_115100.csv
2017-06-08 11:51:00.637 CST,,,2451,,5938c9a4.993,1,,
2017-06-08 11:51:00 CST,,0,LOG,00000,"ending log output to stderr",,
"Future log output will go to log destination ""csvlog"".",,,,,,,""
2017-06-08 11:51:00.641 CST,,,2453,,5938c9a4.995,1,,
2017-06-08 11:51:00 CST,,0,LOG,00000,"database system was shut down in recovery 
                                       at 2017-06-08 11:50:59 CST",,,,,,,,,""
2017-06-08 11:51:01.035 CST,,,2453,,5938c9a4.995,2,,
2017-06-08 11:51:00 CST,,0,LOG,00000,"entering standby mode",,,,,,,,,""
2017-06-08 11:51:01.297 CST,,,2453,,5938c9a4.995,3,,
2017-06-08 11:51:00 CST,,0,LOG,00000,"restored log file ""00000003000000000000006B""
                                      from archive",,,,,,,,,""
2017-06-08 11:51:01.419 CST,,,2453,,5938c9a4.995,4,,
2017-06-08 11:51:00 CST,1/0,0,LOG,00000,"redo starts at 0/6B000100",,,,,,,,,""
2017-06-08 11:51:01.419 CST,,,2453,,5938c9a4.995,5,,
2017-06-08 11:51:00 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/6B0001E0",,,,,,""
2017-06-08 11:51:01.420 CST,,,2451,,5938c9a4.993,2,,
2017-06-08 11:51:00 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,""
2017-06-08 11:51:01.700 CST,,,2453,,5938c9a4.995,6,,
2017-06-08 11:51:00 CST,1/0,0,LOG,00000,"restored log file ""00000003000000000000006C"" 
                               from archive",,,,,,,,,""
2017-06-08 11:51:07.308 CST,,,2487,,5938c9ab.9b7,1,,
2017-06-08 11:51:07 CST,,0,LOG,00000,"started streaming WAL from primary at 0/7E000000
 on timeline 3",,,,,,,,,""
```

## 十一、应用级别上的同步异步混合使用
###1、在当前连接中配置业务同步或异步
```
[postgres@masterdb data]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 
-U postgres -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# show synchronous_standby_names;
 synchronous_standby_names 
---------------------------
 synccluster1,synccluster2
(1 row)

postgres=# show synchronous_commit;
 synchronous_commit 
--------------------
 on
(1 row)

postgres=# insert into t values(0);
^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated 
to the standby.
INSERT 0 1
postgres=# set synchronous_commit = local;
SET
postgres=# insert into t values(0);       
INSERT 0 1
postgres=# 
```

### 2、为某个数据库配置异步模式
```
[postgres@masterdb data]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 
-U postgres -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# set synchronous_commit = local;
SET
postgres=# alter database postgres set synchronous_commit = local;     
ALTER DATABASE
postgres=# \q
[postgres@masterdb data]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 
-U postgres -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# insert into t values(1);
INSERT 0 1
postgres=# \q
[postgres@masterdb data]$ 
```
###3、为某个role配置异步模式
```
[postgres@masterdb data]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 
-U postgres -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# set synchronous_commit = local;
SET
template1=# alter role postgres set synchronous_commit = local;
ALTER ROLE
template1=# \q
[postgres@masterdb data]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610
 -U postgres -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# create table t(id integer);
CREATE TABLE
template1=# drop table t;
DROP TABLE
template1=# 
```

## 十二、使用过程一些问题
###1、使用pg_basebackup时提示ERROR:  could not open file xxx Permission denied

使用pg_basebackup生成一个基础备份时一定要确保被复制的节点的文件访问授权和用户属性是可访问的，否则会导到生成过程出错，需要重新生成。

## 社区资源
### 参考文档

http://www.postgres.cn/docs/9.6/warm-standby.html#STREAMING-REPLICATION

### 主备切换管理工具

https://github.com/chenaisheng/pgclusteradmin/

![PG_China_Community](/images/news/2016/pg_bot_banner.jpg)

评论:1　　

顶: 1300　

踩: 1312　

请在登录后发表评论，否则无法保存。

1# __ xiaowu 回答于 2024-04-23 14:14:29+08

领了红包怎么幽默的说谢谢:https://www.nanss.com/shenghuo/4514.html 描写春天的优美短句:https://www.nanss.com/xuexi/4499.html 感谢幼儿园小班老师的话简短:https://www.nanss.com/xuexi/4102.html 湘菜馆取名:https://www.nanss.com/mingcheng/4416.html 形容多才多艺:https://www.nanss.com/xuexi/4382.html 描写雪的词语:https://www.nanss.com/xuexi/4192.html 放空自己:https://www.nanss.com/yuedu/4360.html 至理名言:https://www.nanss.com/xuexi/4436.html 备注名称大全:https://www.nanss.com/mingcheng/4333.html 贺卡寄语简短10字:https://www.nanss.com/shenghuo/4307.html 关于草原的诗句:https://www.nanss.com/xuexi/4474.html 战队名字大全要霸气:https://www.nanss.com/mingcheng/4283.html 整人搞笑又坑人的问题:https://www.nanss.com/shenghuo/4480.html 和女生道歉最管用的话:https://www.nanss.com/yulu/4111.html 简单朴素的网名:https://www.nanss.com/mingcheng/4485.html 王者荣耀高冷简短名字:https://www.nanss.com/mingcheng/4370.html 阳光正好的唯美句子:https://www.nanss.com/yulu/4074.html 用高情商表达我想你:https://www.nanss.com/yulu/4335.html 笑死人的脑筋急转弯:https://www.nanss.com/shenghuo/4142.html 整人语言陷阱:https://www.nanss.com/shenghuo/4354.html 付出不讨好的精辟句子:https://www.nanss.com/yulu/4178.html 看电影发朋友圈的句子:https://www.nanss.com/wenan/4109.html 情侣爱称:https://www.nanss.com/mingcheng/4169.html 托物言志的诗:https://www.nanss.com/xuexi/4366.html 我希望有个如你一般的人:https://www.nanss.com/xuexi/4435.html 健康就好:https://www.nanss.com/yuedu/4352.html 家族名:https://www.nanss.com/mingcheng/4286.html 夏天的夜晚写一段话:https://www.nanss.com/xuexi/4395.html 表达心存感激的句子:https://www.nanss.com/yulu/4118.html 看淡一切释然的说说:https://www.nanss.com/yulu/4379.html