Thread: pg_restore ordering questions

Background:
I am preparing to collapse two databases in a cluster into a single
database (current step in a roadmap preparing to update a system from
7.4.6 up to 8.4.1).

In testing, I have a step that dumps the schema of one of the databases,
creates an object list, removes the unwanted objects, then restores the
schema into an empty database. This step is failing due to attempts to
restore views/indexes prior to creation of the referenced table. I have
stripped it down to the minimum and it still fails:

pg_dump -U postgres --schema-only -Fc thedb > thedb.dump
pg_restore -U postgres -d thedb thedb.dump

My overall testing has raised several questions:

1. Is this a known issue in 7.4.6? I've Googled and searched the docs
but haven't yet located it if so.

2. What is the default order of the --list option? (I noted that the
--rearrange and --list options can be simultaneously applied to
pg_restore but the generated list is identical with/without --rearrange
so is the default list order the same as --rearrange would create?) If
used unchanged as the input to --use-list should the restore succeed?

3. It appears that if --use-list and --rearrange are both used, the
order will be determined by --rearrange rather than the order of the
list. Is this correct?

4. The recommended upgrade procedure is to use pg_dump from the newer
PostgreSQL version. But is it safe to use an up-to-date
pg_dump/pg_restore on an old version of PostgreSQL?

5. If this is a known issue in 7.4.6, is there a good workaround?

Cheers,
Steve

Steve Crawford <scrawford@pinpointresearch.com> writes:
> I am preparing to collapse two databases in a cluster into a single
> database (current step in a roadmap preparing to update a system from
> 7.4.6 up to 8.4.1).

> In testing, I have a step that dumps the schema of one of the databases,
> creates an object list, removes the unwanted objects, then restores the
> schema into an empty database. This step is failing due to attempts to
> restore views/indexes prior to creation of the referenced table. I have
> stripped it down to the minimum and it still fails:

I think that we didn't teach pg_dump about dependency ordering until 8.0
or so.  If you're unlucky enough to hit this in 7.4, you have to fix it
via manual reordering of the dump items.

> 4. The recommended upgrade procedure is to use pg_dump from the newer
> PostgreSQL version. But is it safe to use an up-to-date
> pg_dump/pg_restore on an old version of PostgreSQL?

No, the dump typically won't load into an older server, at least not
without some manual editing to correct uses of newer syntax.

Why don't you update to 8.4 first and then do your other housekeeping?

            regards, tom lane