Thread: Forgot to dump old data before re-installing machine

Hi there,

I re-installed my machine and "forgot" to dump my database(s). I
naturally still have the whole database folders. For the moment I
installed the "old" postgres version (8.1) to be able to read my data.
But how can I read them? It seems that it doesn't work that I just
overwrite the new database folder with the old one... Would be too
simple, I guess...

Can someone please give me a hint on how I should proceed?!

Thanks a lot!

Stef

Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> writes:
> I re-installed my machine and "forgot" to dump my database(s). I
> naturally still have the whole database folders. For the moment I
> installed the "old" postgres version (8.1) to be able to read my data.
> But how can I read them? It seems that it doesn't work that I just
> overwrite the new database folder with the old one... Would be too
> simple, I guess...

Should work, if you've got the whole $PGDATA directory tree.  Maybe
you forgot to stop the postmaster while copying the backup into place?

            regards, tom lane

>> I re-installed my machine and "forgot" to dump my database(s). I
>> naturally still have the whole database folders. For the moment I
>> installed the "old" postgres version (8.1) to be able to read my
>> data.
>> But how can I read them? It seems that it doesn't work that I just
>> overwrite the new database folder with the old one... Would be too
>> simple, I guess...
>
> Should work, if you've got the whole $PGDATA directory tree.  Maybe
> you forgot to stop the postmaster while copying the backup into place?

Thanks a lot for this. Still trying. But although the postmaster did
run at one time, now, after copying back and forth, it doesn't want to
do anything anymore... Gush, getting really frustrated...

Stef

Stefan Schwarzer wrote:
>>> I re-installed my machine and "forgot" to dump my database(s). I
>>> naturally still have the whole database folders. For the moment I
>>> installed the "old" postgres version (8.1) to be able to read my data.
>>> But how can I read them? It seems that it doesn't work that I just
>>> overwrite the new database folder with the old one... Would be too
>>> simple, I guess...
>>
>> Should work, if you've got the whole $PGDATA directory tree.  Maybe
>> you forgot to stop the postmaster while copying the backup into place?
>
> Thanks a lot for this. Still trying. But although the postmaster did run
> at one time, now, after copying back and forth, it doesn't want to do
> anything anymore... Gush, getting really frustrated...

Stop. Deep breath. Cup of coffee / tea, optional biscuit.
Stop the server (if it's running)
Check the version-number of the installed postgresql packages.
Check you still have your backups somewhere safe.
Re-run initdb to re-create your "data" directory.
Make sure plenty of logging is turned on in postgresql.conf
Start the server.
Verify that everything is fine, particularly that select version()
displays what you expect.
Stop the server.
Do the logs contain everything you'd expect? If not, update
postgresql.conf and re-start the server until they do.
Rename the data directory to "data.old" (or similar).
Try starting the server - check that it fails complaining "you need to
run initdb" (or similar).
Restore your backup to "data".
Make sure plenty of logging is turned on in postgresql.conf
Compare ownership + permissions on "data" vs "data.old", correct if
necessary.
Start the server.

With this setup you can compare both versions of your data directory and
see what's different. If the version of your backup and the version of
the software are the same then it will work.

--
   Richard Huxton
   Archonet Ltd

On Tue, Jan 15, 2008 at 02:42:05PM +0100, Stefan Schwarzer wrote:
> Thanks a lot for this. Still trying. But although the postmaster did
> run at one time, now, after copying back and forth, it doesn't want to
> do anything anymore... Gush, getting really frustrated...

If it really doesn't work, try this:
http://svana.org/kleptog/pgsql/pgfsck.html

Point it at the right directory and it can give you a dump of data.
It's not pretty, doesn't handle arrays or some of the less common
datatypes but it should get you 99% of the way.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

>>>> I re-installed my machine and "forgot" to dump my database(s). I
>>>> naturally still have the whole database folders. For the moment I
>>>> installed the "old" postgres version (8.1) to be able to read my
>>>> data.
>>>> But how can I read them? It seems that it doesn't work that I just
>>>> overwrite the new database folder with the old one... Would be too
>>>> simple, I guess...
>>>
>>> Should work, if you've got the whole $PGDATA directory tree.  Maybe
>>> you forgot to stop the postmaster while copying the backup into
>>> place?
>> Thanks a lot for this. Still trying. But although the postmaster
>> did run at one time, now, after copying back and forth, it doesn't
>> want to do anything anymore... Gush, getting really frustrated...
>
> Stop. Deep breath. Cup of coffee / tea, optional biscuit.
> Stop the server (if it's running)
> Check the version-number of the installed postgresql packages.
> Check you still have your backups somewhere safe.
> Re-run initdb to re-create your "data" directory.
> Make sure plenty of logging is turned on in postgresql.conf
> Start the server.
> Verify that everything is fine, particularly that select version()
> displays what you expect.
> Stop the server.
> Do the logs contain everything you'd expect? If not, update
> postgresql.conf and re-start the server until they do.
> Rename the data directory to "data.old" (or similar).
> Try starting the server - check that it fails complaining "you need
> to run initdb" (or similar).
> Restore your backup to "data".
> Make sure plenty of logging is turned on in postgresql.conf
> Compare ownership + permissions on "data" vs "data.old", correct if
> necessary.
> Start the server.
>
> With this setup you can compare both versions of your data directory
> and see what's different. If the version of your backup and the
> version of the software are the same then it will work.

Ok, thanks for these steps. Coffee and chocolate helped... :-))

