Re: Recovery Assistance

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?

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

*Mob: *0410660003
*Melbourne* 03 9012 3460 or 03 8376 6327 *|* *Sydney* 02 8064 3600 *|*
*Brisbane* 07 3173 1570
Level 1 *|* 600 Chapel Street *|* South Yarra*|* VIC *|* 3141 *|*
Australia

<https://www.facebook.com/TryBooking/> <https://twitter.com/trybooking>
<https://www.linkedin.com/company/trybooking-com>

On 29 January 2017 at 12:58, Brian Mills <brian(at)trybooking(dot)com> wrote:

> I have a consistent sql dump from 24 hour previous.
>
> The file level backup was done with rsync -a of full data directory after
> the issue occurred so could reset as I learned.
>
> Brian
>
>
> On Sun, 29 Jan 2017 at 9:18 am, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>> On 01/28/2017 01:55 PM, Brian Mills wrote:
>> > Yes, its the last one in the directory, pg_xlog directory
>> >
>> > ...more files...
>> > -rw------- 1 postgres postgres 16777216 Jan 21 10:05
>> > 0000000100000005000000A1
>> > -rw------- 1 postgres postgres 16777216 Jan 22 21:29
>> > 0000000100000005000000A2
>> > -rw------- 1 postgres postgres 16777216 Jan 24 02:08
>> > 0000000100000005000000A3
>>
>> Best guess is the last WAL is not complete.
>>
>> From your original post:
>> "Attempt 2 - startup manually and let it try recovery
>>
>> I restored my file level backup and started again. "
>>
>> How was the file level backup done?
>>
>> >
>> >
>> > *Brian Mills*
>> > CTO
>> >
>> >
>> > *Mob: *0410660003 <0410%20660%20003> <tel:0410660003 <0410%20660%20003>
>> >
>> > *Melbourne* 03 9012 3460 <(03)%209012%203460> <tel:03%209012%203460>
>> or 03 8376 6327 <(03)%208376%206327>
>> > <tel:03%208376%206327> *|* * **Sydney* 02 8064 3600
>> <(02)%208064%203600>
>> > <tel:02%208064%203600> *|* *Brisbane* 07 3173 1570
>> <(07)%203173%201570> <tel:07%203173%201570>
>> > Level 1 *|* 600 Chapel Street *|* South
>> > Yarra*|* VIC *|* 3141 *|* Australia
>> >
>> > <https://www.facebook.com/TryBooking/> <https://twitter.com/trybooking>
>> <https://www.linkedin.com/company/trybooking-com>
>> >
>> > On 29 January 2017 at 08:18, rob stone <floriparob(at)gmail(dot)com
>> > <mailto:floriparob(at)gmail(dot)com>> wrote:
>> >
>> > Hello Brian,
>> > On Sun, 2017-01-29 at 07:16 +1100, Brian Mills wrote:
>> > > Hi,
>> > >
>> > > No, it hasn't changed since the first time I looked at it.
>> > >
>> > > root(at)atlassian:/home/tbadmin# ps ax | grep post
>> > > 1364 ? Ss 0:00 /usr/lib/postfix/master
>> > > 5198 pts/3 S 0:00 su postgres
>> > > 5221 pts/3 S 0:00 /usr/lib/postgresql/9.3/bin/postgres
>> -D
>> > > /etc/postgresql/9.3/main
>> > > 5222 ? Ss 0:10 postgres: startup process recovering
>> > > 0000000100000005000000A3
>> > > 11161 pts/4 S+ 0:00 grep --color=auto post
>> > >
>> >
>> >
>> > Does this WAL file exist "0000000100000005000000A3"?
>> >
>> > Cheers,
>> > Rob
>> >
>> >
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>>
> --
> *Brian Mills*
> CTO
>
>
> *Mob: *0410660003
> *Melbourne* 03 9012 3460 or 03 8376 6327 *|* *Sydney* 02 8064 3600 *|*
> *Brisbane* 07 3173 1570
> Level 1 *|* 600 Chapel Street *|* South Yarra*|* VIC *|* 3141 *|*
> Australia
>
> <https://www.facebook.com/TryBooking/> <https://twitter.com/trybooking>
> <https://www.linkedin.com/company/trybooking-com>
>