2007年3月15日星期四

Postgreaql PITR 技术

<转贴>--<<PostgreSQL的热备和恢复>>
 
为什么要写这篇文章？
因为我看了一下，国内所有的PostgreSQL教程都没有很详细的介绍该功能，而相反，国内的Oracle
文章对这块非常的看重。虽然，PostgreSQL的官方文档有一个章节是介绍这块内容的，
但是写得太过'文学'化。
的确，一个数据库的可靠性和完整性是非常重要的，否则，很难叫人们所接受它。

本文假设读者对PostgreSQL已经有基本的认识，如果你对PostgreSQL还完全不熟悉的话，建议你先
去http://www.postgresql.org看看它的Documentation.


作为最强大的开源数据库，PostgreSQL拥有一切商业数据库所拥有的功能，甚至比商业数据库更好。
在以前的版本中，它在数据恢复，可靠性方面做的不太好，但经过最近几年的发展，已经可以和Oracle
媲美了。


在PostgreSQL7的时候就引入了WAL（Write Ahead Logging）的概念，即预写日志，所有对数据库的更改，
在更改之前必须写到该LOG中，这样，就算机器断电，PostgreSQL也可以从该LOG中知道数据库在断电前做
了什么操作，已经做到第几步了，这样保证了所有事务的完整性，但PostgreSQL7没有提供很好的灾难恢复
机制，一旦数据库崩溃，除非你曾经对数据库作过pg_dump或者file system level backup,否则，你的数据
将全部丢失，并且，就算你曾经对数据库做过备份，也只能恢复到你备份的那一刻的数据，这对一个生产数据库
（特别是24*7生产库）来说，是无法容忍的。

PostgreSQL8的推出，使PostgreSQL的稳定性和可靠性又迈出了划时代的一步。
除了提供对tablespace的支持外，PostgreSQL8提供了支持时间点的恢复---PITR.
其基本原理和Oracle的热备份完全一样：

首先，对数据库在file system level做一个backup(PostgreSQL是首先用pg_start_backup('label')命令，
然后用tar直接tar整个data目录，假设命名为base.tar,然后pg_stop_backup();结束热备。
Oracle首先是用alter tablespace xxx begin backup,然后直接cp数据文件);

然后，备份相关的配置文件(PostgreSQL只需备份postgresql.conf,pg_hba.conf,pg_ident.conf就可以了，其实，
前面的tar已经将这些文件备份了，Oracle需要alter database backup control file......)；

最后，备份WAL(
可以设置postgresql.conf中的archive_command，
该命令可以让PostgreSQL8自动将需要的归档的日志文件备份的其他地方中。
但是注意：如果你是让PostgreSQL8调用archive_command来备份WAL的话，
可能根本就做不到PITR，我做过实验，如果依靠base.tar和archive_command产生的WAL其实只能恢复到最后一个
archive_command保存的WAL的数据，pg_xlog/下面可能还有数据，如果PostgreSQL8的数据目录彻底损坏的话，还是会
丢失数据，所以，我建议，在写数据备份脚本的时候，最好将pg_xlog/下面的WAL也一起备份，见下面的cpArch.sh。
)。

如果数据库崩溃，我们就可以使用热备产生的base.tar和archive_command产生的WAL和我们自己备份的WAL(pg_xlog)来进行数据库的
recovery.

下面举例来说明：
我的PostgreSQL运行在：/home/pgsql/下面
数据目录在：/home/pgsql/database/
将热备数据文件备份到/disk3/PostgreSQL/base/下面
将WAL备份到/disk3/PostgreSQL/archives/下面

postgresql.conf中定义了如下的archive_command:
archive_command = 'cp -f %p /disk3/PostgreSQL/archives/%f'
该命令会将PostgreSQL产生的WAL cp到/disk3/PostgreSQL/archives/中。

我的热备脚本如下：
(1)为了使丢失的数据在一分钟之内，在crontab中每分钟将pg_xlog/下面的WAL
backup到/disk3/PostgreSQL/archives/。

