Re: Read db files directly

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(at)frostconsultingllc(dot)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(dot)shellam(at)mailnetwork(dot)co(dot)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(at)mailnetwork(dot)co(dot)uk <mailto:support(at)mailnetwork(dot)co(dot)uk>