Re: postgresql upgrade/downgrade -- help!
will trillich wrote:
>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!
I think you have fallen foul of bugs in 6.5's pg_dump; it was not
brilliant at describing the database. Unfortunately for you, the
upgrade procedure did work and your database has been upgraded to
7.0. That being the case, it can now be read only by 7.0.
Take, for example, your views: a view is implemented as an empty table
with a rule attached. This may still be a good description of
what you have, but the system may have forgotten to call them
views.
You could use postgresql-dump to bring the database back to 6.5, but
this is only likely to screw up your data even more, since it will
have to go through pg_dump again to get there. You might just as
well put 7.0.3 back and go on from there. It will not change the data
again, since it will recognise it as up-to-date.
>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:
postgres, from 7.0!
>-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.
What exactly is missing from it? It seems to have data in it, by its
size...
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"If we confess our sins, he is faithful and just to
forgive us our sins, and to cleanse us from all
unrighteousness." I John 1:9
Reply to: