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

On 01/12/2015 02:16 PM, Antony Gelberg wrote:
> 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?

Not sure missed as much as not fully understand:) When you use either -x
or -X you are telling pg_basebackup that you care that the WAL files in
the backup directory are update to the point the backup completed. If
you use -Xf which the same as -x then you are saying wait till the rest
of the backup is finished then collect and copy over all the relevant
WAL files. This is where wal_keep_segments comes into play. It needs to
be set high enough that relevant WAL files in place at the beginning of
the backup are still there when the backup completes in order to have a
complete set. If you use -Xs, then a parallel process is started to copy
over the WAL files while the other data files are being copied over.
Though as the docs say:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

"As long as the client can keep up with transaction log received, using
this mode requires no extra transaction logs to be saved on the master."

So it is possible for the client to fall behind and have a WAL file be
recycled before it can be transferred. If you are experiencing this then
again wal_keep_segments is way of forcing Postgres to keep WAL files
around. The basic concept is that by default WAL files are recycled when
they fall out of scope on the primary and so you have to 'catch' them
before they do or force them to hang around.

Compression is a separate operation and applies only in the tar format
case and should not be affected by the -x(X) options.

If it where me I would start looking at another 'machine' to offload the
backup to. Otherwise you will be looking at increasingly convoluted
methods of getting two bodies to occupy one space.

>
> 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
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com