PostgreSQL12数据库备份与恢复(IT民工)

PostgreSQL12数据库备份与恢复主要类型为“恢复到指定时间点、 恢复到指定还原点 和 恢复到指定事务。具体如下：
01-指定时间点

1. 主要参数配置
   archive_mode=on
   wal_level = 'replica'
   archive_mode = 'on'
   archive_command = 'cp %p /opt/archive5432/%f'
2. 数据插入
   create user repuser replication login connection limit 5 encrypted password'123456';
   create table t(id int,info text);
   insert into t values(1,'finish init '||now());
   insert into t values(2,'before backup 1 '||now());
3. 基础备份
   pg_basebackup -D /opt/data-pitr/ -Fp -Xs -v -P -h127.0.0.1 -p5432 -Urepuser

4. 模拟灾难操作
   insert into t values(3,'before delete '||now());
   select current_timestamp;

   current_timestamp

   2019-12-27 12:53:07.530293+08
   delete from t;

5. 恢复操作与测试
   $ pg_ctl -D /opt/data5432 stop
   $ rm -rf data5432
   $ cp -R data-pitr/ data5432
   $ vi data5432/postgresql.conf
   restore_command = 'cp /opt/archive5432/%f %p'
   recovery_target_time='2019-12-27 12:53:07.530293+08'
   $ vi data5432/recovery.signal --保存空文件即可
   $ pg_ctl -D /opt/data5432 start &
   2019-12-27 12:55:15.825 CST [2393] LOG: recovery has paused
   2019-12-27 12:55:15.825 CST [2393] HINT: Execute pg_wal_replay_resume() to continue.
   $ psql -p5432 -Upostgres
   -- 注意此时数据库进入read only 模式
   postgres=# delete from t;
   ERROR: cannot execute DELETE in a read-only transaction
   postgres=# select * from t;

   id	info

   
   
6. | finish init 2019-12-27 12:46:34.823462+08
7. | before backup 1 2019-12-27 12:46:35.631821+08

8. | before delete 2019-12-27 12:47:07.919464+08
   (3 rows)
   postgres=# select pg_wal_replay_resume();

   pg_wal_replay_resume

   (1 row)
   postgres=# delete from t where id=2;
   DELETE 1
   postgres=# select * from t;

   id	info

   
   
9. | finish init 2019-12-27 12:46:34.823462+08
10. | before delete 2019-12-27 12:47:07.919464+08
    (2 rows)
11. 注意事项
    注意查看恢复过程数据库日志，确认数据后执行函数pg_wal_replay_resume()，否则数据库将一直处于read-only 状态。
    02-指定还原点
12. 主要参数配置
    archive_mode=on
    wal_level = 'replica'
    archive_mode = 'on'
    archive_command = 'cp %p /opt/archive5432/%f'
13. 数据插入
    create user repuser replication login connection limit 5 encrypted password'123456';
    create table t2(id int,info text);
    insert into t2 values(1,'data1 '||now());
    insert into t2 values(2,'data2 '||now());
    insert into t2 values(3,'data3 '||now());
14. 基础备份
    pg_basebackup -D /opt/data-pitr/ -Fp -Xs -v -P -h127.0.0.1 -p5432 -Urepuser

15. 创建还原点
    select pg_create_restore_point('my_res_point1');
    --数据变更
    insert into t2 values(4,'data4 '||now());
    update t2 set info=null where id=2;
    select * from t2 order by id;

    id	info

    
    
16. | data1 2019-12-27 13:37:23.986668+08
17. |
18. | data3 2019-12-27 13:37:24.834763+08
19. | data4 2019-12-27 13:39:16.07917+08
    (4 rows)

20. 恢复到还原点“my_res_point1”
    $ pg_ctl -D /opt/data5432 stop
    $ rm -rf data5432
    $ cp -R data-pitr/ data5432
    $ vi data5432/postgresql.conf
    restore_command = 'cp /opt/archive5432/%f %p'
    recovery_target_name='my_res_point1'
    $ vi data5432/recovery.signal --保存空文件即可
    $ pg_ctl -D /opt/data5432 start &
    2019-12-27 13:42:00.626 CST [2554] LOG: recovery stopping at restore point "my_res_point1", time 2019-12-27 13:39:01.703618+08
    2019-12-27 13:42:00.626 CST [2554] LOG: recovery has paused
    2019-12-27 13:42:00.626 CST [2554] HINT: Execute pg_wal_replay_resume() to continue.
    $ psql -p5432 -Upostgres
    -- 注意此时数据库进入read only 模式
    postgres=# select * from t2;

    id	info

    
    
21. | data1 2019-12-27 13:37:23.986668+08
22. | data2 2019-12-27 13:37:23.997032+08

23. | data3 2019-12-27 13:37:24.834763+08
    (3 rows)
    postgres=# select pg_wal_replay_resume();

    pg_wal_replay_resume

    (1 row)
    03-指定事务

24. 主要参数配置
    archive_mode=on
    wal_level = 'replica'
    archive_mode = 'on'
    archive_command = 'cp %p /opt/archive5432/%f'
25. 数据插入
    create user repuser replication login connection limit 5 encrypted password'123456';
    create table t3(id int,info text);
    insert into t3 values(1,'data1 '||now());
    insert into t3 values(3,'data2 '||now());
    insert into t3 values(5,'data3 '||now());

26. 基础备份
    pg_basebackup -D /opt/data-pitr/ -Fp -Xs -v -P -h127.0.0.1 -p5432 -Urepuser
    4.开启事务删数据
    postgres=# begin;
    BEGIN
    postgres=# select txid_current();

    txid_current

    492
    (1 row)
    postgres=# delete from t3 where id=3;
    DELETE 1
    postgres=# end;
    COMMIT
    postgres=# select * from t3;

    id	info

    
    
27. | data1 2019-12-27 14:17:11.442802+08
28. | data3 2019-12-27 14:17:12.330043+08
    (2 rows)

29. 恢复到事务的xid=491(前面的492-1)
    $ pg_ctl -D /opt/data5432 stop
    $ rm -rf data5432
    $ cp -R data-pitr/ data5432
    $ vi data5432/postgresql.conf
    restore_command = 'cp /opt/archive5432/%f %p'
    recovery_target_xid=491
    $ vi data5432/recovery.signal --保存空文件即可
    $ pg_ctl -D /opt/data5432 start &
    2019-12-27 14:21:47.748 CST [2879] LOG: recovery stopping after abort of transaction 491, time 2019-12-27 14:18:51.63361+08
    2019-12-27 14:21:47.748 CST [2879] LOG: recovery has paused
    2019-12-27 14:21:47.748 CST [2879] HINT: Execute pg_wal_replay_resume() to continue.
    $ psql -p5432 -Upostgres
    -- 注意此时数据库进入read only模式
    postgres=# select * from t3;

    id	info

    
    
30. | data1 2019-12-27 14:17:11.442802+08
31. | data2 2019-12-27 14:17:11.450694+08

32. | data3 2019-12-27 14:17:12.330043+08
    (3 rows)
    postgres=# select pg_wal_replay_resume();

    pg_wal_replay_resume

    (1 row)