Обсуждение: PITR question with base backup
I'm in the process of testing PITR recovery, and I have an issue. My "data" directory is 30GB. Not huge, but it certainly takes awhile to tar up. My understanding is: a) pg_start_backup b) tar up c) pg_stop_backup d) restore tar file The problem is that I create databases pretty regularly. Let's say I create 3 in a week. I'm not looking forward to going to my colo, grabbing the 20ish GB tgz file and restoring it 3 times per week. I'd rather do that dance monthly or quarterly and rely on the WALs in the interim. So...can I: a) pg_start_backup b) tar up my data dir c) pg_stop_backup d) restore e) WALs are all happy f) Day 2 - create new db g) pg_start_backup h) JUST grab the data/base directory for the new db i) pg_stop_backup move on? What about "global" or "pg_clog" or "pg_multixact" or "pg_tblspc" and "pg_twophase?" I don't _think_ I would ever want clog, but maybe I'm wrong. Is this possible, or do I absolutely need an entire new backup of the whole data dir?
Scott Whitney wrote: > The problem is that I create databases pretty regularly. Let's say I create > 3 in a week. I'm not looking forward to going to my colo, grabbing the 20ish > GB tgz file and restoring it 3 times per week. I'd rather do that dance > monthly or quarterly and rely on the WALs in the interim. What's the actual problem? Database creation is also replayed via WAL. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
It is, is it? I was completely under the impression that it was not. Don't ask me where I got that impression. :) No problem whatsoever, in that case! Thanks for clearing up my inability to comprehend documentation... -----Original Message----- From: Alvaro Herrera [mailto:alvherre@commandprompt.com] Sent: Tuesday, October 21, 2008 3:10 PM To: Scott Whitney Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] PITR question with base backup Scott Whitney wrote: > The problem is that I create databases pretty regularly. Let's say I create > 3 in a week. I'm not looking forward to going to my colo, grabbing the 20ish > GB tgz file and restoring it 3 times per week. I'd rather do that dance > monthly or quarterly and rely on the WALs in the interim. What's the actual problem? Database creation is also replayed via WAL. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Tue, Oct 21, 2008 at 2:29 PM, Scott Whitney <swhitney@journyx.com> wrote: > It is, is it? I was completely under the impression that it was not. Don't > ask me where I got that impression. :) > > No problem whatsoever, in that case! > > Thanks for clearing up my inability to comprehend documentation... This is what I like so much about PostgreSQL. Most of the surprises are of the "oh wow! That's cool" Not the "oh shit!" kind. :)