Postgresql在线备份和恢复

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.执行基础备份，并添加测试数据

-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文件不会自动备份，需要手工备份。

[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

[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