linux系统修复pg数据库,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 的工作是个细活， 一般情况下是容不得做错了重来的。

务必谨慎为之！！

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