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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFHjMi1IB7bNG8LQkwRAjt2AJ91tLxoP7dN4zjMxUteGZ T8bDJGPACeI86H
38SADq1/5stmtzi89HyhR5Q=
=ZA2p
-----END PGP SIGNATURE-----

>>>> 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...



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

>> 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



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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/ ----

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster