Search Postgresql Archives

Re: Recovery Assistance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


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 

  

On 29 January 2017 at 12:58, Brian Mills <brian@xxxxxxxxxxxxxx> 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@xxxxxxxxxxx> 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: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 08:18, rob stone <floriparob@xxxxxxxxx
> <mailto:floriparob@xxxxxxxxx>> 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@xxxxxxxxxxx
--
Brian Mills
CTO


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 

  


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux