From: | Brian Hurt <bhurt(at)janestcapital(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Combined PITR/pg_dump backups? |
Date: | 2008-01-02 17:32:51 |
Message-ID: | 477BCAC3.50709@janestcapital.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-01-02 17:42:32 | Re: Combined PITR/pg_dump backups? |
Previous Message | Sean Davis | 2007-12-29 03:28:39 | Re: Setting a FK to look at only selected rows in the 'look-up' table... |