Re: postgresql upgrade/downgrade -- help!
Oliver Elphick wrote:
>
> will trillich wrote:
> ><moron>
> >i munged my sources.list to point to fundet.no for the latest
> >potato-friendly postgresql DEB package, and upgraded via apt-get
> >update && apt-get upgrade.
> ></moron>
> >
> ><moron level=2>
> >it broke my databases, so i tried apt-get --purge remove
> >postgresql<various7.0.3potato*> while hanging on to the
> >/var/lib/postgres/data area. then i used dpkg -i *p*sql*6.5.3*deb
> >to try to recover, and there's more broken stuff than ever.
> ></moron>
>
> "Broke" is a bit non-specific!
>
> ><idiot>no, i don't have a recent-enough backup</idiot>.
> >
> >is there a five-hour intensive plan that might restore my
> >postgresql 6.5.3->7.0.3->6.5.3 database? suggestions welcome
> >(i'm crafting a database-backup plan now, in the meantime.)
>
> What version is the database? `cat $PGDATA/PG_VERSION'
hi, oliver. got your superman cape on again today?
us ijits need a reality check now and then, and today's
my scheduled rendezvous...
--
sorry, 'broke' is a bit broad, i know.
i did
apt-get update && apt-get upgrade
like a idiot without any pg_dump, and version 7.0.3potato* installed
rather cleanly, and it said it could update the database formats
behind-the-scenes, so i said 'okie dokie'.
when i did
psql -u puz
to connect to database puz with 7.0.3* it did so without any
trouble. then i did
puz=> \dt
and most of the tables were there, but not all; and one of my
views had become a table, maybe beause the table it was based
on, didn't exist any more. the tables that ARE there seem to have
the proper data.
nothing fancy -- no REFERENCES, no stored procedures (sql or
pgplsql, nothing at all there) or anything else advanced. just
a serial (sequence) and indexes, among a handful of tables.
the upgrade changed PG_VERSION from 6.5 to 7.0; when i did
dpkg -i postgre*6.5.3*
(using backups of my apt repository) the number stayed at 7.0!
is there a binary/compiled routine somewhere that can parse
through a /var/lib/postgresql/data/base/* file and extract
the stuff that's there? from what i can tell i still have
the data:
-rw------- 1 postgres postgres 4 Feb 27 12:24 PG_VERSION
-rw------- 1 postgres postgres 0 Oct 13 21:05 active
-rw------- 1 postgres postgres 8192 Feb 27 04:00 cust
-rw------- 1 postgres postgres 16384 Feb 22 00:35 cust_handle_key
-rw------- 1 postgres postgres 8192 Feb 22 00:35 cust_id_seq
-rw------- 1 postgres postgres 16384 Feb 22 00:35 cust_sendto_idx
-rw------- 1 postgres postgres 16384 Feb 22 00:35 cust_source_idx
-rw------- 1 postgres postgres 0 Oct 4 21:24 exp
-rw------- 1 postgres postgres 0 Oct 4 21:13 ponly
-rw------- 1 postgres postgres 8192 Feb 27 04:00 puzct
-rw------- 1 postgres postgres 0 Sep 29 12:01 puzno
-rw------- 1 postgres postgres 8192 Feb 27 04:00 range
-rw------- 1 postgres postgres 16384 Feb 11 07:39 range_pkey
-rw------- 1 postgres postgres 8192 Aug 5 2000 sell
-rw------- 1 postgres postgres 16384 Aug 5 2000 sell_id_idx
-rw------- 1 postgres postgres 0 Oct 1 11:52 tot
-rw------- 1 postgres postgres 0 Oct 13 21:04 unconf
unconf, tot, puzno, ponly, exp, active are views; the *idx/*key are
indexes, *seq is the one sequence, and the rest are tables.
the trouble is not that there's a ton of data there -- there isn't --
rather, it's that i've got pseudo-random sequences designed in here,
and unfortunately don't record anywhere what the breakpoints
are, and recreating them would be impossible. hopefully i can recover
the numerics and not have to explain to the customers that their
server manager is a dipshit... :)
actually, i think the 'range' table is the main one i need to
resurrect... everything else is gravy.
--
mailto:will@serensoft.com
http://www.dontUthink.com/
Reply to: