Thanks for your thoughts, Glyn.<div><br></div><div>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.<div>
<br></div><div><br><div class="gmail_quote">On Wed, Oct 10, 2012 at 2:02 AM, Glyn Astill <span dir="ltr"><<a href="mailto:glynastill@yahoo.co.uk" target="_blank">glynastill@yahoo.co.uk</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="im HOEnZb">> From: Ryan Oblak <<a href="mailto:rroblak@gmail.com">rroblak@gmail.com</a>><br>
>Subject: [Slony1-general] Staging/reporting replication<br>
><br>
</div><div class="HOEnZb"><div class="h5">>Hi,<br>
><br>
><br>
>I'd<br>
like to replicate my production database to 2 separate Postgres<br>
instances --- one for reporting and one for staging. The main<br>
requirement is that the data needs be current up to the state of the<br>
production db the day before. <br>
><br>
><br>
>From what I've<br>
read it seems like using an asynchronous replication solution (i.e.<br>
slony) or just a simple pg_dump/pg_restore script would work. Ideally<br>
I'd like to have as little additional load on the production server as<br>
possible. Also, the production db is currently at 12GB (and growing<br>
steadily), so I'm a bit concerned that doing a full pg_restore every day<br>
might become infeasible at some point.<br>
><br>
><br>
>Any thoughts?<br>
><br>
><br>
>Thanks,<br>
>Ryan<br>
<br>
<br>
</div></div><div class="HOEnZb"><div class="h5">If<br>
you want to have a slave lag behind you can pass the "lag_interval"<br>
parameter to the slon, or set it in the slon.conf you're passing. If<br>
you needed the data to be a point in time you could probably do that<br>
with slonys log shipping. This would of course be a read only replica.<br>
<br>
I'd<br>
guess a slony slave would be best for your reporting, but for a staging<br>
environment you might want to just go with a dump and restore.<br>
<br>
Here<br>
I've a couple of servers (old dell 1850s with md raid, hopefully to be<br>
upgraded soon) that restore our latest backups daily in the early hours,<br>
the database is 160Gb at present and it takes 4 hours including checks<br>
to ensure the backups are good and a database wide vacuum analyse. This<br>
serves two purposes; to check the backups are viable and once done<br>
provide a staging environment for our devs.<br>
<br>
Glyn<br>
</div></div></blockquote></div><br></div></div>