> 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.
I think that will not happen for the reason stated above.
I concur that it would be nice.
You can do it yourself by using pg_dumpall to just dump the
global data (switch -g) and then call "pgdump -Fc" for all
the databases in the cluster.
>>> 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.
I am not sure that I understand you right, but let me explain:
You cannot perform crash recovery with a backup unless it was taken
with a truly atomic snapshot of everything at once.
In all other cases, the individual files in the backup are not
consistent in ways that crash recovery cannot repair, and even if
you manage to get the backup running using crash recovery
(by removing the "backup_label" file), you will end up
with a corrupt database that will eat and destroy your data.
On the other hand, the cluster from which the backup is being
taken is fully operational and consistent.
Does that answer your question?
>>> 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?)
That is up to you in any version of PostgreSQL.
PostgreSQL does not know how long you need to keep your archived
WAL files, so it will never delete them.
You can use cron jobs or similar things to remove WAL archives
older than a certain time, or you can use tools like Barman
(http://www.pgbarman.org/) that manage your backups for you.
Yours,
Laurenz Albe
