Re: Restore data to an existing populated table

Renato Oliveira <renato.oliveira@grant.co.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