pg_basebackup on standby node failed

On Sat, Feb 1, 2014 at 7:07 AM, Ying He <yinghe0101@yahoo.com> wrote:

hi, All,

I intend to do a basebackup on a hot standby node. I followed the
instructions on
http://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html to turn
full_page_writes on at master and other settings on standby.

When I try to select pg_start_backup('backuplabel'); on the standby
instance, it give me the following:
ERROR: recovery is in progress
HINT: WAL control functions cannot be executed during recovery.

I tried select pg_xlog_replay_pause(); before the select
pg_start_backup('backuplabel'); but that does not help.

Also why select pg_is_in_recovery(); always returns true when select
pg_is_xlog_replay_paused(); is true? I thought when I do select
pg_xlog_replay_pause(); the standby should not be in recovery

Please help if you know anything about this. Thank you.

Taking a backup with pg_basebackup and with pg_start/stop_backup are
two different things on a standby. In short, you are not allowed to
run pg_start/stop_backup in recovery on a standby because
pg_stop_backup needs to write a WAL record called XLOG_BACKUP_END once
it is done. Only pg_basebackup is able to take backups from a standby
because it uses the replication protocol to take the backup and
bypasses the WAL record by waiting that all the needed WAL files have
been archived.
So use pg_basebackup for a standby :)

Regards,
--
Michael

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

Thanks Michael. It seems having an issue using pg_basebackup.

When I do:
pg_basebackup -D /backupDir

I am getting:
pg_basebackup: directory "/usr/local/pgsql/data/pg_tblspc/tablespace1" exists but is not empty

tablespace1 is a tablespace created by CREATE TABLESPACE and ""/usr/local/pgsql/data/pg_tblspc/tablespace1" is a symlink pointing to a dir which will always exists and not empty

I tried using -Ft -P, that works to create tar files in the /backupDir but 

pg_basebackup -D /backupDir -Ft -P -X s will fail with:

pg_basebackup: wal streaming can only be used in plain mode

So looks like I cannot really get a standalone backup with xlog files during the backup.

Please advise. Thank you.

best,
Ying

On Friday, January 31, 2014 8:00 PM, Michael Paquier <michael.paquier@gmail.com> wrote:

On Sat, Feb 1, 2014 at 7:07 AM, Ying He <yinghe0101@yahoo.com> wrote:

hi, All,

I intend to do a basebackup on a hot standby node. I followed  the
instructions on
http://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html to turn
full_page_writes on at master and other settings on standby.

When I try to select pg_start_backup('backuplabel'); on the standby
instance, it give me the following:
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.

I tried select  pg_xlog_replay_pause(); before the select
pg_start_backup('backuplabel'); but that does not help.

Also why select pg_is_in_recovery(); always returns true when select
pg_is_xlog_replay_paused(); is true? I thought when I do select
pg_xlog_replay_pause(); the standby should not be in recovery

Please help if you know anything about this. Thank you.

Taking a backup with pg_basebackup and with pg_start/stop_backup are
two different things on a standby. In short, you are not allowed to
run pg_start/stop_backup in recovery on a standby because
pg_stop_backup needs to write a WAL record called XLOG_BACKUP_END once
it is done. Only pg_basebackup is able to take backups from a standby
because it uses the replication protocol to take the backup and
bypasses the WAL record by waiting that all the needed WAL files have
been archived.
So use pg_basebackup for a standby :)

Regards,
--
Michael

On Tue, Feb 4, 2014 at 5:51 AM, Ying He <yinghe0101@yahoo.com> wrote:

When I do:
pg_basebackup -D /backupDir

I am getting:
pg_basebackup: directory "/usr/local/pgsql/data/pg_tblspc/tablespace1"
exists but is not empty

tablespace1 is a tablespace created by CREATE TABLESPACE and
""/usr/local/pgsql/data/pg_tblspc/tablespace1" is a symlink pointing to a
dir which will always exists and not empty

Creating a tablespace *inside* PGDATA, which is what you seem to be
doing, is not good practice. Btw, it looks like you faced the bug of a
tablespace inside PGDATA fixed by this commit and back-patched down to
9.1:
commit b168c5ef2730d0ecaa7462f0b90345b0a3798c16
Author: Magnus Hagander <magnus@hagander.net>
Date: Tue Jan 7 17:04:40 2014 +0100

Avoid including tablespaces inside PGDATA twice in base backups

If a tablespace was crated inside PGDATA it was backed up both as part
of the PGDATA backup and as the backup of the tablespace. Avoid this
by skipping any directory inside PGDATA that contains one of the active
tablespaces.

Dimitri Fontaine and Magnus Hagander
This will appear in the next minor release.

I tried using -Ft -P, that works to create tar files in the /backupDir but
pg_basebackup -D /backupDir -Ft -P -X s will fail with:
pg_basebackup: wal streaming can only be used in plain mode

I see no problem with this error message. You are trying to create a
tarball and to stream xlog files at the same time.
Regards,
--
Michael

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