Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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.

share|improve this answer
Thanks for explanation. What I am looking for is solution for doing incremental backups of single database in portable format (e.g. moving from 64bit to 32bit). pg_dump is not incremental. Can you recommend anything else? –  Roddick Feb 24 '12 at 5:04
@Roddick: OK, now I understand your problem. I will add an update to my answer and put my 2 cents in on that as soon as I have time. But you should edit your question/title to make your requirements clear. I'd guess something like 'Incremental backup from 64bit to 32bit PostgreSQL' will draw more attention on your question. How comes you're restricted to 32bit on your backup database? Is the backup system used for read-only access or just for disaster recovery? –  tscho Feb 24 '12 at 7:27
There is a live database which is 64bit. And there's test machines that could be 32 or 64 bit where we need to deploy copy of live database. –  Roddick Feb 24 '12 at 14:23
Why is using a full dump not an option? Is the database too large or WAN connection bad? You could setup replication or log shipping to a 64bit slave and dump the replicated slave into your 32bit test system in the LAN. But to be honest I don't understand the need for a 32bit test system when the production system is 64bit, what's the point in testing on a 32bit system? –  tscho Feb 24 '12 at 15:54
well a developer or tester may have 32bit system where they deploy the copy of database. –  Roddick Feb 25 '12 at 7:14
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.