We are running a PostgreSQL 9.1.20 with a database of about 50GB.

  1. All things works fine.
  2. After some days strange unexpected values appear in numeric columns, like negative or bigger values in unexpected places.
  3. The strange part: if we run a pg_dump of the same "corrupted" database, and then restore it (in the same server), the data becomes all ok. Now the same queries return different values!

Why does the database dump have different values when we are taking the dump exactly from the "corrupted" database? (The dump has corrected values from our perception!)

Can this be related to some kind of broken transaction or hard drive trouble? Note that restarting the database does not solve the problem.

What can be the cause behind this problem?

As requested in the comments some examples of data:

    \c base
    select * from salXXXX where sal_id=2323; 
    sal_id | sal_XXXX | XXXXXX | XXXXXX | XXXXX | XXXXX | XXXXX 
   2323 |     -30.43 | 42586501      |                  6 |         13 | f                     | 

    pg_dump -h -p 5432 --user=postgres >backup.sql
    drop database base;
    create database base;
    \c base 
    \i backup.sql
    (no errors found here) 
    select * from salXXXX where sal_id=2323; 
    sal_id | sal_XXXX | XXXXXX | XXXXXX | XXXXX | XXXXX | XXXXX 
   2323 |    245.43 | 42586501      |                  6 |         13 | f                     | 

It is hard to emulate it, rarely occurs, but when it occurs we just do a dump, it is a ridiculous solution, but it works.

This is just an example, many more wrong values start to appear, even if we reprocess the data. But if the database is dumped and restored, values are in the expected range again.

  • Neither of the problems you listed can cause this. You should show the data that you expect to be there, the query you use, indexes that might be involved, the actual output and the exact PostgreSQL version. At least. Sep 28, 2016 at 14:54
  • Dezso i am thinking about transactions, because it is very like the transaction isolation is interfering on the whole system, what is not permited.. but some bug is causing it. Sep 28, 2016 at 15:14
  • I'd rather look into devs testing on this DB or similar. Turn on logging all statements, and the next time this occurs, find the culprit. Sep 28, 2016 at 16:34

1 Answer 1


Might be index corruption - assuming you have an index on sal_id.

If so, REINDEX TABLE salXXXX; (or just REINDEX INDEX index_name would do the same trick as your dump/restore cycle, which also recreates all indexes from scratch.

The manual advises:

Note that while this method does not require locking out other clients, it might still be wise to prevent other users from connecting to the damaged database until repairs have been completed.

Generally, consider upgrading to a current version of Postgres. Version 9.1 has just reached its EOL in Sept. 2016. There have been many related updates since, might very well fix the problem for good.

  • @777Anon: None of what you report is "normal". Index corruption should not occur in the first place. If you have concurrent access to the db, locks on the table might slow the REINDEX process down. You might want to take a write lock on the table first or drop and recreate the index instead (CREATE INDEX also locks the table). Sep 28, 2016 at 17:22
  • I runned the reindex. It causes a strange behavior, the values changed with reindex what probably means that you are in the right way, but it changes not to the correct value, but a random value, i am reindexing the entire database now. The interesting thing is that a bigger database (with the same structure) run in the same postgresql instance without any trouble. Sep 28, 2016 at 17:45
  • @777Anon: You did take a backup, right? Normally REINDEX is uncritical, but with strange things happening, it's best to have a backup ... Sep 28, 2016 at 17:50
  • Dont worry this is not in production anymore! We have a lot of backups (dumps) or in a funny way: auto-corrected dumps. The production is just stopped (waiting) for this database. Sep 28, 2016 at 17:53
  • Friend thank you. I changed the memory sticks from the server, and it is looking like pretty good now, but i am still testing it. Thanks! Sep 28, 2016 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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