I've just rebooted my server after a crash/reboot (which I haven't determined the cause of yet). Server: centos, Postgresql: 9.2.8

It looks like all my postgresql databases were reverted to the state they were in 3 months ago. Columns that I added recently are missing and the maximum values for created timestamp columns are all from 3 months ago.

Is there any crash scenario that could have caused this? Any ideas on an approach to recovery, apart from restoring from backup?

  • 1
    Did you add storage and mount that on top of the data directory prior to reboot say 3 months ago... and forget to put it into /etc/fstab? – yoonix Jun 30 '14 at 20:11
  • Sounds more like a server/filesystem level issue than anything that could be to do with PostgreSQL, if your file timestamps are all old. Any chance you upgraded PostgreSQL to one you installed by hand, with a different data directory, and forgot about it? – Craig Ringer Jul 1 '14 at 1:59

My first guess would be that you've upgraded to a new PostgreSQL or installed a patched one by hand, and your new version isn't set to autostart. So the old version started up with the old data in it. Check to see if there's a newer datadir elsewhere.

Failing that, as @yoonix suggests, perhaps you moved the datadir and mounted the new volume over the old location, but forgot to set it to automount in /etc/fstab.

  • Yes, that's it! I upgraded from 9.2 to 9.3 a few months ago and left the old one around. Upon restart, the 9.2 service got executed and not 9.3. I should have done chkconfig off postgresql-9.2, and of course tested that the system was usable after a restart without waiting for a crisis. – EoghanM Jul 1 '14 at 12:08

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.