Ok, did what you said: stopping server, deleting "newly" created
"data" directory, re-running initdb, starting the server, stopping the
server.

Renamed "empty" data directory.

Restarting server: NOT COMPLAINING "you need to run initdb" or
something else.... Although it's saying that it starts, nothing
appears in the process list (ps -ef).

and renamed "old, backuped" data directory to "data_postgres"

Can't see any differences in permissions... Looks like this:

drwx------   14 schwarzer  schwarzer   476 Jan 14 13:33 data_postgres

Restarted the server.

/opt/local/lib/postgresql81/bin/psql postgres

\l

         List of databases
    Name    |   Owner   | Encoding
-----------+-----------+----------
  postgres  | schwarzer | UTF8
  template0 | schwarzer | UTF8
  template1 | schwarzer | UTF8


I should have a couple of databases appearing here...

Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> writes:
> Ok, did what you said: stopping server, deleting "newly" created
> "data" directory, re-running initdb, starting the server, stopping the
> server.

> Renamed "empty" data directory.

> Restarting server: NOT COMPLAINING "you need to run initdb" or
> something else.... Although it's saying that it starts, nothing
> appears in the process list (ps -ef).

Hmm, you need to stop here and figure out exactly what happens.

What procedure are you using to start the server?  I assume you
are not directly typing "postmaster", but using some script,
because the bare postmaster would certainly not act that way.
I guess that either the script silently runs initdb for you
(which is generally thought a bad idea nowadays) or that it
is redirecting the postmaster's log output someplace that you're
not looking.  Anyway, don't go past this step until you understand
what you're seeing.

            regards, tom lane

>> Ok, did what you said: stopping server, deleting "newly" created
>> "data" directory, re-running initdb, starting the server, stopping
>> the
>> server.
>
>> Renamed "empty" data directory.
>
>> Restarting server: NOT COMPLAINING "you need to run initdb" or
>> something else.... Although it's saying that it starts, nothing
>> appears in the process list (ps -ef).
>
> Hmm, you need to stop here and figure out exactly what happens.
>
> What procedure are you using to start the server?  I assume you
> are not directly typing "postmaster", but using some script,
> because the bare postmaster would certainly not act that way.
> I guess that either the script silently runs initdb for you
> (which is generally thought a bad idea nowadays) or that it
> is redirecting the postmaster's log output someplace that you're
> not looking.  Anyway, don't go past this step until you understand
> what you're seeing.


Uff....

Ok, here is what I did after "compiling" postgres8.1 (getting it from
MacPorts):

/opt/local/lib/postgresql81/bin/initdb -D Documents/data_postgres
/opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l
logfile start
    ps -ef shows the postmaster process
/opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l
logfile stop
    renaming "data_postgres" to "data_postgres.orig"
/opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l
logfile start
    which tells me:
        postmaster starting
    but in ps -ef there is no process listed

When I re-rename the newly created folder (data_postgres.orig into
data_postgres) the "start" works again. But it does not work with the
"old" (backuped) data folder...

So, as I mentioned before, it seems not to be that simple, that I can
just copy the "old" (backuped) data folder onto the newly created.

Is there any way I can figure out with which version I have created
the "old" databases? Perhaps, in a "worst" case scenario they have
been created in 8.0.... I will try...

Stef

>>> Ok, did what you said: stopping server, deleting "newly" created
>>> "data" directory, re-running initdb, starting the server, stopping
>>> the
>>> server.
>>
>>> Renamed "empty" data directory.
>>
>>> Restarting server: NOT COMPLAINING "you need to run initdb" or
>>> something else.... Although it's saying that it starts, nothing
>>> appears in the process list (ps -ef).
>>
>> Hmm, you need to stop here and figure out exactly what happens.
>>
>> What procedure are you using to start the server?  I assume you
>> are not directly typing "postmaster", but using some script,
>> because the bare postmaster would certainly not act that way.
>> I guess that either the script silently runs initdb for you
>> (which is generally thought a bad idea nowadays) or that it
>> is redirecting the postmaster's log output someplace that you're
>> not looking.  Anyway, don't go past this step until you understand
>> what you're seeing.
>
>
> Uff....
>
> Ok, here is what I did after "compiling" postgres8.1 (getting it
> from MacPorts):
>
> /opt/local/lib/postgresql81/bin/initdb -D Documents/data_postgres
> /opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l
> logfile start
>     ps -ef shows the postmaster process
> /opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l
> logfile stop
>     renaming "data_postgres" to "data_postgres.orig"
> /opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l
> logfile start
>     which tells me:
>         postmaster starting
>     but in ps -ef there is no process listed
>
> When I re-rename the newly created folder (data_postgres.orig into
> data_postgres) the "start" works again. But it does not work with
> the "old" (backuped) data folder...
>
> So, as I mentioned before, it seems not to be that simple, that I
> can just copy the "old" (backuped) data folder onto the newly created.
>
> Is there any way I can figure out with which version I have created
> the "old" databases? Perhaps, in a "worst" case scenario they have
> been created in 8.0.... I will try...

The logfile is telling me this when I try to start the server with my
"old" data folder:

