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

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? Appreciate insight as to how
pg_basebackup is scuppering things.

> 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