Re: Advancing the archiver position safely

Christophe Pettus <xof(at)thebuild(dot)com> writes:

> I've encountered a rather unusual situation (PostgreSQL 9.6). On a
> particular server, for reasons I've not fully diagnosed, the archiver
> thinks that the current WAL segment to be archived is
> 0000000200003B6800000062. This is unfortunate, because the oldest WAL
> segment that actually exists on disk is 0000000200003F1100000004, so
> the archive script is failing repeatedly because of the missing
> segment.
>
> The system is not actually missing important (for recovery) WAL segments, at least:
>
> Latest checkpoint's REDO WAL file: 000000020000417600000029
>
> I'd like to "catch up" the archiver such that it is operating on files
> that actually exist; besides setting archive_command to '/bin/true'
> and letting it chew through the old ones, is there a way of safely
> advancing the position of the archiver?

Take a look at the contents of your pg_xlog/archive_status directory
where you will likely find a .ready file corresponding to the missing
segment and perhaps others.

Deleting the .ready file should allow the archiver to get past the
missing file.

Make certain you're *not* mucking with the WAL files themselves.

> --
> -- Christophe Pettus
> xof(at)thebuild(dot)com
>
>
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net