Re: [GENERAL] Recovery Assistance
От | Adrian Klaver |
---|---|
Тема | Re: [GENERAL] Recovery Assistance |
Дата | |
Msg-id | 61eb2030-c759-debe-4b25-a6f891bb423d@aklaver.com обсуждение исходный текст |
Ответ на | Re: [GENERAL] Recovery Assistance (Brian Mills <brian@trybooking.com>) |
Список | pgsql-general |
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? Yes, though I am not sure you have the setup to do it. I would suggest reading the below to see how much of it applies: https://www.postgresql.org/docs/9.3/static/continuous-archiving.html In particular: 24.3.4. Recovering Using a Continuous Archive Backup https://www.postgresql.org/docs/9.3/static/recovery-target-settings.html > > 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 <tel:0410660003> > *Melbourne* 03 9012 3460 <tel:03%209012%203460> or 03 8376 6327 > <tel:03%208376%206327> *|* * **Sydney* 02 8064 3600 > <tel:02%208064%203600> *|* *Brisbane* 07 3173 1570 <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 12:58, Brian Mills <brian@trybooking.com > <mailto:brian@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.klaver@aklaver.com <mailto:adrian.klaver@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 <tel:0410%20660%20003> <tel:0410660003 > <tel:0410%20660%20003>> > > *Melbourne* 03 9012 3460 <tel:(03)%209012%203460> > <tel:03%209012%203460> or 03 8376 6327 <tel:(03)%208376%206327> > > <tel:03%208376%206327> *|* * **Sydney* 02 8064 3600 > <tel:(02)%208064%203600> > > <tel:02%208064%203600> *|* *Brisbane* 07 3173 1570 > <tel:(07)%203173%201570> <tel:07%203173%201570> > > Level 1 *|* 600 Chapel Street *|* South > > Yarra*|* VIC *|* 3141 *|* Australia > > > > <https://www.facebook.com/TryBooking/ > <https://www.facebook.com/TryBooking/>> > <https://twitter.com/trybooking > <https://twitter.com/trybooking>> > <https://www.linkedin.com/company/trybooking-com > <https://www.linkedin.com/company/trybooking-com>> > > > > On 29 January 2017 at 08:18, rob stone <floriparob@gmail.com > <mailto:floriparob@gmail.com> > > <mailto:floriparob@gmail.com <mailto:floriparob@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.klaver@aklaver.com <mailto:adrian.klaver@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> > > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления: