Thread: database files are incompatible with server, after computer restart

I installed and started PostgreSQL and it worked fine
for days. Then I restarted my computer and now I can't
start PostgreSQL ('pg_ctl -D pgdata -l pgdata/psql.log
start'). Here's what's in my log:

LOG:  received immediate shutdown request
WARNING:  terminating connection because of crash of
another server process
DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit,
because another server process exited abnormally and
possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to
the database and repeat your command.
FATAL:  database files are incompatible with server
DETAIL:  The database cluster was initialized without
HAVE_INT64_TIMESTAMP but the server was compiled with
HAVE_INT64_TIMESTAMP.
HINT:  It looks like you need to recompile or initdb.

I guess my computer restart didn't agree with
PostgreSQL. But I've had crashes before and never had
a problem getting PostgreSQL going again. Anybody know
how to fix this? I also tried 'pg_resetxlog -f pgdata'
but still get the same error trying to start up.

Thanks,
csn
Mac OS 10.4.7
PostgreSQL 8.1.3

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: database files are incompatible with server, after

From
Douglas McNaught
Date:
CSN <cool_screen_name90001@yahoo.com> writes:

> DETAIL:  The database cluster was initialized without
> HAVE_INT64_TIMESTAMP but the server was compiled with
> HAVE_INT64_TIMESTAMP.
> HINT:  It looks like you need to recompile or initdb.

Is it possible you have two PG installs on this machine, and you're
trying to start the wrong one?  Because it's hard to see how a restart
could cause this error message--it's saying that your data files are
incompatible with the code you're trying to run.

-Doug

I don't think so -- I followed the instructions here:

http://www.robbyonrails.com/articles/2006/05/29/install-ruby-rails-and-postgresql-on-osx

But looking around, I see there's a pg_ctl in
/usr/local/bin, but 'port contents postgresql8' shows
a pg_ctl in /opt/local/lib/pgsql8/bin.

~ $ ll /opt/local/lib/pgsql8/bin/pg_ctl
-rwxr-xr-x   2 root  admin  47380 Aug 24 14:24
/opt/local/lib/pgsql8/bin/pg_ctl
~ $ ll /usr/local/bin/pg_ctl
-rwxr-xr-x   1 root  admin  149456 Apr 23 15:00
/usr/local/bin/pg_ctl

I can't remember if I tried installing PostgreSQL some
time ago using Fink or some other way. Any further
ideas?

Thanks,
csn


--- Douglas McNaught <doug@mcnaught.org> wrote:

> CSN <cool_screen_name90001@yahoo.com> writes:
>
> > DETAIL:  The database cluster was initialized
> without
> > HAVE_INT64_TIMESTAMP but the server was compiled
> with
> > HAVE_INT64_TIMESTAMP.
> > HINT:  It looks like you need to recompile or
> initdb.
>
> Is it possible you have two PG installs on this
> machine, and you're
> trying to start the wrong one?  Because it's hard to
> see how a restart
> could cause this error message--it's saying that
> your data files are
> incompatible with the code you're trying to run.
>
> -Doug
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: database files are incompatible with server, after

From
Douglas McNaught
Date:
CSN <cool_screen_name90001@yahoo.com> writes:

> I don't think so -- I followed the instructions here:
>
> http://www.robbyonrails.com/articles/2006/05/29/install-ruby-rails-and-postgresql-on-osx
>
> But looking around, I see there's a pg_ctl in
> /usr/local/bin, but 'port contents postgresql8' shows
> a pg_ctl in /opt/local/lib/pgsql8/bin.

Which one are you intending to run, and which one is first in the
PATH?

-Doug

--- Douglas McNaught <doug@mcnaught.org> wrote:

> CSN <cool_screen_name90001@yahoo.com> writes:
>
> > I don't think so -- I followed the instructions
> here:
> >
> >
>
http://www.robbyonrails.com/articles/2006/05/29/install-ruby-rails-and-postgresql-on-osx
> >
> > But looking around, I see there's a pg_ctl in
> > /usr/local/bin, but 'port contents postgresql8'
> shows
> > a pg_ctl in /opt/local/lib/pgsql8/bin.
>
> Which one are you intending to run, and which one is
> first in the
> PATH?
>
> -Doug
>

I don't know which to run now! I tried both
'/opt/local/lib/pgsql8/bin/pg_ctl start -D pgdata' and
putting /opt/local/lib/pgsql8/bin before
/usr/local/bin in PATH and still get the same error.

Is there some way to specify HAVE_INT64_TIMESTAMP to
pg_ctl or just export what's in the existing pgdata
and initdb a new dir and import?

Thanks,
csn

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: database files are incompatible with server, after

From
Douglas McNaught
Date:
CSN <cool_screen_name90001@yahoo.com> writes:

> I don't know which to run now! I tried both
> '/opt/local/lib/pgsql8/bin/pg_ctl start -D pgdata' and

You might want an absolute path for 'pgdata' here--have you tried
that?

> putting /opt/local/lib/pgsql8/bin before
> /usr/local/bin in PATH and still get the same error.

Why don't you run both pg_ctl binaries with the --version option, then
compare against the pg_control file in the data directory (I think
that's what it's called).  That should hopeully tell you which one to
run.

> Is there some way to specify HAVE_INT64_TIMESTAMP to
> pg_ctl or just export what's in the existing pgdata
> and initdb a new dir and import?

You need to have the server running to export.  :)

