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@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.Elphick@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