Re: Changing column order through dump and restore

Alex Stanier <Alex.stanier@proactis.com> writes:
> # Dump the database (schema only)
> pg_dump -s my_database > my_database_schema.dmp
> # Dump the database (data only) as insert statements and with commands to disable triggers
> pg_dump -a --disable-triggers --column-inserts my_database > my_database_data.dmp

> I understand the data restore will be slow (due to the inefficiency of
> the INSERT statements), but is this a valid way to go about column
> re-ordering?

FWIW, you shouldn't need to use --column-inserts mode, because even in
default COPY output, pg_dump emits column name lists in the COPY commands.
So the right things should happen when reloading into a database with
modified column order.

Haven't thought about it in great detail, but your plan sounds generally
sane otherwise.

            regards, tom lane