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 <br...@trybooking.com
<mailto: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 <mailto: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 <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 <floripa...@gmail.com
<mailto:floripa...@gmail.com>
> <mailto: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 <mailto: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>
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
