Обсуждение: 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/