"Ken Winter" <ken@xxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
