Restoring a database from original files (Was: Need help

On Mon, May 18, 2009 at 11:33:03PM +0430,
Zico <mailzico(at)gmail(dot)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()