PostgreSQL 基于日志的备份与还原

2. 修改配置文件，开启日志备份，将写满的文件复制到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‘

3. 创建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

4. 创建测试表

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

5. 创建基础备份

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

2. 解压备份数据文件，开启服务，验证此时只有基础备份前的表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)

3. 停掉数据库，删除数据文件夹，重新解压基础备份，创建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

4. 验证，进入数据库，发现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。

PostgreSQL 基于日志的备份与还原

标签：