Обсуждение: upgrade broke stuff, downgrade still broke

hi. i've got a little database going on debian (potato) and
i tried updating my postgresql to 7.0.3 (from 6.5.3). there
were no fancy plpgsql procedures, no foreign keys (references),
nothing very advanced at all. just about five tables, a few
views, and a sequence (serial type).

i did the debian
    apt-get update && apt-get upgrade
to install the 7.0.3potato* version from www.samfundet.no/~tfheen
and during the upgrade a view turned into an empty table, and
some other tables disappeared altogether.

then i did
    apt-get --purge remove postgres*
(keeping data directories intact) and then
    dpkg -i postgres*6.5.3*
to force the reinstallation of the oldder server engine.

now i have an existing database (psql 'mydb' selects the
database) but there are no tables, no views, no indexes,
no series...

any suggestions welcome. (i'm figuring out how to automate
backups now, and thanks for the idea. :) :( :)

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
        -- Isaac Asimov, 'The Genetic Code'

will@serensoft.com
http://groups.yahoo.com/group/newbieDoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

On Tue, Feb 27, 2001 at 04:06:03PM -0600, will trillich wrote:
> hi. i've got a little database going on debian (potato) and
> i tried updating my postgresql to 7.0.3 (from 6.5.3). there
> were no fancy plpgsql procedures, no foreign keys (references),
> nothing very advanced at all. just about five tables, a few
> views, and a sequence (serial type).
>
> i did the debian
>     apt-get update && apt-get upgrade
> to install the 7.0.3potato* version from www.samfundet.no/~tfheen
> and during the upgrade a view turned into an empty table, and
> some other tables disappeared altogether.

I don't know much automation Debian's packages provide, but the
database format between 6.5 and 7.0 is _not_ compatible -- you
can't use the same data directories with both versions. To
upgrade and keep the same data, you'll probably need to use
pg_dump to copy the data out of the copy and then reload it
after the upload is complete. Some docs:

    http://postgresql.readysetnet.com/users-lounge/docs/7.0/admin/manage-ag1655.htm

You might be able to use pg_upgrade, but IIRC the 'recommended'
method is to use pg_dump

HTH,

Neil

--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Never criticize anybody until you have walked a mile in their shoes,
because by that time you will be a mile away and have their shoes.

Joseph Shraibman wrote:
>
> The data files aren't compatible, so I'm surprised it worked at all.  I
> thought postgres would look for a version number in the data directory
> and refuse to run if it didn't match.

i'd agree if the sequence was brain-dead:
    1) zap old binaries.
    2) install new binaries.
    3) pray a lot.
    4) see if you can read the old data with the new binaries.

but why wouldn't the install be intelligent, like:
    1) use existing binaries to dump schema and data
    2) shelve/archive current binaries
    3) install new binaries.
    4) restore structure and content from dump...

if there are major alterations between versions, there might
be snags -- using oids in a strange manner, relying on a
trigger feature that's nonexistent in the current iteration,
using stored procedures to do obscure things... but my data
was really simple tables, views and indexes. (one sequence,
period.) no triggers, no rules, no procedures.

some populated tables are just fine, others are missing
completely; some views became empty tables.

ls /var/lib/postgresql/data/base/puz shows that the files
i'm looking for do exist, and have nonzero size (and haven't
been modified, so maybe they're still copacetic). any ideas
on breathing life into them?

(i've been following the 'vacuum/backup' thread so this won't
bit me in the ass next time... :) )

--
mailto:will@serensoft.com
http://www.dontUthink.com/

Joseph Shraibman wrote:
> rpms are a pain.  There was some discussion on the list a while ago
> about making a smarter upgrader, but I don't know what came of it.

<plug> if you haven't seen debian's "apt-get" facility,
you don't know how wonderful upgrades can be. </plug>

i've never messed with rpm's ... :)

i just do

    apt-get update && apt-get upgrade

and all my currently-installed packages are checked for available
patches, which are then applied. done! very, very handy.

so handy, in fact, that i can screw up my postgresql database
by being too trusting...

--
mailto:will@serensoft.com
http://www.dontUthink.com/