Optimizing warm standby. Will this work? [PITR WAL]

From: "Dhaval Shah" <dhaval(dot)shah(dot)m(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Optimizing warm standby. Will this work? [PITR WAL]
Date: 2007-03-19 22:27:55
Message-ID: 565237760703191527x1f27ca74sbe08d39fcddcb9a0@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I was planning to setup a warm standby using WAL Files and my current
setup seems to work.

Here is what I do in the current setup

On the primary:

1. On the primary, setup continuous archive every 2 minutes. The WAL
files are stored on a known location.
2. On the primary : pg_start_backup('label');
3. Wait for 30 seconds
4. On the primary : pg_stop_backup();
5. Tar, gzip the data directory except the pg_xlog on the primary and
ship it to the standby.

On the Standby:

1. unzip, untar the data directory obtained from the primary
2. remove everything from pg_xlog.
3. Copy the wal files from the known location to pg_xlog
4. Move to recovery.conf and restart the db.

The optimization step I am looking for is as follows, instead of doing
a tar/gzip of the data directory, I am planning to do a pg_dumpall of
the primary. That is, the new steps will be:

0. Ensure that no application connects to the db.
1. On the primary, do a pg_dumpall
2. Do steps 1..4 as done earlier on the primary
3. Ship the pg_dumpall file

On the secondary

1. dropdb and restore from the supplied pg_dumpall
2. Stop db, remove everything from pg_xlog, copy the WAL Files
3. Move to recovery.conf and restart the db.

That way, I figure I do not have to move the entire data directory
around. I just need the base schema and data contents. The question
is, will the above setup work? Any gotchas?

Regards
Dhaval

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2007-03-19 22:54:50 PostgreSQL Party, July 22nd 2007 (Day before OSCON 2007)
Previous Message Joshua D. Drake 2007-03-19 21:31:10 Re: UPGRADATION TO 8.1