How to restore pgdumpall data

bilalcochin · 02-21-2010, 06:18 AM

Hello,

I regularly take pgdumpall from production database and restore the staging database.
Production database size is 4.5 GB.
After restoring the staging database size is incremented.

The following command is used to restore pgdumpall file.

psql -U postgres -f dbdumpall

My aim is to sync up production database and staging database.

Any solution?

Thanks

Tinkster · 02-21-2010, 12:05 PM

You'll be wanting to look at something like maybe DBI-link
or a solution like Slony. With pgdumpall you can't do anything
that resembles incremental backups, and if you have any form
of constraints on the target database the import will fail.

You could conceivably use a user-defined function that skips
insert when the target row exists, but I'd expect that to take
a rather long time to complete given the size of your DB.

Cheers,
Tink

bilalcochin · 02-22-2010, 03:37 AM

Hi Tinkster,

Thanks for the reply.
How to set up a slony in production servers without down time?

Thanks

Tinkster · 02-22-2010, 10:43 AM

I'd recommend reading their documentation, but I think it can't
be done w/o any downtime at all ... in a mission-critical
environment this wouldn't be very good.

The next best approach would be to see whether you can export
data on criteria like the age of a record or something like
that - of course you haven't given us no indication as to what
the nature of the data is, the complexity of the data and
relationships between tables.