Renato Oliveira <renato(dot)oliveira(at)grant(dot)co(dot)uk> wrote:
> I would like to know if it is possible to restore data to a
> pre-populated db/table.
Yes.
> Does that mean the table can exist and can be populated with
> data, pg_restore will append the data to the db/table without
> deleting/dropping the existing data?
Unless you explicitly use the "clean" option, yes.
> What I need to achieve is:
> 1 - Download the schema from an old DB
pg_dump -s
> 2 - Restore the schema to a new DB
psql or pg_restore (depending on dump format)
> 3 - Point my application to the new DB, it will populate with new
> data
> 4 - at later date do a pg_dump to dump the data from old DB -
> (Should I use 'COPY' statement instead of pg_dump?
pg_dump -a
(It will use COPY statements.)
> 5 - Restore the data only to the new DB - as in copying the data
> and appending it to existing db/tables.
psql or pg_restore (depending on dump format)
(You'll have problems if there are duplicates on primary key or
unique indexes or constraints.)
> What is the natural behaviour of pg_restore, does it wipe the
> existing data on the existing DB, or it displays 'errors' and
> carries on copying the data?
It never destroys data unless you explicitly tell it to do so. If
it hits and error attempting to add data to a table (due to schema
mismatch or duplicate rows, for example) the entire copy in to the
table fails.
I hope this helps.
-Kevin