postgresql 使用pg_rman恢復還原資料庫
1、備份準備工作
--注意pg_rman需要一個備份目錄
[postgres@webtest bin]$ export BACKUP_PATH=/u01/backup
注意，這個可以直接編輯在.bash_profile檔案裡面
--建立並初始化目錄
[postgres@webtest bin]$ mkdir -p /u01/backup
[postgres@webtest bin]$ ./pg_rman init -B /u01/backup
2、postgresql備份
--首先進行一次全備份
[postgres@webtest bin]$ ./pg_rman backup --backup-mode=full
INFO: copying database files
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
INFO: copying archived WAL files
INFO: backup complete
HINT: Please execute 'pg_rman validate' to verify the files are correctly copied.
--檢查備份情況
[postgres@webtest bin]$ ./pg_rman show
==========================================================
StartTime Mode Duration Size TLI Status
==========================================================
2014-07-24 15:38:35 FULL 0m 1603MB 1 DONE
2014-07-22 17:10:23 FULL 0m 93MB 1 OK
可以看到，已經存在一次備份，剛才備份的沒有進行validate，所以是DONE狀態的。
--進行一次增量備份
[postgres@webtest bin]$ ./pg_rman backup --backup-mode=incremental
INFO: copying database files
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
INFO: copying archived WAL files
INFO: backup complete
HINT: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@webtest bin]$ ./pg_rman show
==========================================================
StartTime Mode Duration Size TLI Status
==========================================================
2014-07-24 15:51:11 INCR 0m 1610MB 1 DONE
2014-07-24 15:38:35 FULL 0m 1603MB 1 DONE
2014-07-22 17:10:23 FULL 0m 93MB 1 OK
--對備份進行驗證
[postgres@webtest bin]$ ./pg_rman validate
INFO: validate: "2014-07-24 15:38:35" backup and archive log files by CRC
INFO: backup "2014-07-24 15:38:35" is valid
INFO: validate: "2014-07-24 15:51:11" backup and archive log files by CRC
INFO: backup "2014-07-24 15:51:11" is valid
[postgres@webtest bin]$ ./pg_rman show
==========================================================
StartTime Mode Duration Size TLI Status
==========================================================
2014-07-24 15:51:11 INCR 0m 1610MB 1 OK
2014-07-24 15:38:35 FULL 0m 1603MB 1 OK
2014-07-22 17:10:23 FULL 0m 93MB 1 OK
3、登入資料庫刪除資料
[postgres@webtest bin]$ psql mytest
psql (9.4.1)
Type "help" for help.
mytest=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------+----------+----------
public | child_t01 | table | postgres
public | child_t02 | table | postgres
public | child_t03 | table | postgres
public | child_t04 | table | postgres
public | child_t05 | table | postgres
public | child_t06 | table | postgres
public | parent | table | postgres
public | parent_dpart_id_seq | sequence | postgres
public | parent_person_id_seq | sequence | postgres
(9 rows)
mytest=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | child_t01 | table | postgres
public | child_t02 | table | postgres
public | child_t03 | table | postgres
public | child_t04 | table | postgres
public | child_t05 | table | postgres
public | child_t06 | table | postgres
public | parent | table | postgres
public | test_t | table | postgres
(8 rows)
mytest=# select count(*) from test_t;
count
-------
28
(1 row)
--刪除test_t表
mytest=# drop table test_t;
DROP TABLE
mytest=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------+----------+----------
public | child_t01 | table | postgres
public | child_t02 | table | postgres
public | child_t03 | table | postgres
public | child_t04 | table | postgres
public | child_t05 | table | postgres
public | child_t06 | table | postgres
public | parent | table | postgres
public | parent_dpart_id_seq | sequence | postgres
public | parent_person_id_seq | sequence | postgres
(9 rows)
mytest=# \q
[postgres@webtest bin]$ psql mytest
psql (9.4.1)
Type "help" for help.
4、進行資料庫恢復
--停止資料庫
[postgres@webtest bin]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
--檢視備份情況
[postgres@webtest bin]$ ./pg_rman show
==========================================================
StartTime Mode Duration Size TLI Status
==========================================================
2014-07-24 15:51:11 INCR 0m 1610MB 1 OK
2014-07-24 15:38:35 FULL 0m 1603MB 1 OK
2014-07-22 17:10:23 FULL 0m 93MB 1 OK
--恢復到最近備份
[postgres@webtest bin]$ ./pg_rman restore -B /u01/backup --recovery-target-time "2014-07-24 15:58:55"
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of current database cluster as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2014-07-24 15:38:35"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2014-07-24 15:38:35" backup and archive log files by SIZE
INFO: backup "2014-07-24 15:38:35" is valid
INFO: restoring database files from the full mode backup "2014-07-24 15:38:35"
INFO: searching incremental backup to be restored
INFO: validate: "2014-07-24 15:51:11" backup and archive log files by SIZE
INFO: backup "2014-07-24 15:51:11" is valid
INFO: restoring database files from the incremental mode backup "2014-07-24 15:51:11"
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2014-07-24 15:51:11" is valid
INFO: restoring WAL files from backup "2014-07-24 15:51:11"
INFO: backup "2014-07-24 15:58:55" is valid
INFO: restoring WAL files from backup "2014-07-24 15:58:55"
INFO: restoring online WAL files and server log files
INFO: generating recovery.conf
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.
--開啟資料庫
[postgres@webtest bin]$ pg_ctl start
server starting
LOG: database system was interrupted; last known up at 2014-07-24 15:51:11 CST
LOG: starting point-in-time recovery to 2014-07-24 15:58:55+08
LOG: restored log file "000000010000000000000064" from archive
LOG: redo starts at 0/64000090
LOG: consistent recovery state reached at 0/640000B8
LOG: restored log file "000000010000000000000065" from archive
LOG: restored log file "000000010000000000000066" from archive
LOG: restored log file "000000010000000000000067" from archive
LOG: recovery stopping before commit of transaction 1864, time 2014-07-24 15:58:58.065224+08
LOG: redo done at 0/67000028
LOG: last completed transaction was at log time 2014-07-24 15:55:47.20653+08
LOG: restored log file "000000010000000000000066" from archive
cp: cannot stat `/u01/postgresql/arch/00000002.history': No such file or directory
LOG: selected new timeline ID: 2
cp: cannot stat `/u01/postgresql/arch/00000001.history': No such file or directory
LOG: archive recovery complete
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
在這裡，可以看到資料庫已經啟處於等待連線狀態
5、驗證恢復情況
[postgres@webtest bin]$ psql mytest
mytest=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | child_t01 | table | postgres
public | child_t02 | table | postgres
public | child_t03 | table | postgres
public | child_t04 | table | postgres
public | child_t05 | table | postgres
public | child_t06 | table | postgres
public | parent | table | postgres
public | test_t | table | postgres
(8 rows)
mytest=# select count(*) from test_t;
count
-------
28
(1 row)
mytest=#
可以看到剛才的表已經恢復回來了！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29371470/viewspace-2123981/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用RMAN還原和恢復資料庫資料庫
- 使用儲存指令碼還原恢復資料庫指令碼資料庫
- oracle資料恢復還原Oracle資料恢復
- SQLSERVER完整資料庫還原（完整恢復模式）SQLServer資料庫模式
- 【備份恢復】下：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- 【備份恢復】上：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- SQL Server 資料庫備份還原和資料恢復SQLServer資料庫資料恢復
- DM7使用DMRMAN執行資料庫還原和恢復資料庫
- 用RMAN還原並恢復資料庫——RMAN使用者手冊資料庫
- 簡單恢復模式執行資料庫完整還原模式資料庫
- 完整恢復模式下執行資料庫完整還原模式資料庫
- 恢復之還原資料檔案
- postgresql備份與恢復資料庫SQL資料庫
- Oracle & MySQL & PostgreSQL資料庫恢復支援OracleMySql資料庫
- 備份和恢復postgreSQL資料庫SQL資料庫
- 【北亞資料恢復】vmfs還原快照操作導致SqlServer資料庫資料丟失的資料恢復資料恢復SQLServer資料庫
- 跨平臺還原、恢復資料庫(Windows->Linux)資料庫WindowsLinux
- 簡單恢復模式下執行資料庫完整還原模式資料庫
- PostgreSql資料庫的備份和恢復SQL資料庫
- 使用恢復建議恢復資料庫資料庫
- 【備份恢復】在 ARCHIVELOG 模式下執行資料庫還原和恢復操作（源庫備份源庫恢復）Hive模式資料庫
- PostgreSQL 恢復大法 - 恢復部分資料庫、跳過壞塊、修復無法啟動的資料庫SQL資料庫
- 北亞資料恢復-WINDOWS還原系統後原分割槽丟失的資料恢復方案資料恢復Windows
- 【備份恢復】閃回資料庫（四）基於可靠還原點閃回資料庫資料庫
- 【資料庫資料恢復】SAP資料庫資料恢復案例資料庫資料恢復
- 寶塔資料庫恢復 mysql資料庫丟失恢復 mysql資料庫刪除庫恢復 寶塔mysql資料庫恢復資料庫MySql
- 使用innobackupex恢復mysql資料庫MySql資料庫
- 使用data dump 恢復資料庫資料庫
- 將 SQL Server 資料庫還原到某個時點（完整恢復模式）SQLServer資料庫模式
- 【資料庫資料恢復】Sql Server資料庫資料恢復案例資料庫資料恢復SQLServer
- rman還原恢復操作
- 恢復資料庫資料庫
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- 還原資料庫資料庫
- 資料庫還原資料庫
- 【資料庫資料恢復】如何恢復Oracle資料庫truncate表的資料資料庫資料恢復Oracle
- 【資料庫資料恢復】windows server下SqlServer資料庫的資料恢復資料庫資料恢復WindowsServerSQL
- 備份與恢復系列 八 丟失所有資料檔案的還原與恢復