PostgreSQL Data Directory

I l @ ve RuBoard

Every PostgreSQL installation requires that a base data directory be specified. Usually this is done with the initdb command during installation. This directory contains a number of files. The following is a typical listing of the files present in the default PostgreSQL data directory. (Note: These files may change from version to version, but a close approximation of these will exist.)

File	Description
$PGBASE/PG_VERSION	The file containing the version number of the version of PostgreSQL that created this data directory.
$PGBASE/base/	The directory that contains user -defined databases and the default template1 database.
$PGBASE/base/template1	The default template that is used as a model for all other user-created databases.
$PGBASE/pg_control	The internal control file for PostgreSQL, used for keeping checkpoints of flushed transactions, position tracking, and so on.
$PGBASE/pg_database	The internal control file for PostgreSQL that keeps a record of every database created on the system.
$PGBASE/pg_geqo	The configuration file used to define Genetic Query Optimizations (GEQO).
$PGBASE/pg_geqo.sample	A sample GEQO file.
$PGBASE/pg_group	The internal control file that stores group information and user membership.
$PGBASE/pg_group_ name_index	The index file for pg_group names .
$PGBASE/pg_group_ sysid_index	The index file for pg_group system IDs (UIDs).
$PGBASE/pg_log	Maintains the current status of transactions, either committed or uncommitted.
$PGBASE/pg_options	The configuration file used when postmaster is started (might be listed as postmaster.opts ).
$PGBASE/pg_pwd	The plain-text file containing usernames and passwords.
$PGBASE/pg_shadow	The system catalog containing usernames, passwords, and associated user rights.
$PGBASE/pg_variable	The internal control file used to store current variable settings such as the next OID and so on.
$PGBASE/pg_xlog	The directory to house log files generated by WAL.These files ensure database integrity through the new Write-Ahead Logging (Version 7.1 feature).
$PGBASE/postmaster.opts	The configuration file used when postmaster is started (might be listed as pg_options ).
$PGBASE/postgresql.conf	The configuration file used when postmaster is started (Version 7.1 feature).

You will notice that all user-defined databases are stored neatly in the $PGBASE/base directory. Every database created in PostgreSQL is stored in its own directory under $PGBASE/base . Within each directory are two main classes of files: system catalogs and user-created.

Note:

Some changes were made to these file locations starting inVersion 7.1. In particular, there now exists a template0 file that is a read-only copy of the template1 file. Additionally, many of the preceding files are now named according to their PID number; this change was made to facilitate the new Write-Ahead Logging (WAL) implementation. Refer to the latest documentation included with your system for more information.

System Catalogs

Every time a new database is created, PostgreSQL extracts a base set of system catalogs from the template1 database. These files are used to track tables, indexes, aggregates, operators, data types, and so on.

A basic set of system catalogs should look something like the following. (Different versions contain different catalog files, but this is a representational sample.)

File	Description
$PGBASE/pg_aggregate	Contains definitions of aggregate functions.
$PGBASE/pg_attrdef	Contains the default value of columns that indicate use of a default value condition.
$PGBASE/pg_attribute	Contains one row for every column in every table that describes attributes (that is, name , data type, and so on).
$PGBASE/pg_class	Contains information on all classes (tables, indexes, views, and so on).
$PGBASE/pg_database	Shared with the entire cluster, it contains information on available databases.
$PGBASE/pg_description	Holds comments created with the COMMENT SQL command.
$PGBASE/pg_group	Defines groups and membership.
$PGBASE/pg_index	Contains information on all defined indexes.
$PGBASE/pg_inherits	Contains information on table inheritance.
$PGBASE/pg_language	Registers the call interfaces for available PostgreSQL languages.
$PGBASE/pg_operator	One row for every operator type in the database.
$PGBASE/pg_proc	Contains information on all defined functions.
$PGBASE/pg_relcheck	Stores information on check constraints.
$PGBASE/pg_shadow	Stores user information, passwords, and effective user rights.
$PGBASE/pg_type	Stores information on all available data types in the database.

It is important to realize that these objects are accessible (from a DBA account) via a standard SQL interface. From psql , for instance, these system catalogs can be called like normal SQL tables.

Warning!

Viewing information is fine, but be careful about making any changes. Your database could quickly become unusable if the wrong changes are made.

User-Defined Catalogs

This directory also contains the names of any user-defined tables, indexes, sequences, and so on. For instance, looking in the directory for your newriders database, you see the following:

$PGBASE/authors	The authors table.
$PGBASE/auth_idx	The index file for the authors table.
$PGBASE/payroll	The payroll table.
$PGBASE/payroll_idx	The payroll index file.
$PGBASE/next_check_seq	Custom-created sequence to compute the next check number.

I l @ ve RuBoard