pg_restore ordering questions

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_restore ordering questions
Date: 2009-09-25 22:15:29
Message-ID: 4ABD4101.703@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-09-25 22:31:24 Re: stored procedure: RETURNS record
Previous Message Tom Lane 2009-09-25 21:53:28 Re: lazy vacuum and AccessExclusiveLock