Backup on PITR slave?

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Backup on PITR slave?
Date: 2009-11-01 14:21:08
Message-ID: 20091101142108.GA16348@depesz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
I'm wondering what steps should I do to make *working* backup on PITR
slave.

General rationale is: to reduce amount of work master has to do.

Let's assume we have master server, slave server, and shared
/wal_archive directory.

Postrges on both servers is in the same version, with datadir being
/data.

My current idea is:
(everything on slave).

1. pg_controldata /data > /tmp/control.before
2. copy /data to /tmp/data
3. pg_controldata /data > /tmp/control.after.cp
4. Wait, periodically checking pg_controldata /data - wait till "Latest checkpoint location" will be different than ne from step #3, then write it as /tmp/control.after.change
5. Create /tmp/data/backup.label with:
START WAL LOCATION: 0/( "Latest checkpoint's REDO location" from /tmp/control.before)
STOP WAL LOCATION: 0/("Latest checkpoint location" from /tmp/control.after.change)
CHECKPOINT LOCATION: 0/("Prior checkpoint location" from /tmp/control.after.change)
START TIME: Timestamp of step #1
LABEL: q
STOP TIME: Timestamp of step #4
6. copy all xlog files from /wal_archive, that are from "START WAL LOCATION:" to the newest - to /tmp/data/pg_xlog
7. rm /tmp/data/postmaster.pid
8. rm /tmp/data/recovery.conf
9. tar/gz/transfer /tmp/data.

Will it work? Any edge cases that will make it useless? Any other way I
should do to make the backup on slave?

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2009-11-01 14:40:35 Re: Store images on database ou in external files?
Previous Message Andre Lopes 2009-11-01 14:20:20 Store images on database ou in external files?