<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Database Physical Storage</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Internals" HREF="internals.html"><LINK REL="PREVIOUS" TITLE="Examples" HREF="examples.html"><LINK REL="NEXT" TITLE="TOAST" HREF="storage-toast.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="CHAPTER" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="examples.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="gist.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="bki.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="storage-toast.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="CHAPTER" ><H1 ><A NAME="STORAGE" ></A >Chapter 49. Database Physical Storage</H1 ><DIV CLASS="TOC" ><DL ><DT ><B >Table of Contents</B ></DT ><DT >49.1. <A HREF="storage.html#STORAGE-FILE-LAYOUT" >Database File Layout</A ></DT ><DT >49.2. <A HREF="storage-toast.html" >TOAST</A ></DT ><DT >49.3. <A HREF="storage-page-layout.html" >Database Page Layout</A ></DT ></DL ></DIV ><P >This chapter provides an overview of the physical storage format used by <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > databases.</P ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="STORAGE-FILE-LAYOUT" >49.1. Database File Layout</A ></H1 ><P >This section describes the storage format at the level of files and directories.</P ><P >All the data needed for a database cluster is stored within the cluster's data directory, commonly referred to as <TT CLASS="VARNAME" >PGDATA</TT > (after the name of the environment variable that can be used to define it). A common location for <TT CLASS="VARNAME" >PGDATA</TT > is <TT CLASS="FILENAME" >/var/lib/pgsql/data</TT >. Multiple clusters, managed by different postmasters, can exist on the same machine.</P ><P >The <TT CLASS="VARNAME" >PGDATA</TT > directory contains several subdirectories and control files, as shown in <A HREF="storage.html#PGDATA-CONTENTS-TABLE" >Table 49-1</A >. In addition to these required items, the cluster configuration files <TT CLASS="FILENAME" >postgresql.conf</TT >, <TT CLASS="FILENAME" >pg_hba.conf</TT >, and <TT CLASS="FILENAME" >pg_ident.conf</TT > are traditionally stored in <TT CLASS="VARNAME" >PGDATA</TT > (although beginning in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > 8.0 it is possible to keep them elsewhere). </P ><DIV CLASS="TABLE" ><A NAME="PGDATA-CONTENTS-TABLE" ></A ><P ><B >Table 49-1. Contents of <TT CLASS="VARNAME" >PGDATA</TT ></B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><THEAD ><TR ><TH >Item</TH ><TH >Description</TH ></TR ></THEAD ><TBODY ><TR ><TD ><TT CLASS="FILENAME" >PG_VERSION</TT ></TD ><TD >A file containing the major version number of <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN ></TD ></TR ><TR ><TD ><TT CLASS="FILENAME" >base</TT ></TD ><TD >Subdirectory containing per-database subdirectories</TD ></TR ><TR ><TD ><TT CLASS="FILENAME" >global</TT ></TD ><TD >Subdirectory containing cluster-wide tables, such as <TT CLASS="STRUCTNAME" >pg_database</TT ></TD ></TR ><TR ><TD ><TT CLASS="FILENAME" >pg_clog</TT ></TD ><TD >Subdirectory containing transaction commit status data</TD ></TR ><TR ><TD ><TT CLASS="FILENAME" >pg_subtrans</TT ></TD ><TD >Subdirectory containing subtransaction status data</TD ></TR ><TR ><TD ><TT CLASS="FILENAME" >pg_tblspc</TT ></TD ><TD >Subdirectory containing symbolic links to tablespaces</TD ></TR ><TR ><TD ><TT CLASS="FILENAME" >pg_xlog</TT ></TD ><TD >Subdirectory containing WAL (Write Ahead Log) files</TD ></TR ><TR ><TD ><TT CLASS="FILENAME" >postmaster.opts</TT ></TD ><TD >A file recording the command-line options the postmaster was last started with</TD ></TR ><TR ><TD ><TT CLASS="FILENAME" >postmaster.pid</TT ></TD ><TD >A lock file recording the current postmaster PID and shared memory segment ID (not present after postmaster shutdown)</TD ></TR ></TBODY ></TABLE ></DIV ><P >For each database in the cluster there is a subdirectory within <TT CLASS="VARNAME" >PGDATA</TT ><TT CLASS="FILENAME" >/base</TT >, named after the database's OID in <TT CLASS="STRUCTNAME" >pg_database</TT >. This subdirectory is the default location for the database's files; in particular, its system catalogs are stored there.</P ><P >Each table and index is stored in a separate file, named after the table or index's <I CLASS="FIRSTTERM" >filenode</I > number, which can be found in <TT CLASS="STRUCTNAME" >pg_class</TT >.<TT CLASS="STRUCTFIELD" >relfilenode</TT >.</P ><DIV CLASS="CAUTION" ><P ></P ><TABLE CLASS="CAUTION" BORDER="1" WIDTH="100%" ><TR ><TD ALIGN="CENTER" ><B >Caution</B ></TD ></TR ><TR ><TD ALIGN="LEFT" ><P >Note that while a table's filenode often matches its OID, this is <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >not</I ></SPAN > necessarily the case; some operations, like <TT CLASS="COMMAND" >TRUNCATE</TT >, <TT CLASS="COMMAND" >REINDEX</TT >, <TT CLASS="COMMAND" >CLUSTER</TT > and some forms of <TT CLASS="COMMAND" >ALTER TABLE</TT >, can change the filenode while preserving the OID. Avoid assuming that filenode and table OID are the same.</P ></TD ></TR ></TABLE ></DIV ><P >When a table or index exceeds 1Gb, it is divided into gigabyte-sized <I CLASS="FIRSTTERM" >segments</I >. 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. The contents of tables and indexes are discussed further in <A HREF="storage-page-layout.html" >Section 49.3</A >.</P ><P >A table that has columns with potentially large entries will have an associated <I CLASS="FIRSTTERM" >TOAST</I > table, which is used for out-of-line storage of field values that are too large to keep in the table rows proper. <TT CLASS="STRUCTNAME" >pg_class</TT >.<TT CLASS="STRUCTFIELD" >reltoastrelid</TT > links from a table to its <ACRONYM CLASS="ACRONYM" >TOAST</ACRONYM > table, if any. See <A HREF="storage-toast.html" >Section 49.2</A > for more information.</P ><P >Tablespaces make the scenario more complicated. Each user-defined tablespace has a symbolic link inside the <TT CLASS="VARNAME" >PGDATA</TT ><TT CLASS="FILENAME" >/pg_tblspc</TT > directory, which points to the physical tablespace directory (as specified in its <TT CLASS="COMMAND" >CREATE TABLESPACE</TT > command). The symbolic link is named after the tablespace's OID. Inside the physical tablespace directory there is a subdirectory for each database that has elements in the tablespace, named after the database's OID. Tables within that directory follow the filenode naming scheme. The <TT CLASS="LITERAL" >pg_default</TT > tablespace is not accessed through <TT CLASS="FILENAME" >pg_tblspc</TT >, but corresponds to <TT CLASS="VARNAME" >PGDATA</TT ><TT CLASS="FILENAME" >/base</TT >. Similarly, the <TT CLASS="LITERAL" >pg_global</TT > tablespace is not accessed through <TT CLASS="FILENAME" >pg_tblspc</TT >, but corresponds to <TT CLASS="VARNAME" >PGDATA</TT ><TT CLASS="FILENAME" >/global</TT >.</P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="examples.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="storage-toast.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Examples</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="internals.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >TOAST</TD ></TR ></TABLE ></DIV ></BODY ></HTML >
