Thread: Database Backup/Restore with Inherited Tables

Postgres 7.2.3

The application I am working had a database schema utilizing table
inheritance. As the application changes so does our schema and recently
modifications where made to these tables. For existing customers I
generally write an upgrade script to migrate the schema. In this case it
only involved adding a few new columns to the base table.

Customers are provided utilities to backup and restore their database as
they see fit. The problem arose when this migrated database was backed
up and restored. When the base table was altered the new columns were
added to the end of the inherited table automatically( base table
columns, inherited table additional columns, new base table columns).
But if you backup and restore the schema changes to base table, new base
table columns then inherited table additional columns. Unfortunately the
data is in the other order.

I've pulled apart the pg_dump file and I understand what is happening I
would like any suggestions on how to migrate my schema so the
backup/restore will work.


Dawn

Dawn Hollingsworth <dmh@airdefense.net> writes:
> The problem arose when this migrated database was backed
> up and restored. When the base table was altered the new columns were
> added to the end of the inherited table automatically( base table
> columns, inherited table additional columns, new base table columns).
> But if you backup and restore the schema changes to base table, new base
> table columns then inherited table additional columns. Unfortunately the
> data is in the other order.

Yeah, this is a longstanding gotcha with adding columns to parent tables
:-(.  It's solved in 7.3 by explicitly specifying the column order in
each COPY command issued by pg_dump.  If you are trying to migrate to
7.3 then the solution is to use 7.3's pg_dump against the 7.2 server to
produce the dump.  If you need to transport data within 7.2 or before,
I think the only simple answer is to use pg_dump's -D option so that the
data is dumped as explicitly labeled INSERTs.  It's a lot slower but it
will work.

            regards, tom lane