Обсуждение: pg_basebackup on standby node failed
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.
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.
best,
Ying
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
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
> 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