Re: Recovery Assistance

On 01/28/2017 11:23 PM, Brian Mills wrote:
> I presume this is a binary log file for the database.
>
> Am I able to recover to a point in time using this log file?
>
> What I would do in SQL Server would be recover to a point in time, say a
> bit before the last completed transaction time the log mentions, then
> take a backup. Is that possible in postgres?

Had another thought. If I remember correctly you are using this as an
exercise in Postgres recovery. If that is indeed the case you might try:

1) Stop the Postgres instance you have running now.

2) Move the WAL file that Postgres is currently stalled on,
0000000100000005000000A3, out of pg_xlog.

3) Restart the Postgres instance.

My guess it it will not bring it back to the exact point you want, but
close. You can get a general idea by running(before and after removing
the WAL), as the postgres user:

pg_controldata -D /etc/postgresql/9.3/main

>
> The log mentions this:
> 2017-01-27 20:36:18 AEDT LOG: last completed transaction was at log
> time 2017-01-24 02:08:00.023064+11
>
> (which is moments before, or possibly as the disk filled up doing a db
> backup dump)
>
> *Brian Mills*
> CTO
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com