Thread: Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

Dear list users,

For some tests, I installed a new cluster with different parameters
than the ones I had used a while ago to create the cluster I use for
my day-to-day activities (let's call it my main cluster). I used
initdb --no-locale -E UTF8 -D <new_data_dir>. Then I used pg_ctl -D
<new_data_dir> to start my new cluster. So far so good.

Then, I realized that I wanted to add a database superuser with the
option -U to initdb so I decided to delete the newly created cluster.
Instead, I mistakenly deleted the data directory of my main cluster
(doh!), using Shift-Delete. I recovered the deleted data directory
using Panda Recovery Tool and copied it back into its location (the
location pointed to by $PGDATA).

Now for the real problems: if I go in pgAdmin and try to connect to my
cluster, I get the "server not listening" message. I also tried going
to the Services and restarting postgresql but it says it is already
stopped and if I try to start it, I get a message that it has started
but then stopped because it was unused.

If I try pg_ctl start, I get this message (my translation from
French):  FATAL: database files are incompatible with server. DETAIL:
Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
Looks like you need initdb.

I really don't know what to do and would appreciate any help, if
anything can be done to recover my databases. My PG version is 9.3.3
on a Windows 7 64-bit OS.

Best regards and many thanks for your advice,

Guillaume

On 04/17/2014 01:29 PM, Guillaume Drolet wrote:
> Dear list users,
>
> For some tests, I installed a new cluster with different parameters
> than the ones I had used a while ago to create the cluster I use for
> my day-to-day activities (let's call it my main cluster). I used
> initdb --no-locale -E UTF8 -D <new_data_dir>. Then I used pg_ctl -D
> <new_data_dir> to start my new cluster. So far so good.
>
> Then, I realized that I wanted to add a database superuser with the
> option -U to initdb so I decided to delete the newly created cluster.

You do not use initdb to add superusers, that can be done with
createuser from the command line or CREATE ROLE via SQL. The -U option
just establishes the first superuser and is not even necessary as it
defaults to the system user running initdb.

> Instead, I mistakenly deleted the data directory of my main cluster
> (doh!), using Shift-Delete. I recovered the deleted data directory
> using Panda Recovery Tool and copied it back into its location (the
> location pointed to by $PGDATA).

Are you sure you recovered the entire directory?

>
> Now for the real problems: if I go in pgAdmin and try to connect to my
> cluster, I get the "server not listening" message. I also tried going
> to the Services and restarting postgresql but it says it is already
> stopped and if I try to start it, I get a message that it has started
> but then stopped because it was unused.
>
> If I try pg_ctl start, I get this message (my translation from
> French):  FATAL: database files are incompatible with server. DETAIL:
> Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
> while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
> Looks like you need initdb.

Well 937 is the version for 9.3. The version number increases with time,
but I am pretty sure Postgres is no where near 16795209, so this does
not look promising :( At a guess either the delete happened while the
cluster was running and a file or files where corrupted or the data
recovery was not entirely successful.

>
> I really don't know what to do and would appreciate any help, if
> anything can be done to recover my databases. My PG version is 9.3.3
> on a Windows 7 64-bit OS.

Do you have a backup of your main cluster?

>
> Best regards and many thanks for your advice,
>
> Guillaume
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/17/2014 01:29 PM, Guillaume Drolet wrote:
> Dear list users,
>
> For some tests, I installed a new cluster with different parameters
> than the ones I had used a while ago to create the cluster I use for
> my day-to-day activities (let's call it my main cluster). I used
> initdb --no-locale -E UTF8 -D <new_data_dir>. Then I used pg_ctl -D
> <new_data_dir> to start my new cluster. So far so good.
>
> Then, I realized that I wanted to add a database superuser with the
> option -U to initdb so I decided to delete the newly created cluster.
> Instead, I mistakenly deleted the data directory of my main cluster
> (doh!), using Shift-Delete. I recovered the deleted data directory
> using Panda Recovery Tool and copied it back into its location (the
> location pointed to by $PGDATA).
>
> Now for the real problems: if I go in pgAdmin and try to connect to my
> cluster, I get the "server not listening" message. I also tried going
> to the Services and restarting postgresql but it says it is already
> stopped and if I try to start it, I get a message that it has started
> but then stopped because it was unused.
>
> If I try pg_ctl start, I get this message (my translation from
> French):  FATAL: database files are incompatible with server. DETAIL:
> Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
> while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
> Looks like you need initdb.
>
> I really don't know what to do and would appreciate any help, if
> anything can be done to recover my databases. My PG version is 9.3.3
> on a Windows 7 64-bit OS.

Further investigation found pg_resetxlog:

http://www.postgresql.org/docs/9.3/static/app-pgresetxlog.html

I have never used it, so all I can do is point to the docs. I would say
that if you go this route make a copy of your $PGDATA in another
location for safe keeping in the event things go wrong.


>
> Best regards and many thanks for your advice,
>
> Guillaume
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/17/2014 01:29 PM, Guillaume Drolet wrote:
> Dear list users,
>
> For some tests, I installed a new cluster with different parameters
> than the ones I had used a while ago to create the cluster I use for
> my day-to-day activities (let's call it my main cluster). I used
> initdb --no-locale -E UTF8 -D <new_data_dir>. Then I used pg_ctl -D
> <new_data_dir> to start my new cluster. So far so good.
>
> Then, I realized that I wanted to add a database superuser with the
> option -U to initdb so I decided to delete the newly created cluster.
> Instead, I mistakenly deleted the data directory of my main cluster
> (doh!), using Shift-Delete. I recovered the deleted data directory
> using Panda Recovery Tool and copied it back into its location (the
> location pointed to by $PGDATA).
>
> Now for the real problems: if I go in pgAdmin and try to connect to my
> cluster, I get the "server not listening" message. I also tried going
> to the Services and restarting postgresql but it says it is already
> stopped and if I try to start it, I get a message that it has started
> but then stopped because it was unused.
>
> If I try pg_ctl start, I get this message (my translation from
> French):  FATAL: database files are incompatible with server. DETAIL:
> Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
> while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
> Looks like you need initdb.


More thoughts.

What happened to the new cluster?

Is it still running?

When you got the error messages where you pointing at the old or new
cluster?

>
> I really don't know what to do and would appreciate any help, if
> anything can be done to recover my databases. My PG version is 9.3.3
> on a Windows 7 64-bit OS.
>
> Best regards and many thanks for your advice,
>
> Guillaume
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks for the doc on pg_resetxlog. Will try it next week when I'm
back from Easter holiday, first backuping $PGDATA.

Most of my databases are stored in a default tablespace I defined on a
different disk than that of my PG installation (and $PGDATA): could I
just reinstall PG and then re-link it with my databases on that disk?

2014-04-17 20:29 GMT-04:00 Adrian Klaver <adrian.klaver@aklaver.com>:
> On 04/17/2014 01:29 PM, Guillaume Drolet wrote:
>>
>> Dear list users,
>>
>> For some tests, I installed a new cluster with different parameters
>> than the ones I had used a while ago to create the cluster I use for
>> my day-to-day activities (let's call it my main cluster). I used
>> initdb --no-locale -E UTF8 -D <new_data_dir>. Then I used pg_ctl -D
>> <new_data_dir> to start my new cluster. So far so good.
>>
>> Then, I realized that I wanted to add a database superuser with the
>> option -U to initdb so I decided to delete the newly created cluster.
>> Instead, I mistakenly deleted the data directory of my main cluster
>> (doh!), using Shift-Delete. I recovered the deleted data directory
>> using Panda Recovery Tool and copied it back into its location (the
>> location pointed to by $PGDATA).
>>
>> Now for the real problems: if I go in pgAdmin and try to connect to my
>> cluster, I get the "server not listening" message. I also tried going
>> to the Services and restarting postgresql but it says it is already
>> stopped and if I try to start it, I get a message that it has started
>> but then stopped because it was unused.
>>
>> If I try pg_ctl start, I get this message (my translation from
>> French):  FATAL: database files are incompatible with server. DETAIL:
>> Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
>> while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
>> Looks like you need initdb.
>>
>> I really don't know what to do and would appreciate any help, if
>> anything can be done to recover my databases. My PG version is 9.3.3
>> on a Windows 7 64-bit OS.
>
>
> Further investigation found pg_resetxlog:
>
> http://www.postgresql.org/docs/9.3/static/app-pgresetxlog.html
>
> I have never used it, so all I can do is point to the docs. I would say that
> if you go this route make a copy of your $PGDATA in another location for
> safe keeping in the event things go wrong.
>
>
>
>>
>> Best regards and many thanks for your advice,
>>
>> Guillaume
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

On 04/18/2014 06:25 AM, Guillaume Drolet wrote:
> Thanks for the doc on pg_resetxlog. Will try it next week when I'm
> back from Easter holiday, first backuping $PGDATA.
>
> Most of my databases are stored in a default tablespace I defined on a
> different disk than that of my PG installation (and $PGDATA): could I
> just reinstall PG and then re-link it with my databases on that disk?
>

Not really, $PGDATA holds the global information for the cluster. If you
reinstalled which implies another initdb then you would have a
head($PGDATA) that knows nothing about the rest of the body.

--
Adrian Klaver
adrian.klaver@aklaver.com