Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL

Hi Able,

Please see the reply in-line:

Thanks,
Patrick Dung

________________________________
From: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: Patrick Dung <patrick_dkt(at)yahoo(dot)com(dot)hk>; "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Sent: Friday, August 23, 2013 4:14 PM
Subject: Re: [ADMIN] several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL

>Patrick Dung wrote:
>> I have some questions about backup on PostgreSQL:
>>
>> 1) pg_dumpall
>> I am sure that this is asked in somewhere: why the pg_dumpall does not support custom or tar format
>> like pg_dump.
>> I have heard that the custom format could be faster and may generate a smaller dump file.
>> Is the feature enhancement being developed?
>
>I suspect that the reason is that pg_dumpall just calls pg_dump
>and concatenates the output, and that probably only works
>in text format.

I would like to see the pg_dumpall support the custom/tar format.

>> 2) pg_start_backup in postgresql 8.4
>> After issuing pg_start_backup, we can take the file based backup (by tar/snapshot) on the postgresql
>> data directory.
>> If the use of file system snapshot is not possible.
>
>I don't understand this sentence.

After calling pg_start_backup, we can make backup by tar on PGDATA root directory.
Or we can take file system snapshot on PGDATA partition and make the backup on the snapshot.
I suppose the latter one is more consistent than the first one.

>> It is possible that there is file changes (added or file size changed) between the pg_start_backup and
>> pg_stop_backup.
>
>Yes.
>
>> Is the backup consistent?
>
>The tar or snapshot itself will not be consistent, it will have to be recovered
>at least until the end on the online backup.

I should ask: is the backup crash consistent?
Which means it is consistent at the time that the pg_start_backup is run.

>> 4) For the WAL backup in postgresql 8.4
>> After the archive mode is on and WAL is backup, how do I remove the old WAL files?
>> How about newer version (for example 9.2?)
>
>You never touch the files in pg_xlog on a running server.
>
>You remove old WAL archives when you don't need them any more.
>Usually that is when there is no older base backup.
>

Let me ask the question in this way:

4) For the WAL backup in postgresql 8.4
After setting the archive mode to on.
And the WAL files is copied to another directory eg. PGDATA\wal.
How do we remove the old WAL files in PGDATA\wal? We are not taking about PGDATA\pg_xlog.

How about newer version (for example 9.2?)

>Yours,
>Laurenz Albe
>
>--
>Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-admin
>