PostgreSQL/EDB PPAS PITR系统备份及恢复操作实例

scottsiu

源文连接：http://www.enterprisedb.org.cn/?action-viewthread-tid-13

EDB Postgres Plus PITR系统备份及恢复操作实例

字体: 小 中 大 | 打印 发表于: 2009-2-19 11:19    作者: scott.siu    来源: EnterpriseDB中文社区
一、PITR系统备份及恢复

1. 对数据库进行设置，修改/opt/PostgresPlus/8.3AS/data/postgresql.conf
找到：

CODE:
# archive_command = ''改为：

CODE:
archive_command = 'cp -f %p /opt/PostgresPlus/PitrWAL/%f'重新启动数据库：
# /etc/init.d/edb_8.3 restart


2. 向数据库写入记录

CODE:
create table pitrtest (event varchar(255));
insert into pitrtest values ('before backup 1');
--操作时间2009-02-10 16:07

insert into pitrtest values ('before backup 2');
--操作时间2009-02-10 16:08

insert into pitrtest values ('before backup 3');
--操作时间2009-02-10 16:093. 做检查点，打包主目录；写入记录，建表，改存储过程；做两组

CODE:
select pg_start_backup('pitrtest');
--操作时间2009-02-10 16:10

# tar cvf data20090211.tar data/

insert into pitrtest values ('before backup 4');
--操作时间2009-02-10 16:13

insert into pitrtest values ('before backup 5');
--操作时间2009-02-10 16:14

create table pitr_create_when_backup (event varchar(255));
--操作时间2009-02-10 16:15

CREATE OR REPLACE PROCEDURE public.emp_query1(p_deptno IN numeric, p_empno IN OUT numeric, p_ename IN OUT character varying, p_job OUT character varying, p_hiredate OUT timestamp without time zone, p_sal OUT numeric) AS
BEGIN
    SELECT empno, ename, job, hiredate, sal
        INTO p_empno, p_ename, p_job, p_hiredate, p_sal
        FROM emp
        WHERE deptno = p_deptno
          AND (empno = p_empno
           OR  ename = UPPER(p_ename));
END
--操作时间2009-02-10 16:16

select pg_stop_backup();
--2009-02-10 16:18在 pg_start_backup后对数据库所做的操作在/var/log/message中有显示，select pg_stop_backup后数据依然存在，在/opt/PostgresPlus/8.3AS/data/pg_xlog/中生成文件 000000010000000000000000.00ED5D68.backup，这应该是运行在线backup过程中数据库操作产生的记录数据


4. 写入记录，建表，改存储过程；做两组

CODE:
insert into pitrtest values ('before backup 6');
--操作时间2009-02-10 16:19

insert into pitrtest values ('before backup 7');
--操作时间2009-02-10 16:205. 数据恢复(指定时间点恢复)
$ cd /opt/PostgresPlus/8.3
$ rm data
$ tar xvf data20090211.tar
$ vim data/recovery.conf

CODE:
restore_command = 'cp /opt/PostgresPlus/MyWAL/%f %p'
recovery_target_time = '2009-02-10 16:15:00'# /etc/init.d/edb_8.3 start
# tail /var/log/message

CODE:
Feb 10 17:22:55 scottsiu postgres[27754]: [6-1] 2009-02-10 17:22:55 CST LOG:  restored log file "000000010000000000000000.00ED5D68.backup" from archive
Feb 10 17:22:55 scottsiu postgres[27754]: [7-1] 2009-02-10 17:22:55 CST LOG:  restored log file "000000010000000000000000" from archive
Feb 10 17:22:55 scottsiu postgres[27754]: [8-1] 2009-02-10 17:22:55 CST LOG:  checkpoint record is at 0/ED5D68
Feb 10 17:22:55 scottsiu postgres[27754]: [9-1] 2009-02-10 17:22:55 CST LOG:  redo record is at 0/ED5D68; undo record is at 0/0; shutdown FALSE
Feb 10 17:22:55 scottsiu postgres[27754]: [10-1] 2009-02-10 17:22:55 CST LOG:  next transaction ID: 0/1360; next OID: 24787
Feb 10 17:22:55 scottsiu postgres[27754]: [11-1] 2009-02-10 17:22:55 CST LOG:  next MultiXactId: 1; next MultiXactOffset: 0
Feb 10 17:22:55 scottsiu postgres[27754]: [12-1] 2009-02-10 17:22:55 CST LOG:  automatic recovery in progress
Feb 10 17:22:56 scottsiu postgres[27754]: [13-1] 2009-02-10 17:22:56 CST LOG:  redo starts at 0/ED5DB8
Feb 10 17:22:56 scottsiu postgres[27754]: [14-1] 2009-02-10 17:22:56 CST LOG:  recovery stopping before commit of transaction 1383, time 2009-02-10 16:14:03 CST
Feb 10 17:22:56 scottsiu postgres[27754]: [15-1] 2009-02-10 17:22:56 CST LOG:  redo done at 0/ED5F40
Feb 10 17:22:56 scottsiu postgres[27754]: [16-1] 2009-02-10 17:22:56 CST FATAL:  requested recovery stop point is before end time of backup dump
Feb 10 17:22:56 scottsiu postgres[27752]: [3-1] 2009-02-10 17:22:56 CST LOG:  startup process (PID 27754) exited with exit code 1
Feb 10 17:22:56 scottsiu postgres[27752]: [4-1] 2009-02-10 17:22:56 CST LOG:  aborting startup due to startup process failure
Feb 10 17:22:56 scottsiu postgres[27753]: [2-1] 2009-02-10 17:22:56 CST LOG:  logger shutting down(系统不能启动，由于要恢复的时间点在比backup dump的时间还要早)

