Re: Restore data to an existing populated table - Mailing list pgsql-admin

From Kevin Grittner
Subject Re: Restore data to an existing populated table
Date
Msg-id 4BBDC43802000025000305A9@gw.wicourts.gov
Whole thread Raw
In response to Restore data to an existing populated table  (Renato Oliveira <renato.oliveira@grant.co.uk>)
List pgsql-admin
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

pgsql-admin by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: WARM standby with pg_standby
Next
From: David Bear
Date:
Subject: failure on system update for lack of gpg key