Re: Minor bug/inconveniance with restore from backup, using PITR base backup and archived wal files

Tom Lane wrote:
> Tommy Gildseth <tommy.gildseth@usit.uio.no> writes:
>> After a bit of looking around, and with some help from the fine people
>> in #postgresql on freenode, I think I figured out what was going on.
>> The last wal archive file was 00000001000000030000009F, and after
>> finishing recovery, postgresql created the file 00000002000000030000009F
>> (ie. 00000002 instead of 00000001) in pg_xlog.
>
> It's customary for PG to "create" new XLOG segments by recycling old
> ones.
>
>> The wal-files were
>> archived read-only, and this file permission seemed to be carried over
>> to the new file created by postgresql in pg_xlog, causing the cluster to
>> fall over and die.
>
> I would say that the bug is in your restore script: it should have made
> sure that the files it copies into the xlog directory are given the
> right ownership/permissions.


Well, the restore command(script) is simply copied from the suggestion
in the manual (restore_command = 'cp /path/to/my/archived/wal/files/%f
"%p"'). In my opinion, it's not very obvious that the last wal file
needs read/write permissions set, and it's certainly not documented
anywhere on
http://www.postgresql.org/docs/current/static/continuous-archiving.html
that I can see.
There's also the matter of the inconsistency that postgresql knows to
recycle *and* chmod the file if it's originally located in pg_xlog/
folder, but not if it's originally located in the wal files archive
folder. I guess it's more of a gotcha than a bug per se.

--
Tommy Gildseth