I'm pretty sure you can get it running; you just haven't tried the
right way yet.

-Doug

--- Douglas McNaught <doug@mcnaught.org> wrote:

> CSN <cool_screen_name90001@yahoo.com> writes:
>
> > I don't know which to run now! I tried both
> > '/opt/local/lib/pgsql8/bin/pg_ctl start -D pgdata'
> and
>
> You might want an absolute path for 'pgdata'
> here--have you tried
> that?

~ $ /opt/local/lib/pgsql8/bin/pg_ctl start -D
/Users/csn/pgdata/
postmaster starting
~ $ FATAL:  database files are incompatible with
server
DETAIL:  The database cluster was initialized without
HAVE_INT64_TIMESTAMP but the server was compiled with
HAVE_INT64_TIMESTAMP.
HINT:  It looks like you need to recompile or initdb.

> > putting /opt/local/lib/pgsql8/bin before
> > /usr/local/bin in PATH and still get the same
> error.
>
> Why don't you run both pg_ctl binaries with the
> --version option, then
> compare against the pg_control file in the data
> directory (I think
> that's what it's called).  That should hopeully tell
> you which one to run

~ $ /usr/local/bin/pg_ctl --version
pg_ctl (PostgreSQL) 8.1.3

~ $ /opt/local/lib/pgsql8/bin/pg_ctl --version
pg_ctl (PostgreSQL) 8.1.3

pgdata/global/pg_control seems to be binary.
pgdata/PG_VERSION has 8.1 in it.

> > Is there some way to specify HAVE_INT64_TIMESTAMP
> to
> > pg_ctl or just export what's in the existing
> pgdata
> > and initdb a new dir and import?
>
> You need to have the server running to export.  :)
>
> I'm pretty sure you can get it running; you just
> haven't tried the
> right way yet.

I'm stymied. ;)

Thanks,
csn


>
> -Doug
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--- Douglas McNaught <doug@mcnaught.org> wrote:

> CSN <cool_screen_name90001@yahoo.com> writes:
>
> > I'm stymied. ;)
>
> So you've tried running both pg_ctl binaries against the data
> directory and both don't work?

Yes.

> I wonder how you ever had a database
> working in that case.  :)

Me too! After 'initdb -D pgdata' and 'pg_ctl start -D pgdata' it ran for days during which I used
it quite a bit. But once I restarted the computer...

> Are you sure you have the right data directory?
> Maybe the one you
> think was used isn't the one that was actually used.

Yes. But I looked around and couldn't find any other data dirs.

> If you can't get either to work, you should hopefully be able to
> compile a version of the code with HAVE_64BIT_TIMESTAMP and get it to
> start up with your data.

Hmm, I installed using DarwinPorts (I think, if that uses 'port') -- I wonder if it can be
reinstalled that way and pass a HAVE_64BIT_TIMESTAMP flag to it. Besides my current problem, does
HAVE_64BIT_TIMESTAMP have much impact on things?

> But I still strongly suspect this'll be an
> easy fix once we figure it out.  :)

Heh, I hope. :) I've never had a problem with PG before, and I have no idea how or why this
happened (other than it coincided with rebooting).

Thanks,
csn


> -Doug
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com