Re: Help! PostgreSQL stuck at starting up after crash

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