crontab:
*/1 * * * * /home/pgsql/bin/cpArch.sh

cpArch.sh:
#!/bin/sh

cp -f /home/pgsql/database/pg_xlog/[0-9]* /disk3/PostgreSQL/archives/


(2)编写热备脚本hotBackup.pl(我用perl)：
#!/usr/bin/perl

#############################################################
# hotBackup.pl
# Use to hot backup the PostgreSQL database.
# Author:Seamus Dean
# Date:2005-04-11
##############################################################

my($datadir) ="/home/pgsql/database";
my($bindir) ="/home/pgsql/bin";
my($backupdir) ="/disk3/PostgreSQL/base";
my($receiver) ="ljh13\@sina.com.cn";

sub begin_backup()
{
open(PSQL,"|$bindir/psql") or mail_user("begin backup error.") && exit(100);
print PSQL "select pg_start_backup('backupnow');\n";
close(PSQL);

}

sub end_backup()
{
open(PSQL,"|$bindir/psql") or mail_user("end backup error.") && exit(100);
print PSQL "select pg_end_backup();\n";
close(PSQL);
}


sub do_backup()
{
system("/bin/tar cvf base.tar $datadir");
system("/bin/mv -f base.tar $backupdir/");
}


sub mail_user()
{
my($msg) =@_;
open(MAIL,"|/bin/mail -s backup-result $receiver") or die("can not talk to:mail command.\n");
print MAIL $msg;
close(MAIL);
}

###################################
# tell psql begin our backup
###################################
&begin_backup();

###################################
# do tar
###################################
&do_backup();

####################################
# tell psql end backup
####################################
&end_backup();

####################################
# mail the user about the result
####################################
&mail_user("PostgreSQL backup successfully.");


到这里，备份脚本基本上就完了，你可以将hotBackup.pl放在crontab中周期性的执行。

