Thanks for your thoughts, Glyn.

I like the idea of using the staging database to check backups in addition
to staging. Restoring sounds like it could be a viable option for both
reporting and staging if 160Gb only takes 4 hours. Though, of course, my
mileage may vary as I'm using virtualized servers.


On Wed, Oct 10, 2012 at 2:02 AM, Glyn Astill <glynastill at yahoo.co.uk> wrote:

> > 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20121010/240e1441/attachment.htm