5

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?

Improve this question

1 Answer 1

Reset to default
5

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.

Improve this answer
5
  • 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?
    – Roman
    Feb 24, 2012 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, 2012 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.
    – Roman
    Feb 24, 2012 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, 2012 at 15:54
  • well a developer or tester may have 32bit system where they deploy the copy of database.
    – Roman
    Feb 25, 2012 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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