pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mingzuo Shen <mzshen@yahoo.com>
To: pgsql-admin@postgresql.org
Subject: Re: Read db files directly
Date: Sun, 30 Jul 2006 14:10:35 -0700 (PDT)
Message-ID: <20060730211035.30619.qmail@web60413.mail.yahoo.com> (raw)
In-Reply-To: <44CB385F.5070600@andycc.net>

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.




view thread (15+ messages)

Message-ID: <20060730211035.30619.qmail@web60413.mail.yahoo.com>
Permalink:  ../20060730211035.30619.qmail@web60413.mail.yahoo.com/
Also on:    postgresql.org/message-id/20060730211035.30619.qmail@web60413.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mzshen@yahoo.com
  Subject: Re: Read db files directly
  In-Reply-To: <20060730211035.30619.qmail@web60413.mail.yahoo.com>
  References: <44CB385F.5070600@andycc.net> <20060730211035.30619.qmail@web60413.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox