How are postgreSQL database files structured?
Hugh Esco
I am mounting the learning curve from MySQL to postgreSQL. It seemed so
straight forward before. On my Win98 box, everything was clearly named as
I had designated in the c:/mysql/data directory. On the Debian server, the
/var/lib/mysql directory is similarly and intuitively laid out in an
understandable way.
But when I take a look at /usr/local/pgsql/data/base, I am baffled by its
contents. I hear that the directory at: /usr/local/pgsql/data/base/1 is
the template1 database. But I do not know where the template0 database
is. And I certainly do not understand how testtest gets named 16555, much
less what the numbered directories and files subordinate to that directory
have to do with anything real in the world.
Can someone please refer me to a document (preferably off the net, as my
book buying budget is non-existent, these days) which will explain it all
for me? I would certainly appreciate that. Thanks.
-- Hugh Esco
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)
Oliver Elphick
> But when I take a look at /usr/local/pgsql/data/base, I am baffled by its
> contents. I hear that the directory at: /usr/local/pgsql/data/base/1 is
> the template1 database. But I do not know where the template0 database
> is. And I certainly do not understand how testtest gets named 16555, much
> less what the numbered directories and files subordinate to that directory
> have to do with anything real in the world.
The files in $PGDATA/base are named by the oid of the database record in
pg_database, like this:
postgres@linda:~$ cd $PGDATA
postgres@linda:/usr1/postgres/data$ ls -l base
total 33
drwx------ 2 postgres postgres 2048 Nov 23 04:01 1
drwx------ 2 postgres postgres 2048 Nov 23 04:00 1063179
drwx------ 2 postgres postgres 2048 Nov 23 04:00 1063190
drwx------ 2 postgres postgres 3072 Nov 23 04:00 1063237
drwx------ 2 postgres postgres 3072 Nov 23 04:00 1063515
drwx------ 2 postgres postgres 3072 Nov 23 04:01 1064226
drwx------ 2 postgres postgres 3072 Nov 23 04:01 1064465
drwx------ 2 postgres postgres 2048 Nov 18 22:39 16975
drwx------ 2 postgres postgres 2048 Nov 23 04:00 16976
drwx------ 2 postgres postgres 3072 Nov 23 04:00 16983
drwx------ 3 postgres postgres 6144 Nov 23 04:00 3884888
drwx------ 2 postgres postgres 2048 Nov 23 04:01 4989386
postgres@linda:/usr1/postgres/data$ psql template1
Welcome to psql 7.3rc1, the PostgreSQL interactive terminal.
...
template1=# select oid,datname from pg_database order by oid;
oid | datname
---------+----------------
1 | template1
16975 | template0
16976 | NEUROMR
16983 | accounts
1063179 | comanagers
1063190 | genealogy
1063237 | junk
1063515 | lfix
1064226 | sql_ledger
1064465 | stjohns
3884888 | bray
4989386 | space database
(12 rows)
Similarly, inside $PGDATA/base/<dbdir>, relation files are named by
their oids in pg_class:
stjohns=# select oid,relname from pg_class where oid > 1000000 and oid <
2000000 order by oid limit 5;
oid | relname
---------+------------------------
1064466 | members
1064470 | pg_toast_1064466
1064472 | pg_toast_1064466_index
1064473 | members_pkey
1064475 | perms
(5 rows)
stjohns=# \! cd $PGDATA/base/1064465 ; ls 10* | head -5
1064466
1064470
1064472
1064473
1064475
pg_class has a column, relfilenode, which almost invariably has the same
value as the oid; I'm not sure whether, should they differ, the file
would be named relfilenode or oid, though I would presume the former.
--
Oliver Elphick Oliver....@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"The LORD is nigh unto all them that call upon him, to
all that call upon him in truth."
Psalms 145:18
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Tom Lane
> pg_class has a column, relfilenode, which almost invariably has the same
> value as the oid; I'm not sure whether, should they differ, the file
> would be named relfilenode or oid, though I would presume the former.
relfilenode is the thing to look at, not oid. AFAIR, only REINDEX and
CLUSTER assign new relfilenodes at present, so relfilenode will often
match oid --- but you'll eventually regret it if you write code that
assumes that.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?