Thread: How are postgreSQL database files structured?

Hey folks:

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

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

Oliver Elphick <olly@lfix.co.uk> writes:
> 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