I recently upgraded from 9.4.1 to 9.4.4. Since then millions of rows have disappeared from several tables containing historical data.

The severity of data loss seems to be directly proportional to the age of the records. 1-2% of the data from 10 days ago is missing, but >90% of the data from 50 days ago is missing.

I haven't seen anything new in the logfile except:
LOG: MultiXact member wraparound protections are now enabled
The 9.4.2 release was supposed to fix an issue with MultiXact wraparound.http://www.postgresql.org/about/news/1587/

I've temporarily disabled autovacuum for this weekend.

I'm using OpenBSD 5.8.

EDIT: Tue Sep 29 13:13:08 PDT 2015

I found the following interesting statement in the logfile:

STATEMENT: delete from history_uint where ctid = any(array(select ctid from history_uint where itemid=96902 limit 500))

EDIT: Tue Sep 29 14:04:41 PDT 2015

Resolved. Not a pgsql bug. When the sysadmin upgraded the server OS he deleted a configuration file for the application using the DB. The configuration change enabled a 'housekeeper' task which deletes old records. Mods are welcome to delete this question.

  • Did you get a backup before doing the upgrade? – ypercubeᵀᴹ Sep 26 '15 at 19:52
  • @ypercube Yes, I got a backup before the upgrade, but it is now a month old, so it's missing recent data. – sheepdog Sep 27 '15 at 0:08
  • 3
    Take a snapshot of the on-disk state now, then please raise this on pgsql-bugs or pgsql-hackers. How exactly did you upgrade? pg_upgrade? Dump and reload? Other? – Craig Ringer Sep 27 '15 at 9:19
  • @CraigRinger I'll post to pgsql list tonight. Can you think of any other information I should include when I post there? My boss/sysadmin did the upgrade. I think he just did the standard OpenBSD release upgrade without doing anything postgres or DB specific like pg_upgrade or dump-and-reload. – sheepdog Sep 28 '15 at 1:53
  • Apart from the possibilities of a bug or an error in the upgrade procedure by the sysadmin, this 1-2% of newer vs 90% of older data missing is weird. Is there any procedure that runs daily (or recurrently) or triggers and that are based on some timestamp? (just a wild guess that the procedure is altered somehow or some settings was changed during the upgrade and affected it, i.e. timezones?) – ypercubeᵀᴹ Sep 28 '15 at 13:36

Your Answer

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

Browse other questions tagged or ask your own question.