Re: How are postgreSQL database files structured?:

Re: How are postgreSQL database files structured?

---

• From: Oliver Elphick <olly ( at ) lfix ( dot ) co ( dot ) uk>
• To: Hugh Esco <hesco ( at ) greens ( dot ) org>
• Subject: Re: How are postgreSQL database files structured?
• Date: 23 Nov 2002 17:08:18 +0000

---

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/, 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