WAL backups with pg_dump not possible?

I am hoping to move to WAL backup from pg_dump. But I am confused by this note in 4.3 documentation:


Perform the backup, using any convenient file-system-backup tool such
as tar or cpio.

And in postgres 9.0 documentation:


Perform the backup, using any convenient file-system-backup tool such
as tar or cpio (not pg_dump or pg_dumpall).

So I am not allowed to run pg_dump? Or it's not allowed only for 9.x ? Filesystem dumps are useless to me as I need to restore 64-bit machine dumps on 32-bit. And I need to be able to restore separate databases.

If pg_dump is forbidden, is the only solution for me to setup slave replication and run pg_dump on slave?

You are confused because you're mixing up a database base backup and a SQL dump (with pg_dump or pg_dumpall) which are actually two completely different things. Take a good look at the PostgreSQL documentation about Backup and Restore. These are the links to the 9.1 docs, but the basic principles of this stuff haven't changed since at least version 8.3 (except for streaming replication which was introduced with 9.0)

• A database base backup is basically a file system level copy of the data directory (and any additional tablespace directories) at a certain point in time. This file system level backup can be created with any tool you like (e.g. tar or cpio). But in order to be able to recover from this backup additional information is needed: the WAL segments (binary transaction log) that have been created during the backup plus the exact start and end position of the backup creation process in the WAL segments. In recovery mode PostgreSQL is started with the backup data directory and it will replay all transactions from the available WAL archives (unless a certain end time or transaction id is configured for point-in-time recovery). The scope of a base backup is a database cluster.

• A SQL dump is the plain text or binary format representation of the schema and/or data information of one (pg_dump) or all (pg_dumpall) of the databases in a cluster. It is also possible to dump just certain schemas or tables. In contrast to a base backup a SQL dump covers only an exact snapshot of the database at a single point in time. The essential difference to a base backup is that the SQL dump is independent of the physical database layout. System architecture, OS, file system and for most parts database version do not matter, the SQL dump is only the information that makes up a database and can therefore be easily restored on another system.

So in your case using pg_dump or pg_dumpall for backup and pg_restore or psql to restore the dump is the way to go.

Hope that helps.

StackExchange Database Administrators Q#13771, answer score: 5