Re: Stuck trying to backup large database - best practice?

On Mon, Jan 12, 2015 at 7:08 PM, Adrian Klaver
<adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 01/12/2015 08:40 AM, Antony Gelberg wrote:
>>
>> On Mon, Jan 12, 2015 at 6:23 PM, Adrian Klaver
>> <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>>
>>> On 01/12/2015 08:10 AM, Antony Gelberg wrote:
>>>>
>>>> On Mon, Jan 12, 2015 at 5:31 PM, Adrian Klaver
>>>> <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>> pg_basebackup has additional features which in your case are creating
>>> issues. pg_dump on the other hand is pretty much a straight forward data
>>> dump and if you use -Fc you get compression.
>>
>>
>> So I should clarify - we want to be able to get back to the same point
>> as we would once the WAL was applied. If we were to use pg_dump,
>> would we lose out in any way?
>
>
> pg_dump does not save WALs, so it would not work for that purpose.
>
> Appreciate insight as to how
>>
>> pg_basebackup is scuppering things.
>
>
> From original post it is not entirely clear whether you are using the -X or -x options. The command you show does not have them, but you mention -Xs. In any case it seems wal_keep_segments will need to be bumped up to keep WAL segments around that are being recycled during the backup process. How much will depend on a determination of fast Postgres is using/recycling log segments? Looking at the turnover in the pg_xlog directory would be a start.

The original script used -xs, but that didn't make sense, so we used
-Xs in the end, but then we cancelled the backup as we assumed that we
wouldn't have enough space for it uncompressed. Did we miss
something?

I think your suggestion of looking in pg_xlog and tweaking
wal_keep_segments is interesting, we'll take a look, and I'll report
back with findings.

Thanks for your very detailed help.

Antony