Timeline for Recover PostgreSQL database from WAL errors on startup?
Current License: CC BY-SA 3.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 29, 2013 at 16:40 | vote | accept | Edward | ||
| Aug 28, 2012 at 6:24 | history | edited | Craig Ringer | CC BY-SA 3.0 | added 309 characters in body |
| Aug 28, 2012 at 6:04 | history | edited | Craig Ringer | CC BY-SA 3.0 | added 401 characters in body |
| Jul 25, 2012 at 8:50 | comment | added | Craig Ringer | Looks like this could be a Debian packaging bug with the PostgreSQL init scripts, though it isn't for sure yet. Yikes. | |
| Jul 24, 2012 at 13:45 | comment | added | Craig Ringer | Wow, migrated again. Follow up posted on pgsql-general: archives.postgresql.org/message-id/… . | |
| Jul 22, 2012 at 0:19 | comment | added | Edward | Thanks for posting that question to the mailing list, though there doesn't seem to be any responses to it yet. In the meantime, I can tell you that I used the /etc/init.d/postgresql restart script to restart PostgresSQL, and never used kill -9. (Question updated, too). | |
| Jul 19, 2012 at 3:38 | comment | added | Craig Ringer | @Edward Query posted to pgsql-general. It might be 404 for a few minutes until the archiver catches up. Extra question: Why is there both a "fast" and "immediate" shutdown in the log? How, exactly, did you shut PostgreSQL down? Did you at any point use kill -9 ? | |
| Jul 19, 2012 at 3:22 | comment | added | Craig Ringer | I just read up on what osm2pgsql is, and it doesn't seem to do any kind of direct writing to the Pg database files, it's a normal libpq based client. It shouldn't have been able to break anything by its self. One question though @Edward - why did you restart the DB after the import? Just curious how that fits in to the problem. | |
| Jul 19, 2012 at 3:04 | comment | added | Craig Ringer | @Edward OK, that's very strange. A normal shutdown looks like this: pastebin.com/SSZiaX3G with the "database system is shut down" line. Yours all looks fine 'till a backend crashes, then goes pear shaped. I almost wonder if the postmaster crashed out, but that should produce a message in the logs too. It's a very strange fault, and not one I know enough about Pg's innards to do much about. I'll raise it on the Pg mailing lists and see if anyone has any ideas. BTW, is there any chance you ran out of disk space at any point? It should recover gracefully, but... | |
| Jul 18, 2012 at 17:20 | comment | added | Edward | Yes, that's the entire log file. The very next log file in the sequence starts with the line 2012-07-13 00:31:22 UTC LOG: database system was interrupted; last known up at 2012-07-13 00:15:20 UTC so there are no more messages from the shutdown process. (I added a pastebin of the next log since it's also the one where you see the WAL errors). | |
| Jul 17, 2012 at 23:51 | comment | added | Craig Ringer | @Edward That's only 220 lines, and doesn't appear to end with postmaster termination. Sure that's the whole log? It looks like maybe a backend crashed during shutdown - which shouldn't hurt anything, but shouldn't happen either. The log seems to end before the shutdown finishes though. | |
| Jul 17, 2012 at 20:17 | comment | added | Edward | I updated my question to include the relevant logs. I hope this helps. (Actually I did that yesterday, but I just realized StackExchange doesn't give you a notification when I edit the question, like it does when I comment). | |
| Jul 16, 2012 at 14:10 | history | migrated | from stackoverflow.com (revisions) | ||
| Jul 15, 2012 at 18:23 | comment | added | Edward | Unfortunately, by the time I saw this post I had already run pg_resetxlog and started a pg_dump of my database. When it finishes I'll go through the logs from before I started having WAL errors and see if I can find any clues. I'm not running with fsync=off so I'm equally puzzled as to how the WAL got corrupted. | |
| Jul 14, 2012 at 2:04 | history | answered | Craig Ringer | CC BY-SA 3.0 |