0

I have an old Postgres server that some major corruption problems on one of the databases. We first noticed the issue when the drives started to fill up on a database that should have plenty of space. This database is supposed to hold a rotating 90 day supply of data for reporting purposes and has millions of DELETEs and INSERTs monthly. After further investigation, the job that does the DELETEs and runs VACUUM FULL was failing. Not only that, but db backups are failing. Now we're in a tough spot due to the drive being so full and filing up fast.

Here are the details:

  1. I'm unable to run VACUUM FULL to free up space.
  2. I can't backup the database due to corruption errors.
  3. All of the largest tables/indexes are corrupted.
  4. Can't export data due to corruption.
  5. Can't take offline long enough to copy the pg dirs off to new drives.
  6. Server has been up for ~2 years. Old physical box that may or may not come back after reboot.

Here are some of the corruption errors...

ERROR: could not read block 7107494 of relation 1663/24832/24875: Success

ERROR: could not read block 277779 of relation 1663/24832/24865: Input/output error

How can I recover from this? I was thinking about using table triggers to insert all of the incoming data into another database on a different drive. Is that an option? Any guidance would be appreciated.

  • What OS are you running on? Linux/Windows/macOS? – Philᵀᴹ Jul 25 '16 at 17:21
  • We're running CentOS 5.3 – Brickpack Jul 25 '16 at 18:46
  • you have tried logical backup or physical backup?, physical backup may help. – Spike Jul 26 '16 at 7:19
0

First of all, stop your database and make a full physical backup.

After the backup, run pg_dump on your database and identify how tables happens this problem.

For every one with this error, run a query with LIMIT and OFFSET to locate this corrupted rows. When you find it, create a new table with this contents.

take a look at this example (suppose the problem are in the row 101) :

CREATE TABLE backup_of_table_with_problem AS
SELECT * FROM table_with_problem
LIMIT 100;

Then skip the tuples with problem with OFFSET:

INSERT INTO backup_of_table_with_problem
SELECT * FROM table_with_problem
OFFSET 101;

When you are able to make a dump of your database (excluding the tables with this error) I recommend you to remove the entire cluster and create a new one to restore this dump file.

Hope it helps.

References:

  1. https://www.postgresql.org/docs/8.1/static/sql-createtableas.html
  2. https://www.postgresql.org/docs/8.1/static/sql-insert.html
0

I can't backup the database due to corruption errors.

All of the largest tables/indexes are corrupted.

Can't export data due to corruption.

IMHO, you no longer have a "database".

You have a collection of broken "stuff" that Postgres is trying its very best to make sense of, but it's fighting a losing battle.

Can't take offline long enough to copy the pg dirs off to new drives.

You have no other choice.

If your disks are failing, then it's only a matter of time until one of those little puddles of magnetism fails under something critical and takes the whole database - and your application stack - with it.

Shut the database down, secure copies of whatever you can for your own investigation and then start restoring your last [good] Backup to get the system running again.

The copies are there to give you a chance to try and recover some of what will be lost by the restore process. You should not expect to get a system running on top of the copied data files; they're toast.

Yes, data will be lost by restoring the database but how much will be lost and how long you have to get the database database back up and running has all been planned for, as part of your Recovery Strategy, hasn't it?

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.