[Date Prev][Date Next] [Thread Prev][Thread Next] [Date Index] [Thread Index]

Is it possible to recover PG-8.4 databases from raw data?

Hi all,

having messed up my GNU/Linux installation (squeeze/wheezy/sid) I had to
reinstall it but forgot to dump my PG database. I do have a fair recent
dump in my laptop but 

	I would like to know whether it is still possible to recover

from raw data, i.e. files in '/var/lib/postgresql/8.4/main'
which I copied  to a backup disk before *re-formatting /var & /usr* dirs
while installing Debian/Squeeze.
More than a necessity this is a challenge that I took to learn a bit
more about how things work in postgresql.

I read in docs that Debian sets thing a bit different from the simple
postgresql installation, so this should be the right NG to ask for help.

That said my system is now running under:

Linux 2.6.32-5-686 #1 SMP Sun Sep 23 09:49:36 UTC 2012 i686 GNU/Linux
postgresql-8.4/squeeze uptodate 8.4.13-0squeeze1
postgresql-client-8.4/squeeze uptodate 8.4.13-0squeeze1
postgresql-client-common/squeeze uptodate 113+squeeze1
postgresql-common/squeeze uptodate 113+squeeze1

a cluster was created in the same directory as before and I overwrote it
with the files in my backup disk.
I created user in the new installed pg-8.4 and, after starting pg, I see
user$ psql mydb
does work, mut no data are found:

Target width for "wrapped" format is 100.
Output format is wrapped.
No relations found.
                              List of databases
   Name    |  Owner   | Encoding | Collation | Ctype |   Access privileges   
 mydb      | ennio    | LATIN1   | en_US     | en_US | 
 postgres  | postgres | LATIN1   | en_US     | en_US | 
 template0 | postgres | LATIN1   | en_US     | en_US | =c/postgres
                                                     : postgres=CTc/postgres
 template1 | postgres | LATIN1   | en_US     | en_US | =c/postgres
                                                     : postgres=CTc/postgres
(4 rows)

In an old post by Tom Lane, one of the PG's authors, I read that copying
old files in the same dir should work. So I'm wondering why it didn't
work in my case.

To this respect I fear this could be related to the situation I had
before re-installation.
AAMOF I tried to install pg-9.1 (from wheezy) but pg_upgradecluster
didn't work,(*) so I came back to previous 8.4 version (but probably I
didn't downgrade postgresql-client as well.
As I continued to use mydb without noticing any irregularity I was not
aware of any problem until, trying to downgrade to squeeze all wheezy
packages, I removed postgresql-client 9.1 sure that it was useless as I
was running pg-8.4 :-(.
After a rboot I realized the mess caused by that removal and so decided
to re-install squeeze from scratch....

May be I should install postgres-client/wheezy to see if Tom Lane's
suggestion works?

Do you have any similar experience?

Thanks for your attention.
Regards, Ennio

This is the impasse emerging from that upgrading:

postgresql/wheezy uptodate 9.1+134wheezy1
postgresql-client/wheezy uptodate 9.1+134wheezy1
postgresql-client-common/wheezy uptodate 134wheezy1
Linux deby.ei.hnet 2.6.32-trunk-686 #1 SMP Sun Jan 10 06:32:16 UTC 2010
i686 GNU/Linux]

I upgraded pg_8.4 to 9.1 and run pg_upgradecluster but if I try to
connect with:
$ psql mydb -p 5433  (to make sure everything is ok) I get:

psql: FATAL:  role "ennio" does not exist

su - postgres &&  createuser ennio, warns:

createuser: creation of new role failed: ERROR:  role "ennio" already exists

Trying to ocnnect as postgres (psql -p 5433) I get the warning in

[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.   \\?//
 Fa' qualche cosa di cui non sei capace!"  (diceva Henry Miller) ]    (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that.              )=(
 Do something you aren't good at!" (as Henry Miller used to say) ]

Reply to: