Thread: Backups over slave instead master?

I've a basic setup with async replication between two distant,
geographically separated servers over vpn.
Replication happens every 0.5 seconds or so, and is incredible reliable.
Today, I've been using backup on master server every twelve hours.
I'm wondering if would be possible to execute these backups in the slave
server instead, so I can avoid the overhead of backups on master system?

Thanks,

Edson

On 05/01/2014 10:31 AM, Edson Richter wrote:

> I'm wondering if would be possible to execute these backups in the slave
> server instead, so I can avoid the overhead of backups on master system?

If you're on PostgreSQL 9.3, you can backup the slave server safely. If
not, you'll need to run this command on the master system first:

SELECT pg_start_backup('some-label');

After the backup is done, run this on the master server:

SELECT pg_stop_backup();

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On Thu, May 1, 2014 at 8:54 AM, Shaun Thomas <sthomas@optionshouse.com> wrote:

On 05/01/2014 10:31 AM, Edson Richter wrote:

I'm wondering if would be possible to execute these backups in the slave
server instead, so I can avoid the overhead of backups on master system?

If you're on PostgreSQL 9.3, you can backup the slave server safely. If not, you'll need to run this command on the master system first:

SELECT pg_start_backup('some-label');

After the backup is done, run this on the master server:

SELECT pg_stop_backup();

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

On 05/02/2014 09:46 AM, Edson Richter wrote:

> Yes, backup = pg_dump.
> Can I use it on slave even on 9.2.7?

Yes. The reason I suggested 9.3 was assuming you were using
pg_basebackup, rsync, or a similar tool to backup the database binaries.
You should be fine with pg_dump.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On Thu, May  1, 2014 at 12:39:44PM -0700, bricklen wrote:
>
> On Thu, May 1, 2014 at 8:54 AM, Shaun Thomas <sthomas@optionshouse.com> wrote:
>
>     On 05/01/2014 10:31 AM, Edson Richter wrote:
>
>
>         I'm wondering if would be possible to execute these backups in the
>         slave
>         server instead, so I can avoid the overhead of backups on master
>         system?
>
>
>     If you're on PostgreSQL 9.3, you can backup the slave server safely. If
>     not, you'll need to run this command on the master system first:
>
>     SELECT pg_start_backup('some-label');
>
>     After the backup is done, run this on the master server:
>
>     SELECT pg_stop_backup();
>
>
> 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?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

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