pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedChanging column order through dump and restore
3+ messages / 2 participants
[nested] [flat]
* Changing column order through dump and restore
@ 2016-06-27 13:49 Alex Stanier <Alex.stanier@proactis.com>
2016-06-27 20:56 ` Re: Changing column order through dump and restore Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Alex Stanier @ 2016-06-27 13:49 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Changing column order through dump and restore
2016-06-27 13:49 Changing column order through dump and restore Alex Stanier <Alex.stanier@proactis.com>
@ 2016-06-27 20:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-06-28 08:10 ` Re: Changing column order through dump and restore Alex Stanier <Alex.stanier@proactis.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2016-06-27 20:56 UTC (permalink / raw)
To: Alex Stanier <Alex.stanier@proactis.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Changing column order through dump and restore
2016-06-27 13:49 Changing column order through dump and restore Alex Stanier <Alex.stanier@proactis.com>
2016-06-27 20:56 ` Re: Changing column order through dump and restore Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-06-28 08:10 ` Alex Stanier <Alex.stanier@proactis.com>
0 siblings, 0 replies; 3+ messages in thread
From: Alex Stanier @ 2016-06-28 08:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 27 June 2016 21:56
> To: Alex Stanier <Alex.stanier@proactis.com>
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] 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
Great thank you. Useful to know about the COPY; it has speeded the process up dramatically.
Regards,
Alex Stanier.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-06-28 08:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-06-27 13:49 Changing column order through dump and restore Alex Stanier <Alex.stanier@proactis.com>
2016-06-27 20:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-06-28 08:10 ` Alex Stanier <Alex.stanier@proactis.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox