Skip site navigation (1) Skip section navigation (2)

Re: Help! PostgreSQL stuck at starting up after crash

From: David Hornsby <david(at)beechglen(dot)com>
To: Samuel Hwang <samuel(at)replicon(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Help! PostgreSQL stuck at starting up after crash
Date: 2012-01-19 22:50:20
Message-ID: 4F189E2C.6000900@beechglen.com (view raw or flat)
Thread:
Lists: pgsql-admin
Glad to hear that you can spin up the database and get to your data. 
Sorry to hear that you did end up losing data on that server.

-David Hornsby

On 1/19/2012 5:46 PM, Samuel Hwang wrote:
> correct typo.
> We pretty much lost the data for the last two days, but since our 
> postgresql wereN'T running well, it is fewer than it looks.
>
>
> On Thu, Jan 19, 2012 at 3:45 PM, Samuel Hwang <samuel(at)replicon(dot)com 
> <mailto:samuel(at)replicon(dot)com>> wrote:
>
>     pg_resetxlog does the trick and db can be started and readable.
>
>     I am dumping the data out and import to a newly created database
>     cluster.
>
>     We pretty much lost the data for the last two days, but since our
>     postgresql were running well, it is fewer than it looks.
>
>     Thanks a lot for the help.
>
>     On Thu, Jan 19, 2012 at 7:30 AM, David Hornsby
>     <david(at)beechglen(dot)com <mailto:david(at)beechglen(dot)com>> wrote:
>
>         Sounds like you have a corrupt wal files that you will have to
>         reset the
>         wal logs with pgresetxlog.
>
>         http://www.postgresql.org/docs/8.2/static/app-pgresetxlog.html
>
>         This will result in missing transactions so before you do this
>         shutdown
>         postgres and make a copy of the database files first. That way
>         if you
>         don't like what happens you can always go back to the way
>         things were.
>
>         Also right now would be a good time to evaluate your backup
>         strategy,
>         which is a different topic for a different thread, but I can
>         certainly
>         help with that as well.
>
>         -David Hornsby
>
>         > version Postgresql 9.1.1 on centos5 x64
>         >
>         > We experience slow performance and found the server is
>         running 3 vacuum
>         > process on the same db which use up 99% of CPU.
>         > Then we kill -9 one of those process which cause postgresql
>         to crash and
>         > it
>         > tried to restart after the crash
>         > However when the starting process reach the last WAL files,
>         it just stuck
>         > there
>         >
>         > pg_controldata shows the db is in Archive Recovery mode and
>         when using
>         > psql
>         > to connect the db, it says FATAL: the database system
>         starting up.
>         >
>         > I took a chance and upgrade to PostgreSql 9.1.2 and see if
>         anything
>         > changed
>         > it still stuck at the end of recovery.
>         > pg_controldata shows db is in Crash recovery, but that
>         probably different
>         > wording I think
>         > using psql to connect the db, it says FATAL: the database
>         system is
>         > starting up.
>         >
>         > I pretty much run out of idea here.
>         > Can anyone help what to go from here?
>         >
>         > Samuel
>         >
>
>
>
>
>
>     -- 
>
>     *Shian-Miin Samuel Hwang* | Software Developer | Phone
>     1-403-2626519 <tel:1-403-2626519> (ext. 276) | Fax 1-403-233-8046
>     <tel:1-403-233-8046>
>
>     *Replicon* | Hassle-Free Time & Expense Management Software -
>     7,300 Customers - 70 Countries
>     www.replicon.com <http://www.replicon.com/> | facebook
>     <http://www.facebook.com/Replicon.inc> | twitter
>     <http://twitter.com/Replicon> | blog
>     <http://www.replicon.com/blog/> | contact us
>     <http://www.replicon.com/about_replicon/contact_us.aspx>
>
>     *We are hiring!* | search jobs
>     <http://tbe.taleo.net/NA2/ats/careers/searchResults.jsp?org=REPLICON&cws=1&act=sort&sortColumn=1&__utma=1.651918544.1299001662.1299170819.1299174966.10&__utmb=1.8.10.1299174966&__utmc=1&__utmx=-&__utmz=1.1299174985.10.3.utmcsr=google%7Cutmccn=%28organic%29%7Cutmcmd=organic%7Cutmctr=replicon%20careers&__utmv=1.%7C3=Visitor%20Type=Prospects=1,&__utmk=40578466>
>
>
>
>
>
>
> -- 
>
> *Shian-Miin Samuel Hwang* | Software Developer | Phone 1-403-2626519 
> (ext. 276) | Fax 1-403-233-8046
>
> *Replicon* | Hassle-Free Time & Expense Management Software - 7,300 
> Customers - 70 Countries
> www.replicon.com <http://www.replicon.com/> | facebook 
> <http://www.facebook.com/Replicon.inc> | twitter 
> <http://twitter.com/Replicon> | blog <http://www.replicon.com/blog/> | 
> contact us <http://www.replicon.com/about_replicon/contact_us.aspx>
>
> *We are hiring!* | search jobs 
> <http://tbe.taleo.net/NA2/ats/careers/searchResults.jsp?org=REPLICON&cws=1&act=sort&sortColumn=1&__utma=1.651918544.1299001662.1299170819.1299174966.10&__utmb=1.8.10.1299174966&__utmc=1&__utmx=-&__utmz=1.1299174985.10.3.utmcsr=google%7Cutmccn=%28organic%29%7Cutmcmd=organic%7Cutmctr=replicon%20careers&__utmv=1.%7C3=Visitor%20Type=Prospects=1,&__utmk=40578466> 
>
>
>


-- 
David Hornsby
Beechglen Development Inc.
P: (513) 922 - 0509 x432
C: (513) 254 - 0605
F: (513) 347 - 2834
W: beechglen.com

In response to

pgsql-admin by date

Next:From: brooks.glennDate: 2012-01-20 05:43:01
Subject: help with Postgres forms (Pfm)
Previous:From: Samuel HwangDate: 2012-01-19 22:46:41
Subject: Re: Help! PostgreSQL stuck at starting up after crash

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group