FATAL:  database files are incompatible with server
DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION
738394112, but the server was compiled with PG_CONTROL_VERSION 812.

What does it mean? I have and had 8.1 installed...

Thanks for any help!!

Stef

On Mittwoch, 16. Januar 2008, Stefan Schwarzer wrote:
| The logfile is telling me this when I try to start the server with my  
| "old" data folder:
|
| FATAL:  database files are incompatible with server
| DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION  
| 738394112, but the server was compiled with PG_CONTROL_VERSION 812.
|
| What does it mean? I have and had 8.1 installed...

I didn't follow the thread, but look at the output of

  $ printf "%x\n" 738394112
  2c030000

and

  $ printf "%x\n" 812
  32c

This looks like an endianess mismatch; did you already mention on what
architecture you are on?

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----

> | The logfile is telling me this when I try to start the server with
> my
> | "old" data folder:
> |
> | FATAL:  database files are incompatible with server
> | DETAIL:  The database cluster was initialized with
> PG_CONTROL_VERSION
> | 738394112, but the server was compiled with PG_CONTROL_VERSION 812.
> |
> | What does it mean? I have and had 8.1 installed...
>
> I didn't follow the thread, but look at the output of
>
>  $ printf "%x\n" 738394112
>  2c030000
>
> and
>
>  $ printf "%x\n" 812
>  32c
>
> This looks like an endianess mismatch; did you already mention on what
> architecture you are on?

MacPro, Leopard

Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> writes:
>> This looks like an endianess mismatch; did you already mention on what
>> architecture you are on?

> MacPro, Leopard

Did you just move from a PPC-based Mac to an Intel-based one?
If so, you're out of luck --- you need to go back to the PPC
to make a dump of those files.

            regards, tom lane

>>> This looks like an endianess mismatch; did you already mention on
>>> what
>>> architecture you are on?
>
>> MacPro, Leopard
>
> Did you just move from a PPC-based Mac to an Intel-based one?
> If so, you're out of luck --- you need to go back to the PPC
> to make a dump of those files.
>

No, I just re-installed my Intel Mac. First I just upgraded from Tiger
to Leopard (without getting my database to run; but I didn't put much
effort into it); and then I completely erased the disk and installed
Leopard from scratch.

>>>> This looks like an endianess mismatch; did you already mention on
>>>> what
>>>> architecture you are on?
>>
>>> MacPro, Leopard
>>
>> Did you just move from a PPC-based Mac to an Intel-based one?
>> If so, you're out of luck --- you need to go back to the PPC
>> to make a dump of those files.
>>
>
> No, I just re-installed my Intel Mac. First I just upgraded from
> Tiger to Leopard (without getting my database to run; but I didn't
> put much effort into it); and then I completely erased the disk and
> installed Leopard from scratch.

Hmmmm..... Can't be that I am standing now there having lost my data,
no? Please, any faintest idea what I can try?

Thanks for hints!

Stef

>>>>>
>>> Did you just move from a PPC-based Mac to an Intel-based one?
>>> If so, you're out of luck --- you need to go back to the PPC
>>> to make a dump of those files.
>>>
>>
>> No, I just re-installed my Intel Mac. First I just upgraded from
>> Tiger to Leopard (without getting my database to run; but I didn't
>> put much effort into it); and then I completely erased the disk and
>> installed Leopard from scratch.
>
> Hmmmm..... Can't be that I am standing now there having lost my
> data, no? Please, any faintest idea what I can try?

Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom
suggested.

So, I copied all files to a PPC, but which runs Linux - don't know if
this is important. Now, it tells me:

"Fatal error: Incorrect checksum on control file"

Any way out of this? Thanks for any advice.

Stef

On Jan 18, 2008 7:25 AM, Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> wrote:
> >>>>>
> >>> Did you just move from a PPC-based Mac to an Intel-based one?
> >>> If so, you're out of luck --- you need to go back to the PPC
> >>> to make a dump of those files.
> >>>
> >>
> >> No, I just re-installed my Intel Mac. First I just upgraded from
> >> Tiger to Leopard (without getting my database to run; but I didn't
> >> put much effort into it); and then I completely erased the disk and
> >> installed Leopard from scratch.
> >
> > Hmmmm..... Can't be that I am standing now there having lost my
> > data, no? Please, any faintest idea what I can try?
>
> Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom
> suggested.
>
> So, I copied all files to a PPC, but which runs Linux - don't know if
> this is important. Now, it tells me:
>
> "Fatal error: Incorrect checksum on control file"
>
> Any way out of this? Thanks for any advice.

Yes, you need to set up a machine running the same OS and pgsql
version and build as before.

On 18/01/2008, Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> wrote:
> Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom
> suggested.
>
> So, I copied all files to a PPC, but which runs Linux - don't know if
> this is important. Now, it tells me:
>
> "Fatal error: Incorrect checksum on control file"
>
> Any way out of this? Thanks for any advice.

That's the kind of error I'd expect to see if you try to start an
Intel data directory on PPC or vice-versa. You said earlier this was
data from your Intel Mac being reloaded on the same Intel Mac. If
thats the case, put the PPC away before you confuse yourself :-)

