postgresql 如何修复 Postgres 使其在突然关闭后启动？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/598200/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I fix Postgres so it will start after an abrupt shutdown?
提问by Tometzky
Due to a sudden power outage, the PostGresserver running on my local machine shut down abruptly. After rebooting, I tried to restart postgres and I get this error:
由于突然停电，PostGres在我本地机器上运行的服务器突然关闭。重新启动后，我尝试重新启动 postgres，但出现此错误：
$ pg_ctl -D /usr/local/pgsql/data restart
$ 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
There is no postmaster.pidfile in the data directory. What possibly could be the reason for this sort of behavior and of course what is the way out?
postmaster.pid数据目录中没有文件。这种行为的原因可能是什么，当然还有什么出路？
回答by Tometzky
You'd need to pg_resetxlog. Your database can be in an inconsistent state after this though, so dump it with pg_dumpall, recreate and import back.
你需要pg_resetxlog。在此之后，您的数据库可能处于不一致状态，因此将其转储pg_dumpall，重新创建并重新导入。
A cause for this could be:
造成这种情况的原因可能是：
You have not turned off hardware write cache on disk, which often prevents the OS from making sure data is written before it reports successful write to application. Check with
hdparm -I /dev/sdaIf it shows "*" before "Write cache" then this could be the case. Source of PostgreSQLhas a program src/tools/fsync/test_fsync.c, which tests speed of syncing data with disk. Run it - if it reports all times shorter than, say, 3 seconds than your disk is lying to OS - on a 7500rpm disks a test of 1000 writes to the same place would need at least 8 seconds to complete (1000/(7500rpm/60s)) as it can only write once per route. You'd need to edit this test_fsync.c if your database is on another disk than /var/tmp partition - change
#define FSYNC_FILENAME "/var/tmp/test_fsync.out"to
#define FSYNC_FILENAME "/usr/local/pgsql/data/test_fsync.out"Your disk is failing and has a bad block, check with badblocks.
You have a bad RAM, check with memtest86+for at least 8 hours.
您尚未关闭磁盘上的硬件写入缓存，这通常会阻止操作系统在向应用程序报告成功写入之前确保数据已写入。检查
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。
您的 RAM 不好，请使用memtest86+检查至少 8 小时。
回答by bortzmeyer
Reading a few similar messages in the archives of the PostgreSQL mailing list ("storage sync failed on magnetic disk: No such file or directory") seems to indicate that there is a very serious hardware trouble, much worse than a simple power failure. You may have to prepare yourself to restore from backups.
在 PostgreSQL 邮件列表的存档中阅读了一些类似的消息（“磁盘上的存储同步失败：没有这样的文件或目录”）似乎表明存在非常严重的硬件故障，比简单的电源故障要严重得多。您可能必须准备好从备份中恢复。
回答by srghma
Had db corruption too, my actions
也有数据库损坏，我的行为
docker run -it --rm -v /path/to/db:/var/lib/postgresql/data postgres:10.3 bash
su - postgres
/usr/lib/postgresql/10/bin/pg_resetwal -D /var/lib/postgresql/data -f
回答by srghma
Run start instead of restart. Execute the below command:
运行 start 而不是重新启动。执行以下命令：
$pg_ctl -D /usr/local/pgsql/data start