就算/home/pgsql/database目录彻底崩溃，我们可以像下面这样迅速恢复到1分钟内的数据：
#cp /disk3/PostgreSQL/base/base.tar ./
#tar xvf base.tar
#cd database/
#vi recovery.conf
输入如下内容：
restore_command='cp /disk3/PostgreSQL/archives/%f "%p"'
然后将/home/pgsql/database/pg_xlog/下面的WAL清空。
启动PostgreSQL,我们可以看到如下的LOG信息：
LOG: could not create IPv6 socket: Address family not supported by protocol
LOG: database system was interrupted at 2005-04-11 23:13:28 PDT
LOG: starting archive recovery
LOG: restore_command = "cp /disk3/PostgreSQL/archives/%f "%p""
cp: cannot stat `/disk3/PostgreSQL/archives/00000001.history': No such file or directory
LOG: restored log file "00000001000000000000002E.008EFCAC.backup" from archive
LOG: restored log file "00000001000000000000002E" from archive
LOG: checkpoint record is at 0/2E8EFCAC
LOG: redo record is at 0/2E8EFCAC; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 5271; next OID: 6351357
LOG: automatic recovery in progress
LOG: redo starts at 0/2E8EFCE8
LOG: restored log file "00000001000000000000002F" from archive
LOG: restored log file "000000010000000000000030" from archive
LOG: restored log file "000000010000000000000031" from archive
LOG: restored log file "000000010000000000000032" from archive
LOG: restored log file "000000010000000000000033" from archive
LOG: restored log file "000000010000000000000034" from archive
LOG: restored log file "000000010000000000000035" from archive
LOG: restored log file "000000010000000000000036" from archive
LOG: restored log file "000000010000000000000037" from archive
LOG: restored log file "000000010000000000000038" from archive
LOG: restored log file "000000010000000000000039" from archive
LOG: restored log file "00000001000000000000003A" from archive
LOG: restored log file "00000001000000000000003B" from archive
LOG: restored log file "00000001000000000000003C" from archive
LOG: restored log file "00000001000000000000003D" from archive
LOG: restored log file "00000001000000000000003E" from archive
LOG: restored log file "00000001000000000000003F" from archive
LOG: restored log file "000000010000000000000040" from archive
LOG: restored log file "000000010000000000000041" from archive
LOG: restored log file "000000010000000000000042" from archive
LOG: restored log file "000000010000000000000043" from archive
LOG: restored log file "000000010000000000000044" from archive
LOG: restored log file "000000010000000000000045" from archive
LOG: restored log file "000000010000000000000046" from archive
LOG: restored log file "000000010000000000000047" from archive
LOG: restored log file "000000010000000000000048" from archive
LOG: restored log file "000000010000000000000049" from archive
LOG: restored log file "00000001000000000000004A" from archive
LOG: restored log file "00000001000000000000004B" from archive
LOG: restored log file "00000001000000000000004C" from archive
LOG: record with zero length at 0/4C2BABE4
LOG: redo done at 0/4C2BABA8
LOG: restored log file "00000001000000000000004C" from archive
LOG: archive recovery complete
LOG: database system is ready


显示数据已经成功恢复。
/home/pgsql/database/下面的recovery.conf会变为：recovery.done.

结论：
PostgreSQL8的PITR已经做得非常的成功，完全有可能替代Oracle,Sqlserver
而成为企业的首选。所以，我们玩PostgreSQL的兄弟们，一定要对它有信心！
 Your sincere,
朱恒
2007-03-15
发帖者 时间：

2 条评论:

匿名 说...

PITR在8.1中的说明见:http://www.pgsqldb.org/pgsqldoc-8.1c/backup-online.html

2007年3月15日 01:39:00
匿名 说...

Data:2007-03-19
Postgresql verison:8.1.3
OS: CentOS server 4.4

在postgresql.conf中确保 WAL 归档打开并且可以运转。
我的archive_command = 'cp -i %p /var/tmp/ %f'
[postgres@www data]$ psql
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# SELECT pg_start_backup('label');
pg_start_backup
-----------------
0/33A750
(1 row)

postgres=# \q
[postgres@www data]$ ls
backup_label global pg_clog pg_ident.conf pg_subtrans pg_twophase pg_xlog postmaster.opts
base logfile pg_hba.conf pg_multixact pg_tblspc PG_VERSION postgresql.conf postmaster.pid
[postgres@www data]$ cat backup_label
START WAL LOCATION: 0/33A750 (file 000000010000000000000000)
CHECKPOINT LOCATION: 0/33A750
START TIME: 2007-03-18 18:20:28 CST
LABEL: label
[postgres@www data]$ cd ..
[postgres@www pgsql]$ tar -cf data_0319.tar data ----------须存放到安全的地方(scp to other place)
[postgres@www pgsql]$ ls
bin data data_0319.tar doc include lib logfile man mig_verican share
[postgres@www pgsql]$ echo ""> logfile
[postgres@www pgsql]$ psql
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# SELECT pg_stop_backup();
pg_stop_backup
----------------
0/33A794
(1 row)

postgres=# \q
[postgres@www pgsql]$ cd data
[postgres@www data]$ ls
base logfile pg_hba.conf pg_multixact pg_tblspc PG_VERSION postgresql.conf postmaster.pid
global pg_clog pg_ident.conf pg_subtrans pg_twophase pg_xlog postmaster.opts
[postgres@www data]$ cd pg_xlog/
[postgres@www pg_xlog]$ ll
total 16412
-rw------- 1 postgres postgres 16777216 Mar 18 18:20 000000010000000000000000
-rw------- 1 postgres postgres 235 Mar 18 18:21 000000010000000000000000.0033A750.backup
drwx------ 2 postgres postgres 4096 Mar 18 18:21 archive_status
[postgres@www pg_xlog]$ date
Sun Mar 18 18:22:55 CST 2007
[postgres@www pg_xlog]$ ls /var/tmp/
000000010000000000000000.0033A750.backup
[postgres@www pg_xlog]$ ls
000000010000000000000000 000000010000000000000000.0033A750.backup archive_status
[postgres@www pg_xlog]$ cd ..
然后我恢复了一个约150M大小的库.因为新安装的postgresql只有一个默认库,不利于测试.
[postgres@www data]$ createdb verican6
CREATE DATABASE
[postgres@www data]$ psql -f /usr/local/backup/verican6_2007-03-18.dump verican6
SET
SET
SET
COMMENT
CREATE LANGUAGE
SET
CREATE TYPE
ALTER TYPE
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
.
.
.
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT

现在查看/data/pg_xlog以及/var/tmp/ 下的日志归档情况
[postgres@www data]$ ll pg_xlog/
total 131240
-rw------- 1 postgres postgres 235 Mar 18 18:21 000000010000000000000000.0033A750.backup
-rw------- 1 postgres postgres 16777216 Mar 18 18:28 000000010000000000000015
-rw------- 1 postgres postgres 16777216 Mar 18 18:28 000000010000000000000016
-rw------- 1 postgres postgres 16777216 Mar 18 18:29 000000010000000000000017
-rw------- 1 postgres postgres 16777216 Mar 18 18:29 000000010000000000000018
-rw------- 1 postgres postgres 16777216 Mar 18 18:29 000000010000000000000019
-rw------- 1 postgres postgres 16777216 Mar 18 18:29 00000001000000000000001A
-rw------- 1 postgres postgres 16777216 Mar 18 18:28 00000001000000000000001B
-rw------- 1 postgres postgres 16777216 Mar 18 18:27 00000001000000000000001C
drwx------ 2 postgres postgres 4096 Mar 18 18:29 archive_status
[postgres@www data]$ ll -l pg_xlog/archive_status/
total 0
-rw------- 1 postgres postgres 0 Mar 18 18:21 000000010000000000000000.0033A750.backup.done
-rw------- 1 postgres postgres 0 Mar 18 18:28 000000010000000000000015.done
-rw------- 1 postgres postgres 0 Mar 18 18:28 000000010000000000000016.done
-rw------- 1 postgres postgres 0 Mar 18 18:29 000000010000000000000017.done
-rw------- 1 postgres postgres 0 Mar 18 18:29 000000010000000000000018.done
-rw------- 1 postgres postgres 0 Mar 18 18:29 000000010000000000000019.done
[postgres@www data]$ ls -lrt /var/tmp/
total 426508
-rw------- 1 postgres postgres 235 Mar 18 18:21 000000010000000000000000.0033A750.backup
-rw------- 1 postgres postgres 16777216 Mar 18 18:24 000000010000000000000000
-rw------- 1 postgres postgres 16777216 Mar 18 18:25 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Mar 18 18:25 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Mar 18 18:25 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Mar 18 18:25 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Mar 18 18:25 000000010000000000000005
-rw------- 1 postgres postgres 16777216 Mar 18 18:25 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Mar 18 18:25 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Mar 18 18:25 000000010000000000000008
-rw------- 1 postgres postgres 16777216 Mar 18 18:25 000000010000000000000009
-rw------- 1 postgres postgres 16777216 Mar 18 18:26 00000001000000000000000A
-rw------- 1 postgres postgres 16777216 Mar 18 18:26 00000001000000000000000B
-rw------- 1 postgres postgres 16777216 Mar 18 18:26 00000001000000000000000C
-rw------- 1 postgres postgres 16777216 Mar 18 18:26 00000001000000000000000D
-rw------- 1 postgres postgres 16777216 Mar 18 18:26 00000001000000000000000E
-rw------- 1 postgres postgres 16777216 Mar 18 18:26 00000001000000000000000F
-rw------- 1 postgres postgres 16777216 Mar 18 18:26 000000010000000000000010
-rw------- 1 postgres postgres 16777216 Mar 18 18:27 000000010000000000000011
-rw------- 1 postgres postgres 16777216 Mar 18 18:27 000000010000000000000012
-rw------- 1 postgres postgres 16777216 Mar 18 18:27 000000010000000000000013
-rw------- 1 postgres postgres 16777216 Mar 18 18:28 000000010000000000000014
-rw------- 1 postgres postgres 16777216 Mar 18 18:28 000000010000000000000015
-rw------- 1 postgres postgres 16777216 Mar 18 18:28 000000010000000000000016
-rw------- 1 postgres postgres 16777216 Mar 18 18:29 000000010000000000000017
-rw------- 1 postgres postgres 16777216 Mar 18 18:29 000000010000000000000018
-rw------- 1 postgres postgres 16777216 Mar 18 18:29 000000010000000000000019
[postgres@www data]$ du -sh /var/tmp/
417M /var/tmp/
[postgres@www data]$ du -sh pg_xlog/
129M pg_xlog/
[postgres@www data]$ du -sh
542M .
好了.最糟糕的事情发生了.假如磁盘损坏或者.../pgsql/data数据群集被损坏
我这里模拟删除/data目录
[postgres@www pgsql]$ pwd
/usr/local/pgsql
[postgres@www pgsql]$ ls
bin data doc include lib logfile man mig_verican share
[postgres@www pgsql]$ rm data -rf
[postgres@www pgsql]$ ls
bin data_bak doc include lib logfile man mig_verican share
这时.需要把other place 的data.tar scp 回来
[postgres@www pgsql]$ ls
bin data_0319.tar doc include lib logfile man mig_verican share
[postgres@www pgsql]$ tar -xf data_0319.tar
[postgres@www pgsql]$ ls
bin data data_0319.tar doc include lib logfile man mig_verican share
停掉postmaster进程,准备恢复数据库到删除时.由于我没用及时备份../pgsql/data/pg_xlog/下的最新文件(新于archive目录的日志),有可能丢失极少的数据
[postgres@www pgsql]$ ps -eaf|grep postmaster
postgres 5370 1 0 18:17 pts/0 00:00:00 /usr/local/pgsql/bin/postmaster -D data
postgres 5486 5123 0 19:02 pts/0 00:00:00 grep postmaster
[postgres@www pgsql]$ kill -9 5370
[postgres@www pgsql]$ netstat -ln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:864 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:2401 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:10000 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:21 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:631 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN
tcp 0 0 :::7009 :::* LISTEN
tcp 0 0 :::7777 :::* LISTEN
tcp 0 0 ::ffff:127.0.0.1:8005 :::* LISTEN
tcp 0 0 :::80 :::* LISTEN
tcp 0 0 :::8080 :::* LISTEN
tcp 0 0 :::7443 :::* LISTEN
tcp 0 0 :::22 :::* LISTEN
tcp 0 0 :::443 :::* LISTEN
udp 0 0 0.0.0.0:10000 0.0.0.0:*
udp 0 0 0.0.0.0:858 0.0.0.0:*
udp 0 0 0.0.0.0:861 0.0.0.0:*
udp 0 0 0.0.0.0:111 0.0.0.0:*
udp 0 0 0.0.0.0:631 0.0.0.0:*
Active UNIX domain sockets (only servers)
Proto RefCnt Flags Type State I-Node Path
unix 2 [ ACC ] STREAM LISTENING 421605 /tmp/ssh-PwKgrP5166/agent.5166
unix 2 [ ACC ] STREAM LISTENING 4869 /tmp/.font-unix/fs7100
unix 2 [ ACC ] STREAM LISTENING 4537 /var/run/acpid.socket
unix 2 [ ACC ] STREAM LISTENING 421322 /tmp/ssh-qBjXlY5074/agent.5074
unix 2 [ ACC ] STREAM LISTENING 4816 /dev/gpmctl
unix 2 [ ACC ] STREAM LISTENING 4921 /var/run/dbus/system_bus_socket
unix 2 [ ACC ] STREAM LISTENING 366019 /tmp/mysql.sock
****5432端口已不再监听了.
好,[postgres@www pgsql]$ pwd
/usr/local/pgsql
[postgres@www pgsql]$ cd data
[postgres@www pgsql]$ vi recovery.conf
restore_command = 'cp /var/tmp/%f %p'
Save it
你可能还需要临时修改 pg_hba.conf 以避免普通用户连接，直到你确信恢复已经正常了为止。
[postgres@www pgsql]$vi pg_hba.con
*&^%$略
启动 postmaster。postmaster 将进入恢复模式并且继续读取它需要的归档的 WAL 文件。
在恢复过程完成后，postmaster 将把 recovery.conf 改名为 recovery.done
（以避免不小心因后面的崩溃再次进入恢复模式）然后开始正常的数据库操作。
[postgres@www pgsql]$ postmaster --version
postmaster (PostgreSQL) 8.1.3
[postgres@www pgsql]$ postmaster -D /usr/local/pgsql/data
LOG: database system was interrupted at 2007-03-18 18:20:28 CST
LOG: starting archive recovery
LOG: restore_command = "cp /var/tmp/%f %p"
cp: cannot stat `/var/tmp/00000001.history': No such file or directory
LOG: restored log file "000000010000000000000000.0033A750.backup" from archive
LOG: restored log file "000000010000000000000000" from archive
LOG: checkpoint record is at 0/33A750
LOG: redo record is at 0/33A750; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 582; next OID: 10794
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: automatic recovery in progress
LOG: redo starts at 0/33A794
LOG: restored log file "000000010000000000000001" from archive
LOG: restored log file "000000010000000000000002" from archive
LOG: restored log file "000000010000000000000003" from archive
LOG: restored log file "000000010000000000000004" from archive
LOG: restored log file "000000010000000000000005" from archive
LOG: restored log file "000000010000000000000006" from archive
LOG: restored log file "000000010000000000000007" from archive
LOG: restored log file "000000010000000000000008" from archive
LOG: restored log file "000000010000000000000009" from archive
LOG: restored log file "00000001000000000000000A" from archive
LOG: restored log file "00000001000000000000000B" from archive
LOG: restored log file "00000001000000000000000C" from archive
LOG: restored log file "00000001000000000000000D" from archive
LOG: restored log file "00000001000000000000000E" from archive
LOG: restored log file "00000001000000000000000F" from archive
LOG: restored log file "000000010000000000000010" from archive
LOG: restored log file "000000010000000000000011" from archive
LOG: restored log file "000000010000000000000012" from archive
LOG: restored log file "000000010000000000000013" from archive
LOG: restored log file "000000010000000000000014" from archive
LOG: restored log file "000000010000000000000015" from archive
LOG: restored log file "000000010000000000000016" from archive
LOG: restored log file "000000010000000000000017" from archive
LOG: restored log file "000000010000000000000018" from archive
LOG: restored log file "000000010000000000000019" from archive
cp: cannot stat `/var/tmp/00000001000000000000001A': No such file or directory
LOG: could not open file "pg_xlog/00000001000000000000001A" (log file 0, segment 26): No such file or directory
LOG: redo done at 0/19FFC9F8
LOG: restored log file "000000010000000000000019" from archive
LOG: archive recovery complete
LOG: database system is ready
LOG: transaction ID wrap limit is 2147484146, limited by database "postgres"
嗯.checked数据库,恢复得很好
.cp: cannot stat `/var/tmp/00000001000000000000001A': No such file or directory
说明丢失了删除时../pg_xlog/下的最新日志文件00000001000000000000001A.所以../data/pg_xlog是非常重要的.
好了,我的数据库verican6已经可以正常使用了.由于我在恢复数据库后没有任何操作,所以00000001000000000000001A中并不包括
我对数据库的任何操作.

2007年3月18日 21:23:00

发表评论

订阅： 博文评论 (Atom)