invalid record length at 1A2/8F240298: wanted 24, got 0

Question

Due to power failure, my server running Postgres database got crashed.

Now when I am trying to start the database, I am getting the below error.

< 2020-01-04 11:40:39.409 CET > LOG: database system shutdown was interrupted; last known up at 2020-01-04 11:39:52 CET

< 2020-01-04 11:40:39.655 CET > LOG: database system was not properly shut down; automatic recovery in progress

< 2020-01-04 11:40:39.657 CET > LOG: redo starts at 1A2/8F237FF8

< 2020-01-04 11:40:39.658 CET > LOG: invalid record length at 1A2/8F240298: wanted 24, got 0

< 2020-01-04 11:40:39.658 CET > LOG: redo done at 1A2/8F240270

< 2020-01-04 11:40:39.658 CET > LOG: last completed transaction was at log time 2020-01-03 07:21:31.432701+01

< 2020-01-04 11:40:39.685 CET > FATAL: could not fsync file "pg_tblspc/16387/PG_9.6_201608131/48151/51110": Input/output error

< 2020-01-04 11:40:39.687 CET > LOG: startup process (PID 33321) exited with exit code 1

< 2020-01-04 11:40:39.687 CET > LOG: aborting startup due to startup process failure

< 2020-01-04 11:40:39.709 CET > LOG: database system is shut down

is there any way to get it running.

Answer 1

You are looking at the wrong message. The "invalid record length" is an ordinary way for WAL to end. It does not necessarily indicate that there is a problem. The "FATAL: could not fsync" however definitely should not be happening. You have a hardware problem, or a corrupt file system, and those need to be dealt with outside the database.

It is possible you can fix it with something like fsck, and then repeat the automatic recovery. But you can never really be sure you don't already have permanent corruption that crash recovery can't fix. So your best bet may be to restore from a backup, if you have one, onto different hardware.

Answer 2

You can use reset_wal to fix the write-ahead-log.

See https://stackoverflow.com/a/73339220/2032777 for an example.