Repair PostgreSQL 8.1.3 on CentOS 4.3
-
This is a tough one and I've been working on it for a bit. Mostly I've tried some high level approaches and tried working with the tonnes of code associated with this to try to work around the database issues but, in the end, I think that fixing the database is the better approach. This is an ancient 32bit system of CentOS 4.3 i386 running a PostgreSQL 8.1.3 database server being used by PHP 5.1. The system is anything but critical but there is a report that gets sent to it for processing and the script that handles that is not trivial to replicate.
The data in the database is not needed, but the structure of the database is. The corruption is pretty significant. The script that uses it is able to connect but is not able to run due to database errors. Even looking at the tables from within PostgreSQL's console is impossible due to the corruption so I cannot even see table names or anything.
Going to be working on seeing what can be recovered.
-
@scottalanmiller said in Repair PostgreSQL 8.1.3 on CentOS 4.3:
This is a tough one and I've been working on it for a bit. Mostly I've tried some high level approaches and tried working with the tonnes of code associated with this to try to work around the database issues but, in the end, I think that fixing the database is the better approach. This is an ancient 32bit system of CentOS 4.3 i386 running a PostgreSQL 8.1.3 database server being used by PHP 5.1. The system is anything but critical but there is a report that gets sent to it for processing and the script that handles that is not trivial to replicate.
The data in the database is not needed, but the structure of the database is. The corruption is pretty significant. The script that uses it is able to connect but is not able to run due to database errors. Even looking at the tables from within PostgreSQL's console is impossible due to the corruption so I cannot even see table names or anything.
Going to be working on seeing what can be recovered.
What are the chances of reverse engineering the PHP scripts to come up with the database structure?
-
@dafyre said in Repair PostgreSQL 8.1.3 on CentOS 4.3:
What are the chances of reverse engineering the PHP scripts to come up with the database structure?
Been looking at that and it is possible, of course. But it relies on responses that don't come through so it's more than just looking at the INSERT statements, I'm afraid. That's an option, though. There is also a text description of the database that might get us partway there. But.... maybe not.
-
Scott you said there are two critical items from this system.
- The database structure
- the script that connects to the database
Would it be possible to at least recover the script while a counterpart attempts to create a clean database with a matching structure?
There is no backup of this system at all?
-
No backup whatsoever. Even an ancient one would have sufficed.
-
The script that connects is no problem. Connecting works fine. It's the script that would recreate the database is what is missing.
-
I have a backup of everything (I hope). I'm going to be working purely from the copy. Okay... so let's see if we can remove some indices.
-
Just to give an idea of where you see the corruption:
postgres=# \connect portal You are now connected to database "portal". portal=# vacuum analyze; WARNING: could not write block 91451 of 1663/1973211/1259 DETAIL: Multiple failures --- write error may be permanent. ERROR: xlog flush request 3B9/5941F5E8 is not satisfied --- flushed only to 3B9/582F5BF8 CONTEXT: writing block 91451 of relation 1663/1973211/1259 -
If we try to look at available tables:
portal=# \dt ERROR: cache lookup failed for relation 109728 -
I can get some table info with this:
SELECT * FROM pg_catalog.pg_tables; -
Finally got a troublesome table to TRUNCATE. Fingers crossed that we are headed in the right direction.
portal=# TRUNCATE portal_custom.batch__jobs; TRUNCATE TABLE portal=# SELECT * FROM portal_custom.batch__jobs; id | batch_group_id | prerequisite_batch_job_id | name | description | code | last_run_time | last_run_result | run_order ----+----------------+---------------------------+------+-------------+------+---------------+-----------------+----------- (0 rows) -
That looks like a win if you didn't need any data from that system...
Export the Schema just in case, lol.
