如何修复Postgres,以便在突然关机后启动？

由于突然断电,PostGres在我的本地计算机上运行的服务器突然关闭.重新启动后,我尝试重新启动postgres,我收到此错误:

$ pg_ctl -D /usr/local/pgsql/data restart

pg_ctl: PID file "/usr/local/pgsql/data/postmaster.pid" does not exist
Is server running?
starting server anyway
server starting
$:/usr/local/pgsql/data$ LOG:  database system shutdown was interrupted at 2009-02-28 21:06:16 
LOG:  checkpoint record is at 2/8FD6F8D0
LOG:  redo record is at 2/8FD6F8D0; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 0/1888104; next OID: 1711752
LOG:  next MultiXactId: 2; next MultiXactOffset: 3
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 2/8FD6F918
LOG:  record with zero length at 2/8FFD94A8
LOG:  redo done at 2/8FFD9480
LOG:  could not fsync segment 0 of relation 1663/1707047/1707304: No such file or directory
FATAL:  storage sync failed on magnetic disk: No such file or directory
LOG:  startup process (PID 5465) exited with exit code 1
LOG:  aborting startup due to startup process failure

postmaster.pid数据目录中没有文件.什么可能是这种行为的原因,当然还有出路？

你需要pg_resetxlog.在此之后,您的数据库可能处于不一致状态,因此请将其转储pg_dumpall,重新创建并导回.

原因可能是:

您尚未关闭磁盘上的硬件写入缓存,这通常会阻止操作系统在报告成功写入应用程序之前确保写入数据.检查

hdparm -I /dev/sda

如果它在"写入缓存"之前显示"*",则可能是这种情况.PostgreSQL的源代码有一个程序src/tools/fsync/test_fsync.c,用于测试数据与磁盘同步的速度.运行它 - 如果它报告的所有时间都比磁盘所在的时间短3秒,那么在7500rpm的磁盘上,1000次写入相同位置的测试需要至少8秒才能完成(1000 /(7500rpm /) 60s))因为每条路线只能写一次.如果数据库位于/ var/tmp分区之外的另一个磁盘上,则需要编辑此test_fsync.c - 更改

#define FSYNC_FILENAME "/var/tmp/test_fsync.out"

至

#define FSYNC_FILENAME "/usr/local/pgsql/data/test_fsync.out"

您的磁盘发生故障,有一个坏块,以检查badblocks.

你有一个糟糕的内存,请至少检查memtest86 + 8小时.

在PostgreSQL邮件列表的档案中读取一些类似的消息("磁盘上的存储同步失败:没有这样的文件或目录")似乎表明存在非常严重的硬件故障,比简单的电源故障更糟糕.您可能必须准备好从备份中恢复.