What pg_restore does to a non-empty target database

From: "Ken Winter" <ken(at)sunward(dot)org>
To: "'PostgreSQL pg-general List'" <pgsql-general(at)postgresql(dot)org>
Subject: What pg_restore does to a non-empty target database
Date: 2008-01-14 00:25:47
Message-ID: 002401c85644$02d22330$6703a8c0@KenIBM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I need to understand, in as much detail as possible, the results that will
occur when pg_restore restores from an archive file into a target database
that already contains some database objects. I can't find any reference
that spells this out. (The PG manual isn't specific enough.)

Instead of just asking questions, I decided to make my best guess about the
answers (below), and ask you to confirm, refute, and correct my guesses
until this becomes a complete and accurate statement. If I have left out
any conditions that would affect the results, please add them into the
account. A definitive version of this story might be worth posting in some
more permanent and visible place than an e-mail thread.

In case it matters, I'm currently working with PostgreSQL 8.0. I don't know
if the truth I'm seeking here is version-dependent. Also, I'm assuming the
archive is in compressed format. I don't know how different the story would
be if the archive were in a different format.

~ TIA
~ Ken

Given a pg_restore command (possibly filtered and reordered by a ToC file),
where:
* A is the source archive file (as filtered and reordered by the ToC file,
if any)
* T is the target database
* O is a database object (table, function, etc) that exists in A
and/or in C

The following are the changes that the pg_restore will produce in T.

If object O exists in both A and T:
If the command says "--clean":
T's version of O is dropped
A's version of O is created
Else:
T's version of O is left unchanged
If object O exists in T but not in A:
T's version of O is left unchanged
If object O exists in A but not in T:
A's version is created

Suppose in addition that O is a data object (a table or sequence) that is
defined by the database schema and contains data in both A and T.

If the command says "--data-only":
T's schema definition of O is left unchanged
T's O data are deleted
A's O data are inserted
If the command says "--schema-only":
T's schema definition of O is dropped
T's O data are deleted (as a side-product of the drop)
A's schema definition of O is created
No O data are inserted
If the command says "--data-only" and "--schema-only":
T's schema definition of O is left unchanged
T's O data are left unchanged
In other words, nothing changes
If the command says neither "--data-only" nor "--schema-only":
T's schema definition of O is dropped
T's O data are deleted (as a side-product of the drop)
A's schema definition of O is created
A's O data are inserted
In other words, A's version of O entirely replaces T's version

Suppose in addition that the command says "--data-only", it doesn't say
"--exit-on-error", and T's schema definition of O is different from A's.

If T's schema includes a column O.C that does not exist in A's schema:
A's O data are inserted, and O.C is Null in all rows
If A's schema includes a column O.C that does not exist in T's schema:
A's O data are inserted, but A's values of O.C are lost
If T's schema includes a constraint K that does not exist in A's schema:
A's O data are inserted, except for those that violate K
If A's schema includes a constraint K that does not exist in T's schema:
A's O data are all inserted

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-01-14 00:31:08 Re: Index trouble with 8.3b4
Previous Message Tom Lane 2008-01-14 00:22:24 Re: tsearch2 install on Fedora Core 5 problems