PostgreSQL 8.3.11 locked; orphaned pg_toast database object recovery -

howdy slack overflowvians.

so came across postgresql server running 8.3.11 (yeah know), in locked state with:

error:  database not accepting commands avoid wraparound data loss in database "postgres" hint:  stop postmaster , use standalone backend vacuum database. 

normally auto vaccum daemon (autovacuum=on), handle this, because following 4 toast (allows storage of large field values 8 kb slices, bread), database object. xid of database never reset because of these corrupt database objects.

below snippet of output when running server in single-user mode admin user:

select oid, relname, age(relfrozenxid) pg_class relkind = 't' order age(relfrozenxid) desc limit 4;       ----      1: oid = "2421459"     (typeid = 26, len = 4, typmod = -1, byval = t)      2: relname = "pg_toast_2421456"        (typeid = 19, len = 64, typmod = -1, byval = f)      3: age = "2146484084"  (typeid = 23, len = 4, typmod = -1, byval = t)     ----      1: oid = "2421450"     (typeid = 26, len = 4, typmod = -1, byval = t)      2: relname = "pg_toast_2421447"        (typeid = 19, len = 64, typmod = -1, byval = f)      3: age = "2146484084"  (typeid = 23, len = 4, typmod = -1, byval = t)     ----      1: oid = "2421435"     (typeid = 26, len = 4, typmod = -1, byval = t)      2: relname = "pg_toast_2421432"        (typeid = 19, len = 64, typmod = -1, byval = f)      3: age = "2146484084"  (typeid = 23, len = 4, typmod = -1, byval = t)     ----      1: oid = "2421426"     (typeid = 26, len = 4, typmod = -1, byval = t)      2: relname = "pg_toast_2421423"        (typeid = 19, len = 64, typmod = -1, byval = f)      3: age = "2146484084"  (typeid = 23, len = 4, typmod = -1, byval = t) 

notice age above vacuum_freeze_min_age (value set after successful vacuum), on server , why issuing original errors above. above after running vacuum full; other tables fine.

select relfilenode pg_class oid=2421459; 

so when looked on disk (used pg_class.relfilenode value each table above) toast table's file missing:

$ find /var/lib/pgsql/data/ -type f -name '2421426' | wc -l  # bad toast 0 

and when looked on disk @ index of toast

 select relfilenode pg_class (select reltoastidxid pg_class oid=2421459)  $ find /var/lib/pgsql/data/ -type f -name '2421459' | wc -l  # bad toast's index 0 

we tried find table bad toast record related with:

select * pg_class reltoastrelid=2421459; 

got 0 results each table above! there no tables vacuum command reset xid of these relations.

when checked pg_depend table , found these toast tables have no references:

select * pg_depend refobjid in(2421459,2421450,2421435,2421426) 

question

1. can delete bad toast table , toast table indexes pg_class table (e.g. delete pg_class oid=2421459)
2. are there other tables need remove relation from?
3. could create temp table , link toast's index's oid?

example #3 above:

create table adoptedparent (colnameblah char(1)); update pg_class set reltoastrelid=2421459 relname='adoptedparent';   vacuum full verbose adoptedparent 

edit:

select txid_current() 3094769499 these tables corrupted long time ago. don't need recover data. running ext4 file system on linux 2.6.18-238.el5. checked relevant lost+found/ directories , files not there.

just home audience, in particular case resolution edit pg_class directly. , update server supported version of postgres, of course!

specific answers:

1. yes can, although in cases it's better create empty table, attach toast relation table, add pg_depend entries, , drop table. in case, didn't make sense because there no other objects depending on toast tables.

   2. usually toast tables have index in pg_index, , entries in pg_depend. these did not.

   3. see above.