Repair PostgreSQL 8.1.3 on CentOS 4.3

scottalanmiller

@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.

DustinB3403

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?

scottalanmiller

No backup whatsoever. Even an ancient one would have sufficed.

scottalanmiller

The script that connects is no problem. Connecting works fine. It's the script that would recreate the database is what is missing.

scottalanmiller

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.

scottalanmiller

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

scottalanmiller

If we try to look at available tables:

portal=# \dt
ERROR:  cache lookup failed for relation 109728

scottalanmiller

I can get some table info with this:

SELECT * FROM pg_catalog.pg_tables;

scottalanmiller

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)

dafyre

That looks like a win if you didn't need any data from that system...

Export the Schema just in case, lol.