postgresql同样支持在线备份,该备份方式与oracle中用户热备的方式相同,手动拷贝数据文库文件与归档日志。可以根据测试过程将备份直接写成script,通过定制,完成数据库的自动备份。
postgresql的恢复支持基于时间戳与事务ID,可以通过时间戳或事务ID的方式,完成数据库的不完全恢复或者因错误操作的故障恢复。
该测试目的：postgresql的在线备份;通过在线备份完成恢复。
1,开启归档
| [postgre@daduxiong~]$more/usr/local/pgsql/data/postgresql.conf|greparchive_ archive_mode=on#allowsarchivingtobedone archive_command='cp-i%p/archive/%f>/dev/null' |
2,重新启动数据库
| [root@daduxiong~]#servicepostgresqlstop StoppingPostgresql:serverstopped ok [root@daduxiong~]#servicepostgresqlstart StartingPostgresql:ok |
3,启动备份
5,完成备份 6,在postgres数据库中创建表并插入记录,作为恢复时的判断。
9,解压之前的备份文件压缩包
10,恢复data目录,重新创建pg_xlog目录及其子目录archive_status
11,配置恢复配置文件 13,验证恢复结果。检查之前创建的表与记录。 说明： 该测试中采用的是系统安装默认的数据,如果是生产库需要注意备份所有的表空间,不能仅仅备份软连接. 数据库完成备份后,recovery.conf文件变成recovery.done,backup_label文件变成了backup_lable.old. oracle的备份通常采用RMAN工具备份,该工具功能强大,使用方便,得到大家的认可。 开源项目组也为postgresql开发了一款类似的工具pg-rman,其功能也很强大,使用方式和特点与ORACLE的RMAN非常类似。
[postgre@daduxiongarchive]$psqlpostgres-c"selectpg_start_backup('hot_backup');"
pg_start_backup
-----------------
0/7000020
(1row)
[postgre@daduxiongarchive]$tar--exclude$PGDATA/pg_xlog-cvjpf/archive/pgbackup.tar.bz2$PGDATA
[postgre@daduxiongarchive]$psqlpostgres-c"selectpg_stop_backup();"pg_stop_backup----------------0/70133A0(1row)
[postgre@daduxiongarchive]$psqlpostgres
Welcometopsql8.3.10,thePostgresqlinteractiveterminal.
Type:\copyrightfordistributionterms
\hforhelpwithsqlcommands
\?forhelpwithpsqlcommands
\gorterminatewithsemicolontoexecutequery
\qtoquit
postgres=#createtableabc(idinteger);
CREATETABLE
postgres=#insertintoabcvalues(1);
INSERT01
postgres=#\q
[root@daduxiongpgsql]#servicepostgresqlstop
StoppingPostgresql:serverstopped
ok
[postgre@daduxiongarchive]$cp$PGDATA/pg_xlog/*00*/archive/
[root@daduxiongpgsql]#rm-rfdata
[postgre@daduxiongpgsql]$tar-xvf/archive/pgbackup.tar.bz2
....省略
/usr/local/pgsql/data/global/2843
/usr/local/pgsql/data/postmaster.opts
/usr/local/pgsql/data/pg_twophase/
/usr/local/pgsql/data/postmaster.pid
/usr/local/pgsql/data/backup_label
/usr/local/pgsql/data/PG_VERSION
[root@daduxiongpgsql]#mv/archive/usr/local/pgsql/data/usr/local/pgsql
[root@daduxiongdata]#mkdirpg_xlog
[root@daduxiongdata]#chmod0700pg_xlog/
[root@daduxiongdata]#chownpostgre:postgrepg_xlog/
[root@daduxiongdata]#cdpg_xlog/
[root@daduxiongpg_xlog]#mkdirarchive_status
[root@daduxiongpg_xlog]#chmod0700archive_status/
[root@daduxiongpg_xlog]#chownpostgre:postgrearchive_status/
[root@daduxiongpg_xlog]#mv/archive/*00*/usr/local/pgsql/data/pg_xlog
[root@daduxiongpg_xlog]#cd..
[root@daduxiongdata]#ls
backup_labelpg_clogpg_multixactpg_twophasepostgresql.conf
basepg_hba.confpg_subtransPG_VERSIONpostmaster.opts
globalpg_ident.confpg_tblspcpg_xlogpostmaster.pid
[root@daduxiongdata]#touchrecovery.conf
[root@daduxiongdata]#echo"restore_command='cp-i/archive/%f%p'">>recovery.conf
[root@daduxiongdata]#chownpostgre:postgrerecovery.conf
[root@daduxiongdata]#chmod0750recovery.conf
[root@daduxiongdata]#servicepostgresqlstart
StartingPostgresql:ok
---省略日志部分内容
LOG:selectednewtimelineID:3
LOG:restoredlogfile"00000002.history"fromarchive
LOG:archiverecoverycomplete
LOG:autovacuumlauncherstarted
LOG:databasesystemisreadytoacceptconnectionssqlinteractiveterminal.
Type:\copyrightfordistributionterms
\hforhelpwithsqlcommands
\?forhelpwithpsqlcommands
\gorterminatewithsemicolontoexecutequery
\qtoquit
postgres=#select*fromabc;
id
----
1
(1row)
postgres=#\q
[root@daduxiongdata]#ls-l
total80
-rw-------1postgrepostgre147Aug3110:26backup_label.old
drwx------6postgrepostgre4096Aug2711:33base
drwx------2postgrepostgre4096Aug3110:41global
drwx------2postgrepostgre4096Aug1011:06pg_clog
-rwx------1postgrepostgre3429Aug1011:10pg_hba.conf
-rwx------1postgrepostgre1460Aug1011:06pg_ident.conf
drwx------4postgrepostgre4096Aug1011:06pg_multixact
drwx------2postgrepostgre4096Aug1011:06pg_subtrans
drwx------2postgrepostgre4096Aug1011:06pg_tblspc
drwx------2postgrepostgre4096Aug1011:06pg_twophase
-rwx------1postgrepostgre4Aug1011:06PG_VERSION
drwx------3postgrepostgre4096Aug3110:35pg_xlog
-rwx------1postgrepostgre16727Aug3109:42postgresql.conf
-rwx------1postgrepostgre59Aug3110:35postmaster.opts
-rw-------1postgrepostgre47Aug3110:35postmaster.pid
-rwxr-x---1postgrepostgre39Aug3110:34recovery.done
