Changing column order through dump and restore

From: Alex Stanier <Alex(dot)stanier(at)proactis(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Changing column order through dump and restore
Date: 2016-06-27 13:49:11
Message-ID: HE1PR0501MB2363159ACDAE1244C0C2E636FC210@HE1PR0501MB2363.eurprd05.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello All,

I have a database in which I want to re-order some of the columns to bring it into line with our standard schema. The problem is that, although the database does not have too much data, the tables I want to change have tons of dependencies on top of them (mostly layered views). First I tried to remove those dependencies, recreate the tables and then put them back again, but I started to get into a mire with all the dependencies. So to avoid having to work out all those dependencies, I thought perhaps I could do it instead by dumping the whole schema and data separately as SQL files, then tweaking CREATE TABLE commands in the schema file, then reloading schema into a fresh database and then finally reloading the data. This way I think the dump and restore ought to take care of the dependencies for me. Currently my script looks like this:

# 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

# Change the order of the columns in the relevant CREATE TABLE commands
vim my_database_schema.dmp

# Create a fresh database
createdb my_new_database
# Restore the corrected schema into the new database
psql my_new_database < my_database_schema.dmp > schema_import.log 2>&1
# Restore the data into the new database
psql my_new_database < my_database_data.dmp > data_import.log 2>&1

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? Will this achieve what I want (no more, no less)? If so, is there anything else I need to consider or beware of?

I would be grateful for any advice and/or reassurance. I am using PG 9.2.4 (upgrade scheduled soon!)

Regards,
Alex.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alex Malek 2016-06-27 18:27:32 Re: problems using pg_start_backup/pg_stop_backup and pg_basebackup at same time
Previous Message Anonymous Anonymous 2016-06-25 06:41:29 Fwd: Need older (9.6~beta1-2.pgdg16.04+1) postgres beta packages