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

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:
>>>
>>> <some snippage>
>>>
>>> On Mon, Jan 12, 2015 at 5:31 PM, Adrian Klaver
>>> <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>>>
>>>>
>>>> On 01/12/2015 07:20 AM, Antony Gelberg wrote:
>>>>>
>>>>>
>>>>> pg_basebackup: could not get transaction log end position from server:
>>>>> ERROR: requested WAL segment 0000000400002B9F000000B4 has already been
>>>>> removed
>>>>>
>>>>> This attempted backup reached 430GB before failing.
>>>>
>>>>
>>>>
>>>> It fails because the WAL file it needs has been removed from under it.
>>>>
>>>
>>> Okay. We simply understood that it took too long. Clearly we have a
>>> lot to learn about WAL and its intricacies.
>>
>>
>> See here:
>>
>> http://www.postgresql.org/docs/9.4/interactive/wal.html
>>
>
> Of course we read the docs before asking here, but really learning
> about a subject comes with time. :)
>
>>>
>>>>> We were advised on IRC to try -Xs, but that only works with a plain
>>>>> (uncompressed) backup, and as you'll note from above, we don't have
>>>>> enough disk space for this.
>>>>>
>>>>> Is there anything else we can do apart from get a bigger disk (not
>>>>> trivial at the moment)? Any best practice?
>>>>
>>>>
>>>> What is the purpose of the backup?
>>>>
>>>> In other words do really want the data and the WALs together or do you
>>>> just want the data?
>>>
>>>
>>> No, we just want to be able to restore our data at a later point. (As
>>> as secondary point, it's not that clear to me why it would be useful
>>> to have both, I'd be interested for some insight.)
>>
>>
>> Seems you may be better served by pg_dump:
>>
>> http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html
>>
>> 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.

>
>> Something I failed to ask in my previous post, how are you determining the
>> size of the database?
>
> It's a managed server - the hosting company told us it was 1.8TB. I
> just ran the query at
> http://stackoverflow.com/questions/2596624/how-do-you-find-the-disk-size-of-a-postgres-postgresql-table-and-its-indexes,
> and I don't have the total, but I'd say the actual table data is less,
> nearer 1TB at a quick glance.
>
>> In addition are you talking about a single database or the Postgres database
>> cluster?
>>
>
> We only have one database in the cluster, so it's the same thing.
>
> Antony
>

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