PG 資料庫恢復實戰

早上， 同事在在做pg歸檔日誌的清理的時候，執行程式碼的當前目錄錯了，導致刪除了pg資料庫的資料檔案， 二進位制程式碼，lib 包

因為是內部使用庫，只有dba 在用，沒有做streaming 複製，只有備份，

故障發生時，資料庫沒有關閉，檔案的控制程式碼是被pg資料庫把持的，我們企圖關閉應用，從檔案控制程式碼裡把檔案恢復回去，

這是在linux 系統裡 資料庫誤刪除常用的方式，mysql oracle 都可以用，但是在pg這裡杯具了。

pg系統對檔案系統做了一層包裝，在/proc/$pid/fd 下面已經沒有實際資料檔案的連線了。

下面的是oracle 資料庫的一個例項：
[code ]

×ü？？ 0
lr-x------ 1 oracle dba 64 07-30 08:05 0 -> /dev/null
lr-x------ 1 oracle dba 64 07-30 08:05 1 -> /dev/null
lr-x------ 1 oracle dba 64 07-30 08:05 10 -> /dev/zero
lr-x------ 1 oracle dba 64 07-30 08:05 11 -> /dev/zero
lr-x------ 1 oracle dba 64 07-30 08:05 12 -> /data/oracle/product/10.2.0/rdbms/mesg/oraus.msb
lrwx------ 1 oracle dba 64 07-30 08:05 13 -> /data/oracle/product/10.2.0/dbs/hc_prework.dat
lrwx------ 1 oracle dba 64 07-30 08:05 14 -> /data/oracle/product/10.2.0/dbs/lkPREWORK
lrwx------ 1 oracle dba 64 07-30 08:05 15 -> /data1/oracle/oradata/prework/prework/control01.ctl
lrwx------ 1 oracle dba 64 07-30 08:05 16 -> /data1/oracle/oradata/prework/prework/control02.ctl
lrwx------ 1 oracle dba 64 07-30 08:05 17 -> /data1/oracle/oradata/prework/prework/control03.ctl
lrwx------ 1 oracle dba 64 07-30 08:05 18 -> /data1/oracle/oradata/prework/prework/system01.dbf
lrwx------ 1 oracle dba 64 07-30 08:05 19 -> /data1/oracle/oradata/prework/prework/undotbs01.dbf
lr-x------ 1 oracle dba 64 07-30 08:05 2 -> /dev/null
lrwx------ 1 oracle dba 64 07-30 08:05 20 -> /data1/oracle/oradata/prework/prework/sysaux01.dbf
lrwx------ 1 oracle dba 64 07-30 08:05 21 -> /data1/oracle/oradata/prework/prework/users01.dbf
lrwx------ 1 oracle dba 64 07-30 08:05 22 -> /data1/oracle/oradata/prework/prework/AUTOBLOG_NEW_DATA.dbf
lrwx------ 1 oracle dba 64 07-30 08:05 23 -> /data1/oracle/oradata/prework/prework/AUTOBLOG_NEW_DATA1.dbf
lrwx------ 1 oracle dba 64 07-30 08:05 24 -> /data1/oracle/oradata/prework/prework/AUTOBLOG_NEW_DATA2.dbf
lrwx------ 1 oracle dba 64 07-30 08:05 25 -> /data1/oracle/oradata/prework/prework/temp01.dbf
lr-x------ 1 oracle dba 64 07-30 08:05 26 -> /data/oracle/product/10.2.0/rdbms/mesg/oraus.msb
lrwx------ 1 oracle dba 64 07-30 08:05 27 -> socket:[110151746]
lr-x------ 1 oracle dba 64 07-30 08:05 3 -> /dev/null
lrwx------ 1 oracle dba 64 07-30 08:05 30 -> /data1/oracle/oradata/prework/prework/gamevideo_nw_data.dbf
lrwx------ 1 oracle dba 64 07-30 08:05 31 -> /data1/oracle/oradata/prework/prework/ladycosme_data.dbf
lr-x------ 1 oracle dba 64 07-30 08:05 4 -> /dev/null
l-wx------ 1 oracle dba 64 07-30 08:05 5 -> /data/oracle/admin/prework/udump/prework_ora_25692.trc
l-wx------ 1 oracle dba 64 07-30 08:05 6 -> /data/oracle/admin/prework/bdump/alert_prework.log
lrwx------ 1 oracle dba 64 07-30 08:05 7 -> /data/oracle/product/10.2.0/dbs/lkinstprework (deleted)
l-wx------ 1 oracle dba 64 07-30 08:05 8 -> /data/oracle/admin/prework/bdump/alert_prework.log
lrwx------ 1 oracle dba 64 07-30 08:05 9 -> /data/oracle/product/10.2.0/dbs/hc_prework.dat
[/code]

只能走資料恢復的路子了。

資料量不很大，幾百M ，

把 最新的備份copy 回去
修改recovery.conf
修改restore_command
[code]
recovery_target_timeline = 'latest'
restore_command = 'cp /usr/local/pgsql/archive/%f %p'
[/code]

啟動資料庫[code]
    2012-07-30 10:01:16 CSTLOG:  starting archive recovery
    2012-07-30 10:01:16 CSTLOG:  restored log file "000000010000002B00000028" from archive
    2012-07-30 10:01:17 CSTLOG:  redo starts at 2B/28000078
    2012-07-30 10:01:17 CSTLOG:  consistent recovery state reached at 2B/29000000
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B00000029" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B0000002A" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B0000002B" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B0000002C" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B0000002D" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B0000002E" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B0000002F" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B00000030" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B00000031" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B00000032" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B00000033" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B00000034" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B00000035" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B00000036" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B00000037" from archive
    2012-07-30 10:01:17 CSTLOG:  restored log file "000000010000002B00000038" from archive
    2012-07-30 10:01:18 CSTLOG:  restored log file "000000010000002B00000039" from archive
cp: cannot stat `/usr/local/pgsql/archive/000000010000002B0000003A': No such file or directory
    2012-07-30 10:01:18 CSTLOG:  unexpected pageaddr 2B/32000000 in log file 43, segment 58, offset 0
    2012-07-30 10:01:18 CSTLOG:  redo done at 2B/39000078
    2012-07-30 10:01:18 CSTLOG:  last completed transaction was at log time 2012-07-30 08:55:27.661619+08
    2012-07-30 10:01:18 CSTLOG:  restored log file "000000010000002B00000039" from archive
cp: cannot stat `/usr/local/pgsql/archive/00000002.history': No such file or directory
    2012-07-30 10:01:18 CSTLOG:  selected new timeline ID: 2
cp: cannot stat `/usr/local/pgsql/archive/00000001.history': No such file or directory
    2012-07-30 10:01:18 CSTLOG:  archive recovery complete
    2012-07-30 10:01:18 CSTLOG:  autovacuum launcher started
    2012-07-30 10:01:18 CSTLOG:  database system is ready to accept connections
[/code]登入資料庫檢查ok

恢復完畢 。


總結：   DBA 的工作是個細活， 一般情況下是容不得做錯了重來的。
         務必謹慎為之！！