Re: Database Backup/Restore with Inherited Tables

Dawn Hollingsworth <dmh(at)airdefense(dot)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