Обсуждение: PostgreSQL Downgrades
Is it possible to downgrade PostgreSQL by a major version?
If I want to upgrade a PG database from 8.x to 9.x, I have to dump the db, install the new binaries and import the dump file. This works fine going forward, but what if I need to rollback my changes? I cannot dump the 9.x database and import it with 8.x binaries, because the 9.x dump contains keywords that the old binaries don't understand (e.g. REPLICATION).
I could import the original 8.x dump file again, but then I've lost any changes made when using the new binaries.
Is there a supported way to downgrade PostgreSQL by major versions?
Thank you,
-Pete
On 4/5/2013 3:33 PM, Pete Wall wrote: > > Is there a supported way to downgrade PostgreSQL by major versions? no. -- john r pierce 37N 122W somewhere on the middle of the left coast
On 04/05/2013 03:33 PM, Pete Wall wrote: > Is it possible to downgrade PostgreSQL by a major version? > > If I want to upgrade a PG database from 8.x to 9.x, I have to dump the > db, install the new binaries and import the dump file. This works fine > going forward, but what if I need to rollback my changes? I cannot dump > the 9.x database and import it with 8.x binaries, because the 9.x dump > contains keywords that the old binaries don't understand (e.g. REPLICATION). > > I could import the original 8.x dump file again, but then I've lost any > changes made when using the new binaries. What changes, data/schema or both? > > Is there a supported way to downgrade PostgreSQL by major versions? Not that I know of. > > Thank you, > -Pete -- Adrian Klaver adrian.klaver@gmail.com
On 04/05/2013 03:33 PM, Pete Wall wrote: > Is it possible to downgrade PostgreSQL by a major version? > > If I want to upgrade a PG database from 8.x to 9.x, I have to dump the > db, install the new binaries and import the dump file. This works > fine going forward, but what if I need to rollback my changes? I > cannot dump the 9.x database and import it with 8.x binaries, because > the 9.x dump contains keywords that the old binaries don't understand > (e.g. REPLICATION). > > I could import the original 8.x dump file again, but then I've lost > any changes made when using the new binaries. > > Is there a supported way to downgrade PostgreSQL by major versions? > Is it *possible*? Yes. Assuming that you haven't made use of any features that are unavailable in the old version or made any changes to accommodate differences in the new version. Is there a *supported* way. No. You will need to do roll-your-own. It's possible that you could do a data-only dump on the new database and get away with only minor tweaks required to restore the data into an existing empty prior-version database. The feasibility depends on the nature of your data and the amount of work you are willing to do. It might help to understand the use-case underlying your interested in progressing backward. Cheers, Steve
It was the data changes I was concerned about: Any additions/deletions/modifications done on the database while it's using the 9.x binaries that should be brought back if we downgrade to the 8.x version. I think the only way would be to "manually" dump the data using custom psql commands instead of using pg_dump/pg_dumpall. Thanks, Adrian. On 4/5/13 6:35 PM, "Adrian Klaver" <adrian.klaver@gmail.com> wrote: >On 04/05/2013 03:33 PM, Pete Wall wrote: >> Is it possible to downgrade PostgreSQL by a major version? >> >> If I want to upgrade a PG database from 8.x to 9.x, I have to dump the >> db, install the new binaries and import the dump file. This works fine >> going forward, but what if I need to rollback my changes? I cannot dump >> the 9.x database and import it with 8.x binaries, because the 9.x dump >> contains keywords that the old binaries don't understand (e.g. >>REPLICATION). >> >> I could import the original 8.x dump file again, but then I've lost any >> changes made when using the new binaries. > >What changes, data/schema or both? > >> >> Is there a supported way to downgrade PostgreSQL by major versions? > >Not that I know of. > >> >> Thank you, >> -Pete > > >-- >Adrian Klaver >adrian.klaver@gmail.com
On 04/08/2013 06:41 AM, Pete Wall wrote: > It was the data changes I was concerned about: Any > additions/deletions/modifications done on the database while it's using > the 9.x binaries that should be brought back if we downgrade to the 8.x > version. > > I think the only way would be to "manually" dump the data using custom > psql commands instead of using pg_dump/pg_dumpall. You could use the -a (data only) switch to pg_dump: http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html This assumes the table layout is the same in your 9.x and 8.x databases, also that you have not used any new data types i.e JSON. > > Thanks, Adrian. > -- Adrian Klaver adrian.klaver@gmail.com
All tables and rules would be the same. I assume with the -a flag, we'd need to create the database and relations beforehand, but that shouldn't be too much trouble. Thanks again, -Pete On 4/8/13 8:46 AM, "Adrian Klaver" <adrian.klaver@gmail.com> wrote: >On 04/08/2013 06:41 AM, Pete Wall wrote: >> It was the data changes I was concerned about: Any >> additions/deletions/modifications done on the database while it's using >> the 9.x binaries that should be brought back if we downgrade to the 8.x >> version. >> >> I think the only way would be to "manually" dump the data using custom >> psql commands instead of using pg_dump/pg_dumpall. > >You could use the -a (data only) switch to pg_dump: >http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html > >This assumes the table layout is the same in your 9.x and 8.x databases, >also that you have not used any new data types i.e JSON. > >> >> Thanks, Adrian. >> > > > >-- >Adrian Klaver >adrian.klaver@gmail.com
On 04/08/2013 06:49 AM, Pete Wall wrote: > All tables and rules would be the same. I assume with the -a flag, we'd > need to create the database and relations beforehand, but that shouldn't > be too much trouble. The complementary switch to -a is -s which dumps only the schema. Might be worth it to do that and make any changes necessary rather than going through table by table. > > Thanks again, > -Pete > > On 4/8/13 8:46 AM, "Adrian Klaver" <adrian.klaver@gmail.com> wrote: > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes: > On 04/08/2013 06:41 AM, Pete Wall wrote: >> I think the only way would be to "manually" dump the data using custom >> psql commands instead of using pg_dump/pg_dumpall. > You could use the -a (data only) switch to pg_dump: > http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html If there's a lot of data, the best way would be to take separate --schema-only and --data-only dumps. You will very possibly have to hand-edit the schema commands to get rid of syntax that's only understood by the newer server, and so keeping the data in a separate file helps keep from giving your text editor indigestion. Keep editing till you have a schema file that loads without complaint, and then you can load the data file after it. When dumping from 9.2 or newer, it'll be worth your trouble to refine that strategy by using pg_dump's new "--section" switch to split the dump file three ways: pre-data, data, post-data. The first and last of these contain the stuff you'd likely need to edit. The advantage of this over a simple schema-vs-data split is that the restore will go faster because it puts the commands in the most efficient order, in particular putting index and constraint creation commands after the data load. BTW, don't forget "pg_dumpall -g" in addition to following the above recipe for each individual database in the installation. regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Tom Lane wrote: > When dumping from 9.2 or newer, it'll be worth your trouble to refine > that strategy by using pg_dump's new "--section" switch to split the > dump file three ways: pre-data, data, post-data. And if you have a dump from 9.1 or older, you can use this script to divide it up into the same sections: http://bucardo.org/wiki/Split_postgres_dump - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201304081134 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlFi45wACgkQvJuQZxSWSsgfEgCgoRowHCFoVFEWC3VXK1YofFaS 9hYAoJuBpOFpWLoyW6HPLcMnl0Akfu8f =LmJS -----END PGP SIGNATURE-----