What pg_restore does to a non-empty target database - Mailing list 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