63.1. Database File Layout
This section describes the storage format at the level of files and directories.
Traditionally, the configuration and data files used by a database cluster are stored together within the cluster's data directory, commonly referred to asPGDATA (after the name of the environment variable that can be used to define it). A common location forPGDATA is/var/lib/pgsql/data. Multiple clusters, managed by different server instances, can exist on the same machine.
ThePGDATA directory contains several subdirectories and control files, as shown inTable 63.1. In addition to these required items, the cluster configuration filespostgresql.conf,pg_hba.conf, andpg_ident.conf are traditionally stored inPGDATA, although it is possible to place them elsewhere.
Table 63.1. Contents ofPGDATA
| Item | Description |
|---|---|
PG_VERSION | A file containing the major version number ofPostgreSQL |
base | Subdirectory containing per-database subdirectories |
global | Subdirectory containing cluster-wide tables, such aspg_database |
pg_commit_ts | Subdirectory containing transaction commit timestamp data |
pg_clog | Subdirectory containing transaction commit status data |
pg_dynshmem | Subdirectory containing files used by the dynamic shared memory subsystem |
pg_logical | Subdirectory containing status data for logical decoding |
pg_multixact | Subdirectory containing multitransaction status data (used for shared row locks) |
pg_notify | Subdirectory containing LISTEN/NOTIFY status data |
pg_replslot | Subdirectory containing replication slot data |
pg_serial | Subdirectory containing information about committed serializable transactions |
pg_snapshots | Subdirectory containing exported snapshots |
pg_stat | Subdirectory containing permanent files for the statistics subsystem |
pg_stat_tmp | Subdirectory containing temporary files for the statistics subsystem |
pg_subtrans | Subdirectory containing subtransaction status data |
pg_tblspc | Subdirectory containing symbolic links to tablespaces |
pg_twophase | Subdirectory containing state files for prepared transactions |
pg_xlog | Subdirectory containing WAL (Write Ahead Log) files |
postgresql.auto.conf | A file used for storing configuration parameters that are set byALTER SYSTEM |
postmaster.opts | A file recording the command-line options the server was last started with |
postmaster.pid | A lock file recording the current postmaster process ID (PID), cluster data directory path, postmaster start timestamp, port number, Unix-domain socket directory path (empty on Windows), first valid listen_address (IP address or*, or empty if not listening on TCP), and shared memory segment ID (this file is not present after server shutdown) |
For each database in the cluster there is a subdirectory withinPGDATA/base, named after the database's OID inpg_database. This subdirectory is the default location for the database's files; in particular, its system catalogs are stored there.
Each table and index is stored in a separate file. For ordinary relations, these files are named after the table or index'sfilenode number, which can be found inpg_class.relfilenode. But for temporary relations, the file name is of the formt, whereBBB_FFFBBB is the backend ID of the backend which created the file, andFFF is the filenode number. In either case, in addition to the main file (a/k/a main fork), each table and index has afree space map (seeSection 63.3), which stores information about free space available in the relation. The free space map is stored in a file named with the filenode number plus the suffix_fsm. Tables also have avisibility map, stored in a fork with the suffix_vm, to track which pages are known to have no dead tuples. The visibility map is described further inSection 63.4. Unlogged tables and indexes have a third fork, known as the initialization fork, which is stored in a fork with the suffix_init (seeSection 63.5).
Caution
Note that while a table's filenode often matches its OID, this isnot necessarily the case; some operations, likeTRUNCATE,REINDEX,CLUSTER and some forms ofALTER TABLE, can change the filenode while preserving the OID. Avoid assuming that filenode and table OID are the same. Also, for certain system catalogs includingpg_class itself,pg_class.relfilenode contains zero. The actual filenode number of these catalogs is stored in a lower-level data structure, and can be obtained using thepg_relation_filenode() function.
When a table or index exceeds 1 GB, it is divided into gigabyte-sizedsegments. The first segment's file name is the same as the filenode; subsequent segments are named filenode.1, filenode.2, etc. This arrangement avoids problems on platforms that have file size limitations. (Actually, 1 GB is just the default segment size. The segment size can be adjusted using the configuration option--with-segsize when buildingPostgreSQL.) In principle, free space map and visibility map forks could require multiple segments as well, though this is unlikely to happen in practice.
A table that has columns with potentially large entries will have an associatedTOAST table, which is used for out-of-line storage of field values that are too large to keep in the table rows proper.pg_class.reltoastrelid links from a table to itsTOAST table, if any. SeeSection 63.2 for more information.
The contents of tables and indexes are discussed further inSection 63.6.
Tablespaces make the scenario more complicated. Each user-defined tablespace has a symbolic link inside thePGDATA/pg_tblspc directory, which points to the physical tablespace directory (i.e., the location specified in the tablespace'sCREATE TABLESPACE command). This symbolic link is named after the tablespace's OID. Inside the physical tablespace directory there is a subdirectory with a name that depends on thePostgreSQL server version, such asPG_9.0_201008051. (The reason for using this subdirectory is so that successive versions of the database can use the sameCREATE TABLESPACE location value without conflicts.) Within the version-specific subdirectory, there is a subdirectory for each database that has elements in the tablespace, named after the database's OID. Tables and indexes are stored within that directory, using the filenode naming scheme. Thepg_default tablespace is not accessed throughpg_tblspc, but corresponds toPGDATA/base. Similarly, thepg_global tablespace is not accessed throughpg_tblspc, but corresponds toPGDATA/global.
Thepg_relation_filepath() function shows the entire path (relative toPGDATA) of any relation. It is often useful as a substitute for remembering many of the above rules. But keep in mind that this function just gives the name of the first segment of the main fork of the relation — you may need to append a segment number and/or_fsm,_vm, or_init to find all the files associated with the relation.
Temporary files (for operations such as sorting more data than can fit in memory) are created withinPGDATA/base/pgsql_tmp, or within apgsql_tmp subdirectory of a tablespace directory if a tablespace other thanpg_default is specified for them. The name of a temporary file has the formpgsql_tmp, wherePPP.NNNPPP is the PID of the owning backend andNNN distinguishes different temporary files of that backend.