Thread: Combined PITR/pg_dump backups?

I'm looking at what I want to do with backups, and from where I sit,
there are two options:
    1) WAL logging/PITR
    2) pg_dump using the binary protocol

The first allows me to recreate the database as it was at an arbitrary
point in time, the second allows me to recover just a single table.

The question I have is: can I do both?  Not as in "backing up the
database twice", but as a combined process.  What I'm thinking of is the
following process:
    - I do normal WAL loging as in PITR.
    - When I want to start a backup, I do a pg_start_backup(...), like PITR.
    - Instead of taring up the files on the filesystem, I instead do a
pg_dump
    - When the pg_dump completes, I do a pg_end_backup(), like PITR.

Single table backup would be like normal with pg_dump.  Whole database
recovery would be a little bit more involved- I'd have to restore the
database from the last pg_dump, then kick it into recovery mode (I
*think* this is possible?) and recover the WAL logs.

Would this insane idea even work?  Possibly, even probably not- I don't
think pg_dump keeps the transaction ids correct (why would it?), so that
could be a problem trying to recover WAL files.  If it's not totally
insane, has anyone actually tried this?

Thanks,
Brian

Brian Hurt <bhurt@janestcapital.com> writes:
> The question I have is: can I do both?  Not as in "backing up the
> database twice", but as a combined process.  What I'm thinking of is the
> following process:
>     - I do normal WAL loging as in PITR.
>     - When I want to start a backup, I do a pg_start_backup(...), like PITR.
>     - Instead of taring up the files on the filesystem, I instead do a
> pg_dump
>     - When the pg_dump completes, I do a pg_end_backup(), like PITR.

No, this will *not* work.  You need an exact file-level image of the
database in order to do PITR restore against, and a pg_dump dump cannot
recreate that.

            regards, tom lane