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

​​

On Tue, Jan 13, 2015 at 7:04 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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

​
​Wouldn't streaming the WAL files
​ (in a parallel process, which is what wal-stream option in pg_basebackup
does)​
during the backup resolve the original issue he faced
​ with WAL being removed​
? ​

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

​But I guess if you take backup on a 2nd machine, the WAL or data-files are
not compressed before sending them over network.​

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

​
+1​

​I agree...​

Best Regards,

*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com

*[image: icons]*

[image: Email patch] <http://www.ashnik.com/>

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).