As for the real problem (on the same hardware), when you rebuilt
Postgres on your new machine did you change any of the configure
options that MacPorts would have used from what would have been used
previously (I assume they can be overridden)? I don't know if you'd
see exactly the same symptoms you have, but changing settings like
integer datetimes will break things in a similar way.

Regards, Dave

>> Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom
>> suggested.
>>
>> So, I copied all files to a PPC, but which runs Linux - don't know if
>> this is important. Now, it tells me:
>>
>> "Fatal error: Incorrect checksum on control file"
>>
>> Any way out of this? Thanks for any advice.
>
> That's the kind of error I'd expect to see if you try to start an
> Intel data directory on PPC or vice-versa. You said earlier this was
> data from your Intel Mac being reloaded on the same Intel Mac. If
> thats the case, put the PPC away before you confuse yourself :-)
>
> As for the real problem (on the same hardware), when you rebuilt
> Postgres on your new machine did you change any of the configure
> options that MacPorts would have used from what would have been used
> previously (I assume they can be overridden)? I don't know if you'd
> see exactly the same symptoms you have, but changing settings like
> integer datetimes will break things in a similar way.


Here is some input from Martijn:

_______________________________________________________

Well, it's clearly byte-swapped. So whatever the database was running
on it was on a PPC or some other big-endian machine.

The give away is that bytes 16&17 are "20 00" rather than "00
20".  You can check a file with:

$  od -t x1 -j 16 -N 2 < filename
0000020 00 20
0000022

That's for Intel, on the file you sent me it's:
0000020 20 00
0000022

I don't understand it either, which is why I was wondering if it was
running under some PPC emulation (can you run standard mac software or
do you have to get special Intel versions).
_______________________________________________________


I have no idea how my old postgres config was. I've contacted already
william@kyngchaos to see if he still has the old .dmgs, which is I
think what I used to install postgres with.

Gush, gush, gush....

On 18/01/2008, Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> wrote:
> I don't understand it either, which is why I was wondering if it was
> running under some PPC emulation (can you run standard mac software or
> do you have to get special Intel versions).]

Yes, Apple have an emulation layer called Rosetta - but you said you
used MacPorts so you should have a native build.

> I have no idea how my old postgres config was. I've contacted already
> william@kyngchaos to see if he still has the old .dmgs, which is I
> think what I used to install postgres with.

Aha - previously undisclosed info :-) Yes, then it does seem quite
feasible that your old build was not only differing in achitecture,
but possibly also other configuration options that would have similar
effects.

Note to the other hackers - is it worth having initdb dump the
architecture details and configure options used into the cluster in a
human readble form so we can pickup on this sort of thing more easily
in the future?

Regards, Dave.

"Dave Page" <dpage@postgresql.org> writes:
> As for the real problem (on the same hardware), when you rebuilt
> Postgres on your new machine did you change any of the configure
> options that MacPorts would have used from what would have been used
> previously (I assume they can be overridden)?

There's not that much that can be overridden that would affect the
layout of pg_control.  The only other thing I can think of at the
moment is that moving from 32-bit to 64-bit time_t can screw things
up --- but that shouldn't affect the interpretation of the pg_control
version number, which as already noted certainly looks like it's the
wrong endianness.

Stefan, could you post the actual pg_control file as a binary
attachment?

            regards, tom lane

"Dave Page" <dpage@postgresql.org> writes:
> Note to the other hackers - is it worth having initdb dump the
> architecture details and configure options used into the cluster in a
> human readble form so we can pickup on this sort of thing more easily
> in the future?

That's what pg_control is for.  We figured out easily enough that this
was an endianness problem; having had "big endian" somewhere in
cleartext wouldn't have improved matters.

            regards, tom lane

On 18/01/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Dave Page" <dpage@postgresql.org> writes:
> > Note to the other hackers - is it worth having initdb dump the
> > architecture details and configure options used into the cluster in a
> > human readble form so we can pickup on this sort of thing more easily
> > in the future?
>
> That's what pg_control is for.  We figured out easily enough that this
> was an endianness problem; having had "big endian" somewhere in
> cleartext wouldn't have improved matters.

It got figured out when someone who knew what they were looking for
peeked at the byte ordering in a file which for all we knew at the
time might have been damaged anyway - and the same test wouldn't have
spotted an integer_datetimes difference for example, something which
bit Greg & I recently and had us puzzled for a while.

For just about zero cost we could drop something like:

--------
Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed
Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386

Configuration: '--prefix=/usr/local/pgsql83/'
'--enable-integer-datetimes' '--with-openssl' '--with-perl'
'--with-python' '--with-tcl' '--without-tk' '--with-bonjour'
'--with-pam' '--with-krb5' 'CFLAGS=-O -g -arch i386 -arch ppc'
'LDFLAGS=-ltcl'
--------

in a file in $PGDATA which would make it much easier for users and
hackers to see where the cluster came from and compare it to the
actual build.

/D

"Dave Page" <dpage@postgresql.org> writes:
> On 18/01/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> That's what pg_control is for.  We figured out easily enough that this
>> was an endianness problem; having had "big endian" somewhere in
>> cleartext wouldn't have improved matters.

> It got figured out when someone who knew what they were looking for
> peeked at the byte ordering in a file which for all we knew at the
> time might have been damaged anyway - and the same test wouldn't have
> spotted an integer_datetimes difference for example, something which
> bit Greg & I recently and had us puzzled for a while.

Hm?  integer_datetimes is encoded separately and there's a very specific
error message if it's wrong.  The case I think you are remembering was
caused by a width-of-time_t discrepancy, which should be fixed but it's
got nothing to do with anything else.

> For just about zero cost we could drop something like:

> --------
> Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed
> Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386

> Configuration: '--prefix=/usr/local/pgsql83/'
> '--enable-integer-datetimes' '--with-openssl' '--with-perl'
> '--with-python' '--with-tcl' '--without-tk' '--with-bonjour'
> '--with-pam' '--with-krb5' 'CFLAGS=-O -g -arch i386 -arch ppc'
> 'LDFLAGS=-ltcl'
> --------

Zero cost and also zero benefit.  The missing piece of information here
was that the executable being used was running under PPC emulation, and
I'll bet money that there would have been nothing in either uname or
pg_config output that would have told us that.

            regards, tom lane

On 18/01/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hm?  integer_datetimes is encoded separately and there's a very specific
> error message if it's wrong.  The case I think you are remembering was
> caused by a width-of-time_t discrepancy, which should be fixed but it's
> got nothing to do with anything else.

Yeah, I think you're probably right there.

> Zero cost and also zero benefit.  The missing piece of information here
> was that the executable being used was running under PPC emulation, and
> I'll bet money that there would have been nothing in either uname or
> pg_config output that would have told us that.

I'd wager there would be a fairly good chance that a PPC-only binary
on a Mac would most likely have been built on a PPC, and thus mention
that in the uname output at build time. I can't imagine many folks are
building PPC-only binaries on Intels.

How much money did you have in mind? :-)

/D

Am Freitag, 18. Januar 2008 schrieb Dave Page:
> It got figured out when someone who knew what they were looking for
> peeked at the byte ordering in a file which for all we knew at the
> time might have been damaged anyway

What might be better is if we had an explicit endianness mark in pg_control
rather than relying on users discovering endianness problems by seemingly
corrupted version numbers.

> For just about zero cost we could drop something like:
>
> --------
> Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed
> Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386

I think we should address the problem were it happens.  Adding this output
will increase the amount of information available for causing confusion,
while it would probably still require expert knowledge to read an endianness
issue out of that.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On 18/01/2008, Peter Eisentraut <peter_e@gmx.net> wrote:
> Am Freitag, 18. Januar 2008 schrieb Dave Page:
> > It got figured out when someone who knew what they were looking for
> > peeked at the byte ordering in a file which for all we knew at the
> > time might have been damaged anyway
>
> What might be better is if we had an explicit endianness mark in pg_control
> rather than relying on users discovering endianness problems by seemingly
> corrupted version numbers.

Seems reasonable to me. Obviously I'd mixed up the datetime/time_t
issues I ran into previously, so having the configure options as well
would be largely useless.

That said, is zlib used by toast or do we have some other code for
that? If it is used for that, do we record it's presence or absence in
pg_control?

/D

Dave Page wrote:

> That said, is zlib used by toast or do we have some other code for
> that? If it is used for that, do we record it's presence or absence in
> pg_control?

Nope, toast uses its own compression code.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

"Dave Page" <dpage@postgresql.org> writes:
> On 18/01/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Zero cost and also zero benefit.  The missing piece of information here
>> was that the executable being used was running under PPC emulation, and
>> I'll bet money that there would have been nothing in either uname or
>> pg_config output that would have told us that.

> I'd wager there would be a fairly good chance that a PPC-only binary
> on a Mac would most likely have been built on a PPC, and thus mention
> that in the uname output at build time. I can't imagine many folks are
> building PPC-only binaries on Intels.

uname is a separate executable.  If you do system("uname") you'll get
results that reflect how uname was built, not how Postgres was built.

I think this is likely to lead to more confusion, not less --- if we'd
had such output in the directory, it might have led us to disregard
the clear evidence of the wrong-endian version number, and fruitlessly
bark up some other tree instead.

            regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:
> What might be better is if we had an explicit endianness mark in pg_control
> rather than relying on users discovering endianness problems by seemingly
> corrupted version numbers.

Chicken-and-egg problem there: you won't know if there's an endianness
flag to check without having tested pg_control_version.

What would work better is to add some code that checks whether
pg_control_version looks like the byte-swap of a small number,
and prints a suitably modified error message if so.

I would not previously have thought this was worth the trouble,
but given what we now know about Apple's migration process,
it might be worth another half dozen lines of code and a new
error message.

What was that about string freeze ;-) ?

            regards, tom lane

On 18/01/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> uname is a separate executable.  If you do system("uname") you'll get
> results that reflect how uname was built, not how Postgres was built.

Right, I realise it's a seperate executable, but doesn't configure
rely on it anyway? Meaning if someones system has a uname that tells
configure it's a PPC when it's actually an Intel, the resulting binary
is likely to go bang anyway, assuming it even builds.

My suggestion was that we take the output of uname at configure/build
time and bung it in a macro, not do anything with system() at
runtime...

Anyway, Peter's suggestion seems much tidier.

/D

"Dave Page" <dpage@postgresql.org> writes:
> On 18/01/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> uname is a separate executable.  If you do system("uname") you'll get
>> results that reflect how uname was built, not how Postgres was built.

