网站开发外包合同,确定网站设计公司简报,网站建设w亿玛酷1专注,上海频道网站建设OS 环境#xff1a;CentOS 6.2数据库 #xff1a;PostgreSQL 9.1.3pg_home/home/postgres/pg_data/database/pgdata/一、前期工作既要恢复#xff0c;肯定是需要一个备份基础的#xff0c;否则再怎么的巧妇也难为无米之炊。1.修改数据库参数#xff0c;修改postgresql.con…OS 环境CentOS 6.2数据库 PostgreSQL 9.1.3pg_home/home/postgres/pg_data/database/pgdata/一、前期工作既要恢复肯定是需要一个备份基础的否则再怎么的巧妇也难为无米之炊。1.修改数据库参数修改postgresql.confarchive_mode onarchive_timeout 300 --单位是秒此处以5分钟为限强制归档仅作测试archive_command cp -i %p /home/postgres/archive/%fwal_level archive修改完重启下reload,DB2.基于文件级别的持续备份a.基础备份postgres# select pg_start_backup(backup_2012_05_20_14:22:10);b.打包备份pg_data# cd /database# tar -cvzf pgdata.tar ./pgdatac.结束基础备份并切换归档postgres# select pg_stop_backup();postgres# select pg_switch_xlog();pg_switch_xlog----------------0/C000020(1 row)postgres# select pg_current_xlog_location();pg_current_xlog_location--------------------------0/C000020(1 row)postgres# create table test_1(id int,name varchar(50));postgres# insert into test_1 values (1,kenyon);INSERT 0 1此时在pg_data路径下会产生一个label,可以查看内容有checkpoint时间,基础备份的开始和结束时间以及标签名称等。因为之前已经设置了archive的三个参数可以在archive的备份路径pg_home/archive下看到归档的文件会定时传过来。二、恢复过程停数据库# pg_stop假定数据库的崩溃场景将pgdata数据删除# rm -rf /database/pgdata恢复之前备份的tar文件# tar xvf pgdata.tar删除pg_xlog文件夹并重建# rm -rf pg_xlog # mkdir -p pg_xlog/archive_status拷贝recovery.conf文件并修改# cp $PG_HOME/recovery.conf.sample /database/pgdata/# vi /database/pgdata/recovery.conf--新增内容指定恢复文件和路径%f,%p见上面说明restore_command cp /home/postgres/archive/%f %p启动数据库# pg_start[postgreslocalhost archive]$ psqlspsql (9.1.3)Type help for help.postgres# select * from test_1;id | name------------1 | kenyon(1 rows) --恢复成功会恢复到之前接收到的最后一个归档文件。另外recovery.conf会改名变成recovery.done日志内容:LOG: shutting downLOG: database system is shut downLOG: database system was interrupted; last known up at 2012-05-20 22:23:15 CSTLOG: starting archive recoveryLOG: restored log file 000000010000000000000002 from archiveLOG: redo starts at 0/8000078LOG: consistent recovery state reached at 0/C000000LOG: restored log file 000000010000000000000003 from archiveLOG: restored log file 000000010000000000000004 from archiveLOG: restored log file 000000010000000000000005 from archiveLOG: restored log file 000000010000000000000006 from archiveLOG: restored log file 000000010000000000000007 from archivecp: cannot stat /home/postgres/archive/000000010000000000000008: No such file or directoryLOG: could not open file pg_xlog/000000010000000000000008 (log file 0, segment 8): No such file or directoryLOG: redo done at 0/1C000078LOG: last completed transaction was at log time 2012-05-20 23:01:22.96059108LOG: restored log file 000000010000000000000007 from archivecp: cannot stat /home/postgres/archive/00000002.history: No such file or directoryLOG: selected new timeline ID: 2cp: cannot stat /home/postgres/archive/00000001.history: No such file or directoryLOG: archive recovery completeLOG: database system is ready to accept connectionsLOG: autovacuum launcher startedPS:若要恢复到指定时间还需要再recovery.conf中设置recovrey_target_time,recovery_target_timeline等参数总结pitr技术对于7*24小时支撑是至关重要的但是如果数据库非常小增大pg_dump备份的频率可能更方便但对于大数据库就需要了。
