3

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:

    #psql 
    \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                     | 

    \quit
    pg_dump -h 127.0.0.1 -p 5432 --user=postgres >backup.sql
    psql 
    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. – dezso Sep 28 '16 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. – Luciano Andress Martini Sep 28 '16 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. – dezso Sep 28 '16 at 16:34
3

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). – Erwin Brandstetter Sep 28 '16 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. – Luciano Andress Martini Sep 28 '16 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 ... – Erwin Brandstetter Sep 28 '16 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. – Luciano Andress Martini Sep 28 '16 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! – Luciano Andress Martini Sep 28 '16 at 18:36

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.