Forgot to dump old data before re-installing machine

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

And also remember to use the same version of Postgres as the previous
installation...

It might be helpful to post the tail of your server's log ahen it fails.

Best Regards,

On Jan 14, 2008 7:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device

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/

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.