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
that:
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
subject.
--
[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: