Re: How are postgreSQL database files structured?

On Sat, 2002-11-23 at 04:12, Hugh Esco wrote:

> 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(at)linda:~$ cd $PGDATA
postgres(at)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(at)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(dot)Elphick(at)lfix(dot)co(dot)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