Thread: PostgreSQL Downgrades

PostgreSQL Downgrades

From
Pete Wall
Date:
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

Re: PostgreSQL Downgrades

From
John R Pierce
Date:
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



Re: PostgreSQL Downgrades

From
Adrian Klaver
Date:
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


Re: PostgreSQL Downgrades

From
Steve Crawford
Date:
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



Re: PostgreSQL Downgrades

From
Pete Wall
Date:
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



Re: PostgreSQL Downgrades

From
Adrian Klaver
Date:
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


Re: PostgreSQL Downgrades

From
Pete Wall
Date:
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



Re: PostgreSQL Downgrades

From
Adrian Klaver
Date:
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


Re: PostgreSQL Downgrades

From
Tom Lane
Date:
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


Re: PostgreSQL Downgrades

From
"Greg Sabino Mullane"
Date:
-----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-----