> My suggestion was that we take the output of uname at configure/build
> time and bung it in a macro, not do anything with system() at
> runtime...

Ah.  That would work better than what I thought you were suggesting, but
I still don't trust it a whole lot --- there's the problem of "universal
binaries" (PPC & PPC64 & Intel) for instance, which I believe some
people have managed to build Postgres as.

> Anyway, Peter's suggestion seems much tidier.

Agreed.  Also we could have it today if we base it off inspection of
pg_control_version.

            regards, tom lane

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Dave Page wrote:
>> That said, is zlib used by toast or do we have some other code for
>> that? If it is used for that, do we record it's presence or absence in
>> pg_control?

> Nope, toast uses its own compression code.

pg_dump/pg_restore use zlib, but I believe there's a pretty specific
error message if you try to read a compressed archive with a
non-zlib-enabled pg_restore.

            regards, tom lane

On 18/01/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Dave Page" <dpage@postgresql.org> writes:
> > On 18/01/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ah.  That would work better than what I thought you were suggesting, but
> I still don't trust it a whole lot --- there's the problem of "universal
> binaries" (PPC & PPC64 & Intel) for instance, which I believe some
> people have managed to build Postgres as.

Yes, I maintain such a distribution :-). Actually, the suggested
output I posted earlier was from a Universal build - the uname output
shows Intel only of course, but the CFLAGS do show both architectures.
The other way of building a universal binary (other than cross
compiling one architecture as I do), is to take native binaries from
each platform and literally glue them together. That won't show the
differences through CFLAGS, but each section of the universal binary
would have it's own native uname output.

/D

On Fri, 18 Jan 2008, Dave Page wrote:

> For just about zero cost we could drop something like:
> --------
> Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed
> Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386
>
> Configuration: '--prefix=/usr/local/pgsql83/'
> '--enable-integer-datetimes' '--with-openssl' '--with-perl'
> '--with-python' '--with-tcl' '--without-tk' '--with-bonjour'
> '--with-pam' '--with-krb5' 'CFLAGS=-O -g -arch i386 -arch ppc'
> 'LDFLAGS=-ltcl'
> --------
> in a file in $PGDATA

Stepping away from the question of whether it would have helped in this
specific case for a second, around once a month or so there's someone in a
panic here because they have a filesystem copy of a database they can't
figure out how to use.  Often the original server is a puddle of molten
metal or something by that point and the person trying to restore the data
is rather stressed.

The usual advice, telling them to replicate the binaries used to create it
in the first place, isn't always the easiest to follow.  It seems to me
that including a "environment at cluster creation" note in $PGDATA like
Dave suggests would be helpful for these cases; PG_VERSION just isn't
enough information.  I'd also throw in the locale information used for the
cluster, as that seems like something it would be nice to have in simple
text form as well there and is also a spot people are confused about.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith <gsmith@gregsmith.com> writes:
> The usual advice, telling them to replicate the binaries used to create it
> in the first place, isn't always the easiest to follow.  It seems to me
> that including a "environment at cluster creation" note in $PGDATA like
> Dave suggests would be helpful for these cases; PG_VERSION just isn't
> enough information.  I'd also throw in the locale information used for the
> cluster, as that seems like something it would be nice to have in simple
> text form as well there and is also a spot people are confused about.

pg_controldata already provides this information, no?  At least barring
the case of wrong-time_t-size, which we already know we want to fix.

            regards, tom lane

On Fri, 18 Jan 2008, Tom Lane wrote:

> pg_controldata already provides this information, no?  At least barring
> the case of wrong-time_t-size, which we already know we want to fix.

It provides some of it, and I think you could make a case that the text
file format Dave suggested could be prototyped in an improved form by
combining the output from pg_controldata plus pg_config plus uname -a.

If people knew to save a copy of the output from those religiously into
the base of the database directory to aid possibly unrelated people who
have to restore that data later, that would be nice.  In the cases I was
mentioning, people can't run pg_controldata until they have binaries
installed, and having a simple text file that contained the information
needed to do that right in the first place would give some guidance as to
get to that step.

You have to put yourself in the shoes of the person who has a database
backup and a crashed server to appreciate that anything that makes this
process easier is a huge help.  Imagine that the previous DBA just quit
(nuking the server on his way out) and you have the kind of real-world
crisis people really run into.  It would be nice if things progressed to
where, for example, someone could hand me a database backup I know nothing
about, I could look for this handy text file information in the base
directory, see:

CONFIGURE = '--build=i686-redhat-linux-gnu' ...
VERSION = PostgreSQL 8.2.5
Linux host.gregsmith.com 2.6.18-8.1.4.el5xen #1 SMP Thu May 17 05:27:09
EDT 2007 i686 i686 i386 GNU/Linux

And now I've got a real good idea what binary this dump came from and how
to get the instance back up and running in a few seconds of work.  Maybe
it's a suggested best-practice for now to save such a thing, maybe it gets
included as an automatic feature at initdb time in 8.4, but I think it's a
worthwhile idea to work toward helping people with.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

>> As for the real problem (on the same hardware), when you rebuilt
>> Postgres on your new machine did you change any of the configure
>> options that MacPorts would have used from what would have been used
>> previously (I assume they can be overridden)?
>
> There's not that much that can be overridden that would affect the
> layout of pg_control.  The only other thing I can think of at the
> moment is that moving from 32-bit to 64-bit time_t can screw things
> up --- but that shouldn't affect the interpretation of the pg_control
> version number, which as already noted certainly looks like it's the
> wrong endianness.
>
> Stefan, could you post the actual pg_control file as a binary
> attachment?
>

