PostgreSQL基于日志的备份和恢复

wal，意思是规范日志，是日志记录的标准实现方法。简而言之，它记录数据库更改，然后更新特定的新数据。到磁盘。 PostgreSQL 将此日志存储在数据文件夹下的 pg_xlog 子文件夹中。如果数据库崩溃，可以通过重放日志中的“操作”来恢复数据库。换句话说，如果你有主备份和完整的日志文件，理论上你可以将数据库恢复到主备份后的任意时间点。这些日志不仅会在另一个实例中不断“重播”，而且您还拥有一个完整的在线备份，即“复制”。

pg_xlog下的日志文件不会无限增长，这意味着无需担心由于日志数量的增加而导致磁盘空间拉伸。默认情况下，每个日志文件的大小为16M，也就是说，如果增长到16M，就会切换到另一个文件，并再次使用之前的文件。因此，为了保证完整的日志链，必须将整个文件复制并保存到特定的文件夹中。对于最后一个小于16M的日志文件，可以手动切换。备份 创建测试表

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

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 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)

恢复

关闭数据库，模拟数据库宕机。目前，数据库测试应该有三张表，其中一张可以在主备份之前，即数据文件恢复之后检索，而另外两张表则必须通过相应的日志文件来恢复。模拟恢复到创建 testPITR2 的时间点。

1. 关闭数据库服务，重命名data文件夹。

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

1. 解压备份数据文件，启动服务，确保常规备份前只有testpitr1表

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)

1. 停止数据库，删除data文件夹并再次解压缩主备份。创建、恢复此刻数据库testpitr2

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

1. 确认，进入数据库发现testpitr2已恢复

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)

如果需要恢复表3，则需要再次删除data文件夹，创建。