1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Chapter50.Database Physical Storage</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="internals.html" title="PartVII.Internals">
<link rel="prev" href="gist-recovery.html" title="49.5.Crash Recovery">
<link rel="next" href="storage-toast.html" title="50.2.TOAST">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="chapter" lang="en" id="storage">
<div class="titlepage"><div><div><h2 class="title">
<a name="storage"></a>Chapter50.Database Physical Storage</h2></div></div></div>
<div class="toc">
<p><b>Table of Contents</b></p>
<dl>
<dt><span class="sect1"><a href="storage.html#storage-file-layout">50.1. Database File Layout</a></span></dt>
<dt><span class="sect1"><a href="storage-toast.html">50.2. TOAST</a></span></dt>
<dt><span class="sect1"><a href="storage-page-layout.html">50.3. Database Page Layout</a></span></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" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="storage-file-layout"></a>50.1.Database File Layout</h2></div></div></div>
<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 <code class="varname">PGDATA</code> (after the name of the
environment variable that can be used to define it). A common location for
<code class="varname">PGDATA</code> is <code class="filename">/var/lib/pgsql/data</code>. Multiple clusters,
managed by different postmasters, can exist on the same machine.</p>
<p>The <code class="varname">PGDATA</code> directory contains several subdirectories and control
files, as shown in <a href="storage.html#pgdata-contents-table" title="Table50.1.Contents of PGDATA">Table50.1, “Contents of <code class="varname">PGDATA</code>”</a>. In addition to
these required items, the cluster configuration files
<code class="filename">postgresql.conf</code>, <code class="filename">pg_hba.conf</code>, and
<code class="filename">pg_ident.conf</code> are traditionally stored in
<code class="varname">PGDATA</code> (although in <span class="productname">PostgreSQL</span> 8.0 and
later, it is possible to keep them elsewhere). </p>
<div class="table">
<a name="pgdata-contents-table"></a><p class="title"><b>Table50.1.Contents of <code class="varname">PGDATA</code></b></p>
<div class="table-contents"><table summary="Contents of PGDATA" border="1">
<colgroup>
<col>
<col>
</colgroup>
<thead><tr>
<th>Item</th>
<th>Description</th>
</tr></thead>
<tbody>
<tr>
<td><code class="filename">PG_VERSION</code></td>
<td>A file containing the major version number of <span class="productname">PostgreSQL</span>
</td>
</tr>
<tr>
<td><code class="filename">base</code></td>
<td>Subdirectory containing per-database subdirectories</td>
</tr>
<tr>
<td><code class="filename">global</code></td>
<td>Subdirectory containing cluster-wide tables, such as
<code class="structname">pg_database</code>
</td>
</tr>
<tr>
<td><code class="filename">pg_clog</code></td>
<td>Subdirectory containing transaction commit status data</td>
</tr>
<tr>
<td><code class="filename">pg_multixact</code></td>
<td>Subdirectory containing multitransaction status data
(used for shared row locks)</td>
</tr>
<tr>
<td><code class="filename">pg_subtrans</code></td>
<td>Subdirectory containing subtransaction status data</td>
</tr>
<tr>
<td><code class="filename">pg_tblspc</code></td>
<td>Subdirectory containing symbolic links to tablespaces</td>
</tr>
<tr>
<td><code class="filename">pg_twophase</code></td>
<td>Subdirectory containing state files for prepared transactions</td>
</tr>
<tr>
<td><code class="filename">pg_xlog</code></td>
<td>Subdirectory containing WAL (Write Ahead Log) files</td>
</tr>
<tr>
<td><code class="filename">postmaster.opts</code></td>
<td>A file recording the command-line options the postmaster was
last started with</td>
</tr>
<tr>
<td><code class="filename">postmaster.pid</code></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>
</div>
<br class="table-break"><p>For each database in the cluster there is a subdirectory within
<code class="varname">PGDATA</code><code class="filename">/base</code>, named after the database's OID in
<code class="structname">pg_database</code>. 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 <em class="firstterm">filenode</em> number, which can be found in
<code class="structname">pg_class</code>.<code class="structfield">relfilenode</code>.</p>
<div class="caution" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Caution</h3>
<p>Note that while a table's filenode often matches its OID, this is
<span class="emphasis"><em>not</em></span> necessarily the case; some operations, like
<code class="command">TRUNCATE</code>, <code class="command">REINDEX</code>, <code class="command">CLUSTER</code> and some forms
of <code class="command">ALTER TABLE</code>, can change the filenode while preserving the OID.
Avoid assuming that filenode and table OID are the same.</p>
</div>
<p>When a table or index exceeds 1Gb, it is divided into gigabyte-sized
<em class="firstterm">segments</em>. 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" title="50.3.Database Page Layout">Section50.3, “Database Page Layout”</a>.</p>
<p>A table that has columns with potentially large entries will have an
associated <em class="firstterm">TOAST</em> table, which is used for out-of-line storage of
field values that are too large to keep in the table rows proper.
<code class="structname">pg_class</code>.<code class="structfield">reltoastrelid</code> links from a table to
its <acronym class="acronym">TOAST</acronym> table, if any.
See <a href="storage-toast.html" title="50.2.TOAST">Section50.2, “TOAST”</a> for more information.</p>
<p>Tablespaces make the scenario more complicated. Each user-defined tablespace
has a symbolic link inside the <code class="varname">PGDATA</code><code class="filename">/pg_tblspc</code>
directory, which points to the physical tablespace directory (as specified in
its <code class="command">CREATE TABLESPACE</code> 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 <code class="literal">pg_default</code> tablespace is not accessed through
<code class="filename">pg_tblspc</code>, but corresponds to
<code class="varname">PGDATA</code><code class="filename">/base</code>. Similarly, the <code class="literal">pg_global</code>
tablespace is not accessed through <code class="filename">pg_tblspc</code>, but corresponds to
<code class="varname">PGDATA</code><code class="filename">/global</code>.</p>
</div>
</div></body>
</html>
|