The "old" pg_control file is attached...


So, I am one step further, that is, I installed Tiger - and then
Postgres 8.1 - on a PPC MacMini.

After running initdb the postmaster started smoothly. I stopped it,
copied my database files into the same location, started the
postmaster again, and then got this error message:

    schwarzers-mac-mini:/usr/local/pgsql schwarzer$ /usr/local/bin/pg_ctl
-D /usr/local/pgsql/data -l postgres.log start
    pg_ctl: could not open PID file "/usr/local/pgsql/data/
postmaster.pid": Permission denied


As one can see from the following listing, there is no
"postmaster.pid". And the settings seemed to be (for me) the same as
in the old database tree:


schwarzers-mac-mini:/usr/local/pgsql schwarzer$ ls -l
total 0
drwx------   16 postgres  admin   544 Jan 23 18:23 data
drwx------   15 postgres  admin   510 Jan 23 17:50 data.orig


schwarzers-mac-mini:/usr/local/pgsql/data postgres$ ls -l
total 64
-rw-------    1 postgres  admin      4 Jan 23 18:23 PG_VERSION
drwx------   11 postgres  admin    374 Jan 23 18:23 base
drwx------   27 postgres  admin    918 Jan 23 18:23 global
drwx------   23 postgres  admin    782 Jan 23 18:23 pg_clog
-rw-------    1 postgres  admin   3396 Jan 23 18:23 pg_hba.conf
-rw-------    1 postgres  admin   1460 Jan 23 18:23 pg_ident.conf
drwx------    4 postgres  admin    136 Jan 23 18:23 pg_multixact
drwx------    3 postgres  admin    102 Jan 23 18:23 pg_subtrans
drwx------    2 postgres  admin     68 Jan 23 18:23 pg_tblspc
drwx------    2 postgres  admin     68 Jan 23 18:23 pg_twophase
drwx------   11 postgres  admin    374 Jan 23 18:23 pg_xlog
-rw-------    1 postgres  admin  13614 Jan 23 18:23 postgresql.conf
-rw-------    1 postgres  admin     74 Jan 23 18:23 postmaster.opts


schwarzers-mac-mini:/usr/local/pgsql/data.orig postgres$ ls -l
total 64
-rw-------    1 postgres  admin      4 Jan 23 17:49 PG_VERSION
drwx------    5 postgres  admin    170 Jan 23 17:49 base
drwx------   28 postgres  admin    952 Jan 23 17:50 global
drwx------    3 postgres  admin    102 Jan 23 17:49 pg_clog
-rw-------    1 postgres  admin   3396 Jan 23 17:49 pg_hba.conf
-rw-------    1 postgres  admin   1460 Jan 23 17:49 pg_ident.conf
drwx------    4 postgres  admin    136 Jan 23 17:49 pg_multixact
drwx------    3 postgres  admin    102 Jan 23 17:49 pg_subtrans
drwx------    2 postgres  admin     68 Jan 23 17:49 pg_tblspc
drwx------    2 postgres  admin     68 Jan 23 17:49 pg_twophase
drwx------    4 postgres  admin    136 Jan 23 17:49 pg_xlog
-rw-------    1 postgres  admin  13680 Jan 23 17:49 postgresql.conf
-rw-------    1 postgres  admin     49 Jan 23 17:50 postmaster.opts



Thanks for any hints...

Stef

Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> writes:
> After running initdb the postmaster started smoothly. I stopped it,
> copied my database files into the same location, started the
> postmaster again, and then got this error message:

>     schwarzers-mac-mini:/usr/local/pgsql schwarzer$ /usr/local/bin/pg_ctl
> -D /usr/local/pgsql/data -l postgres.log start
>     pg_ctl: could not open PID file "/usr/local/pgsql/data/
> postmaster.pid": Permission denied

You're trying to start the server as yourself (user schwarzer) but the
files all belong to user postgres:

> drwx------   16 postgres  admin   544 Jan 23 18:23 data

The files have to be owned by the user that runs the server.  If you
want to switch over to doing that as schwarzer, a quick "chown -R"
will help.

            regards, tom lane

On Jan 24, 2008, at 4:41 PM, Tom Lane wrote:

> Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> writes:
>> After running initdb the postmaster started smoothly. I stopped it,
>> copied my database files into the same location, started the
>> postmaster again, and then got this error message:
>
>>     schwarzers-mac-mini:/usr/local/pgsql schwarzer$ /usr/local/bin/
>> pg_ctl
>> -D /usr/local/pgsql/data -l postgres.log start
>>     pg_ctl: could not open PID file "/usr/local/pgsql/data/
>> postmaster.pid": Permission denied
>
> You're trying to start the server as yourself (user schwarzer) but the
> files all belong to user postgres:
>
>> drwx------   16 postgres  admin   544 Jan 23 18:23 data
>
> The files have to be owned by the user that runs the server.  If you
> want to switch over to doing that as schwarzer, a quick "chown -R"
> will help.

Oh, stupid me! Gush, I'll never learn it...

But nevertheless:

