The Database Hacker's Handbook: Defending Database Servers

The PostgreSQL File Structure

PostgreSQL is typically installed to /usr/local/pgsql or /var/lib/pgsql on Unix systems and C:\Program Files\PostgreSQL\<version number> under Windows. The file structure is as follows:

/var/lib/pgsql/data
|
|   pg_hba.conf
|   pg_ident.conf
|   PG_VERSION
|   postgresql.conf
|   postmaster.opts
|   postmaster.pid
|
+---base
|   +---1
|   |       1247
|   |       ...
|   |       17187
|   |       pg_internal.init
|   |       PG_VERSION
|   |
|   \---17229
|           1247
|           ...
|           17187
|           pg_internal.init
|           PG_VERSION
|
|
+---global
|       1213
|       ...
|       16758
|       config_exec_params
|       pgstat.stat
|       pg_control
|       pg_pwd
|
+---pg_clog
|       0000
|
+---pg_log
|       postgresql-2004-12-05_000000.log
|
+---pg_subtrans
|       0000
|
+---pg_tblspc
\---pg_xlog
    |   000000010000000000000000
    |
    \---archive_status

Configuration files and the databases themselves are stored in the data directory, which is assigned to the environment variable $PGDATA. The $PGDATA directory contains three subdirectories. The base subdirectory contains the databases, represented as directories named after their object identifier (OID). The pg_database table holds the OID to database name mapping.

In addition to the databases created by the user, there are also a number of template databases. The CREATE DATABASE command takes an optional TEMPLATE parameter that specifies the template on which to base the new database. This enables “ready made” databases to be created that contain specific tables, views, data types, and functions defined in the template. On a default configuration, ...