PostgreSQL Downgrades

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----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-----

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general