Re: PostgreSQL Downgrades

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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