Postgresql归档及恢复

作者：PostgreSQL中国社区---阿弟

联系: 4893310 / 4893310@qq.com

欢迎大家踊跃投稿，投稿信箱：press@postgres.cn

一、服务器架构图

1、基于主节点归档服务器架构图

2、基于备节点归档服务器架构图

二、部署环境介绍

1、nfs服务器

2、PostgreSQL主节点

3、PostgreSQL备节点

4、PostgreSQL恢复节点

三、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 listenaddresses = '*' port = 9610 wallevel = replica maxwalsenders = 3 #视自己业务需要调整 walkeepsegments = 8000 #视自己业务需要调整 hotstandby = on logdestination = 'csvlog' loggingcollector = on logdirectory = 'pglog' ##下面配置记录所有日志，对io的要求非常的高，线上如果开启记录所有日志， ----最好有独立的io设备来保存，否则对业务影响比较大，如果你有其它地方记录了日志，建议这里只收集必要的 ----日志 logcheckpoints = on logconnections = on logdisconnections = on logduration = on loglineprefix = '%m %a %r %d %u %p %x' logmindurationstatement = 0 logstatement = 'all' logtimezone = '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/pgctl 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 "pglog". [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/pgbasebackup -h 192.168.0.232 -U postgres -p 9610 -D /home/postgres/data9.6.1 Password: NOTICE: pgstop_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 archivemode = always archivecommand = '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 standbymode = 'on' primaryconninfo = 'host=192.168.0.232 port=9610 user=postgres password=pgsql' recoverytargettimeline = '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 archivecleanupcommand = '/usr/local/pgsql9.6.1/bin/pgarchivecleanup /home/postgres/data9.6.1/pgxlog %r' standbymode = 'on' restorecommand = 'cp /home/postgres/archive/20[1-9][0-9]-[0-1][0-9]-[0-3][0-9]/%f %p;' recoverytargettime = '2017-06-05 12:50:00+08' recoverytargetinclusive = 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/pgctl 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 "pglog".

[postgres@recovery data9.6.1]$ cd /home/postgres/data9.6.1/pglog [postgres@recovery pglog]$ ll -rt 总用量 68 -rw-------. 1 postgres postgres 1267 6月 5 13:15 postgresql-2017-06-05131551.csv -rw-------. 1 postgres postgres 557 6月 5 13:15 postgresql-2017-06-05131551.log [postgres@recovery pglog]$ cat postgresql-2017-06-05131551.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 pgswitchxlog();

pgswitchxlog

0/8000620 (1 row)

postgres=#

###2、配置恢复参数

恢复到删除数据前的时间点，但不包含该时间点

[root@recovery data9.6.1]# vim /home/postgres/data9.6.1/recovery.conf archivecleanupcommand='/usr/local/pgsql9.6.1/bin/pgarchivecleanup /home/postgres/data9.6.1/pgxlog %r' standbymode = 'on' restorecommand = 'cp /home/postgres/archive/20[1-9][0-9]-[0-1][0-9]-[0-3][0-9]/%f %p;' recoverytargettime = '2017-06-05 13:27:55.52218+08' recoverytargetinclusive = false

### 3、检验恢复结果

[postgres@recovery data9.6.1]$ /usr/local/pgsql9.6.1/bin/pgctl 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 "pglog".

