In my PostgreSQL logs, I find this message:

LOG:  invalid magic number 0000 in log file 0, segment 1, offset 16121856

Which file is log file 0? Here is my pg_xlog directory:

-bash-4.1$ ll pg_xlog/
total 49160
-rw------- 1 postgres postgres 16777216 Dec  6 06:27 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jan 21 09:10 000000020000000000000001
-rw------- 1 postgres postgres 16777216 Dec 21 05:29 000000020000000000000002
-rw------- 1 postgres postgres       56 Dec  6 06:30 00000002.history
drwx------ 2 postgres postgres     4096 Dec  6 06:30 archive_status
-bash-4.1$ ll pg_xlog/archive_status/
total 0
-rw------- 1 postgres postgres 0 Dec  6 06:30 00000002.history.ready

That would be 000000010000000000000001 or 000000020000000000000001. That's timeline 1 or 2, log 0, segment 1. Which one is being accessed depends on which timeline your system in (you can use pg_controldata to find that out, but basically the system switches timeline when you do a PITR recovery or a replication failover)

The error message basically says that file is corrupt, which is really bad. Another reason this could happen is if that file is from a different version of postgresql - any chance you accidentally mixed two different versions?

  • Concerning the error message: This is a streaming replication standby database, and I recently removed recovery_target_timeline = 'latest', and then everything started to work again. (It syncs with the master database, no invalid magic number error no more). Perhaps PostgreSQL for some weird reason used the wrong timeline and found that log file, which for some reason is corrupt. Oddly enough, after PostgreSQL in some way re-synced with the master, everything works although I've now re-added recovery_target_timeline = 'latest'. – KajMagnus Feb 3 '12 at 16:32
  • Any chance I accidentally mixed two different versions? -- I don't think so: I've run pg_controldata on the master and slave databases, the output is identical. Also they are the same Amazon EC2 images, and use the same PostgreSQL repo. – KajMagnus Feb 3 '12 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.