Postgresql归档及恢复__数据库的归档和恢复是每一位DBA必掌握的基本技能，社区专家阿弟今天带我们大家一起，来研讨PostgreSQL这一重要的开源数据库，其在不同场景下备份和恢复操作过程，涉及通过主、备节点直接进行日志复制的恢复，以及通过NFS目录共享服务情况下的日志复制及恢复。_

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-01 20:02:29+08　　

doudou586 发布于2017-09-01 20:02:29　　

评论: 1　　

顶: 1146　

踩: 1015　

# Postgresql归档及恢复

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

##一、服务器架构图
### 1、基于主节点归档服务器架构图
![pic1](/images/news/2017/pg_archive_restore_01.jpg)
### 2、基于备节点归档服务器架构图
![pic2](/images/news/2017/pg_archive_restore_02.jpg)

## 二、部署环境介绍
### 1、nfs服务器

|项目|值|
|----|----|
|操作系统|Centos 6.9|
|IP地址|192.168.0.231|
|Hostname|nfs|

### 2、PostgreSQL主节点

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

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

### 4、PostgreSQL恢复节点
项目|值
----|----
操作系统|Centos 6.9
IP地址|192.168.0.234
Hostname|recoveryb
PostgreSQL版本号|9.6.1

## 三、nfs服务部署及配置
### 1、yum安装nfs服务

``` 
[root@nfs ~]# yum install nfs* -y  
```

### 2、配置共享目录

```
创建postgres用户
[root@nfs home]# adduser postgres -U -u 500

创建要存储归档的共享目录
[root@nfs home]# mkdir /home/postgres/archive

配置目录为可存取
[root@nfs home]# chown postgres:postgres /home/postgres -Rf
[root@nfs home]# chmod 0755 /home/postgres -Rf

配置共享目录访问属性
[root@nfs home]# vim /etc/exports 
/home/postgres/archive 192.168.0.0/24(rw,sync,all_squash,anonuid=500,anongid=500)
```

anonuid=500是postgres用户的uid值，anongid=500是postgres用户组的gid值,通过下面语句即可查询uid,gid

```
[root@nfs home]# cat /etc/passwd | grep postgres
postgres:x:500:500::/home/postgres:/bin/bash
```

如果你的postgres用户是先前建立的，并且其uid，gid值也不是500，则可以使用usermod,groupmod修改用户和用户组的uid,gid值，如下所示

```
[root@nfs home]# usermod -u 501 postgres
[root@nfs home]# groupmod -g 501 postgres 
[root@nfs home]# cat /etc/passwd | grep postgres
postgres:x:501:501::/home/postgres:/bin/bash
[root@nfs home]#
```

### 3、启动nfs服务
启动nfs服务时需要先启动rpcbind服务

```
[root@nfs home]# service rpcbind start  
正在启动 rpcbind：                                         [确定]

启动nfs服务

[root@nfs home]# service nfs start
启动 NFS 服务：                                            [确定]
启动 NFS mountd：                                          [确定]
启动 NFS 守护进程：                                        [确定]
正在启动 RPC idmapd：                                      [确定]
[root@nfs home]# 
```

### 4、配置服务自动启动

```
[root@nfs home]# chkconfig --add nfs
[root@nfs home]# chkconfig --add rpcbind
[root@nfs home]# chkconfig nfs on   
[root@nfs home]# chkconfig rpcbind on   
```

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

为了让nfs能对外提供服务，你需要将nfs服务port添加到iptables规则中，具体操作如下

```
查询nfs 端口号
[root@nfs home]# rpcinfo -p | grep  -i nfs
100003    2   tcp   2049  nfs

添加至iptables规则表中
[root@nfs ~]# vim /etc/sysconfig/iptables

添加port=2049可以对外提供连接
-A INPUT -m state --state NEW -m tcp -p tcp --dport 2049 -j ACCEPT
-A INPUT -m state --state NEW -m udp -p udp --dport 2049 -j ACCEPT

重启iptables服务
[root@nfs home]# service iptables restart
iptables：将链设置为政策 ACCEPT：filter                    [确定]
iptables：清除防火墙规则：                                 [确定]
iptables：正在卸载模块：                                   [确定]
iptables：应用防火墙规则：                                 [确定]
[root@nfs home]# 
```

如果你不想启用防火墙，则直接关闭iptables服务即可，如下所示

```
[root@nfs home]# service iptables stop
[root@nfs home]# chkconfig --add iptables
[root@nfs home]# chkconfig nfs off   
```
## 四、PostgreSQL主节点部署及配置
### 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

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
```
### 2、配置运行参数

修改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 
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 = 0
log_statement = 'all'
log_timezone = 'PRC'
```

