Re: What pg_restore does to a non-empty target database

"Ken Winter" <ken(at)sunward(dot)org> writes:
> 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.)

It's not nearly as clean as you are hoping, unless you use --clean.

When you don't, the restore will just try to issue the creation
commands. Obviously the initial CREATE for a conflicting object will
fail due to a name collision, but in some cases the script involves a
CREATE followed by various ALTERs, which might or might not succeed
against the object that was there before. And don't forget that any
data to be inserted into a table will be appended to what was there
before, assuming that the existing table is close enough to the schema
of the source table for this to succeed.

You could run the restore in exit-on-error mode, which would ensure
no damage to existing objects, but then any new objects following the
first error wouldn't get loaded.

We don't (and won't) specify either the order in which pg_dump dumps
unrelated objects or the exact breakdown of CREATE/ALTER operations,
so the details of what would happen are very subject to change across
releases.

Bottom line: best use --clean if you want at-all-predictable behavior
for this scenario.

> 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

In no case (other than --clean) will the restore attempt to remove any
existing data. None of the other fancy stuff you have invented out of
thin air will happen, either.

One control you have over what happens in a data-only restore is to dump
the data as COPY (one command per table) or as INSERTs (one command per
row). If you use COPY, and there's any error in the incoming data for a
table, then none of it gets inserted; while with INSERTs each row
succeeds or fails independently. I can see uses for each behavior.

regards, tom lane