Hi,
I want to know if the "pg_restore -a" (data only, no schema) function
is a good method to restore data from a backup into an existing
database which already has data in its tables (and ensuring that
existing data is preserved).
I've done a simple test by backing up the database using pg_dump and
the custom format, then changing the primary keys of the tables to be
different (update TABLE set ID = nextval('SEQ')), then 'pg_restore -a'
and it looks like the data restores fine. During the restoration
period I was able to perform normal CRUD operations on existing rows.
Has anyone had experience doing such an activity on a production
database? i.e. Restoring tables of 50GB and up? Should I expect
problems with this method - are there any tricks to be aware of?
Potential data inconsistencies? The only other option I can see is
'dump as inserts' but I'd prefer to avoid the disk overhead of such a
verbose backup file, plus COPY is faster than INSERT.
I'm going to continue my testing on some larger data sets, but would
appreciate if anyone already has some insights about it.
Regards,
Mike
