PostgreSQL 基于日志的备份与还原

参考:http://www.postgresql.org/docs/9.5/static/continuous-archiving.html

http://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/

 

wal,即预写式日志,是日志的标准实现方式,简单而言就是将对数据库的变动记录到日志 中,而后在将具体的新数据刷新到磁盘。PostgreSQL将该日志维护在数据文件夹下的子文件夹pg_xlog中。当数据库崩溃后,可以通过“重放”日志中的“动作”,将数据库恢复。也就是说,只要拥有一个基础备份和完整的日志文件,理论上可以将数据库库恢复到任意基础备份以来的任意时刻点。不仅如此,如果在另一个实例上将这些日志不停的“重放”,那么就拥有了一个完整的在线备份,也就是“复制”。

pg_xlog下日志文件不会无限制增多,也就是说并不用担心日志的增多使得磁盘空间捉襟见肘。默认每个日志文件为16M大小,即当增长到16M时,就会切换到别的文件,并复用之前的文件 。因此,为了保证有个完整的日志链,必须将写满的文件复制保存到一个特定的文件 夹。对于最后一个还未满16M的日志文件,可以手动触发一次切换。

 

备份(操作均使用系统账号postgres完成 )

  1. 登录数据库创建测试数据库
postgres@debian:~$ psql
psql (9.5.0)

Type "help" for help.

postgres=# CREATE DATABASE test;

CREATE DATABASE

 

  1. 修改配置文件,开启日志备份,将写满的文件复制到archive文件夹下
vim /etc/postgresql/9.5/main/postgresql.conf
wal_level = archive

archive_mode = on

archive_command = ' test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f'

 

  1. 创建archive文件夹,并重启数据库服务
postgres@debian:~$ mkdir archive

postgres@debian:~$ /usr/lib/postgresql/9.5/bin/pg_ctl restart -D /var/lib/postgresql/9.5/main/ -o "-c config_file=/etc/postgresql/9.5/main/postgresql.conf"

2016-01-18 09:30:42 CST [2937-2] LOG:  received fast shutdown request

2016-01-18 09:30:42 CST [2937-3] LOG:  aborting any active transactions

2016-01-18 09:30:42 CST [2942-2] LOG:  autovacuum launcher shutting down

2016-01-18 09:30:42 CST [2939-1] LOG:  shutting down

waiting for server to shut down.....2016-01-18 09:30:44 CST [2939-2] LOG:  database system is shut down

 done

server stopped

server starting

postgres@debian:~$ 2016-01-18 09:30:45 CST [2972-1] LOG:  database system was shut down at 2016-01-18 09:30:44 CST

2016-01-18 09:30:45 CST [2972-2] LOG:  MultiXact member wraparound protections are now enabled

2016-01-18 09:30:45 CST [2971-1] LOG:  database system is ready to accept connections

2016-01-18 09:30:45 CST [2976-1] LOG:  autovacuum launcher started
  1. 创建测试表
postgres@debian:~$ psql

psql (9.5.0)

Type "help" for help.

postgres=# \c test

You are now connected to database "test" as user "postgres".

test=# CREATE TABLE testPITR1 AS SELECT * FROM pg_class, pg_description;

SELECT 1192063
  1. 创建基础备份
psql -c "SELECT pg_start_backup('base', true)"

cd /var/lib/postgresql/9.5/

tar -cvf main.tar main

psql -c "SELECT pg_stop_backup()"

  6. 继续创建测试表,切换日志

postgres@debian:~$ psql

psql (9.5.0)

Type "help" for help.

 

postgres=# \c test

You are now connected to database "test" as user "postgres".

test=# CREATE TABLE testPITR2 AS SELECT * FROM pg_class, pg_description;

SELECT 1203562

test=#  select * from current_timestamp;

              now             

-------------------------------

 2016-01-18 10:02:15.229335+08

(1 row)

 

test=# CREATE TABLE testPITR3 AS SELECT * FROM pg_class, pg_description;

SELECT 1215061

test=#  select * from current_timestamp;

              now             

-------------------------------

 2016-01-18 10:02:51.029447+08

(1 row)

test=# select pg_switch_xlog();

 pg_switch_xlog

----------------

 0/3DDE6750

(1 row)

恢复

关闭数据库,模拟数据库宕机,此时,数据库test中应该有3张表,其中1张表在基础备份前,也就是恢复完数据文件即可找回,而另2张表则需恢复相应的日志文件。模拟恢复到testPITR2创建时刻点。

  1. 关闭数据库服务,重命名数据文件夹
postgres@debian:~$ /usr/lib/postgresql/9.5/bin/pg_ctl stop -D /var/lib/postgresql/9.5/main/

2016-01-18 10:06:12 CST [2971-2] LOG:  received fast shutdown request

2016-01-18 10:06:12 CST [2971-3] LOG:  aborting any active transactions

2016-01-18 10:06:12 CST [2976-2] LOG:  autovacuum launcher shutting down

2016-01-18 10:06:12 CST [2973-1] LOG:  shutting down

waiting for server to shut down.....2016-01-18 10:06:13 CST [2973-2] LOG:  database system is shut down

 done

server stopped

postgres@debian:~$ mv 9.5/main 9.5/main.old
  1. 解压备份数据文件,开启服务,验证此时只有基础备份前的表testpitr1
postgres@debian:~$cd /var/lib/postgresql/9.5/

postgres@debian:~/9.5$ tar -xvf 9.5/main.tar

postgres@debian:~$ 2016-01-18 10:26:40 CST [3342-1] LOG:  database system was interrupted; last known up at 2016-01-18 09:54:56 CST

2016-01-18 10:26:40 CST [3342-2] LOG:  redo starts at 0/17000098

2016-01-18 10:26:40 CST [3342-3] LOG:  invalid record length at 0/17009348

2016-01-18 10:26:40 CST [3342-4] LOG:  redo done at 0/170092D8

2016-01-18 10:26:40 CST [3342-5] LOG:  last completed transaction was at log time 2016-01-18 09:48:26.585085+08

2016-01-18 10:26:40 CST [3342-6] LOG:  MultiXact member wraparound protections are now enabled

2016-01-18 10:26:40 CST [3341-1] LOG:  database system is ready to accept connections

2016-01-18 10:26:40 CST [3346-1] LOG:  autovacuum launcher started

 

postgres@debian:~$ psql

psql (9.5.0)

Type "help" for help.

 

postgres=# \c test

You are now connected to database "test" as user "postgres".

test=# \d

           List of relations

 Schema |   Name    | Type  |  Owner  

--------+-----------+-------+----------

 public | testpitr1 | table | postgres

(1 row)
  1. 停掉数据库,删除数据文件夹,重新解压基础备份,创建recovery.conf,将数据库恢复到testpitr2时刻
vi recovery.conf

restore_command = 'cp /var/lib/postgresql/archive/%f %p'

recovery_target_time = '2016-01-18 10:02:15'

postgres@debian:~/9.5/main$ /usr/lib/postgresql/9.5/bin/pg_ctl start -D /var/lib/postgresql/9.5/main/ -o "-c config_file=/etc/postgresql/9.5/main/postgresql.conf" -l /var/lib/postgresql/recovery.log

可以在恢复日志中看到这么一句话:

2016-01-18 11:22:39 CST [1743-44] LOG:  recovery stopping before commit of transaction 630, time 2016-01-18 10:02:46.080443+08
  1. 验证,进入数据库,发现testpitr2已经恢复
postgres@debian:~$ psql

psql (9.5.0)

Type "help" for help.

 

postgres=# \c test

You are now connected to database "test" as user "postgres".

test=# \dt

           List of relations

 Schema |   Name    | Type  |  Owner  

--------+-----------+-------+----------

 public | testpitr1 | table | postgres

 public | testpitr2 | table | postgres

(2 rows)

 

如果需要恢复table3,则必须再次删除数据文件夹,建立recovery.conf。

posted @ 2016-01-18 19:30  cxy486  阅读(3936)  评论(0编辑  收藏  举报