重新修改要恢复的时间点
$ vim data/recovery.conf

CODE:
restore_command = 'cp /opt/PostgresPlus/MyWAL/%f %p'
recovery_target_time = '2009-02-10 16:19:00'# /etc/init.d/edb_8.3 start
# tail /var/log/message

CODE:
Feb 10 17:24:00 scottsiu postgres[27851]: [6-1] 2009-02-10 17:24:00 CST LOG:  restored log file "000000010000000000000000" from archive
Feb 10 17:24:00 scottsiu postgres[27851]: [7-1] 2009-02-10 17:24:00 CST LOG:  checkpoint record is at 0/ED5D68
Feb 10 17:24:00 scottsiu postgres[27851]: [8-1] 2009-02-10 17:24:00 CST LOG:  redo record is at 0/ED5D68; undo record is at 0/0; shutdown FALSE
Feb 10 17:24:00 scottsiu postgres[27851]: [9-1] 2009-02-10 17:24:00 CST LOG:  next transaction ID: 0/1360; next OID: 24787
Feb 10 17:24:00 scottsiu postgres[27851]: [10-1] 2009-02-10 17:24:00 CST LOG:  next MultiXactId: 1; next MultiXactOffset: 0
Feb 10 17:24:00 scottsiu postgres[27851]: [11-1] 2009-02-10 17:24:00 CST LOG:  automatic recovery in progress
Feb 10 17:24:00 scottsiu postgres[27851]: [12-1] 2009-02-10 17:24:00 CST LOG:  redo starts at 0/ED5DB8
Feb 10 17:24:01 scottsiu postgres[27851]: [13-1] 2009-02-10 17:24:01 CST LOG:  restored log file "000000010000000000000001" from archive
Feb 10 17:24:01 scottsiu postgres[27851]: [14-1] 2009-02-10 17:24:01 CST LOG:  recovery stopping before commit of transaction 1501, time 2009-02-10 16:19:52 CST
Feb 10 17:24:01 scottsiu postgres[27851]: [15-1] 2009-02-10 17:24:01 CST LOG:  redo done at 0/1000188
Feb 10 17:24:01 scottsiu postgres[27851]: [16-1] 2009-02-10 17:24:01 CST LOG:  selected new timeline ID: 2
Feb 10 17:24:01 scottsiu postgres[27851]: [17-1] 2009-02-10 17:24:01 CST LOG:  archive recovery complete
Feb 10 17:24:02 scottsiu postgres[27851]: [18-1] 2009-02-10 17:24:02 CST LOG:  database system is ready
Feb 10 17:24:02 scottsiu postgres[27891]: [3-1] 2009-02-10 17:24:02 CST LOG:  archived transaction log file "00000002.history"(系统正常起动恢复到2009-02-10 16:19:00前的数据)
$ cat data/recovery.done

CODE:
restore_command = 'cp /opt/PostgresPlus/MyWAL/%f %p'
recovery_target_time = '2009-02-10 16:19:00'二、WAL日志清理，当服务器中有文件损坏时才使用此命令，一定要小心，相关于fsck   

# su - enterprisedb
$ /opt/PostgresPlus/8.3AS/dbserver/bin/pg_resetxlog /opt/PostgresPlus/8.3AS/data/
pg_resetxlog: lock file "/opt/PostgresPlus/8.3AS/data//postmaster.pid" exists
Is a server running?  If not, delete the lock file and try again.

# /etc/init.d/edb_8.3 stop
$ /opt/PostgresPlus/8.3AS/dbserver/bin/pg_resetxlog /opt/PostgresPlus/8.3AS/data/
Transaction log reset

# /etc/init.d/edb_8.3 start

此操作不会清理通过PITR生成的“检查点”记录