Re: BUG #8043: 9.2.4 doesn't open WAL files from archive, only looks in pg_xlog

On Fri, Apr 5, 2013 at 12:27 PM, <bohmer(at)visionlink(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 8043
> Logged by: Jeff Bohmer
> Email address: bohmer(at)visionlink(dot)org
> PostgreSQL version: 9.2.4
> Operating system: CentOS 5.9 x86_64 kernel 2.6.18-348.3.1.el5
> Description:
>
> Hi pgsql-bugs,
>
> PG version: 9.2.4, from yum.postgresql.org
> OS: CentOS 5.9 x86_64 kernel 2.6.18-348.3.1.el5
>
> Upgrading from 9.2.3 to 9.2.4 has broken archive recovery for me. Probably
> related to this 9.2.4 change:
>
> Ensure we do crash recovery before entering archive recovery,
> if the database was not stopped cleanly and a recovery.conf
> file is present (Heikki Linnakangas, Kyotaro Horiguchi,
> Mitsumasa Kondo)
>
> When starting the PostgreSQL 9.2.4 service on a base backup, I get this:
>
> 2013-04-05 12:49:04 MDT [10302]: [1-1] user=,db= LOG: database system was
> interrupted; last known up at 2013-04-05 10:02:01 MDT
> 2013-04-05 12:49:04 MDT [10302]: [2-1] user=,db= LOG: starting archive
> recovery
> 2013-04-05 12:49:04 MDT [10302]: [3-1] user=,db= LOG: could not open file
> "pg_xlog/000000010000002F0000002D" (log file 47, segment 45): No such file
> or directory
>

...

> I use a custom base backup script to call pg_start/stop_backup() and make
> the backup with rsync.
>
> The restore_command in recovery.conf is never called by PG 9.2.4 during
> startup. I confirmed this by adding a "touch /tmp/restore_command.`date
> +%H:%M:%S`" line at the beginning of the shell script I use for my
> restore_command. No such files are created when starting PG 9.2.4.
>
> After downgrading back to 9.2.3, archive recovery works using the very same
> base backup, recovery.conf file, and restore_command. The log indicates
> that
> PG 9.2.3 begins recovery by pulling WAL files from the archive instead of
> pg_xlog:
>

I can reproduce the behavior you report only if I remove the "backup_label"
file from the restored data directory before I begin recovery. Of course,
doing that renders the backup invalid, as without it recovery is very
likely to begin from the wrong WAL recovery location.

I think it is appropriate that 9.2.4 refuses to cooperate in those
circumstances, and it was a bug that 9.2.3 did allow it.

Do you have a "backup_label" file?

> Or, must I now include pg_xlog files when taking base backups with 9.2.4,
> contrary to the documentation?
>

You do not need to include pg_xlog, but you do need to include
backup_label. And you always did need to include it--if you were not
including it in the past, then you were playing with fire and is only due
to luck that your database survived.

Cheers,

Jeff