PostgreSQL pg_basebackup做数据备份及恢复

之前有篇文章是讲通过pg_basebackup来搭建流复制环境的。今天正好因为审计要做恢复测试，而我们的备份都是通过basebackup来做的，所以这里讲下pg_basebackup做备份恢复的过程，与搭建流复制环境略有不同。

一，备份（每周一次，以日期来命名）

pg_basebackup -h 100.100.100.1 -D backup_date +%F -U repuser -Ft -z -Z 9 -P

备份文件是一个以日期为后缀的文件夹，如backup_2016-01-03

看下里面的内容：

[postgres@db-backup-16 aly]$ cd backup_2016-01-03
[postgres@db-backup-16 backup_2016-01-03]$ ll
total 136980
-rw-rw-r-- 1 postgres postgres  845670 Jan  3 11:10 131356.tar.gz
-rw-rw-r-- 1 postgres postgres     561 Jan  3 11:21 131358.tar.gz
-rw-rw-r-- 1 postgres postgres 70274228 Jan  3 11:21 16389.tar.gz
-rw-rw-r-- 1 postgres postgres  2243098 Jan  3 11:10 16390.tar.gz
-rw-rw-r-- 1 postgres postgres 60964910 Jan  3 11:15 16391.tar.gz
-rw-rw-r-- 1 postgres postgres  2525540 Jan  3 11:15 16392.tar.gz
-rw-rw-r-- 1 postgres postgres  3398196 Jan  3 11:22 base.tar.gz

跟表空间对应起来：

postgres=# select oid,spcname from pg_tablespace;
  oid  |       spcname        
--------+------------------------
   1663 | pg_default
   1664 | pg_global
  16389 | tbs_reading
  16390 | tbs_reading_idx
  16391 | tbs_reading_market
  16392 | tbs_reading_market_idx
 131356 | tbs_ad
 131358 | tbs_ad_idx

postgres=# \db
                       List of tablespaces
         Name         |  Owner  |           Location           
------------------------+----------+--------------------------------
 pg_default            | postgres |
 pg_global             | postgres |
 tbs_ad                | postgres | /opt/pg_tbs/ad
 tbs_ad_idx            | postgres | /opt/pg_tbs/ad_idx
 tbs_reading           | postgres | /opt/pg_tbs/reading
 tbs_reading_idx       | postgres | /opt/pg_tbs/reading_idx
 tbs_reading_market    | postgres | /opt/pg_tbs/reading_market
 tbs_reading_market_idx | postgres | /opt/pg_tbs/reading_market_idx

这样就很清楚了。数字分别对应表空间，base.tar.gz则对应$PGDATA.

二，恢复：

1，在恢复服务器上建好表空间对应的目录，如/opt/pg_tbs/ad，以及$PGDATA，如/opt/pgdata

2，把备份文件分别copy至对应目录下，

               cp base.tar.gz $PGDATA

               cp  131356.tar.gz /opt/pg_tbs/ad   等等

3，解压缩备份文件 tar -zxvf 131356.tar.gz 等等

4，pg_ctl start -D $PGDATA

结果报错：

2016-01-05 11:14:57.444 CST,,,2720,,568b3531.aa0,2,,2016-01-05 11:14:57 CST,,0,LOG,00000,"invalid checkpoint record",,,,,,,,"ReadChe
ckpointRecord, xlog.c:7576",""
2016-01-05 11:14:57.444 CST,,,2720,,568b3531.aa0,3,,2016-01-05 11:14:57 CST,,0,FATAL,XX000,"could not locate required checkpoint rec
ord",,"If you are not restoring from a backup, try removing the file ""/opt/pgdata/backup_label"".",,,,,,"StartupXLOG, xlog.c:6171",
""

去看下$PGDATA/backup_label文件的内容，如下：

START WAL LOCATION: 5/7C000060 (file 00000001000000050000001F)
CHECKPOINT LOCATION: 5/7C000098
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-01-03 11:12:13 CST
LABEL: pg_basebackup base backup

说明：这个库较小，且不繁忙，wal文件数量不多，且没有归档。所以去原来的库的pg_xlog找到00000001000000050000001F文件copy至恢复服务区的pg_xlog，再启动pg，就OK了。

.
.
.
.
.
来自
http://blog.sina.com.cn/s/blog_544a710b0102w54h.html