Re: Changing column order through dump and restore

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 27 June 2016 21:56
> To: Alex Stanier <Alex(dot)stanier(at)proactis(dot)com>
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] Changing column order through dump and restore
>
> Alex Stanier <Alex(dot)stanier(at)proactis(dot)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

Great thank you. Useful to know about the COPY; it has speeded the process up dramatically.

Regards,
Alex Stanier.