Thread: Recovering databases

Recovering databases

From
Peter Falk
Date:
Hi,
I did some updating on my Linux server and was a bit too efficient.
Databases on the server prior to the update is no longer reachable from
pgsql all though still present on the server. Of course I didn't do a
pg_dump before the update!!!

I've tried using initdb --pgbase  but without luck. Does anyone knows
how to get acces to the databases?

Regards
Peter Falk

--
Med venlig hilsen
Peter Falk
Nexus Kommunikation
Østre Messegade 2
2650 Hvidovre
36 77 00 46
www.nexus.dk


Re: Recovering databases

From
Oliver Elphick
Date:
On Mon, 2007-08-20 at 10:35 +0200, Peter Falk wrote:
> Hi,
> I did some updating on my Linux server and was a bit too efficient.
> Databases on the server prior to the update is no longer reachable from
> pgsql all though still present on the server. Of course I didn't do a
> pg_dump before the update!!!

What precisely did you update?  What are the versions involved?  Is this
a package installation (Red Hat/Debian/etc)? or did you build from
source?

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


Re: Recovering databases

From
Peter Falk
Date:
The installation is Debian Sarge (3.1). I did a complete update, but
during this I got the information, that Postgresql has to be upgraded
separately - can't remember the exact words.

I did this update and to my suprise it installed a version 7.4. Then i
removed this installation and installed a version 8.1. Afterwards I
cheched to see the packages installed and recognized, that some
7.4-packages still resided om the server.

I there upon (and this is when the big mistake occured) decided to
remove all PG-packages and do a clean install.
The misson succeded but the patient died!

At this moment I got a runing PG but without connecion to the old
databases. The database-files still resides on the server, but I can't
get PG to recognize them.

I think that the PG-installation prior to the mistake was v. 8.0 - I
know for sure that it was newer than 7.x

Regards
Peter


Oliver Elphick skrev:
> On Mon, 2007-08-20 at 10:35 +0200, Peter Falk wrote:
>
>> Hi,
>> I did some updating on my Linux server and was a bit too efficient.
>> Databases on the server prior to the update is no longer reachable from
>> pgsql all though still present on the server. Of course I didn't do a
>> pg_dump before the update!!!
>>
>
> What precisely did you update?  What are the versions involved?  Is this
> a package installation (Red Hat/Debian/etc)? or did you build from
> source?
>
>


--
Med venlig hilsen
Peter Falk
Nexus Kommunikation
Østre Messegade 2
2650 Hvidovre
36 77 00 46
www.nexus.dk


Re: Recovering databases

From
Shane Ambler
Date:
Peter Falk wrote:
> The installation is Debian Sarge (3.1). I did a complete update, but
> during this I got the information, that Postgresql has to be upgraded
> separately - can't remember the exact words.
>
> I did this update and to my suprise it installed a version 7.4. Then i
> removed this installation and installed a version 8.1. Afterwards I
> cheched to see the packages installed and recognized, that some
> 7.4-packages still resided om the server.
>
> I there upon (and this is when the big mistake occured) decided to
> remove all PG-packages and do a clean install.
> The misson succeded but the patient died!
>
> At this moment I got a runing PG but without connecion to the old
> databases. The database-files still resides on the server, but I can't
> get PG to recognize them.
>
> I think that the PG-installation prior to the mistake was v. 8.0 - I
> know for sure that it was newer than 7.x

Locate the main version number that was running with the data. If you
where running 8.0 then 8.1 won't read the data files. A dump and restore
is required to transfer data between releases (but not minor updates).

In the data dir you will find a file named PG_VERSION that contains the
version that created the data files.
The data dir will be the one with base and global dir's and have files
such as pg_hba.conf and postgresql.conf
You can also find the same file inside the base dir folders.

Install the newest update to the version found (if PG_VERSION shows 8.0
then install 8.0.13) and then consider whether you want to dump and
restore to a newer version or to stay with that version.


> Regards
> Peter
>
>
> Oliver Elphick skrev:
>> On Mon, 2007-08-20 at 10:35 +0200, Peter Falk wrote:
>>
>>> Hi,
>>> I did some updating on my Linux server and was a bit too efficient.
>>> Databases on the server prior to the update is no longer reachable
>>> from pgsql all though still present on the server. Of course I didn't
>>> do a pg_dump before the update!!!
>>>
>>
>> What precisely did you update?  What are the versions involved?  Is this
>> a package installation (Red Hat/Debian/etc)? or did you build from
>> source?
>>
>>
>
>


--

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz

Re: Recovering databases

From
Oliver Elphick
Date:
On Mon, 2007-08-20 at 14:04 +0200, Peter Falk wrote:
> The installation is Debian Sarge (3.1). I did a complete update, but
> during this I got the information, that Postgresql has to be upgraded
> separately - can't remember the exact words.
>
> I did this update and to my suprise it installed a version 7.4. Then i
> removed this installation and installed a version 8.1. Afterwards I
> cheched to see the packages installed and recognized, that some
> 7.4-packages still resided om the server.
>
> I there upon (and this is when the big mistake occured) decided to
> remove all PG-packages and do a clean install.
> The misson succeded but the patient died!
>
> At this moment I got a runing PG but without connecion to the old
> databases. The database-files still resides on the server, but I can't
> get PG to recognize them.
>
> I think that the PG-installation prior to the mistake was v. 8.0 - I
> know for sure that it was newer than 7.x

In that case you should install postgresql-8.0 and
postgresql-client-8.0.  That should enable you  to get access to your
8.0 database.  Unfortunately, they aren't available from the Debian
archive, although they were available in testing for a while before 8.1
came out.  They should still exist somewhere on the web.

What puzzles me though is that you are/were running Sarge.  The latest
version of postgresql in Sarge is 7.4 (and the current stable release of
Debian is Etch, which has PostgreSQL 8.1).  "Doing a complete update"
will presumably have upgraded you to Etch.  In Sarge, the postgresql
packages were of version 7.4 but 7.4 did not appear in their names; the
fact that the upgrade to Etch installed *-7.4 packages suggests to me
that you were in fact running 7.4 in Sarge, because the *-7.4 packages
would have been needed to achieve a smooth upgrade.  (The postgresql
package in Etch is a transitional package that depends on
postgresql-7.4.)

To check on the version you want, check the contents of the file
PG_VERSION in the database directory.  If you are right, it should say
8.0

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.