Re: Backups over slave instead master?

OmniPITR (https://github.com/omniti-labs/omnipitr) has working backups off
slave. Working as in - we were using in it production since 8.3 at least.

depesz

On Thu, May 29, 2014 at 8:02 PM, Andres Freund <andres(at)2ndquadrant(dot)com>
wrote:

> Hi,
>
> On 2014-05-16 12:49:25 +0530, chiru r wrote:
> > Yes, It is possible to execute backups on the slave server instead of
> > master.
> >
> > Below are the steps we run for one of our past customer every day to
> > refresh his Dev/test environments using slave backups.
> >
> > *On Slave:*
> >
> > 1. Pause the replication
> >
> > postgres=# select pg_xlog_replay_pause();
> >
> > pg_xlog_replay_pause
> >
> > ----------------------
> >
> > (1 row)
> >
> > 2. Make sure wheather Replication paused or not.
> >
> > postgres =# select pg_is_xlog_replay_paused();
> >
> > pg_is_xlog_replay_paused
> >
> > --------------------------
> >
> > * t*
> >
> > (1 row)
> >
> > 3. Copy the data directory using any one rsync,tar,scp or cp..etc
> >
> > 4. Resume the replication to continue the replication process.
> >
> > postgres=# select pg_xlog_replay_resume();
> >
> > pg_xlog_replay_resume
> >
> > -----------------------
> >
> > (1 row)
> >
> > 5. Verify the weather replication is resumed or not.
> >
> > postgres=# select pg_is_xlog_replay_paused();
> >
> > pg_is_xlog_replay_paused
> >
> > --------------------------
> >
> > *f*
>
> This procedure is absolutely broken:
> a) There'll be further writes even if you stop replay. Both the
> background writer and the checkpointer are active. The latter will
> only create restartpoints, but that's still problematic.
> b) Because of the nonexistance of a backup label a backup that's been
> created won't necessarily start up from the right point.
>
> From 9.2. you can simply use pg_basebackup from standby servers
> though. That does all the necessary things internally.
>
> Greetings,
>
> Andres Freund
>
> --
> Andres Freund http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>