pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andy Shellam <andy@andycc.net>
To: Mingzuo Shen <mzshen@yahoo.com>
To: pgsql-admin@postgresql.org
Subject: Re: Read db files directly
Date: Sat, 29 Jul 2006 11:28:47 +0100
Message-ID: <44CB385F.5070600@andycc.net> (raw)
In-Reply-To: <20060729095536.85729.qmail@web60414.mail.yahoo.com>
References: <20060729095536.85729.qmail@web60414.mail.yahoo.com>

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.

Mingzuo Shen wrote:
> Sorry I did not make it clear.
>
> I still do not see the db, tables, etc.
>
> vacuumdb, pg_dumpdball, psql, etc
> that requires a normal PostgreSQL do not
> see the db.
> Tried them all, many times.
>
> --- Jeff Frost <jeff@frostconsultingllc.com> wrote:
>
>   
>> So, is the working postmaster running in
>> /var/lib/pgsql/data?
>>
>> Have you taken Tom's advice and run 'VACUUM
>> pg_database' ?  Or even just 
>> 'vacuumdb -av' ?
>>
>> On Fri, 28 Jul 2006, Mingzuo Shen wrote:
>>
>>     
>>> Thanks Jeff!
>>>
>>> No, cannot do that.
>>> Because the folder has only the "oid" files.
>>> Don't know that to call them.
>>> All file names are numbers.
>>> Except the three following:
>>> pg_internal.init
>>> pgsql_tmp (empty folder)
>>> PG_VERSION
>>>
>>> /seagate400/1061329089 is the actual location
>>> of those 50 GB worth of files.
>>> I have a symlink like so:
>>> /var/lib/pgsql/data/base/1061329089
>>> ->
>>> /seagate400/1061329089
>>> Restarted many times.
>>>       
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
> !DSPAM:14,44cb30f5143291875220417!
>
>
>   

-- 
Andy Shellam <mailto:andy.shellam@mailnetwork.co.uk>,
the Mail Network <http://www.mailnetwork.co.uk/;

NetServe Support - we don't go the extra mile; we go the whole distance!

p: (+44) 0 845 838 0879 / +44 0 7818 000834
w: www.mailnetwork.co.uk <http://www.mailnetwork.co.uk/;
e: support@mailnetwork.co.uk <mailto:support@mailnetwork.co.uk>






view thread (15+ messages)  latest in thread

Message-ID: <44CB385F.5070600@andycc.net>
Permalink:  ../44CB385F.5070600@andycc.net/
Also on:    postgresql.org/message-id/44CB385F.5070600@andycc.net

 · 

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: andy@andycc.net, mzshen@yahoo.com
  Subject: Re: Read db files directly
  In-Reply-To: <44CB385F.5070600@andycc.net>
  References: <20060729095536.85729.qmail@web60414.mail.yahoo.com> <44CB385F.5070600@andycc.net>

* 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