Thread: Need help

Hi, I am running Dspace 1.5.1, which is an institutional repository software. I am using postgresql-8.3 there. Here, i am facing the problem is:

our server suddenly went down and after searching, i found out that, the OS crashed. So, i had to re-install my OS again. But, the problem is, I couldnot take the dump file for my postgresql database. Now, i have the raw data, that means, my datas are in a disk. Is it possible to restore those data here in new server?

--
Best,
Zico

Zico wrote:
> our server suddenly went down and after searching, i found
> out that, the OS crashed. So, i had to re-install my OS
> again. But, the problem is, I couldnot take the dump file for
> my postgresql database. Now, i have the raw data, that means,
> my datas are in a disk. Is it possible to restore those data
> here in new server?

Copy all the files that belong to the PostgreSQL server (everything
under the data directory) to a working machine with PostgreSQL
installed, and you should be able to start the server there.

And remember to keep backups from now on.

Yours,
Laurenz Albe

--
Best,
Zico

Zico wrote:
[wants to restore a database without a backup]
>> Copy all the files that belong to the PostgreSQL server (everything
>> under the data directory) to a working machine with PostgreSQL
>> installed, and you should be able to start the server there.
> 
> the problem is, i don`t have files of previous PostgreSQL!!!
> I have only soft copies of my datas like pds, docs and
> others. Now what?

Could you explain that in more detail?
What is a pds or docs?

Yours,
Laurenz Albe

--
Best,
Zico

Zico wrote:
[wants to restore a database without a backup]
>> Could you explain that in more detail?
>> What is a pds or docs?
>
> PDF ==> Portable Document Format files
> Docs ==> Documents
> 
> Means, i have the backup of my files only which was in that database. 

I'm still not sure what that means, but if you have the contents of
the database, but the database itself is gone without a trace,
then what else can you do but create a new database and fill it anew?

Yours,
Laurenz Albe

--
Best,
Zico

On Mon, May 18, 2009 at 09:21:41PM +0430, Zico wrote:
> On Mon, May 18, 2009 at 6:11 PM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:
> > then what else can you do but create a new database and fill it anew?
> >
>
> That`s what i am asking about. :)  Fill it anew? That means, i have to
> insert all 2000 data again?? Is there any other option to "restore" all data
> there?

You need to get the contents of the database from somewhere; the obvious
choices seem to be an existing copy of the PG database directory, a
backup of the database, or the original data.  Your previous messages
seem to indicate that you do not have either of the first to items and
so the only choice remaining would be to use the original data.

--
  Sam  http://samason.me.uk/

Zico wrote:
> On Mon, May 18, 2009 at 6:11 PM, Albe Laurenz <laurenz.albe@wien.gv.at
> <mailto:laurenz.albe@wien.gv.at>> wrote:
>
>     I'm still not sure what that means, but if you have the contents of
>     the database, but the database itself is gone without a trace,
>
>
>
>
>     then what else can you do but create a new database and fill it anew?
>
>
> That`s what i am asking about. :)  Fill it anew? That means, i have to
> insert all 2000 data again?? Is there any other option to "restore"
> all data there?

we still don't understand exactly what you have to work from....

Do you have a backup of the Postgres "data" directory, which typically
is something like "C:\Program Files\PostgreSQL\8.2\data" on a Windows
system?   If so, you can install the same version of postgres, then
"STOP" it, copy the 'data' directory into the proper place, and restart
the server, and it should see your databases all intact.  You might have
to mess around a bit with file permissions, windows makes that kind of
sketchy.

if you don't have a backup of this data directory, and you've said you
don't have any pg_dumpall type backups made prior to the system crash,
then, what exactly do you expect to be able to restore your data from?

On Mon, May 18, 2009 at 06:15:28PM +0100, Sam Mason wrote:
> You need to get the contents of the database from somewhere; the obvious
> choices seem to be an existing copy of the PG database directory, a
> backup of the database, or the original data.  Your previous messages
> seem to indicate that you do not have either of the first to items and

bah, what's happening to my spelling!                 this ^^^^ should
obviously be "two".

> so the only choice remaining would be to use the original data.

--
  Sam  http://samason.me.uk/

--
Best,
Zico

On Mon, May 18, 2009 at 11:33:03PM +0430, Zico wrote:

> > we still don't understand exactly what you have to work from....
> > Do you have a backup of the Postgres "data" directory,
>
> No, I don`t have any data of Postgres "data" directory.

Well, in that case I would suggest to IMMEDIATELY STOP
WRITING anything to the hard drive the data directory used
to be on (in other words, unmount it).

Then, mount it read-only and use low level tools to try and
recover the data.

If you are EXTREMELY lucky you'll find something.

> I don`t know, what should i do!

First of all you need to provide detailed answers to what
people ask you on this list. I know there are extremely
knowledgeable people around here but they can only help if
you provide ample and precise details.

> Because, i have only the softcopy of my data

What is a "softcopy" ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Mon, May 18, 2009 at 11:33:03PM +0430,
 Zico <mailzico@gmail.com> wrote
 a message of 74 lines which said:

> No, I don`t have any data of Postgres "data" directory.

Next time, do not forget backups...

> As far as i can remember, my postgre files were in /usr/share/postgresql/8.3
>
> as i am using the Debian distro.

In that case, they were (that's the default location) in
/var/lib/postgresql/$VERSION/$CLUSTERNAME

Many people probably assumed you use MS-Windows because of your
difficulties to provide hard information, or to set a proper subject
(I fixed that).

> I don`t know, what should i do! Because, i have only the softcopy of
> my data, nothing else. No, postgresql directory, no dumped sql
> file!! :(

OK, if I read correctly the whole thread, you have binary documents
(in formats like PDF or MS-Word) and they were in the past inserted
into the database. Now, the database is gone and you want to insert
them again? Correct?

If so, first a question, how were these documents inserted? By a
program? If so, you simply have to run this program again.

Did you put the whole file in a PostgreSQL field of type "bytea" or
was there some process to extract from the files the relevant info? In
the last case, was the process manual or by a program?

I suspect that it is complicated and that these files were inserted by
hand, may be after manual extraction of the fields? Correct? If so, by
direct SQL INSERT statements or through some interface?

If the files were inserted by hand, and you don't want to do it again
for the 2000 documents, the only solution is to write a program that
will perform the insertion again.

It can be as simple as the following Python script which inserts into
the database all the files mentioned on its command line:

#!/usr/bin/python

#CREATE TABLE PDF_files (id SERIAL UNIQUE NOT NULL,
#                        added TIMESTAMP NOT NULL DEFAULT now(),
#                        name TEXT UNIQUE NOT NULL,
#                        value BYTEA NOT NULL);

import psycopg
import sys

connection = psycopg.connect("dbname=essais")
cursor = connection.cursor()
for filename in sys.argv[1:]:
    cursor.execute("INSERT INTO PDF_files (name, value) VALUES (%s, %s);",
                   (filename, psycopg.Binary(open(filename).read())))
cursor.execute("COMMIT;")
connection.close()