On 10/17/2014 09:00 AM, Dave Cramer wrote:
> 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

basebackup or some combination of pg_start_backup() and rsync will work. 
You can do that against master or replica and do the UNINSTALL NODE in 
case you have to use it.


Jan

>
> Dave Cramer
>
> On 17 October 2014 08:51, Jan Wieck <jan at wi3ck.info
> <mailto:jan at wi3ck.info>> wrote:
>
>     On 10/16/2014 11:48 AM, Glyn Astill wrote:
>
>             From: Glyn Astill <glynastill at yahoo.co.uk
>             <mailto:glynastill at yahoo.co.uk>>
>
>
>             To: Dave Cramer <davecramer at gmail.com
>             <mailto:davecramer at gmail.com>>
>             Cc: slony <slony1-general at lists.slony.__info
>             <mailto: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
>                 <mailto:davecramer at gmail.com>>
>                 To: Glyn Astill <glynastill at yahoo.co.uk
>                 <mailto:glynastill at yahoo.co.uk>
>                 Cc: slony <slony1-general at lists.slony.__info
>                 <mailto: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
>
>


-- 
Jan Wieck
Senior Software Engineer
http://slony.info