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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
