X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 75B0D9FB310 for ; Sun, 30 Jul 2006 18:10:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 63513-02 for ; Sun, 30 Jul 2006 18:10:37 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web60413.mail.yahoo.com (web60413.mail.yahoo.com [209.73.178.156]) by postgresql.org (Postfix) with SMTP id 215269FA65C for ; Sun, 30 Jul 2006 18:10:36 -0300 (ADT) Received: (qmail 30621 invoked by uid 60001); 30 Jul 2006 21:10:35 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=WWpPR7ehqHWKGiQ54s70VICoTut+v8cww26GCJTY34xAPucOYxOJ4w2o7Tcx5Rse+Lc/w/A1Rowl68/afc+Pg0aXc2cly/c1oxpIseLJ8c2TwGWnqgDETG6DAy2FQ2Vj8cWHXOB6DNsVFi9POTiEX3o9CWrb7GjXhOFTVD/8Riw= ; Message-ID: <20060730211035.30619.qmail@web60413.mail.yahoo.com> Received: from [128.249.216.224] by web60413.mail.yahoo.com via HTTP; Sun, 30 Jul 2006 14:10:35 PDT Date: Sun, 30 Jul 2006 14:10:35 -0700 (PDT) From: Mingzuo Shen Subject: Re: Read db files directly To: pgsql-admin@postgresql.org In-Reply-To: <44CB385F.5070600@andycc.net> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.89 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: ** X-Archive-Number: 200607/281 X-Sequence-Number: 22527 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 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/ --> > /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 /base/65960 directory > 4. Find the OID of an existing database using SELECT > oid FROM > pg_database WHERE datname = 'another_db'; = 16384 > 5. In /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.