PostgreSQL PITR示例 (物理备份+WAL日志)

参考地址：http://blog.sciencenet.cn/home.php?mod=space&uid=419883&do=blog&id=537939

我将步骤整理如下：PITR：时间点恢复(Point In Time Recovery)

一、备份
1. 创建表，插入100W测试数据：
create table foo(id integer);
insert into foo values(generate_series(1,1000000));
2. 可以查看现在数据库的大小：
select oid from pg_dtabase where datname='postgres'; 返回 16384 ；
在base目录下找到该文件夹，进入16384文件夹，执行 du -sh查看大小。
或者可以执行
select pg_size_pretty(pg_database_size('postgres'));来查看大小
3. 设置postgresql.conf：添加
        wal_level = archive
        archive_mode = on
        archive_command = 'cp %p /home/postgres/archive/%f'
/home/postgres/archive/ 是postgresql归档的目录，当然你要手动创建该目录文件夹
其中archive_command中%p会自动识别为WAL目录，你不用管，%f你也不用管。这个archive_command在什么时候执行呢，即PostgreSQL在每次WAL日志16MB段满的时候才执行，即把其拷贝到/home/postgres/archive中，那么为了想在不满的时候也备份，怎么办？也即可采用Linux中定时任务的方式来实现。
开始添加crontab定时器

• ①、切换到postgres用户：su postgres
• ②、执行命令开始编写定时器：crontab -e，内容如下：

*/5 * * * * cp -f /usr/local/postgres-9.3.5/data/pg_xlog/[0-9]*  /home/postgres/archive/archive_bak2/
4. 好了，配置完，重启服务器.
5. 做一次基础备份：

• ①、建立存储存储基础备份的目录，mkdir /home/postgres/base.
• ②、执行 select pg_start_backup('bak20150206');
• ③、切换到root，把data压缩并存放到/home/postgres/base目录下(换一个命令行操作)

su root;
tar -czvf /home/postgres/base/base_data.tar.gz /usr/local/postgres-9.3.5/data

• ④、执行 select pg_stop_backup();
• ⑤、切换日志，执行 select pg_switck_xlog(); '可以多执行几次'

6. 备份成功，可以看/home/postgres/archive目录中是否有了备份的wal日志段了

二、恢复
1. 打开psql，往表中再插入100W条数据，使得总数据为200W：
insert into foo values(generate_series(1,1000000));
假定此时由于某种原因我们新的插入100万条记录的数据库出问题了，我们的疑问是可否利用前面的“基础备份库”+“新插入100万条记录所产生的WAL备份日志”恢复呢？
2. 关闭数据库。
3. 接着把data目录改名：

mv /usr/local/postgres-9.3.5/data /usr/local/postgres-9.3.5/data.bk

4. 把我们放在/home/postgres/base下的基础备份拷贝到/usr/local/postgres-9.3.5下，并解压：
cp /home/postgres/base/base_data.tar.gz /usr/local/postgres-9.3.5
tar -xzvf base_data.tar.gz
5. 进入解压得到的data目录，删除pg_xlog文件夹，创建pg_xlog/archive_status文件夹：
rm -rf pg_xlog
mkdir -p pg_xlog/archive_status
6. 删除data下的postmaster.pid文件：

rm -rf postmaster.pid
7. 从/usr/local/postgres-9.3.5/share目录下，拷贝一份recovery.conf：
cp /usr/local/postgres-9.3.5/share/recovery.conf.sample recovery.conf
8. 修改recovery.conf
vi recovery.conf： 添加： restore_command =  'cp /home/postgres/archive/%f %p'
9. 启动数据库。