Re: Backups over slave instead master?

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