早上， 同事在在做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 的工作是个细活， 一般情况下是容不得做错了重来的。

         务必谨慎为之！！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133735/viewspace-738611/，如需转载，请注明出处，否则将追究法律责任。