Postgresql – In PostgreSQL, if crashes during VACUUM, will it recover from a checkpoint that is before the frozen transaction ID of VACUUM

You asked:

how postgreSQL will handle the recovery with a pg_data content containing some files which are inconsistent.

pg_start_backup() ensure the data file is at least as new as the checkpoint. On recovery, the logs are applied.

If the data is old, the log will update it..

If the data is new, the log will have same content. There is no hurt writing it again.

The data are never newer then the log, because the logs are write ahead (WAL).

You asked:

... xfs-freeze ...

xfs-freeze is alike to pg_start_backup(), it don't take a snapshot. You need a volume manager to do that.

You asked:

... why do create tablespace & create database statements are unsupported if the WAL can replay everything?

It is supported, just some little gotcha. See http://www.postgresql.org/docs/8.1/static/backup-online.html :

23.3.5. Caveats

CREATE TABLESPACE commands are WAL-logged with the literal absolute path, and will therefore be replayed as tablespace creations with the same absolute path. This might be undesirable if the log is being replayed on a different machine. It can be dangerous even if the log is being replayed on the same machine, but into a new data directory: the replay will still overwrite the contents of the original tablespace. To avoid potential gotchas of this sort, the best practice is to take a new base backup after creating or dropping tablespaces.

On my PostgreSQL (8.3) I use this trick:

1. I get table's disk size using pg_total_relation_size() - this includes indexes and TOAST size, which is what VACUUM processes. This gives me the idea of how many bytes the VACUUM has to read.
2. I run VACUUM on the table.
3. I find the pid of the VACUUM process (in pg_catalog.pg_stat_activity).
4. In Linux shell I run while true; do cat /proc/123/io | grep read_bytes; sleep 60; done (where 123 is the pid) - this shows me bytes read by the process from the disk so far.

This gives me rough idea on how many bytes are processed (read) every minute by the VACUUM. I presume that the VACUUM must read through the whole table (including indexes and TOAST), whose disk size I know from the step 1.

I presume that the table is large enough so that the majority of it's pages must be read from disk (they are not present in Postgres shared memory), so the read_bytes field is good enough to be used as a progress counter.

Everytime I did this, the total bytes read by the process was no more than 5% from the total relation size, so I guess this approach may be good enough for You.