Thread: pg-8.4: is it possible to recover not dumped data?

pg-8.4: is it possible to recover not dumped data?

From
Ennio-Sr
Date:
[Using pg-8.4 under Gnu/Linux-Squeeze]

Hi all!
I have stupidly deleted postgresql-common, postgresql-client and
postgresql-client-common (they where shown as version 9.1 while I was
running pg 8-4 driving me to this error).
After realizing (on reboot) I could not connect to my db I installed the
8.4 version of the 3 deleted pckgs and, after su-ing to postgres,
re-created the 'user'.

Now I can see the data directory (/var/lib/postgresql/8.4/main) is still
populated (I presume with all my data) but I am unable to access them.
Although I have a copy on my laptop (lacking only minor updatings) I
would like to know if there is any possibility to recover my data on PC.

if yes, would you please suggest a solution or some chapter to read?

Thanks for your attention. Regards,
     Ennio

--
[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) ]


Re: pg-8.4: is it possible to recover not dumped data?

From
Jeff Davis
Date:
On Thu, 2012-11-08 at 17:26 +0100, Ennio-Sr wrote:
> [Using pg-8.4 under Gnu/Linux-Squeeze]
>
> Hi all!
> I have stupidly deleted postgresql-common, postgresql-client and
> postgresql-client-common (they where shown as version 9.1 while I was
> running pg 8-4 driving me to this error).
> After realizing (on reboot) I could not connect to my db I installed the
> 8.4 version of the 3 deleted pckgs and, after su-ing to postgres,
> re-created the 'user'.
>
> Now I can see the data directory (/var/lib/postgresql/8.4/main) is still
> populated (I presume with all my data) but I am unable to access them.
> Although I have a copy on my laptop (lacking only minor updatings) I
> would like to know if there is any possibility to recover my data on PC.
>
> if yes, would you please suggest a solution or some chapter to read?

What error message are you getting? Are you able to start the server?

What is in /var/lib/postgresql/8.4/main/PG_VERSION? Do you still have
that version of postgres installed?

Who is the owner of /var/lib/postgresql/8.4/main and its contents? Does
that unix user still exist?

Regards,
    Jeff Davis



Re: pg-8.4: is it possible to recover not dumped data?

From
Ennio-Sr
Date:
* Jeff Davis <pgsql@j-davis.com> [081112, 17:59]:
> On Thu, 2012-11-08 at 17:26 +0100, Ennio-Sr wrote:
> > [Using pg-8.4 under Gnu/Linux-Squeeze]
> >
> > Hi all!
> > I have stupidly deleted postgresql-common, postgresql-client and
> > postgresql-client-common (they where shown as version 9.1 while I was
> > running pg 8-4 driving me to this error).
> > After realizing (on reboot) I could not connect to my db I installed the
> > 8.4 version of the 3 deleted pckgs and, after su-ing to postgres,
> > re-created the 'user'.
> >
> > Now I can see the data directory (/var/lib/postgresql/8.4/main) is still
> > populated (I presume with all my data) but I am unable to access them.
> > Although I have a copy on my laptop (lacking only minor updatings) I
> > would like to know if there is any possibility to recover my data on PC.
> >
> > if yes, would you please suggest a solution or some chapter to read?
>
> What error message are you getting? Are you able to start the server?
>
Thank you, Jeff, for your answer.
Here follow the info required:

Yes, the server is started and running:

root-# ps | grep postgres
postgres  4424  0.0  0.3  47908  1584 ?        S    06:46   0:01 /usr/lib/postgresql/8.4/bin/postgres -D
/var/lib/postgresql/8.4/main-c config_file=/etc/postgresql/8.4/main/postgresql.conf -c
external_pid_file=/var/run/postgresql/8.4-main.pid
postgres  4426  0.0  0.2  48020  1060 ?        Ss   06:46   0:03  \_ postgres: writer process

      
postgres  4427  0.0  0.1  47908   520 ?        Ss   06:46   0:02  \_ postgres: wal writer process

      
postgres  4428  0.0  0.1  48044   716 ?        Ss   06:46   0:00  \_ postgres: autovacuum launcher process

      
postgres  4429  0.0  0.0  16220   484 ?        Ss   06:46   0:00  \_ postgres: stats collector process

      
root     31781  0.0  0.1   3988   760 tty1     S+   16:22   0:00      \_ grep postgres

As I told in previous message, I re-created user 'ennio' from
(superuser) 'postgres' and gave the command:
postgres-# createdb -O ennio mydb

Then, I logged in as ennio:

ennio-$ psql mydb
Target width for "wrapped" format is 100.
Output format is wrapped.
psql (8.4.13)
Type "help" for help.

mydb=> \d
No relations found.

mydb=> \l
                              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)



> What is in /var/lib/postgresql/8.4/main/PG_VERSION? Do you still have
> that version of postgres installed?
>
The result is '8.4'

> Who is the owner of /var/lib/postgresql/8.4/main and its contents? Does
> that unix user still exist?
>

The owner is postgres.postgres with 'drwx' permissions for subdirs and
'rw' for files.

Regards,
    Ennio
--
[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) ]