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:
- I'm unable to run VACUUM FULL to free up space.
- 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.
- Can't take offline long enough to copy the pg dirs off to new drives.
- 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.