Thread: Very stupid backup I made

First little background:
    I had created database of my owm (great success for me), was
able to use with OpenOffice thrue ODBC and spen couple of weeks to
populate that database. ANd everithing was going fine!
    At some point I decidet to reoragnize my hard disk (wanted to  prove
myself how good am I with linux). Of course I did not like to lose my data
that I have typeing in for weeks. So I made a backup (everybody are saying
hoe important backup is). But how did I do this!!!
    To make a backup I decided to simply copy /var/lib/pgsql to safe
place.
    After reinstlling linux (same distribution as was before), copying
my 'backup' back to the place and restarting server for my big surrprice I
was not able to connect to my database.
    Originally I had two databases created:
tester     - this is also my loginname (as ordinally user)
soff       - second database I made by 'createdb soff' as user tester

    Now when I use command 'psql' I get connect to 'tester' datase.
'\l' command list theese databasess:
tester=# \l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
 tester    | tester   | SQL_ASCII
(3 rows)

Note that there is no 'soff' database. Command '\dt' say: 'No relations
foud'. At this point I issued command '\c soff' even if that is not listed
above as existing. And DO get connect to 'soff' database.

tester=# \c soff
You are now connected to database soff.
soff=# \dt
No relations found.
soff=#

But all tables are gone.
After little peeking I found that file data/base/16593/16685 contain data
in soff database that I need.

[root@alfa pgsql]# grep -rl nekret .
./data/base/16593/16685
./data/pg_xlog/0000000000000000

Simillary other file contains data from table in 'tester' databse:
[root@alfa pgsql]# grep -lr Fedel .
./data/base/16599/16606
./data/pg_xlog/0000000000000000

In those directories are also many other files (some of them were probabbly
created when I was useing pgaccess).
    Now I am wondering if there is some chance to recover data from
'soff' database which are in ./data/base/16593/16685 file.
    And after You stop laughing (not that I did not deserved it :) maybe
You can tell me where can I find some reading about backup!
Thanks
Juraj

On 20 Mar 2003 at 7:18, Juraj Fedel wrote:
>     And after You stop laughing (not that I did not deserved it :) maybe
> You can tell me where can I find some reading about backup!

man pg_dump

Bye
 Shridhar

--
Be warned that typing

    Bkillall

    Iname

    P may not have the desiredeffect on non-Linux systems, especially when done by
a privileged user.(From the killall manual page)

On Thu, Mar 20, 2003 at 11:59:38AM +0530, Shridhar Daithankar wrote:
> man pg_dump
>
> Bye
>  Shridhar

    I will remmember this. For shure I will. If I was not so compulsive
maybe I could save myself lot of trouble with little reading before doiiing
something stupid.
Thanks a lot. This will help in the future.

Juraj

On Thu, 2003-03-20 at 23:48, Juraj Fedel wrote:
> On Thu, Mar 20, 2003 at 11:59:38AM +0530, Shridhar Daithankar wrote:
> > man pg_dump
> >
> > Bye
> >  Shridhar
>
>     I will remmember this. For shure I will. If I was not so compulsive
> maybe I could save myself lot of trouble with little reading before doiiing
> something stupid.
> Thanks a lot. This will help in the future.

As an item of curiosity - is there a way to resurrect the database from
a backup of the files? I understand that if the backup was taken while
the db was running it would be in an inconsistent state, but surely if
the whole thing was there postgres would be able to roll back the
transactions?

Why can't postgres be restored from file backups? Is there a hack to do
it?

James.