When I start the postmaster it seems ok. But there is no process
running. When I try to stop it it says:

pg_ctl: PID file "/usr/local/pgsql/data/postmaster.pid" does not exist
Is postmaster running?

I slowly get the feeling that all my efforts are in vain...

Stef

Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> writes:
> When I start the postmaster it seems ok. But there is no process
> running.

Then it didn't really start successfully.  (Unless you use the -w
option, "pg_ctl start" just launches the postmaster --- it doesn't
wait around to see what happens.)  You need to look into the log
file to see what the problem was.

            regards, tom lane

>> When I start the postmaster it seems ok. But there is no process
>> running.
>
> Then it didn't really start successfully.  (Unless you use the -w
> option, "pg_ctl start" just launches the postmaster --- it doesn't
> wait around to see what happens.)  You need to look into the log
> file to see what the problem was.

Ok, it's a bit confusing for a non-admin-user to enter into the pg-
config to find out what parameters one has to uncomment in order to
get the log working. Succeed after some while, got the postmaster
working:

   827  p2  S      0:00.22 /usr/local/bin/postmaster -D /usr/local/
pgsql/data TERM=xterm-color SHELL=/bin/bash USER=postgres
__CF_USER_TEXT_ENCODING=0x1F6:0:0 P
   829  p2  S      0:00.01 postgres: writer process
   830  p2  S      0:00.00 postgres: stats buffer process
   831  p2  S      0:00.00 postgres: stats collector process


What would you recommend now in order to get the data back? I have
postgis data in the databases too, although this is not too important
for me...

Thanks you so much for all your help!

Stef

On Thu, Jan 24, 2008 at 05:10:51PM +0100, Stefan Schwarzer wrote:
> Oh, stupid me! Gush, I'll never learn it...
>
> But nevertheless:
>
> When I start the postmaster it seems ok. But there is no process
> running. When I try to stop it it says:
>
> pg_ctl: PID file "/usr/local/pgsql/data/postmaster.pid" does not exist
> Is postmaster running?

If it fails to start it must log somehwere.

If you run the postmaster directly from the console it'll get you into
singleuser mode, try that.

(PS. Have you tried pgfsck on the new machine. With the same byte-order
it should work much betteR).

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

On Thu, Jan 24, 2008 at 05:41:20PM +0100, Stefan Schwarzer wrote:
> What would you recommend now in order to get the data back? I have
> postgis data in the databases too, although this is not too important
> for me...

pg_dump, pg_dumpall...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

On Jan 24, 2008 10:41 AM, Stefan Schwarzer
<stefan.schwarzer@grid.unep.ch> wrote:
> >> When I start the postmaster it seems ok. But there is no process
> >> running.
> >
> > Then it didn't really start successfully.  (Unless you use the -w
> > option, "pg_ctl start" just launches the postmaster --- it doesn't
> > wait around to see what happens.)  You need to look into the log
> > file to see what the problem was.
>
> Ok, it's a bit confusing for a non-admin-user to enter into the pg-
> config to find out what parameters one has to uncomment in order to
> get the log working. Succeed after some while, got the postmaster
> working:
>
>    827  p2  S      0:00.22 /usr/local/bin/postmaster -D /usr/local/
> pgsql/data TERM=xterm-color SHELL=/bin/bash USER=postgres
> __CF_USER_TEXT_ENCODING=0x1F6:0:0 P
>    829  p2  S      0:00.01 postgres: writer process
>    830  p2  S      0:00.00 postgres: stats buffer process
>    831  p2  S      0:00.00 postgres: stats collector process
>
>
> What would you recommend now in order to get the data back? I have
> postgis data in the databases too, although this is not too important
> for me...

Sounds like you're ready to run pg_dump.

>>>> When I start the postmaster it seems ok. But there is no process
>>>> running.
>>>
>>> Then it didn't really start successfully.  (Unless you use the -w
>>> option, "pg_ctl start" just launches the postmaster --- it doesn't
>>> wait around to see what happens.)  You need to look into the log
>>> file to see what the problem was.
>>
>> Ok, it's a bit confusing for a non-admin-user to enter into the pg-
>> config to find out what parameters one has to uncomment in order to
>> get the log working. Succeed after some while, got the postmaster
>> working:
>>
>>   827  p2  S      0:00.22 /usr/local/bin/postmaster -D /usr/local/
>> pgsql/data TERM=xterm-color SHELL=/bin/bash USER=postgres
>> __CF_USER_TEXT_ENCODING=0x1F6:0:0 P
>>   829  p2  S      0:00.01 postgres: writer process
>>   830  p2  S      0:00.00 postgres: stats buffer process
>>   831  p2  S      0:00.00 postgres: stats collector process
>>
>>
>> What would you recommend now in order to get the data back? I have
>> postgis data in the databases too, although this is not too important
>> for me...
>
> Sounds like you're ready to run pg_dump.

Ok, finally got everything working. I really don't understand a thing
about what has happened, since I was running the database since a year
on an Intel Mac.... But perhaps the fact that I moved to Leopard now
made it happen.

Anyway, installing the old postgres version on an old PPC and then re-
importing them into the new Intel-postgres database did the thing.

So, many thanks (!!!!) to everybody (!!!!!) for the valuable inputs,
time etc.

Best wishes,

Stef