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
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 |