Thread: problem after upgrade db missing

problem after upgrade db missing

From
Aleksander Rozman
Date:
Hi !

I am long time user of postgresql (on several platforms), and last month
I came across weird problem...

I started upgrade of Ubuntu and halfway through, upgrade failed... I was
forced to do clean install. After install all my databases were missing
(I also had to reinstall postgres, so I installed same version of pgsql
I had before - 8.4).

Like I said before all databases were missing... One of thoose database
was very important, but since I didn't have time I didn't pursue it
further. Now that I can breathe again, I am here again... I looked
through data directory and I found in /var/lib/postgresql/8.4/main/base
I found 3 directories which leads me to beleive, that old databases were
not in fact deleted, they are just not "registered". Two of this
directories have old date (like date when my computer run on previous
version of OS) and one has new date.

Now I am not sure two of this directories are old databases, but I think
they are... Is there a way to register one of this databases into new
installation (I am sure that directory "1" is old postgres database, and
"11563" is my database I want to save, and "11564" is new postgresql
database (this is only directory with new date).

Any help is appreciated? I would really need data from that database...

Thanks for all help in advance.
Andy

Re: problem after upgrade db missing

From
Adrian Klaver
Date:
On 06/05/2012 02:51 PM, Aleksander Rozman wrote:
> Hi !
>
> I am long time user of postgresql (on several platforms), and last month
> I came across weird problem...
>
> I started upgrade of Ubuntu and halfway through, upgrade failed... I was
> forced to do clean install. After install all my databases were missing
> (I also had to reinstall postgres, so I installed same version of pgsql
> I had before - 8.4).
>
> Like I said before all databases were missing... One of thoose database
> was very important, but since I didn't have time I didn't pursue it
> further. Now that I can breathe again, I am here again... I looked
> through data directory and I found in /var/lib/postgresql/8.4/main/base
> I found 3 directories which leads me to beleive, that old databases were
> not in fact deleted, they are just not "registered". Two of this
> directories have old date (like date when my computer run on previous
> version of OS) and one has new date.
>
> Now I am not sure two of this directories are old databases, but I think
> they are... Is there a way to register one of this databases into new
> installation (I am sure that directory "1" is old postgres database, and
> "11563" is my database I want to save, and "11564" is new postgresql
> database (this is only directory with new date).
>
> Any help is appreciated? I would really need data from that database...

Can you start the 8.4 instance you installed?
If so, what do you see when you do \l in psql?

>
> Thanks for all help in advance.
> Andy
>


--
Adrian Klaver
adrian.klaver@gmail.com

Re: problem after upgrade db missing

From
"Daniel Verite"
Date:
    Aleksander Rozman wrote:

> Now I am not sure two of this directories are old databases, but I think
> they are... Is there a way to register one of this databases into new
> installation (I am sure that directory "1" is old postgres database, and
> "11563" is my database I want to save, and "11564" is new postgresql
> database (this is only directory with new date).

Actually 1, 11563 and 11564 are what you get with a fresh new installation.
On a 8.4 install on Ubuntu, the corresponding databases are:

SELECT oid, datname from pg_database where oid in (1,11563,11564);

  oid  |  datname
-------+-----------
     1 | template1
 11563 | template0
 11564 | postgres

Also normally initdb wouldn't work on a non-empty data directory, anyway.
I'd say that either the old data directory has been moved aside at some point
of the upgrade procedure, or it has been wiped out :(

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

Re: problem after upgrade db missing

From
Alban Hertroys
Date:
On 5 June 2012 23:51, Aleksander Rozman <andy.rozman@gmail.com> wrote:
> Like I said before all databases were missing... One of thoose database was
> very important, but since I didn't have time I didn't pursue it further.

If it was very important, that means that you have backups, right?
From the other replies it seems that you really did lose the
database(s), in which case your backups are pretty much your only
option for recovery...

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: problem after upgrade db missing

From
Jasen Betts
Date:
On 2012-06-05, Aleksander Rozman <andy.rozman@gmail.com> wrote:

> Now I am not sure two of this directories are old databases, but I think
> they are... Is there a way to register one of this databases into new
> installation (I am sure that directory "1" is old postgres database, and
> "11563" is my database I want to save, and "11564" is new postgresql
> database (this is only directory with new date).

bad news:

template0
template1
postgres



>
> Any help is appreciated? I would really need data from that database...
>
> Thanks for all help in advance.
> Andy
>


--
⚂⚃ 100% natural