Re: Read db files directly

Thanks so much Andy!

"attach" is a better word.
I was asking how to make PostgreSQL "connect"
to my old folder (fs dir, file system directory).
Or something that would read a folder directly.

psql -V
psql (PostgreSQL) 7.2.2

Yeah, I tried something like what you descibed
before I asked the list for help.
Create my testdb, find its fs dir name.
Then symlink that to the old fs dir in question.
that did not help.

also tried in my testdb, created a table t1.
Find out its file name,
then "cat" the contents of one of the old files
over the new file.
This time "select * from t1" returns an error.
I suspect that is because the columns of t1
is different from the columns of the old
table. But of course I do not know the columns
of the old table.

So my question is still,
is there something that can read
such fs dir directly, without needing info
from any other table such as pg_database,
pg_class. All you get is the fs dir.
at least to give me the list of tables,
and better yet give me the columns of tables,
and best of all to give me the rows in text.
If I were to write such a program,
where roughly in the PostgreSQL source code
would I want to start looking?


--- Andy Shellam <andy@andycc.net> wrote:

> I know exactly what you're trying to do - it's a
> similar thing to
> "attaching database files" in MS SQL 2000.  You've
> got a set of data
> files for a single database from a previous PGSQL
> install and want to
> get another PGSQL to see it and use it as a normal
> DB so you can extract
> the data, right?
>
> I'm guessing PGSQL needs an entry in pg_database
> with an oid value of
> what your data directory you want to "attach" is
> called.  This is
> particularly risky, and obviously this shouldn't be
> done on a production
> or mission-critical database cluster.  (I don't even
> know if it'd work,
> but it's how I'd see it based on my MS SQL 2000
> knowledge.)
>
> I've just tried it, and it's difficult if not
> impossible to insert a new
> record into pg_database.  Another thing I'd thought
> of was to create
> your own new database, note it's OID ("SELECT * FROM
> pg_database WHERE
> datname = 'new_db_name';"), then remove it's
> corresponding data
> directory and instead symlink it to your
> /seagate/1061329089 directory,
> keeping the symlink name as the oid from the above
> query (i.e.
> /var/lib/pgsql/data/base/<new DB OID>  -->
> /seagate/1061329089.)
>
> Like I say this is risky, and these are just a
> couple of ideas I've
> thought of.  (Note these are right for an 8.1
> server, don't know what
> version you're running so they may or may not work.)
>
> I've just tested my above "create new DB and symlink
> it" method by doing
> this:
>
> 1. Create new DB called "test_db"
> 2. Get it's OID using SELECT oid FROM pg_database
> WHERE datname =
> 'test_db'; = 65960
> 3. Remove the <pg_dataroot>/base/65960 directory
> 4. Find the OID of an existing database using SELECT
> oid FROM
> pg_database WHERE datname = 'another_db'; = 16384
> 5. In <pg_dataroot>/base, symlink 65960 to 16384
> 6. In psql, switch to "test_db"  - "\c test_db"
> 7. Run a SELECT query on a table that exists in
> another_db - SELECT *
> FROM public.alias;
> 8. Switch back to another_db - "\c test_db"
> 9. Run the same SELECT query in 7 - SELECT * FROM
> public.alias;
> 10. Check the data bought back is the same - YEP
>
> Obviously worthy of note is that the server versions
> of the one you're
> working on, and the one you're data directory is
> from should be the same
> (this is given in PG_VERSION in your databae
> directory.)
>
> Hope this works for you!
>
> Andy.