修改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     postgres        192.168.0.0/24          md5
#host    replication     postgres        ::1/128                 trust
```
连接防火墙视自己业务需要配置

### 3、启动服务
```
[postgres@masterdb data9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_ctl start 
-D /home/postgres/data9.6.1/
server starting
[postgres@masterdb data9.6.1]$ 2017-06-05 10:12:11.044 CST     11586 0LOG:  
redirecting log output to logging collector process
2017-06-05 10:12:11.044 CST     11586 0HINT:  Future log output will appear in directory "pg_log".
[postgres@masterdb data9.6.1]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 
-d postgres -U postgres -p 9610
Password for user postgres: 
psql (9.6.1)
Type "help" for help.

postgres=# create table t(id integer);
CREATE TABLE
postgres=# 
```
### 4、添加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
```

### 5、配置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"
```

## 五、PostgreSQL备节点部署及配置
### 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 postgres -p 9610 -D /home/postgres/data9.6.1
Password: 
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
```

### 3、配置运行参数
配置postgresql.conf
```
[postgres@slavedb data9.6.1]$ vim /home/postgres/data9.6.1/postgresql.conf 
archive_mode = always
archive_command = 'DIR=/home/postgres/archive/`date +%F`; test ! -d $DIR && 
mkdir $DIR; test ! -f $DIR/%f && cp %p $DIR/%f' 
```

配置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=postgres password=pgsql'
recovery_target_timeline = 'latest'

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

### 4、mount NFS共享目录存储Xlog归档 ，并测试是否可以存储文件
建立挂载目录/home/postgres/archive
```
[root@slavedb postgresql-9.6.1]# cd /home/postgres/
[root@slavedb postgres]# mkdir archive
```

Mount nfs共享目录192.168.0.231:/home/postgres/archive挂载至/home/postgres/archive
```
[root@slavedb postgres]# mount -t nfs -o nosuid,noatime,noexec,nodev,rw,hard,intr,
rsize=32768,wsize=32768 192.168.0.231:/home/postgres/archive /home/postgres/archive
```

测试挂载后的目录是否读写正常
```
[root@slavedb postgres]# df
Filesystem           1K-blocks    Used Available Use% Mounted on
/dev/sda2             18446076 1516224  15986188   9% /
tmpfs                   502056       0    502056   0% /dev/shm
192.168.0.231:/home/postgres/archive
                      18446080 1168224  16334176   7% /home/postgres/archive
[root@slavedb postgres]# cd /home/postgres/archive/
[root@slavedb archive]# touch a.txt
[root@slavedb archive]# ll
总用量 0
-rw-r--r--. 1 postgres postgres 0 6月   5 11:47 a.txt
[root@slavedb archive]# rm a.txt  -rf
```

### 5、配置开机时自动挂载nfs目录

在/etc/rc.d/rc.local中加入下面脚本
```
[postgres@slavedb data9.6.1]$ vim /etc/rc.d/rc.local 
/bin/mount -t nfs -o nosuid,noatime,noexec,nodev,rw,hard,intr,rsize=32768,
wsize=32768 192.168.0.231:/home/postgres/archive /home/postgres/archive &
```
### 6、启动服务并且验证归档是否成功
启动服务
```
[root@slavedb archive]# su postgres
[postgres@slavedb archive]$ /usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.1/
```

查看是否有归档
```
[postgres@slavedb data9.6.1]$ cd /home/postgres/archive/
[postgres@slavedb archive]$ ls
2017-06-05
[postgres@slavedb archive]$ cd 2017-06-05/
[postgres@slavedb 2017-06-05]$ ls
000000010000000000000004
[postgres@slavedb 2017-06-05]$ 
```
###7、添加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
```
### 8、配置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"
```

## 六、PostgreSQL恢复节点部署及配置
### 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、使用pg_basebackup从备节点生成一个归档节点
```
[postgres@recovery ~]$ /usr/local/pgsql9.6.1/bin/pg_basebackup -h 192.168.0.233 -p 9610 
-U postgres -D /home/postgres/data9.6.1
Password: 
[postgres@recovery ~]$ 
```
###3、配置运行参数

配置postgresql.conf
```
恢复节点不需要再归档
#archive_mode = off
#archive_command = ''
```

配置recovery.conf
```
[root@recovery data9.6.1]# vim /home/postgres/data9.6.1/recovery.conf
archive_cleanup_command = '/usr/local/pgsql9.6.1/bin/pg_archivecleanup 
                             /home/postgres/data9.6.1/pg_xlog %r'
standby_mode = 'on'
restore_command = 'cp /home/postgres/archive/20[1-9][0-9]-[0-1][0-9]-[0-3][0-9]/%f %p;'
recovery_target_time = '2017-06-05 12:50:00+08'
recovery_target_inclusive = false

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

### 4、mount NFS共享目录复制Xlog文件 ，并测试是否可以复制Xlog文件
```
[root@recovery postgres]# cd /home/postgres/
[root@recovery postgres]# mkdir archive
[root@recovery postgres]# chown postgres:postgres archive/ 
[root@recovery postgres]# mount -t nfs -o nosuid,noatime,noexec,nodev,rw,hard,intr,
  rsize=32768,wsize=32768 192.168.0.231:/home/postgres/archive /home/postgres/archive
[root@recovery postgres]# cd /home/postgres/archive/2017-06-05/
[root@recovery 2017-06-05]# cp 000000010000000000000004 /tmp
[root@recovery 2017-06-05]# df
Filesystem           1K-blocks    Used Available Use% Mounted on
/dev/sda2             18446076 1532836  15969576   9% /
tmpfs                   502056       0    502056   0% /dev/shm
192.168.0.231:/home/postgres/archive
                      18446080 1184704  16317696   7% /home/postgres/archive
[root@recovery 2017-06-05]# 
```

### 5、配置开机时自动挂载nfs目录
在/etc/rc.d/rc.local中加入下面脚本
```
/bin/mount -t nfs -o nosuid,noatime,noexec,nodev,rw,hard,intr,rsize=32768,wsize=32768 
                  192.168.0.231:/home/postgres/archive /home/postgres/archive &
```

### 6、启动恢复节点服务并且验证数据恢复是否成功
```
[postgres@recovery data9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.1/
server starting
[postgres@recovery data9.6.1]$ 2017-06-05 13:15:51.968 CST     11303 0LOG:  
redirecting log output to logging collector process
2017-06-05 13:15:51.968 CST     11303 0HINT:  Future log output will appear in directory "pg_log".

[postgres@recovery data9.6.1]$ cd /home/postgres/data9.6.1/pg_log
[postgres@recovery pg_log]$ ll -rt
总用量 68
-rw-------. 1 postgres postgres  1267 6月   5 13:15 postgresql-2017-06-05_131551.csv
-rw-------. 1 postgres postgres   557 6月   5 13:15 postgresql-2017-06-05_131551.log
[postgres@recovery pg_log]$ cat postgresql-2017-06-05_131551.csv
CST,1/0,0,LOG,00000,"restored log file ""000000010000000000000007"" from archive",,,,,,,,,""
2017-06-05 13:15:52.310 CST,,,11305,,5934e907.2c29,6,,2017-06-05 13:15:51 CST,1/0,0,LOG,00000,
"consistent recovery state reached at 0/8000000",,,,,,,,,""
2017-06-05 13:15:52.311 CST,,,11303,,5934e907.2c27,2,,2017-06-05 13:15:51 CST,,0,LOG,00000,
"database system is ready to accept read only connections",,,,,,,,,""
[postgres@recovery pg_log]$ 
```

看到""000000010000000000000007"" from archive"和database system is ready to accept read only

connections表示恢复节点部署成功，并且恢复数据完成，当前可以接受访问

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

在/etc/sysconfig/iptables规则表中添加上下面这条规则即可让9610 port即提供对外访问
```
[root@recovery postgresql-9.6.1]# vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 9610 -j ACCEPT
```
记得重启iptables服务哦
```
[root@recovery postgresql-9.6.1]# service iptables restart
```
### 8、配置PostgreSQL服务开机自动启动
在 /etc/rc.d/rc.local  文件中添加下面启动脚本
```
[root@recovery 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、主节点DML操作一些数据并归档
```
[root@masterdb postgres]# /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 
-d postgres -U postgres -p 9610
Password for user postgres: 
psql (9.6.1)
Type "help" for help.

postgres=# insert into t values(1),(2),(3);
INSERT 0 3
postgres=# select * from t;
 id 
----
  1
  2
  3
(3 rows)

postgres=# select now();
             now              
------------------------------
 2017-06-05 13:27:55.52218+08
(1 row)

postgres=# delete from t;
DELETE 3
postgres=# select * from t;
 id 
----
(0 rows)

postgres=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/8000620
(1 row)

postgres=# 
```

###2、配置恢复参数

恢复到删除数据前的时间点，但不包含该时间点
```
[root@recovery data9.6.1]# vim /home/postgres/data9.6.1/recovery.conf
archive_cleanup_command='/usr/local/pgsql9.6.1/bin/pg_archivecleanup 
            /home/postgres/data9.6.1/pg_xlog %r'
standby_mode = 'on'
restore_command = 'cp /home/postgres/archive/20[1-9][0-9]-[0-1][0-9]-[0-3][0-9]/%f %p;'
recovery_target_time = '2017-06-05 13:27:55.52218+08'
recovery_target_inclusive = false
```

### 3、检验恢复结果
```
[postgres@recovery data9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_ctl restart 
-D /home/postgres/data9.6.1/
waiting for server to shut down.... done
server stopped
server starting
[postgres@recovery data9.6.1]$ 2017-06-05 13:35:15.258 CST     11627 0LOG:  redirecting log 
output to logging collector process
2017-06-05 13:35:15.258 CST     11627 0HINT:  Future log output will appear in directory "pg_log".

[postgres@recovery data9.6.1]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.234 -p 9610 
-U postgres -d postgres
Password for user postgres: 
psql (9.6.1)
Type "help" for help.

postgres=# select * from t;
 id 
----
  1
  2
  3
(3 rows)
```
恢复到删除数据前的时间点，但不包含该时间点

## 八、基于事务ID恢复检验
### 1、主节点DML操作一些数据并归档
```
[postgres@masterdb ~]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -d postgres 
-U postgres -p 9610
Password for user postgres: 
psql (9.6.1)
Type "help" for help.

postgres=# select * from t;
 id 
----
(0 rows)

postgres=# begin;
BEGIN
postgres=# insert into t values(5);
INSERT 0 1
postgres=# select * from t;
 id 
----
  5
(1 row)

postgres=# select txid_current();
 txid_current 
--------------
         1688
(1 row)

postgres=# commit;
COMMIT
postgres=# delete from t;
DELETE 1
postgres=# select * from t ;
 id 
----
(0 rows)

postgres=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/90006B0
(1 row)

postgres=# 
```

### 2、配置恢复参数
恢复到删除数据前的事务xid，包含该事务xid
```
[postgres@recovery data9.6.1]$ vim /home/postgres/data9.6.1/recovery.conf 
archive_cleanup_command='/usr/local/pgsql9.6.1/bin/pg_archivecleanup 
/home/postgres/data9.6.1/pg_xlog %r'
standby_mode = 'on'
restore_command = 'cp /home/postgres/archive/20[1-9][0-9]-[0-1][0-9]-[0-3][0-9]/%f %p;'
recovery_target_xid = 1688
recovery_target_inclusive = true
[postgres@recovery data9.6.1]$ 
```
### 3、检验恢复结果
```
[postgres@recovery data9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_ctl restart 
-D /home/postgres/data9.6.1/
waiting for server to shut down.... done
server stopped
server starting
[postgres@recovery data9.6.1]$ 2017-06-05 13:56:46.395 CST     11725 0LOG:  redirecting log 
output to logging collector process
2017-06-05 13:56:46.395 CST     11725 0HINT:  Future log output will appear in directory "pg_log".

[postgres@recovery data9.6.1]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.234 -p 9610 
-U postgres -d postgres
Password for user postgres: 
psql (9.6.1)
Type "help" for help.

postgres=# select * from t;
 id 
----
  5
(1 row)

postgres=# 
```
恢复到删除数据前的事务xid，包含该事务xid

## 九、如何获取SQL语句执行的时间点和事务ID号
### 1、修改主节点运行参数
```
[postgres@masterdb ~]$ vim /home/postgres/data9.6.1/postgresql.conf

log_line_prefix = '%m %a %r %d %u %p %x'
log_min_duration_statement = 0
```
### 2、重启服务查看日志获取时间点和事务ID号
重启服务
```
[postgres@masterdb ~]$ /usr/local/pgsql9.6.1/bin/pg_ctl restart -D /home/postgres/data9.6.1/
```
连接数据库执行一些DML操作
```
[postgres@masterdb ~]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.232 -p 9610 
-U postgres -d postgres 
Password for user postgres: 
psql (9.6.1)
Type "help" for help.

postgres=# delete from t;
DELETE 0
postgres=# begin;
BEGIN
postgres=# insert into t values(1);
INSERT 0 1
postgres=# select txid_current();       
 txid_current 
--------------
         1690
(1 row)

postgres=# commit;
COMMIT
postgres=# \q
```

查看用户访问日志
```
[postgres@masterdb ~]$ cd /home/postgres/data9.6.1/pg_log/
[postgres@masterdb pg_log]$ ll -rt
总用量 80
-rw-------. 1 postgres postgres   172 6月   5 14:57 postgresql-2017-06-05_145727.log
-rw-------. 1 postgres postgres  4229 6月   5 14:59 postgresql-2017-06-05_145727.csv
[postgres@masterdb pg_log]$ cat postgresql-2017-06-05_145727.csv

2017-06-05 14:58:36.277 
CST,"postgres","postgres",2659,"192.168.0.232:55912",5935010e.a63,3,"idle",
2017-06-05 14:58:22 
CST,3/4,0,LOG,00000,"statement: delete from t;",,,,,,,,,"psql"
2017-06-05 14:58:36.279 
CST,"postgres","postgres",2659,"192.168.0.232:55912",5935010e.a63,4,"DELETE",
2017-06-05 14:58:22 
CST,3/0,0,LOG,00000,"duration: 1.728 ms",,,,,,,,,"psql"
2017-06-05 14:58:48.446 
CST,"postgres","postgres",2659,"192.168.0.232:55912",5935010e.a63,5,"idle",
2017-06-05 14:58:22 CST,3/5,0,LOG,00000,"statement: begin;",,,,,,,,,"psql" 
2017-06-05 14:58:48.446 
CST,"postgres","postgres",2659,"192.168.0.232:55912",5935010e.a63,6,"BEGIN",
2017-06-05 14:58:22 
CST,3/5,0,LOG,00000,"duration: 0.144 ms",,,,,,,,,"psql"
2017-06-05 14:58:56.669 
CST,"postgres","postgres",2659,"192.168.0.232:55912",5935010e.a63,7,
"idle in transaction",
2017-06-05 14:58:22 
CST,3/5,0,LOG,00000,"statement: insert into t values(1);",,,,,,,,,"psql"
2017-06-05 14:58:56.669 
CST,"postgres","postgres",2659,"192.168.0.232:55912",5935010e.a63,8,"INSERT",
2017-06-05 14:58:22 CST,3/5,1690,LOG,00000,"duration: 0.389 ms",,,,,,,,,"psql"
2017-06-05 14:59:24.637 
CST,"postgres","postgres",2659,"192.168.0.232:55912",5935010e.a63,9,
"idle in transaction",2017-06-05 14:58:22 CST,3/5,1690,LOG,00000,
"statement: select txid_current();",,,,,,,,,"psql"
```
注意：需要客户端发出begin才能记录，这个有点不友好

## 十、使用pg_xlogdump获取事务的ID号
### 1、配置恢复时间点于事务发生时间点之前大约10分钟

设置数据恢复接近于语句执行之前的时间（一般10分钟以上即可）如sql执行的时间'2017-06-05 15:42:00'
```
[postgres@recovery data9.6.1]$ vim /home/postgres/data9.6.1/recovery.conf 
archive_cleanup_command='/usr/local/pgsql9.6.1/bin/pg_archivecleanup 
   /home/postgres/data9.6.1/pg_xlog %r'
standby_mode = 'on'
restore_command = 'cp /home/postgres/archive/20[1-9][0-9]-[0-1][0-9]-[0-3][0-9]/%f %p;'
recovery_target_inclusive = false
recovery_target_time = '2017-06-05 15:32:00'
```
### 2、重启恢复节点PostgreSQL服务进行数据恢复
```
[postgres@recovery data9.6.1]$ /usr/local/pgsql9.6.1/bin/pg_ctl 
  -D /home/postgres/data9.6.1/ restart
```
### 3、连接恢复节点查询涉及表的物理文件名
```
[postgres@recovery data9.6.1]$ /usr/local/pgsql9.6.1/bin/psql -h 192.168.0.234 -p 9610 
-U postgres -d postgres
Password for user postgres: 
psql (9.6.1)
Type "help" for help.

postgres=# select oid from pg_class where relname='t' ; 
  oid  
-------
 16384
(1 row)

postgres=# select pg_relation_filepath(16384);
 pg_relation_filepath 
----------------------
 base/13275/16384
(1 row)
```
### 4、根据物理文件名使用pg_xlogdump查询xlog日志获取事务ID号
```
[postgres@recovery 2017-06-05]$ /usr/local/pgsql9.6.1/bin/pg_xlogdump 
-b /home/postgres/archive/2017-06-05/00000001000000000000000E
rmgr: Standby     len (rec/tot):     24/    50, tx:          0, lsn: 0/0E000028, prev 0/0D0002C0, 
desc: RUNNING_XACTS nextXid 1693 latestCompletedXid 1692 oldestRunningXid 1693
rmgr: Standby     len (rec/tot):     24/    50, tx:          0, lsn: 0/0E000060, prev 0/0E000028, 
desc: RUNNING_XACTS nextXid 1693 latestCompletedXid 1692 oldestRunningXid 1693
rmgr: XLOG        len (rec/tot):     80/   106, tx:          0, lsn: 0/0E000098, prev 0/0E000060, 
desc: CHECKPOINT_ONLINE redo 0/E000060; tli 1; prev tli 1; fpw true; xid 0:1693; oid 16387; 
multi 1; offset 0; oldest xid 1678 in DB 1; oldest multi 1 in DB 1; 
oldest/newest commit timestamp xid: 0/0; oldest running xid 1693; online
rmgr: Heap        len (rec/tot):      8/   335, tx:       1693, lsn: 0/0E000108, prev 0/0E000098, 
desc: DELETE off 7 KEYS_UPDATED 
        blkref #0: rel 1663/13275/16384 fork main blk 0 (FPW); hole: offset: 52, length: 7916

rmgr: Transaction len (rec/tot):      8/    34, tx:       1693, lsn: 0/0E000258, prev 0/0E000108, 
desc: COMMIT 2017-06-05 15:42:31.781703 CST
rmgr: XLOG        len (rec/tot):      0/    24, tx:          0, lsn: 0/0E000280, prev 0/0E000258, 
desc: SWITCH 
[postgres@recovery 2017-06-05]$
```

## 十一、如何实现归档恢复与生产节点只差xxx分钟
### 1、建立执行shell脚本
```
[postgres@recovery shell]$ vim archive_restart.sh

#!/bin/sh
export PATH=/usr/local/pgsql9.6.1/bin:$PATH
PGDATA=/home/postgres/data9.6.1
RECOVERY_PATH=/home/postgres/data9.6.1/recovery.conf

#比当前时间少5分钟
td=`date '+%Y-%m-%d %H:%M:%S' -d  '-5 minutes'`

echo "archive_cleanup_command = '/usr/local/pgsql9.6.1/bin/pg_archivecleanup 
/home/postgres/data9.6.1/pg_xlog %r'" > $RECOVERY_PATH
echo "standby_mode = 'on'" >> $RECOVERY_PATH
echo "restore_command = 'cp /home/postgres/archive/20[1-9][0-9]-[0-1][0-9]-[0-3][0-9]/%f %p;'" 
      >> $RECOVERY_PATH
echo "recovery_target_time = '$td'" >> $RECOVERY_PATH

pg_ctl restart -D $PGDATA -m i
```
其它时间点可以这样表示
```
#比当前时间前一个小时
#td=`date '+%Y-%m-%d %H:%M:%S' -d  '-1 hours'`
#比当前时间前一个月
#td=`date '+%Y-%m-%d %H:%M:%S' -d  '1 month ago'`
#比当前时间前一周 
#td=`date '+%Y-%m-%d %H:%M:%S' -d  '1 week ago'`
#比当前时间前一天
#td=`date '+%Y-%m-%d %H:%M:%S' -d  '1 day ago'`
```
### 2、配置一个cron定时任务执行shell脚本
```
[postgres@recovery shell]$ crontab -e
MAILTO=""

#归档节点与生产库相差5分钟，每分钟同步一次
*/1 * * * * /home/postgres/shell/archive_restart.sh >> 
               /home/postgres/shell/archive_restart.log 2>&1
```

## 十二、遏制PostgreSQL WAL的疯狂增长
### 1、WAL写放大的原因

PostgreSQL在写入频繁的场景中，会产生大量的WAL日志，而且WAL日志量会远远超过实际更新的数据量。 我们可以把这种现象起个名字,叫做“WAL写放大”，造成WAL写放大的主要原因见下面。

在checkpoint之后第一次修改页面，需要在WAL中输出整个page，即全页写(full page writes)。全页写的目的是防止在意外宕机时出现的数据块部分写导致数据库无法恢复。

更新记录时如果新记录位置(ctid)发生变更，索引记录也要相应变更，这个变更也要记入WAL。更严重的是索引记录的变更又有可能导致索引页的全页写，进一步加剧了WAL写放大。

过量的WAL输出会对系统资源造成很大的消耗，因此需要进行适当的优化。

磁盘IO

WAL写入是顺序写,通常再差的硬盘对付WAL的写入速度也是绰绰有余。所以一般可以忽略。

网络IO

对局域网内的复制估计还不算问题，远程复制就难说了。

磁盘空间

如果做WAL归档，需要的磁盘空间也是巨大的。

### 2、实验环境准备

建立测试表
```
postgres=# create table xlog_up(id integer not null primary key,f1 integer not null,
                                f2 integer not null);
CREATE TABLE
postgres=# create index xlog_up_f1_idx on xlog_up using btree(f1);
CREATE INDEX
postgres=# create index xlog_up_f2_idx on xlog_up using btree(f2); 
CREATE INDEX
postgres=# insert into xlog_up(id,f1,f2) select t,t,t from generate_series(1,1000000) as t;
INSERT 0 1000000
postgres=# vacuum analyze xlog_up ;
VACUUM
postgres=# 
```

建立pgbench测试脚本
```
[postgres@master shell]$ cat xlog_bench.sql 
\set id random(1,1000000)
update xlog_up set f1=:id where id=:id
```
postgresql.conf参数配置
```
#把checkpoint的条件设置大一些
checkpoint_timeout = 180min     # range 30s-1d
max_wal_size = 4GB

autovacuum = on                             
log_autovacuum_min_duration = 0
autovacuum_max_workers = 4 
autovacuum_naptime = 1s 
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.002  
autovacuum_analyze_scale_factor = 0.001 
autovacuum_vacuum_cost_delay = 0ms 
autovacuum_vacuum_cost_limit = -1 
```

#vacuum参数可以针对表进行设置，如 alter table xlog_up set (autovacuum_vacuum_scale_factor =0.002);

### 3、在应用的写负载不变的情况下，减少WAL生成量主要有下面几种办法

checkpoint的影响

pgbench更新测试前执行checkpoint操作和获得测试前lsn
```
[postgres@master bin]$ /usr/local/pgsql9.6.1/bin/psql -U postgres -p 9610 -d postgres 
                                  -c 'checkpoint;select pg_current_xlog_location();'
 pg_current_xlog_location 
--------------------------
 1/66EAEBB8
(1 row)
```

pgbench更新测试
```
[postgres@master bin]$ /usr/local/pgsql9.6.1/bin/pgbench -p 9610 -d postgres -U postgres 
                     -n -M prepared -c 6 -t 5000 -f /home/postgres/shell/xlog_bench.sql 
```
获得测试后lsn
```
[postgres@master bin]$ /usr/local/pgsql9.6.1/bin/psql -U postgres -p 9610 -d postgres 
                            -c 'select pg_current_xlog_location();'           
 pg_current_xlog_location 
--------------------------
 1/6DE09870
(1 row)
```

使用pg_xlogdump统计不同类型的WAL记录的数量，大小以及FPI的比例
```
[postgres@master bin]$ /usr/local/pgsql9.6.1/bin/pg_xlogdump --stats=record 
-p /home/postgres/data9.6.1/pg_xlog -s 1/66EAEBB8 -e 1/6DE09870 
````

Type   |  N   (%) | Record size (%) | FPI size (%) | Combined size  (%)
----|---:|---:|---:|----:|
XLOG/FPI_FOR_HINT  |   933 (  0.39)  |22392 (  0.30) | 7601104 (  7.46) | 7623496 (  6.98)
Transaction/COMMIT|   30000 ( 12.47) | 960000 ( 13.06) | 0 (  0.00) |    960000 (  0.88)
Transaction/COMMIT| 26 (  0.01)|7488 (  0.10)|   0 (  0.00)|7488 (  0.01)
CLOG/ZEROPAGE|       1 (  0.00)|  28 (  0.00)|   0 (  0.00)|  28 (  0.00)
Standby/RUNNING_XACTS|        12 (  0.00)| 816 (  0.01)|   0 (  0.00)| 816 (  0.00)
Standby/INVALIDATIONS|        13 (  0.01)|3016 (  0.04)|   0 (  0.00)|3016 (  0.00)
Heap2/CLEAN|     45091 ( 18.74)|1442912 ( 19.63)|6057020 (  5.94)|7499932 (  6.86)
Heap2/CLEANUP_INFO| 13 (  0.01)| 520 (  0.01)|   0 (  0.00)| 520 (  0.00)
Heap2/VISIBLE|   35590 ( 14.79)|1032110 ( 14.04)|2055296 (  2.02)|3087406 (  2.83)
Heap/UPDATE|     14456 (  6.01)| 549328 (  7.47)|2001820 (  1.96)|2551148 (  2.33)
Heap/HOT_UPDATE| 15622 (  6.49)| 593636 (  8.07)|12068724 ( 11.84)|12662360 ( 11.59)
Heap/LOCK|       14456 (  6.01)| 462592 (  6.29)|14917432 ( 14.64)|15380024 ( 14.07)
Heap/INPLACE|     156 (  0.06)|4056 (  0.06)|  14360 (  0.01)|  18416 (  0.02)
Btree/INSERT_LEAF|43368 ( 18.02)|1127568 ( 15.34)|49034740 ( 48.11)|50162308 ( 45.90)
Btree/VACUUM|    40914 ( 17.00)|1145592 ( 15.58)|8173660 (  8.02)|9319252 (  8.53)
Total |          240651   | 7352054 [6.73%] |101924156 [93.27%]  |   109276210 [100%]

第二次pgbench后wal统计结果

Type   |  N   (%) | Record size (%) | FPI size (%) | Combined size  (%)
----|---:|---:|---:|----:|
XLOG/FPI|20 (  0.01)|480 (  0.01)|480 (  0.01)|960 (  0.01)
Transaction/COMMIT|       30000 ( 12.01)| 960000 ( 12.60)|  0 (  0.00)| 960000 (  8.51)
Transaction/COMMIT|          25 (  0.01)|   6768 (  0.09)|  0 (  0.00)|   6768 (  0.06)
CLOG/ZEROPAGE||  1 (  0.00)| 28 (  0.00)|  0 (  0.00)| 28 (  0.00)
Standby/RUNNING_XACTS|        9 (  0.00)|624 (  0.01)|  0 (  0.00)|624 (  0.01)
Standby/INVALIDATIONS|       13 (  0.01)|   3016 (  0.04)|  0 (  0.00)|   3016 (  0.03)
Heap2/CLEAN||46713 ( 18.70)|1494816 ( 19.63)|  0 (  0.00)|1494816 ( 13.25)
Heap2/CLEANUP_INFO|          13 (  0.01)|520 (  0.01)|  0 (  0.00)|520 (  0.00)
Heap2/VISIBLE|            36983 ( 14.81)|1072507 ( 14.08)|  0 (  0.00)|1072507 (  9.51)
Heap/UPDATE||15357 (  6.15)| 583566 (  7.66)|  0 (  0.00)| 583566 (  5.17)
Heap/HOT_UPDATE|          14711 (  5.89)| 559018 (  7.34)| 114552 (  3.12)| 673570 (  5.97)
Heap/LOCK||  15364 (  6.15)| 491648 (  6.45)|  81544 (  2.22)| 573192 (  5.08)
Heap/INPLACE|| 143 (  0.06)|   3718 (  0.05)|  0 (  0.00)|   3718 (  0.03)
Heap/UPDATE+INIT|             7 (  0.00)|266 (  0.00)|  0 (  0.00)|266 (  0.00)
Btree/INSERT_LEAF|        46092 ( 18.45)|1198392 ( 15.73)|3470100 ( 94.64)|4668492 ( 41.37)
Btree/VACUUM|             44340 ( 17.75)|1241520 ( 16.30)|  0 (  0.00)|1241520 ( 11.00)
Total|| 249791|     7616887 [67.50%]|3666676 [32.50%] |11283563 [100%]

第三次pgbench后wal统计结果
Type   |  N   (%) | Record size (%) | FPI size (%) | Combined size  (%)
----|---:|---:|---:|----:|
Transaction/COMMIT| 30000 ( 12.61)|960000 ( 13.21)|0 (  0.00)|960000 ( 12.51)
Transaction/COMMIT|    25 (  0.01)|  6768 (  0.09)|0 (  0.00)|  6768 (  0.09)
CLOG/ZEROPAGE|1 (  0.00)|    28 (  0.00)|0 (  0.00)|    28 (  0.00)
Standby/RUNNING_XACTS|  9 (  0.00)|   624 (  0.01)|0 (  0.00)|   624 (  0.01)
Standby/INVALIDATIONS| 12 (  0.01)|  2784 (  0.04)|0 (  0.00)|  2784 (  0.04)
Heap2/CLEAN|   43413 ( 18.25)|1389216 ( 19.12)|0 (  0.00)|1389216 ( 18.11)
Heap2/CLEANUP_INFO|    12 (  0.01)|   480 (  0.01)|0 (  0.00)|   480 (  0.01)
Heap2/VISIBLE| 34138 ( 14.35)|990002 ( 13.62)|0 (  0.00)|990002 ( 12.91)
Heap/UPDATE|   14928 (  6.28)|567264 (  7.81)|0 (  0.00)|567264 (  7.39)
Heap/HOT_UPDATE|    15141 (  6.37)|575358 (  7.92)|0 (  0.00)|575358 (  7.50)
Heap/LOCK|14934 (  6.28)|477888 (  6.58)|0 (  0.00)|477888 (  6.23)
Heap/INPLACE|    139 (  0.06)|  3614 (  0.05)|0 (  0.00)|  3614 (  0.05)
Heap/UPDATE+INIT|  6 (  0.00)|   228 (  0.00)|0 (  0.00)|   228 (  0.00)
Btree/INSERT_LEAF|  44802 ( 18.84)|1164852 ( 16.03)|404640 (100.00)|1569492 ( 20.46)
Btree/VACUUM|  40272 ( 16.93)|1127616 ( 15.52)|0 (  0.00)|1127616 ( 14.70)
Total|   237832|   7266722 [94.73%]|404640 [5.27%]|7671362 [100%]

次数|FPI比例|记录量
:---:|---:|---|
1|93.27|109276210
2|32.5|11283563
3|5.27|7671362

从上面的测试结果来看，fpi导致wal写入放大达到10倍以上，所以适当的调整checkpoint_timeout 和max_wal_size 的参数值非常有必需

增加HOT_UPDATE比例

普通的UPDATE经常需要更新2个数据块，并且可能还要更新索引page，这些又都有可能产生FPI。而HOT_UPDATE只修改1个数据块，需要写的WAL量也大大减少。

HOT_UPDATE比例过低的一个很常见的原因是更新频繁的表的fillfactor设置不恰当。fillfactor的默认值为100%，可以先将其调整为80%。

```
postgres=# alter table xlog_up set (fillfactor =80);
ALTER TABLE
postgres=# alter index xlog_up_f1_idx set (fillfactor =80);     
ALTER INDEX
postgres=# alter index xlog_up_f2_idx set (fillfactor =80); 
ALTER INDEX
postgres=# alter index xlog_up_pkey set (fillfactor =80);      
ALTER INDEX
```

对于宽表，要进一步减小fillfactor使得至少可以保留一个tuple的空闲空间。可以查询pg_class系统表估算平均tuple大小，并算出合理的fillfactor值。

```
postgres=# select 1-relpages/reltuples as max_fillfactor from pg_class where relname='xlog_up';  
 max_fillfactor 
----------------
       0.993243
(1 row)
```

上面估算出的99%的基础上，可以把fillfactor再稍微设小一点，比如设成95%

fillfactor =100 pgbench后wal统计结果

```
[postgres@master bin]$ /usr/local/pgsql9.6.1/bin/psql -U postgres -p 9610 -d postgres 
-c 'checkpoint;select pg_current_xlog_location();'
 pg_current_xlog_location 
--------------------------
 1/60BCE770
(1 row)

[postgres@master bin]$ /usr/local/pgsql9.6.1/bin/pgbench -p 9610 -d postgres -U postgres 
-n -M prepared -c 6 -t 5000 -f /home/postgres/shell/xlog_bench.sql

[postgres@dywl bin]$ /usr/local/pgsql9.6.1/bin/psql -U postgres -p 9610 -d postgres 
-c 'select pg_current_xlog_location();'           
 pg_current_xlog_location 
--------------------------
 1/66EAEB10
(1 row)

[postgres@master bin]$ /usr/local/pgsql9.6.1/bin/pg_xlogdump --stats=record 
-p /home/postgres/data9.6.1/pg_xlog -s 1/60BCE770 -e 1/66EAEB10
```

Type   |  N   (%) | Record size (%) | FPI size (%) | Combined size  (%)
----|---:|---:|---:|----:|
XLOG/FPI_FOR_HINT|846 (  0.45)|20304 (  0.35)|6889348 (  7.48)|6909652 (  7.05)
Transaction/COMMIT|30000 ( 16.04)| 960000 ( 16.47)|    0 (  0.00)| 960000 (  0.98)
Transaction/COMMIT|26 (  0.01)| 6192 (  0.11)|    0 (  0.00)| 6192 (  0.01)
Standby/RUNNING_XACTS|13 (  0.01)|  880 (  0.02)|    0 (  0.00)|  880 (  0.00)
Standby/INVALIDATIONS|12 (  0.01)| 2784 (  0.05)|    0 (  0.00)| 2784 (  0.00)
Heap2/CLEAN|    37981 ( 20.31)|1215392 ( 20.86)|3909192 (  4.24)|5124584 (  5.23)
Heap2/CLEANUP_INFO|12 (  0.01)|  480 (  0.01)|    0 (  0.00)|  480 (  0.00)
Heap2/VISIBLE|  30099 ( 16.09)| 872871 ( 14.98)| 8192 (  0.01)| 881063 (  0.90)
Heap/UPDATE|     8656 (  4.63)| 328928 (  5.64)|3776376 (  4.10)|4105304 (  4.19)
Heap/HOT_UPDATE|21422 ( 11.45)| 814036 ( 13.97)|27564424 ( 29.92)|28378460 ( 28.97)
Heap/LOCK|       8656 (  4.63)| 276992 (  4.75)|2649400 (  2.88)|2926392 (  2.99)
Heap/INPLACE|     125 (  0.07)| 3250 (  0.06)|14360 (  0.02)|17610 (  0.02)
Btree/INSERT_LEAF| 25968 ( 13.88)| 675168 ( 11.59)|45373920 ( 49.24)|46049088 ( 47.00)
Btree/VACUUM|   23223 ( 12.42)| 650244 ( 11.16)|1954240 (  2.12)|2604484 (  2.66)
Total|187039|       5827521 [5.95%]|92139452 [94.05%]|97966973 [100%]

fillfactor =80 pgbench后wal统计结果
```
[postgres@master bin]$ /usr/local/pgsql9.6.1/bin/psql -U postgres -p 9610 -d postgres 
-c 'checkpoint;select pg_current_xlog_location();'
 pg_current_xlog_location 
--------------------------
 1/5C477ED8
(1 row)

[postgres@master bin]$ /usr/local/pgsql9.6.1/bin/pgbench -p 9610 -d postgres 
-U postgres -n -M prepared -c 6 -t 5000 -f /home/postgres/shell/xlog_bench.sql

[postgres@dywl bin]$ /usr/local/pgsql9.6.1/bin/psql -U postgres -p 9610 -d postgres
 -c 'select pg_current_xlog_location();'           
 pg_current_xlog_location 
--------------------------
 1/60BCE2F8
(1 row)

[postgres@master bin]$ /usr/local/pgsql9.6.1/bin/pg_xlogdump --stats=record 
-p /home/postgres/data9.6.1/pg_xlog -s 1/5C477ED8 -e 1/60BCE2F8
```

Type   |  N   (%) | Record size (%) | FPI size (%) | Combined size  (%)
----|---:|---:|---:|----:|
XLOG/FPI_FOR_HINT|4 (  0.00)|96 (  0.00)|32768 (  0.05)|32864 (  0.05)
Transaction/COMMIT|  30000 ( 23.61)|960000 ( 23.27)| 0 (  0.00)|960000 (  1.35)
Transaction/COMMIT|   25 (  0.02)| 5904 (  0.14)| 0 (  0.00)| 5904 (  0.01)
CLOG/ZEROPAGE|    1 (  0.00)|28 (  0.00)| 0 (  0.00)|28 (  0.00)
Standby/RUNNING_XACTS|   11 (  0.01)|  744 (  0.02)| 0 (  0.00)|  744 (  0.00)
Standby/INVALIDATIONS|   11 (  0.01)| 2456 (  0.06)| 0 (  0.00)| 2456 (  0.00)
Heap2/CLEAN|  30540 ( 24.04)|977280 ( 23.68)| 0 (  0.00)|977280 (  1.37)
Heap2/CLEANUP_INFO|         11 (  0.01)|  440 (  0.01)| 0 (  0.00)|  440 (  0.00)
Heap2/VISIBLE|22609 ( 17.80)|655661 ( 15.89)| 8192 (  0.01)|663853 (  0.93)
Heap/UPDATE|   2000 (  1.57)|76000 (  1.84)|58804 (  0.09)|134804 (  0.19)
Heap/HOT_UPDATE| 28063 ( 22.09)|1066394 ( 25.84)|42226972 ( 63.05)|43293366 ( 60.89)
Heap/LOCK|     2012 (  1.58)|64384 (  1.56)|2298908 (  3.43)|2363292 (  3.32)
Heap/INPLACE|   115 (  0.09)| 2990 (  0.07)|13712 (  0.02)|16702 (  0.02)
Heap/UPDATE+INIT|12 (  0.01)|  456 (  0.01)| 0 (  0.00)|  456 (  0.00)
Btree/INSERT_LEAF| 6036 (  4.75)|156936 (  3.80)|22175760 ( 33.11)|22332696 ( 31.41)
Btree/VACUUM|  5592 (  4.40)|156576 (  3.79)|153660 (  0.23)|310236 (  0.44)
Total|       127042|    4126345 [5.80%]|66968776 [94.20%]|71095121 [100%]

修改fillfactor 前后测试数据对比

Fillfactor值|Heap/UPDATE|Heap/HOT_UPDATE|记录量
:----:|---:|---:|---:|
100|4105304 (  4.19)|28378460 ( 28.97)|97966973
80|134804 (  0.19)|43293366 ( 60.89)|71095121

大约会减少27%的记录量

压缩WAL

PostgreSQL9.5新增加了一个wal_compression参数，设为on可以对FPI进行压缩，削减WAL的大小。
修改postgresql.conf，开启WAL压缩

wal_compression = off pgbench后wal统计结果

Type   |  N   (%) | Record size (%) | FPI size (%) | Combined size  (%)
----|---:|---:|---:|----:|
XLOG/FPI_FOR_HINT|1073 (  0.44)|25752 (  0.34)|8748072 (  8.57)|8773824 (  8.01)
Transaction/COMMIT| 30000 ( 12.21)| 960000 ( 12.81)|    0 (  0.00)| 960000 (  0.88)
Transaction/COMMIT| 26 (  0.01)| 7200 (  0.10)|    0 (  0.00)| 7200 (  0.01)
CLOG/ZEROPAGE|       1 (  0.00)|   28 (  0.00)|    0 (  0.00)|   28 (  0.00)
Standby/RUNNING_XACTS|         11 (  0.00)|  744 (  0.01)|    0 (  0.00)|  744 (  0.00)
Standby/INVALIDATIONS|         13 (  0.01)| 3016 (  0.04)|    0 (  0.00)| 3016 (  0.00)
Heap2/CLEAN|     46178 ( 18.80)|1477696 ( 19.71)|2428640 (  2.38)|3906336 (  3.57)
Heap2/CLEANUP_INFO| 13 (  0.01)|  520 (  0.01)|    0 (  0.00)|  520 (  0.00)
Heap2/VISIBLE|   36498 ( 14.86)|1058442 ( 14.12)| 797884 (  0.78)|1856326 (  1.69)
Heap/UPDATE|     14747 (  6.00)| 560386 (  7.48)|4864068 (  4.77)|5424454 (  4.95)
Heap/HOT_UPDATE| 15328 (  6.24)| 582464 (  7.77)|9845620 (  9.65)|10428084 (  9.52)
Heap/LOCK|       14750 (  6.00)| 472000 (  6.30)|18240288 ( 17.88)|18712288 ( 17.09)
Heap/INPLACE|      150 (  0.06)| 3900 (  0.05)|14360 (  0.01)|18260 (  0.02)
Heap/UPDATE+INIT|    3 (  0.00)|  114 (  0.00)|    0 (  0.00)|  114 (  0.00)
Btree/INSERT_LEAF| 44250 ( 18.01)|1150500 ( 15.35)|50766760 ( 49.76)|51917260 ( 47.40)
Btree/VACUUM|    42627 ( 17.35)|1193556 ( 15.92)|6319740 (  6.19)|7513296 (  6.86)
Total|          245668|       7496318 [6.84%]|102025432 [93.16%]|109521750 [100%]

wal_compression = on pgbench后wal统计结果
Type   |  N   (%) | Record size (%) | FPI size (%) | Combined size  (%)
----|---:|---:|---:|----:|
XLOG/FPI_FOR_HINT|4 (  0.00)|96 (  0.00)|6550 (  0.01)|6646 (  0.01)
Transaction/COMMIT| 30000 ( 12.71)|960000 ( 13.31)| 0 (  0.00)|960000 (  1.58)
Transaction/COMMIT|     25 (  0.01)|6048 (  0.08)| 0 (  0.00)|6048 (  0.01)
CLOG/ZEROPAGE|    1 (  0.00)|28 (  0.00)| 0 (  0.00)|28 (  0.00)
Standby/RUNNING_XACTS|   11 (  0.00)| 768 (  0.01)| 0 (  0.00)| 768 (  0.00)
Standby/INVALIDATIONS|      12 (  0.01)|2784 (  0.04)| 0 (  0.00)|2784 (  0.00)
Heap2/CLEAN|  42641 ( 18.06)|1364512 ( 18.92)|378970 (  0.71)|1743482 (  2.88)
Heap2/CLEANUP_INFO|         12 (  0.01)| 480 (  0.01)| 0 (  0.00)| 480 (  0.00)
Heap2/VISIBLE|33624 ( 14.24)|975096 ( 13.52)| 70382 (  0.13)|1045478 (  1.72)
Heap/UPDATE|  15056 (  6.38)|572128 (  7.93)|3807880 (  7.13)|4380008 (  7.22)
Heap/HOT_UPDATE|  15015 (  6.36)|570570 (  7.91)|5564332 ( 10.42)|6134902 ( 10.12)
Heap/LOCK|    15060 (  6.38)|481920 (  6.68)|9065952 ( 16.97)|9547872 ( 15.75)
Heap/INPLACE|   124 (  0.05)|3224 (  0.04)|3211 (  0.01)|6435 (  0.01)
Heap/UPDATE+INIT| 4 (  0.00)| 152 (  0.00)| 0 (  0.00)| 152 (  0.00)
Btree/INSERT_LEAF| 45180 ( 19.14)|1174680 ( 16.28)|34039761 ( 63.72)|35214441 ( 58.08)
Btree/VACUUM| 39315 ( 16.65)|1100820 ( 15.26)|482697 (  0.90)|1583517 (  2.61)
Total|       236084|    7213306 [11.90%]|53419735 [88.10%]|60633041 [100%]

开启wal_compression 前后测试数据对比

wal_compression值|记录量
---|---:|
off|109521750
on|60633041

压缩后记录量减少了48888709，压缩率为44%左右

### 4、优化WAL的副作用

前面用到了3种优化手段，如果设置不当，也会产生副作用，具体如下：

延长checkpoint时间间隔

导致crash恢复时间变长。crash恢复时需要回放的WAL日志量一般小于max_wal_size的一半，WAL回放速度(wal_compression=on时)一般是50MB/s~150MB/s之间。可以根据可容忍的最大crash恢复时间，估算出允许的max_wal_size的最大值。

调整fillfactor

过小的设置会浪费存储空间，这个不难理解。不过，对于频繁更新的表，即使把fillfactor设成100%，每个page里还是要一部分空间被dead tuple占据，不会比设置成一个合适的稍小的fillfactor更节省空间。

设置wal_compression=on

需要额外占用CPU资源进行压缩，但影响不大。

## 十三、使用过程一些问题
### 1、启动nfs提示rpc.nfsd: unable to set any sockets for nfsd
```
#启动 NFS 守护进程：
rpc.nfsd: writing fd to kernel failed: errno 111 (Connection refused)
rpc.nfsd: unable to set any sockets for nfsd
```
这是rpmbind服务没动，把这个服务开启后即可，如下所示
```
[root@nfs postgres]# service rpcbind start
正在启动 rpcbind：                                         [确定]
[root@nfs postgres]# /etc/rc.d/init.d/nfs start
启动 NFS 服务：                                            [确定]
启动 NFS mountd：                                          [确定]
启动 NFS 守护进程：                                        [确定]
正在启动 RPC idmapd：                                      [确定]
[root@nfs postgres]# 
```

把rpcbind和nfs服务加入到配置为自动启动
```
[root@nfs postgres]# chkconfig --add nfs
[root@nfs postgres]# chkconfig --add rpcbind
[root@nfs postgres]# chkconfig nfs on
[root@nfs postgres]# chkconfig rpcbind on
```

### 2、mount nfs目录时一直停留在那里
```
[root@slavedb postgres]# mount -v -t nfs -o nosuid,noatime,noexec,nodev,rw,hard,intr,
rsize=32768,wsize=32768 192.168.0.231:/home/postgres/archive /home/postgres/archive
```

如果提示这样的错误mount.nfs: mount(2): No route to host，则是firewall限制连接了，可以先关闭iptables服务，如下所示
```
[root@nfs ~]# service iptables stop
```
下面命令设置iptables服务在开机时不自动重启
```
[root@nfs ~]# chkconfig --add iptables
[root@nfs ~]# chkconfig --iptables off
```
除了上面把防火墙关闭外，还可以把相应的port加入到firewall中，如下所示
```
使用rpcinfo命令查到nfs的port
[root@nfs ~]# rpcinfo -p | grep nfs
100003    2   tcp   2049  nfs

把port添加到iptables规则表中
[root@nfs ~]# vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 2049 -j ACCEPT
-A INPUT -m state --state NEW -m udp -p udp --dport 2049 -j ACCEPT
```

### 3、提示mount.nfs: mount(2): Permission denied

这个就是nfs共享目录不准许访问，使用chmod把共享目录的读取权限放开即可，如下所示
```
[root@nfs ~]# chmod 0755 /home/postgres -Rf
```
### 4、提示mount.nfs: xxxxxx is busy or already mounted

提示上面的错误，但df又没查到该目录已经mount,这时先执行umount，然后再执行mount
```
[root@slavedb postgres]# umount /home/postgres/archive/
[root@slavedb postgres]# mount -t nfs -o nosuid,noatime,noexec,nodev,rw,hard,intr,
rsize=32768,wsize=32768 192.168.0.231:/home/postgres/archive /home/postgres/archive
```

### 5、备节点上归档进程提示process   failed on 00000081000000010000005F

原因 “xlog没有及时归档，并且xlog文件数目超过设置的参数值”

```
2017-08-23 21:31:01.831 CST,,,1435,,599c90ea.59b,19,,
2017-08-23 04:15:38 CST,,0,LOG,00000,"archive command failed with exit code 1",
"The failed archive command was: DIR=/home/postgres/archive/`date +%F`; 
test ! -d $DIR && mkdir $DIR; test ! -f $DIR/0000000F0000000100000074 && 
cp pg_xlog/0000000F0000000100000074 $DIR/0000000F0000000100000074",,,,,,,,""
2017-08-23 21:31:01.831 CST,,,1435,,599c90ea.59b,20,,
2017-08-23 04:15:38 CST,,0,WARNING,01000,"archiving transaction log file 
""0000000F0000000100000074"" failed too many times, will try again later",,,,,,,,,""
```

如果nfs服务停止时间过长，xlog没有及时归档，并且xlog文件数目超过设置的参数值（与max_wal_size和wal_keep_segments参数有关），会导致archiver process   failed on 00000081000000010000005F 这样的错误，然后归档进程无法再正常运行，而且会造成归档不连续，造成先前做的基础备份无法再使用。所以xlog归档的状态需要监控。

解决方法这个问题的方法

1. 把nfs服务开启来，确保远程能正确访问，一般开启后，远程过一会就能恢复操作
2. 删除归档节点pg_xlog/archive_status目录所有的xxxxxxxx.ready并且pg_xlog目录下实体文件已经被清除的文件，再观察归档能否顺利执行
3. 重新做一份基础备份

原因 xlog归档一半，备机服务Crash

在生产机上遇到过xlog写入量为15M左右，竟然不是16MB，然后备节点不知什么原因崩掉，备机重启后日志一起提示
```
2017-08-23 21:40:04.065 CST,,,1435,,599c90ea.59b,23,,
2017-08-23 04:15:38 CST,,0,LOG,00000,"archive command failed with exit code 1",
"The failed archive command was: DIR=/home/postgres/archive/`date +%F`; test ! 
-d $DIR && mkdir $DIR; test ! -f $DIR/0000000F0000000100000080 &&
 cp pg_xlog/0000000F0000000100000080 $DIR/0000000F0000000100000080",,,,,,,,""
2017-08-23 21:40:04.065 CST,,,1435,,599c90ea.59b,24,,
2017-08-23 04:15:38 CST,,0,WARNING,01000,"archiving transaction log file 
""0000000F0000000100000080"" failed too many times, will try again later",,,,,,,,,""
```

但在备机的pg_xlog发现有0000000F0000000100000080文件，archive_status目录下也有0000000F0000000100000080.ready文件，最后发现归档的/home/postgres/archive/目录下竟然有一个15.xMb的xlog文件，导致复制失败

解决方法这个问题的方法

把归档/home/postgres/archive/目录下的0000000F0000000100000080文件删除掉即可解决问题

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

评论:1　　

顶: 1146　

踩: 1015　

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

1# __ xiaowu 回答于 2024-04-22 09:37:01+08

我的乐园作文350字:https://www.nanss.com/xuexi/3705.html 满分作文神仙开头:https://www.nanss.com/xuexi/3664.html 非主流女生个性签名:https://www.nanss.com/mingcheng/3758.html 独一无二的单字名字:https://www.nanss.com/mingcheng/3736.html 抖音上最火的下雨说说:https://www.nanss.com/wenan/3504.html 游戏名仙气古风唯美情侣:https://www.nanss.com/mingcheng/3562.html 男人好累:https://www.nanss.com/yuedu/3975.html 爱护花草树木的宣传标语:https://www.nanss.com/yulu/3249.html 帅气的网名:https://www.nanss.com/mingcheng/3719.html 一句话表达旅游愉快:https://www.nanss.com/yulu/3715.html 五四青年节演讲稿:https://www.nanss.com/xuexi/2647.html 军训小结:https://www.nanss.com/xuexi/3377.html 表扬信:https://www.nanss.com/gongzuo/2653.html 毕业典礼方案:https://www.nanss.com/xuexi/3421.html 办公室主任先进事迹:https://www.nanss.com/gongzuo/3557.html 期末个人总结:https://www.nanss.com/xuexi/3346.html 英语演讲稿:https://www.nanss.com/xuexi/3255.html 妩媚女网名:https://www.nanss.com/mingcheng/3812.html 搞笑段子短句:https://www.nanss.com/wenan/3732.html 高级讽刺人虚伪的句子:https://www.nanss.com/yulu/3817.html 表达爱孩子温馨句子:https://www.nanss.com/yulu/3976.html 一周工作总结:https://www.nanss.com/gongzuo/3370.html 请假条:https://www.nanss.com/shenghuo/3015.html 设计总结:https://www.nanss.com/gongzuo/3361.html 军训心得体会:https://www.nanss.com/xuexi/3259.html 夸人大全:https://www.nanss.com/shenghuo/3551.html 撒娇的话:https://www.nanss.com/yulu/3783.html 思想报告:https://www.nanss.com/xuexi/2618.html 关于爱情的哲理句子:https://www.nanss.com/yulu/3859.html 培训通知:https://www.nanss.com/gongzuo/3071.html