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 !

I'm thinking of using PITR instead. Rolling over once a week

Dave Cramer

On 17 October 2014 08:51, Jan Wieck <jan at wi3ck.info> wrote:

> On 10/16/2014 11:48 AM, Glyn Astill wrote:
>
>> From: Glyn Astill <glynastill at yahoo.co.uk>
>>>
>>
>>  To: Dave Cramer <davecramer at gmail.com>
>>> Cc: slony <slony1-general at lists.slony.info>
>>> Sent: Thursday, 16 October 2014, 16:26
>>> Subject: Re: [Slony1-general] Lag time increasing but there are no events
>>>
>>>
>>>   From: Dave Cramer <davecramer at gmail.com>
>>>> To: Glyn Astill <glynastill at yahoo.co.uk>
>>>> Cc: slony <slony1-general at lists.slony.info>
>>>> Sent: Thursday, 16 October 2014, 15:41
>>>> Subject: Re: [Slony1-general] Lag time increasing but there are no
>>>> events
>>>>
>>>>
>>>>
>>>> Actually I think it is because a pg_dump of the db is going on.
>>>>
>>>>
>>>> Can you dump a slave ?
>>>>
>>>>
>>>> Do you have to exclude the slony clusters (which would make sense)?
>>>>
>>>
>>>
>>> Yeah from past experience the locks taken by pg_dump cause replication
>>> to lag. I
>>> normally use the -N switch in pg_dump to exclude the schema.  For
>>> pg_restore I
>>> usually create a TOC with the -l option and pipe it to grep to filter
>>> out the
>>> references to the slony schema that get dumped for the triggers, and
>>> then -L
>>> option to use it with my restore.
>>>
>>
>> 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.
>>
>
> 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.
>
> 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.
>
> 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.
>
>
> Regards,
> Jan
>
> --
> Jan Wieck
> Senior Software Engineer
> http://slony.info
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20141017/0a3e600a/attachment.htm