Re: Backups over slave instead master?

Hi ,

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*

(1 row)

--Chiru

On Fri, May 16, 2014 at 10:06 AM, bricklen <bricklen(at)gmail(dot)com> wrote:

>
> On Thu, May 15, 2014 at 1:55 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
>> On Thu, May 1, 2014 at 12:39:44PM -0700, bricklen wrote:
>> > Or alternatively, if "backup" = pg_dump, then backups can taken from
>> the slave
>> > too. Have a look at pg_xlog_replay_pause() + pg_dump +
>> pg_xlog_replay_resume().
>> > http://www.postgresql.org/docs/current/static/functions-admin.html#
>> > FUNCTIONS-RECOVERY-CONTROL-TABLE
>>
>> Uh, what is the pause for? So the transaction will not be cancelled?
>>
>>
> Yes.
>
>