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 <br...@trybooking.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.kla...@aklaver.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 <floripa...@gmail.com >> > <mailto:floripa...@gmail.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@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.kla...@aklaver.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> >
