> From: Ryan Oblak <rroblak at gmail.com>
>Subject: [Slony1-general] Staging/reporting replication
> 
>Hi,
>
>
>I'd
 like to replicate my production database to 2 separate Postgres 
instances --- one for reporting and one for staging. The main 
requirement is that the data needs be current up to the state of the 
production db the day before. 
>
>
>From what I've 
read it seems like using an asynchronous replication solution (i.e. 
slony) or just a simple pg_dump/pg_restore script would work. Ideally 
I'd like to have as little additional load on the production server as 
possible. Also, the production db is currently at 12GB (and growing 
steadily), so I'm a bit concerned that doing a full pg_restore every day
 might become infeasible at some point.
>
>
>Any thoughts?
>
>
>Thanks,
>Ryan


If
 you want to have a slave lag behind you can pass the "lag_interval" 
parameter to the slon, or set it in the slon.conf you're passing.  If 
you needed the data to be a point in time you could probably do that 
with slonys log shipping.  This would of course be a read only replica.

I'd
 guess a slony slave would be best for your reporting, but for a staging
 environment you might want to just go with a dump and restore.

Here
 I've a couple of servers (old dell 1850s with md raid, hopefully to be 
upgraded soon) that restore our latest backups daily in the early hours,
 the database is 160Gb at present and it takes 4 hours including checks 
to ensure the backups are good and a database wide vacuum analyse.  This
 serves two purposes; to check the backups are viable and once done 
provide a staging environment for our devs.

Glyn