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

Started by Dhaval Shahabout 19 years ago2 messagesgeneral
Jump to latest
#1Dhaval Shah
dhaval.shah.m@gmail.com

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

#2Richard Huxton
dev@archonet.com
In reply to: Dhaval Shah (#1)
Re: Optimizing warm standby. Will this work? [PITR WAL]

Dhaval Shah wrote:

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

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?

Won't work - the WAL refers to disk blocks, not primary keys etc. You
need the actual disk files.

--
Richard Huxton
Archonet Ltd