Re: [GENERAL] Recovery Assistance - Mailing list 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