PostgreSQL 基于日志的备份与还原

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

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

备份（操作均使用系统账号postgres完成 ）

postgres@debian:~$ psql
psql (9.5.0)

Type "help" for help.

postgres=# CREATEDATABASE test;

CREATEDATABASE

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'

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/"2016-01-1809:30:42 CST [2937-2] LOG:  received fast shutdown request

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

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

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

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

 done

server stopped

server starting

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

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

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

2016-01-1809:30:45 CST [2976-1] LOG:  autovacuum launcher started

postgres@debian:~$ psql

psql (9.5.0)

Type "help" for help.

postgres=# \c test

You are now connected todatabase "test" asuser "postgres".

test=# CREATETABLE testPITR1 ASSELECT*FROM pg_class, pg_description;

SELECT1192063

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 todatabase "test" asuser "postgres".

test=# CREATETABLE testPITR2 ASSELECT*FROM pg_class, pg_description;

SELECT1203562

test=#  select*fromcurrent_timestamp;

              now             

-------------------------------2016-01-1810:02:15.229335+08

(1 row)

 

test=# CREATETABLE testPITR3 ASSELECT*FROM pg_class, pg_description;

SELECT1215061

test=#  select*fromcurrent_timestamp;

              now             

-------------------------------2016-01-1810:02:51.029447+08

(1 row)

test=# select pg_switch_xlog();

 pg_switch_xlog

----------------0/3DDE6750

(1 row)

恢复

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

postgres@debian:~$ /usr/lib/postgresql/9.5/bin/pg_ctl stop -D /var/lib/postgresql/9.5/main/

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

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

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

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

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

 done

server stopped

postgres@debian:~$ mv9.5/main 9.5/main.old

postgres@debian:~$cd /var/lib/postgresql/9.5/

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

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

2016-01-1810:26:40 CST [3342-2]LOG:  redo starts at 0/170000982016-01-1810:26:40 CST [3342-3]LOG:  invalid record length at 0/170093482016-01-1810:26:40 CST [3342-4]LOG:  redo done at 0/170092D8

2016-01-1810:26:40 CST [3342-5]LOG:  last completed transaction was at log time 2016-01-1809:48:26.585085+082016-01-1810:26:40 CST [3342-6]LOG:  MultiXact member wraparound protections are now enabled

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

2016-01-1810: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 todatabase "test" asuser "postgres".

test=# \d

           List of relations

 Schema|   Name    | Type  |  Owner  

--------+-----------+-------+----------public| testpitr1 |table| postgres

(1 row)

vi 

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/" -l /var/lib/postgresql/

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

2016-01-1811:22:39 CST [1743-44] LOG:  recovery stopping before commit of transaction 630, time2016-01-1810:02:46.080443+08

postgres@debian:~$ psql

psql (9.5.0)

Type "help" for help.

 

postgres=# \c test

You are now connected todatabase "test" asuser "postgres".

test=# \dt

           List of relations

 Schema|   Name    | Type  |  Owner  

--------+-----------+-------+----------public| testpitr1 |table| postgres

 public| testpitr2 |table| postgres

(2 rows)

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