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

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

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

Something I failed to ask in my previous post, how are you determining
the size of the database?

In addition are you talking about a single database or the Postgres
database cluster?

>
> Antony
>
>

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