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
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>73.1. Database File Layout</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="storage.html" title="Chapter 73. Database Physical Storage" /><link rel="next" href="storage-toast.html" title="73.2. TOAST" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">73.1. Database File Layout</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="storage.html" title="Chapter 73. Database Physical Storage">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="storage.html" title="Chapter 73. Database Physical Storage">Up</a></td><th width="60%" align="center">Chapter 73. Database Physical Storage</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="storage-toast.html" title="73.2. TOAST">Next</a></td></tr></table><hr /></div><div class="sect1" id="STORAGE-FILE-LAYOUT"><div class="titlepage"><div><div><h2 class="title" style="clear: both">73.1. Database File Layout</h2></div></div></div><p>
This section describes the storage format at the level of files and
directories.
</p><p>
Traditionally, the configuration and data files used by a database
cluster are stored together 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 server instances, 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 class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 73.1. Contents of PGDATA">Table 73.1</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 it is possible to place them elsewhere.
</p><div class="table" id="PGDATA-CONTENTS-TABLE"><p class="title"><strong>Table 73.1. Contents of <code class="varname">PGDATA</code></strong></p><div class="table-contents"><table class="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">current_logfiles</code></td><td>File recording the log file(s) currently written to by the logging
collector</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_commit_ts</code></td><td>Subdirectory containing transaction commit timestamp data</td></tr><tr><td><code class="filename">pg_dynshmem</code></td><td>Subdirectory containing files used by the dynamic shared memory
subsystem</td></tr><tr><td><code class="filename">pg_logical</code></td><td>Subdirectory containing status data for logical decoding</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_notify</code></td><td>Subdirectory containing LISTEN/NOTIFY status data</td></tr><tr><td><code class="filename">pg_replslot</code></td><td>Subdirectory containing replication slot data</td></tr><tr><td><code class="filename">pg_serial</code></td><td>Subdirectory containing information about committed serializable transactions</td></tr><tr><td><code class="filename">pg_snapshots</code></td><td>Subdirectory containing exported snapshots</td></tr><tr><td><code class="filename">pg_stat</code></td><td>Subdirectory containing permanent files for the statistics
subsystem</td></tr><tr><td><code class="filename">pg_stat_tmp</code></td><td>Subdirectory containing temporary files for the statistics
subsystem</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_wal</code></td><td>Subdirectory containing WAL (Write Ahead Log) files</td></tr><tr><td><code class="filename">pg_xact</code></td><td>Subdirectory containing transaction commit status data</td></tr><tr><td><code class="filename">postgresql.auto.conf</code></td><td>A file used for storing configuration parameters that are set by
<code class="command">ALTER SYSTEM</code></td></tr><tr><td><code class="filename">postmaster.opts</code></td><td>A file recording the command-line options the server was
last started with</td></tr><tr><td><code class="filename">postmaster.pid</code></td><td>A lock file recording the current postmaster process ID (PID),
cluster data directory path,
postmaster start timestamp,
port number,
Unix-domain socket directory path (could be empty),
first valid listen_address (IP address or <code class="literal">*</code>, or empty if
not listening on TCP),
and shared memory segment ID
(this file is not present after server 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>
Note that the following sections describe the behavior of the builtin
<code class="literal">heap</code> <a class="link" href="tableam.html" title="Chapter 63. Table Access Method Interface Definition">table access method</a>,
and the builtin <a class="link" href="indexam.html" title="Chapter 64. Index Access Method Interface Definition">index access methods</a>. Due
to the extensible nature of <span class="productname">PostgreSQL</span>, other
access methods might work differently.
</p><p>
Each table and index is stored in a separate file. For ordinary relations,
these files are 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>. But
for temporary relations, the file name is of the form
<code class="literal">t<em class="replaceable"><code>BBB</code></em>_<em class="replaceable"><code>FFF</code></em></code>, where <em class="replaceable"><code>BBB</code></em>
is the backend ID of the backend which created the file, and <em class="replaceable"><code>FFF</code></em>
is the filenode number. In either case, in addition to the main file (a/k/a
main fork), each table and index has a <em class="firstterm">free space map</em> (see <a class="xref" href="storage-fsm.html" title="73.3. Free Space Map">Section 73.3</a>), 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 <code class="literal">_fsm</code>. Tables also have a
<em class="firstterm">visibility map</em>, stored in a fork with the suffix <code class="literal">_vm</code>,
to track which pages are known to have no dead tuples. The visibility map is
described further in <a class="xref" href="storage-vm.html" title="73.4. Visibility Map">Section 73.4</a>. Unlogged tables and indexes
have a third fork, known as the initialization fork, which is stored in a fork
with the suffix <code class="literal">_init</code> (see <a class="xref" href="storage-init.html" title="73.5. The Initialization Fork">Section 73.5</a>).
</p><div class="caution"><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.
Also, for certain system catalogs including <code class="structname">pg_class</code> itself,
<code class="structname">pg_class</code>.<code class="structfield">relfilenode</code> contains zero. The
actual filenode number of these catalogs is stored in a lower-level data
structure, and can be obtained using the <code class="function">pg_relation_filenode()</code>
function.
</p></div><p>
When a table or index exceeds 1 GB, 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.
(Actually, 1 GB is just the default segment size. The segment size can be
adjusted using the configuration option <code class="option">--with-segsize</code>
when building <span class="productname">PostgreSQL</span>.)
In principle, free space map and visibility map forks could require multiple
segments as well, though this is unlikely to happen in practice.
</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 class="xref" href="storage-toast.html" title="73.2. TOAST">Section 73.2</a> for more information.
</p><p>
The contents of tables and indexes are discussed further in
<a class="xref" href="storage-page-layout.html" title="73.6. Database Page Layout">Section 73.6</a>.
</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 (i.e., the
location specified in the tablespace's <code class="command">CREATE TABLESPACE</code> 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 the <span class="productname">PostgreSQL</span>
server version, such as <code class="literal">PG_9.0_201008051</code>. (The reason for using
this subdirectory is so that successive versions of the database can use
the same <code class="command">CREATE TABLESPACE</code> 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.
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><p>
The <code class="function">pg_relation_filepath()</code> function shows the entire path
(relative to <code class="varname">PGDATA</code>) 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 <code class="literal">_fsm</code>, <code class="literal">_vm</code>, or <code class="literal">_init</code> to find all
the files associated with the relation.
</p><p>
Temporary files (for operations such as sorting more data than can fit in
memory) are created within <code class="varname">PGDATA</code><code class="filename">/base/pgsql_tmp</code>,
or within a <code class="filename">pgsql_tmp</code> subdirectory of a tablespace directory
if a tablespace other than <code class="literal">pg_default</code> is specified for them.
The name of a temporary file has the form
<code class="filename">pgsql_tmp<em class="replaceable"><code>PPP</code></em>.<em class="replaceable"><code>NNN</code></em></code>,
where <em class="replaceable"><code>PPP</code></em> is the PID of the owning backend and
<em class="replaceable"><code>NNN</code></em> distinguishes different temporary files of that backend.
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="storage.html" title="Chapter 73. Database Physical Storage">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="storage.html" title="Chapter 73. Database Physical Storage">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="storage-toast.html" title="73.2. TOAST">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 73. Database Physical Storage </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 73.2. TOAST</td></tr></table></div></body></html>
|
