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

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

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

> 3) extension of 2): If the postgresql version is 9.2
> Is pg_basebackup better than pg_start_backup + file based backup + pg_stop_backup?

Technically they are equivalent.
pg_basebackup may be easier to use, the other option may provide
more flexibility.

One difference is that there can be many pg_basebackups running at the
same time.

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

Yours,
Laurenz Albe