[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 pgswitchxlog();

pgswitchxlog

0/90006B0 (1 row)

postgres=#

### 2、配置恢复参数
恢复到删除数据前的事务xid，包含该事务xid

[postgres@recovery data9.6.1]$ vim /home/postgres/data9.6.1/recovery.conf archivecleanupcommand='/usr/local/pgsql9.6.1/bin/pgarchivecleanup /home/postgres/data9.6.1/pgxlog %r' standbymode = 'on' restorecommand = 'cp /home/postgres/archive/20[1-9][0-9]-[0-1][0-9]-[0-3][0-9]/%f %p;' recoverytargetxid = 1688 recoverytargetinclusive = true [postgres@recovery data9.6.1]$

### 3、检验恢复结果

[postgres@recovery data9.6.1]$ /usr/local/pgsql9.6.1/bin/pgctl 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 "pglog".

[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

loglineprefix = '%m %a %r %d %u %p %x' logminduration_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/pglog/ [postgres@masterdb pglog]$ ll -rt 总用量 80 -rw-------. 1 postgres postgres 172 6月 5 14:57 postgresql-2017-06-05145727.log -rw-------. 1 postgres postgres 4229 6月 5 14:59 postgresql-2017-06-05145727.csv [postgres@masterdb pglog]$ cat postgresql-2017-06-05145727.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 archivecleanupcommand='/usr/local/pgsql9.6.1/bin/pgarchivecleanup /home/postgres/data9.6.1/pgxlog %r' standbymode = 'on' restorecommand = 'cp /home/postgres/archive/20[1-9][0-9]-[0-1][0-9]-[0-3][0-9]/%f %p;' recoverytargetinclusive = false recoverytargettime = '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 pgrelationfilepath(16384);

pgrelationfilepath

base/13275/16384 (1 row)

### 4、根据物理文件名使用pg_xlogdump查询xlog日志获取事务ID号

[postgres@recovery 2017-06-05]$ /usr/local/pgsql9.6.1/bin/pgxlogdump -b /home/postgres/archive/2017-06-05/00000001000000000000000E rmgr: Standby len (rec/tot): 24/ 50, tx: 0, lsn: 0/0E000028, prev 0/0D0002C0, desc: RUNNINGXACTS nextXid 1693 latestCompletedXid 1692 oldestRunningXid 1693 rmgr: Standby len (rec/tot): 24/ 50, tx: 0, lsn: 0/0E000060, prev 0/0E000028, desc: RUNNINGXACTS nextXid 1693 latestCompletedXid 1692 oldestRunningXid 1693 rmgr: XLOG len (rec/tot): 80/ 106, tx: 0, lsn: 0/0E000098, prev 0/0E000060, desc: CHECKPOINTONLINE 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 "archivecleanupcommand = '/usr/local/pgsql9.6.1/bin/pgarchivecleanup /home/postgres/data9.6.1/pgxlog %r'" > $RECOVERYPATH echo "standbymode = 'on'" >> $RECOVERYPATH echo "restorecommand = 'cp /home/postgres/archive/20[1-9][0-9]-[0-1][0-9]-[0-3][0-9]/%f %p;'" >> $RECOVERYPATH echo "recoverytargettime = '$td'" >> $RECOVERYPATH

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/archiverestart.sh >> /home/postgres/shell/archiverestart.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 xlogup(id integer not null primary key,f1 integer not null, f2 integer not null); CREATE TABLE postgres=# create index xlogupf1idx on xlogup using btree(f1); CREATE INDEX postgres=# create index xlogupf2idx on xlogup using btree(f2); CREATE INDEX postgres=# insert into xlogup(id,f1,f2) select t,t,t from generateseries(1,1000000) as t; INSERT 0 1000000 postgres=# vacuum analyze xlogup ; VACUUM postgres=#

建立pgbench测试脚本

[postgres@master shell]$ cat xlogbench.sql \set id random(1,1000000) update xlogup set f1=:id where id=:id

postgresql.conf参数配置

把checkpoint的条件设置大一些

checkpointtimeout = 180min # range 30s-1d maxwal_size = 4GB

autovacuum = on
logautovacuumminduration = 0 autovacuummaxworkers = 4 autovacuumnaptime = 1s autovacuumvacuumthreshold = 50 autovacuumanalyzethreshold = 50 autovacuumvacuumscalefactor = 0.002 autovacuumanalyzescalefactor = 0.001 autovacuumvacuumcostdelay = 0ms autovacuumvacuumcostlimit = -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 pgcurrentxlog_location();'

pgcurrentxlog_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 pgcurrentxlog_location();'

pgcurrentxlog_location

1/6DE09870 (1 row)

使用pg_xlogdump统计不同类型的WAL记录的数量，大小以及FPI的比例

[postgres@master bin]$ /usr/local/pgsql9.6.1/bin/pgxlogdump --stats=record -p /home/postgres/data9.6.1/pgxlog -s 1/66EAEBB8 -e 1/6DE09870 ````

第二次pgbench后wal统计结果

第三次pgbench后wal统计结果

从上面的测试结果来看，fpi导致wal写入放大达到10倍以上，所以适当的调整checkpointtimeout 和maxwal_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

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

修改fillfactor 前后测试数据对比

大约会减少27%的记录量

压缩WAL

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

wal_compression = off pgbench后wal统计结果

wal_compression = on pgbench后wal统计结果

开启wal_compression 前后测试数据对比

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

4、优化WAL的副作用

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

延长checkpoint时间间隔

导致crash恢复时间变长。crash恢复时需要回放的WAL日志量一般小于maxwalsize的一半，WAL回放速度(walcompression=on时)一般是50MB/s~150MB/s之间。可以根据可容忍的最大crash恢复时间，估算出允许的maxwal_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文件数目超过设置的参数值（与maxwalsize和walkeepsegments参数有关），会导致archiver process failed on 00000081000000010000005F 这样的错误，然后归档进程无法再正常运行，而且会造成归档不连续，造成先前做的基础备份无法再使用。所以xlog归档的状态需要监控。

解决方法这个问题的方法

1. 把nfs服务开启来，确保远程能正确访问，一般开启后，远程过一会就能恢复操作
2. 删除归档节点pgxlog/archivestatus目录所有的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",,,,,,,,,""

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

解决方法这个问题的方法

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