1.实验环境
OS： RedHat Linux Enterprisedb 6.3
DB： postgresql 9.3
PGHOME: /opt/PostgreSQL/9.3
PGDATA: /opt/PostgreSQL/9.3/data
归档目录：/opt/pg_archive
基础备份目录：/opt/base_archive
--生产环境中归档和数据等目录应该放在不同的分区，或者存储上。
2.修改归档配置，添加测试数据
- 修改pg配置文件postgresql.conf中以下条目
wal_level = archive
archive_mode = on
archive_command = 'cp -i %p /opt/pg_archive/%f </dev/null'
- 建立归档目录，并重启数据库，使归档生效
[root@rhela /]# mkdir -p /opt/pg_archive/
[root@rhela /]# chown -R postgres:postgres /opt/pg_archive/
[root@rhela /]# chmod -R 700 /opt/pg_archive/
[root@rhela /]# mkdir -p /opt/base_archive
[root@rhela /]# chown -R postgres:postgres /opt/base_archive
[root@rhela /]# chmod -R 700 /opt/base_archive
[root@rhela opt]# su - postgres
-bash-4.1$ pg_ctl restart
- 创建一个测试表
-bash-4.1$ psql -d test
psql.bin (9.3.6)
Type "help" for help.
test=# create table time (ctime time);
CREATE TABLE
test=# insert into time values(current_time);
INSERT 0 1
test=# select * from time;
ctime
-----------------
11:40:26.229787
(1 row)
test=# \q
3.执行基础备份，并添加测试数据
- 备份data目录，并压缩
-bash-4.1$ psql postgres -c "select pg_start_backup('hot_backup');"
pg_start_backup
-----------------
0/A000028
(1 row)
-bash-4.1$ tar --exclude $PGDATA/pg_xlog -cvjpf /opt/base_archive/pgbackup.tar.bz2 $PGDATA
tar: Removing leading `/' from member names
/opt/PostgreSQL/9.3/data/
/opt/PostgreSQL/9.3/data/pg_clo
......
-bash-4.1$ psql postgres -c "select pg_stop_backup();"
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
----------------
0/A0000B8
(1 row)
--pg_start_backup会执行一个checkpoint的检查点，可能需要消耗一段时间
--pg_stop_backup会终止备份模式，并自动切换到下一个WAL文件，直到最后的WAL段文件被归档后pg_stop_backup命令才会返回。
- 继续添加测试数据
-bash-4.1$ psql -d test
psql.bin (9.3.6)
Type "help" for help. test=# insert into time values (current_time);
INSERT 0 1
test=# insert into time values (current_time);
INSERT 0 1
test=# select * from time;
ctime
-----------------
11:40:26.229787
11:49:56.836129
11:52:04.235879
(3 rows) test=# \q
- 执行一次手动归档
-bash-4.1$ psql
psql.bin (9.3.6)
Type "help" for help. postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/B000368
(1 row)
postgres=# \q
-bash-4.1$ ls -alt /opt/pg_archive/
total 196628
-rw-------. 1 postgres postgres 16777216 May 5 11:54 00000002000000000000000B
drwx------. 2 postgres postgres 4096 May 5 11:54 .
-rw-------. 1 postgres postgres 294 May 5 11:48 00000002000000000000000A.00000028.backup
-rw-------. 1 postgres postgres 16777216 May 5 11:48 00000002000000000000000A
-rw-------. 1 postgres postgres 16777216 May 5 11:45 000000020000000000000009
-rw-------. 1 postgres postgres 16777216 May 5 10:30 000000020000000000000008
drwxr-xr-x. 7 root root 4096 May 4 12:01 ..
4.模拟数据库崩溃
[root@rhela pg_archive]# ps -ef |grep postgres
postgres 8112 1 0 10:30 pts/4 00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres 8113 8112 0 10:30 ? 00:00:00 postgres: logger process
postgres 8115 8112 0 10:30 ? 00:00:00 postgres: checkpointer process
postgres 8116 8112 0 10:30 ? 00:00:00 postgres: writer process
postgres 8117 8112 0 10:30 ? 00:00:00 postgres: wal writer process
postgres 8118 8112 0 10:30 ? 00:00:00 postgres: autovacuum launcher process
postgres 8119 8112 0 10:30 ? 00:00:00 postgres: archiver process last was 00000002000000000000000B
postgres 8120 8112 0 10:30 ? 00:00:00 postgres: stats collector process
root 9161 9034 0 14:02 pts/3 00:00:00 grep postgres
[root@rhela pg_archive]# kill -9 8112
[root@rhela pg_archive]# ps -ef |grep postgres
root 9174 9034 0 14:06 pts/3 00:00:00 grep postgres
5.数据库恢复到指定时间 11:50:00
- 移除数据目录
[root@rhela pg_archive]# cd /opt/PostgreSQL/9.3/
[root@rhela 9.3]# mv data data_bak
--一般为了安全考虑，存储空间足够时，会把事故发生时的文件做一个备份，而不是直接删除掉。具体到这里，整个data目录和归档目录最好不要删除。
- 复制基础备份到指定的位置，并检查属主和权限
[root@rhela 9.3]# cd /opt/base_archive/
[root@rhela base_archive]# ls
pgbackup.tar.bz2
[root@rhela base_archive]# tar -xvf pgbackup.tar.bz2
......
[root@rhela base_archive]# cd opt/PostgreSQL/9.3/
[root@rhela 9.3]# ls
data
[root@rhela 9.3]# cp -r data/ /opt/PostgreSQL/9.3/
[root@rhela 9.3]# cd /opt/PostgreSQL/9.3/
[root@rhela 9.3]# ls -al |grep data
drwx------. 15 root root 4096 May 5 14:15 data
drwx------. 16 postgres postgres 4096 May 5 11:48 data_bak
[root@rhela 9.3]# chown -R postgres:postgres data
[root@rhela 9.3]# chmod -R 700 data
--如果复制的数据中有软连接（如建立了表空间等情况），要确认软连接是否备份完整，恢复的时候也要注意处理。
--备份的时候，postgresql.conf、 pg_hba.conf 和 pg_ident.conf文件不会自动备份，需要手工备份。
- 在data目录中创建pg_xlog目录
[root@rhela data]# mkdir -p /opt/PostgreSQL/9.3/data/pg_xlog
[root@rhela data]# chown -R postgres:postgres /opt/PostgreSQL/9.3/data/pg_xlog
[root@rhela data]# chmod -R 700 /opt/PostgreSQL/9.3/data/pg_xlog
- 在pg_xlog目录中创建子目录
[root@rhela data]# mkdir -p /opt/PostgreSQL/9.3/data/pg_xlog/archive_status
[root@rhela data]# chown -R postgres:postgres /opt/PostgreSQL/9.3/data/pg_xlog/archive_status/
[root@rhela data]# chmod -R 700 /opt/PostgreSQL/9.3/data/pg_xlog/archive_status/
- 在$PGDATA目录中，创建恢复配置文件recovery.conf
[root@rhela data]# cd /opt/PostgreSQL/9.3/data
[root@rhela data]# touch recovery.conf
[root@rhela data]# echo "restore_command='cp -i /opt/pg_archive/%f %p'" >>recovery.conf
[root@rhela data]# echo "recovery_target_time = '2015-05-05 11:50:00'" >>recovery.conf
[root@rhela data]# cat recovery.conf
restore_command='cp -i /opt/pg_archive/%f %p'
recovery_target_time = '2015-05-05 11:50:00'
[root@rhela data]# chown postgres:postgres recovery.conf
[root@rhela data]# chmod 700 recovery.conf
--恢复完成后，recovery.conf会被自动重命名为recovery.done
--如果不指定恢复时间点的话，默认恢复到接收到的最后一个归档文件。
6.检查数据恢复情况
- 启动数据库服务
[root@rhela data]# service postgresql-9.3 start
Starting PostgreSQL 9.3:
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... done
server started
PostgreSQL 9.3 started successfully
- 连接进入数据库查看是否恢复到对应的时间点
[root@rhela archive_status]# su - postgres
-bash-4.1$ psql -d test
psql.bin (9.3.6)
Type "help" for help. test=# select * from time;
ctime
-----------------
11:40:26.229787
11:49:56.836129
(2 rows) test=# \q
-bash-4.1$
可以看到该表已经成功恢复到指定的时间点。
参考文档：https://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%BA%8C%E5%8D%81%E5%9B%9B%E7%AB%A0