Re: BUG #8043: 9.2.4 doesn't open WAL files from archive, only looks in pg_xlog - Mailing list pgsql-bugs

On Fri, Apr 5, 2013 at 12:27 PM, <bohmer@visionlink.org> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      8043
> Logged by:          Jeff Bohmer
> Email address:      bohmer@visionlink.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