Backups over slave instead master?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

Em 01/05/2014 16:39, bricklen escreveu:

On Thu, May 1, 2014 at 8:54 AM, Shaun Thomas <sthomas@optionshouse.com
<mailto: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

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

Edson

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, May 15, 2014 at 1:55 PM, Bruce Momjian <bruce@momjian.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.

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@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@2ndquadrant.com>
wrote: