<div dir="ltr">So what's the best way to deal with very large databases. pg_dump just doesn't cut it. By the time we are finished dumping the data is stale anyway !<div><br></div><div>I'm thinking of using PITR instead. Rolling over once a week</div></div><div class="gmail_extra"><br clear="all"><div>Dave Cramer</div>
<br><div class="gmail_quote">On 17 October 2014 08:51, Jan Wieck <span dir="ltr"><<a href="mailto:jan@wi3ck.info" target="_blank">jan@wi3ck.info</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="HOEnZb"><div class="h5">On 10/16/2014 11:48 AM, Glyn Astill wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
From: Glyn Astill <<a href="mailto:glynastill@yahoo.co.uk" target="_blank">glynastill@yahoo.co.uk</a>><br>
</blockquote>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
To: Dave Cramer <<a href="mailto:davecramer@gmail.com" target="_blank">davecramer@gmail.com</a>><br>
Cc: slony <<a href="mailto:slony1-general@lists.slony.info" target="_blank">slony1-general@lists.slony.<u></u>info</a>><br>
Sent: Thursday, 16 October 2014, 16:26<br>
Subject: Re: [Slony1-general] Lag time increasing but there are no events<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
From: Dave Cramer <<a href="mailto:davecramer@gmail.com" target="_blank">davecramer@gmail.com</a>><br>
To: Glyn Astill <<a href="mailto:glynastill@yahoo.co.uk" target="_blank">glynastill@yahoo.co.uk</a>><br>
Cc: slony <<a href="mailto:slony1-general@lists.slony.info" target="_blank">slony1-general@lists.slony.<u></u>info</a>><br>
Sent: Thursday, 16 October 2014, 15:41<br>
Subject: Re: [Slony1-general] Lag time increasing but there are no events<br>
<br>
<br>
<br>
Actually I think it is because a pg_dump of the db is going on.<br>
<br>
<br>
Can you dump a slave ?<br>
<br>
<br>
Do you have to exclude the slony clusters (which would make sense)?<br>
</blockquote>
<br>
<br>
Yeah from past experience the locks taken by pg_dump cause replication to lag. I<br>
normally use the -N switch in pg_dump to exclude the schema. For pg_restore I<br>
usually create a TOC with the -l option and pipe it to grep to filter out the<br>
references to the slony schema that get dumped for the triggers, and then -L<br>
option to use it with my restore.<br>
</blockquote>
<br>
Also I assumed you were using 2.0+ there, with 1.2 there's some slight fiddling done in pg_catalog to disable triggers, from memory pg_trigger.tgrelid is pointed to an index rather than the table or something like that. I used to dump out with the slony schema, and run uninstallnode() and drop schema cascade against the restored database.<br>
</blockquote>
<br></div></div>
Dumping a replica won't work <2.0 because the dump won't contain all the information. With recent PG versions it will actually error out. With 2.0+ it does work, so with 2.0+ it is possible to take the backup from a replica.<br>
<br>
Dave: A pg_dump can very well stop the creation of new SYNC events since slon needs a brief exclusive lock on the sl_event table. If that is the case, the st_last_event in sl_status will not be advancing as if no slon would be running, which would explain the increase of st_lag_time with zero st_lag_num_events.<br>
<br>
As suggested, dumping the database with -N to exclude the Slony-I schema should do the trick and dumping it has very little value anyway. If you ever have to restore from that dump, you'd either have to restore all replicas from it as well and rebuild the cluster with OMIT COPY, or rebuild the cluster with Slony copying the data. You would need to do the latter if replicas aren't full copies of the entire master.<br>
<br>
<br>
Regards,<br>
Jan<span class="HOEnZb"><font color="#888888"><br>
<br>
-- <br>
Jan Wieck<br>
Senior Software Engineer<br>
<a href="http://slony.info" target="_blank">http://slony.info</a><br>
</font></span></blockquote></div><br></div>