Re: [ADMIN] dump from 8.x restore to 7.3.x
On the other hand, dumping a newer version database with an older version of *pg_dump* is much more likely to succeed. It's not a guarantee, but it should get you pretty close. And as someone else mentioned, doing a plain text dump is probably your best bet in this case. No, that's not an option, I have tried several combinations like using old pg_dump on a new db but the results are always the same: no way to restore a newer db on an older pg (my test were only about PG8 - PG7) The best solution is the one that Adam was suggesting (btw Thank you) in this same thread, doing a pg_dump -Fp on the new db and restoring it with a \i FILENAME from the old PG The only issue about this is that it doesn't treat LO's at all, but it was enough in my disaster recovery scenario. I started investigating about dumping and restoring LO's and I found a pretty interesting project under the 7.4 contrib dir, but it seems it's not maintained any more and I didn't manage to compile it. Using the lo_export - import function seems doesn't work for ALL the LO's of the database.. maybe it's possible to write a query to do that but - again - it wasn't so important to save blobs Thank you again to everybody for your help Edoardo ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] dump from 8.x restore to 7.3.x
Jim Nasby [EMAIL PROTECTED] writes: On the other hand, dumping a newer version database with an older version of *pg_dump* is much more likely to succeed. You meant to say that the other way around, no? Certainly for a jump this big I'd rather expect the older pg_dump to fail altogether. [ experiments a bit... ] Both 7.3 and 7.4 pg_dump fail to talk to an 8.1 server; they expect pg_database.datpath to exist. 8.0 pg_dump seems to do OK with 8.1 ... has some problems with 8.2 though. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] dump from 8.x restore to 7.3.x
Richard Broersma Jr wrote: My understanding is that dumps are not backwards compatible. Any dumps created with a Version 8.* pg_dump cannot be used for any older versions. If you want play it safe you should create a new server for the postgresql version 8.1 to preform validation tests with your front-ends. After you are satisfied that everything works well you can replace the version 7 production server with version 8. I don't see that as being the case - there will be exceptions where you have new features in an 8.x database that won't work in a 7.x db but otherwise it shouldn't matter, the dumps can be used to import into other databases than postgres as long as you use dump with inserts instead of copy and use common datatypes. I would expect that if you dump from 7.x into 8.x and have issues then a dump from 8.x into 7.x would work as long as you haven't changed your schema to add 8.x only features. If you have any concerns and want a fall back safety net, then you will want to either not add any 8.x features or document the changes so that the structure can be changed back to be 7.x compatible. Of course not making any schema changes until you are happy to stay with 8.x will help and you could just dump data only to go back. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] dump from 8.x restore to 7.3.x
Shane Ambler wrote: I don't see that as being the case - there will be exceptions where you have new features in an 8.x database that won't work in a 7.x db but otherwise it shouldn't matter, the dumps can be used to import into other databases than postgres as long as you use dump with inserts instead of copy and use common datatypes. Restoring dumps from new versions into old versions doesn't work (without a lot of manual work). Example cases: 8.2 dump won't install into 8.1 because it sets standard_conforming_stings, which can't be set in 8.1 8.1 dump won't install into 8.0 because it dumps users as roles, but roles don't exist in 8.0. 8.0 dump won't install into 7.4 because it dumps tablespaces, which don't exist in 7.4. etc. etc. Obviously you can see that getting pretty bad if you want to go from 8.1 all the way to 7.3. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] dump from 8.x restore to 7.3.x
Hi to all, We have a pretty big database that is going for an upgrade (PG7 - PG8) in the next few days, we have tested all the features of our application but we cannot be sure that everything will work out perfectly (db is managing several blob's only tables that have proven to be pretty hard to configure). I am currently investigating a worst-case-scenario strategy that would be to downgrade the database from PG8 to 7 if anything goes wrong;00 this implies that we: 1) dump from a running PG8 2) remove PG8 and reinstall PG7 3) restore the dump into the running PG7 unfortunately pg_restore complains about the dump header and doesn't restore the db at all, having tried to dump with both -Ft and -Fc flags shall I try the --ignore-version option in pg_restore? is there going to be any problem in using this option? (corrupted data, tables, etc.) Thanks, any help appreciated Edoardo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] dump from 8.x restore to 7.3.x
We have a pretty big database that is going for an upgrade (PG7 - PG8) in the next few days, we have tested all the features of our application but we cannot be sure that everything will work out perfectly (db is managing several blob's only tables that have proven to be pretty hard to configure). I am currently investigating a worst-case-scenario strategy that would be to downgrade the database from PG8 to 7 if anything goes wrong;00 this implies that we: 1) dump from a running PG8 2) remove PG8 and reinstall PG7 3) restore the dump into the running PG7 unfortunately pg_restore complains about the dump header and doesn't restore the db at all, having tried to dump with both -Ft and -Fc flags shall I try the --ignore-version option in pg_restore? is there going to be any problem in using this option? (corrupted data, tables, etc.) My understanding is that dumps are not backwards compatible. Any dumps created with a Version 8.* pg_dump cannot be used for any older versions. If you want play it safe you should create a new server for the postgresql version 8.1 to preform validation tests with your front-ends. After you are satisfied that everything works well you can replace the version 7 production server with version 8. If you would like a safety net beyond this you could contact one of the PostgreSQL consulting companies to walk you through the integration of V.8.1 into your projection system. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] dump from 8.x restore to 7.3.x
Hi, I've already made almost same upgrade (7.4-8.1) under Debian linux. On Debian the 8.1 database structure will be installed into /var/lib/postgresql/ by default and on 7.4 it's /var/lib/postgres/. So if you remove the packages and you (or your package manager) do not remove the databases physically you only need to reinstall the old package (without owewrite the old sturcture, (move the directory to somewhere if you want to be sure)) and the fallback will fast and safe. Szabek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] dump from 8.x restore to 7.3.x
Thank you for this infos, I wasn't sure about this, now, at least, I know that this is not possible. On the other hand, even the most experienced database programmer / administrator when upgrading a production environment can't be 100% sure about whether the application,will fail or not. Of course, testing is the only way to ensure that everything will be as smooth as possible but a real safety net would be to establish a common dump file format across different PG versions. I will keep searching ... and testing of course :) My understanding is that dumps are not backwards compatible. Any dumps created with a Version 8.* pg_dump cannot be used for any older versions. If you want play it safe you should create a new server for the postgresql version 8.1 to preform validation tests with your front-ends. After you are satisfied that everything works well you can replace the version 7 production server with version 8. If you would like a safety net beyond this you could contact one of the PostgreSQL consulting companies to walk you through the integration of V.8.1 into your projection system. Regards, Richard Broersma Jr.
