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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>PartIII.Server Administration</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="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="prev" href="populate.html" title="13.4.Populating a Database">
<link rel="next" href="installation.html" title="Chapter14. Installation Instructions">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="part" lang="en" id="admin">
<div class="titlepage"><div><div><h1 class="title">
<a name="admin"></a>PartIII.Server Administration</h1></div></div></div>
<div class="partintro" lang="en" id="id634132">
<div></div>
<p> This part covers topics that are of interest to a
<span class="productname">PostgreSQL</span> database administrator. This includes
installation of the software, set up and configuration of the
server, management of users and databases, and maintenance tasks.
Anyone who runs a <span class="productname">PostgreSQL</span> server, even for
personal use, but especially in production, should be familiar
with the topics covered in this part.
</p>
<p> The information in this part is arranged approximately in the
order in which a new user should read it. But the chapters are
self-contained and can be read individually as desired. The
information in this part is presented in a narrative fashion in
topical units. Readers looking for a complete description of a
particular command should look into <a href="reference.html" title="PartVI.Reference">PartVI, “Reference”</a>.
</p>
<p> The first few chapters are written so that they can be understood
without prerequisite knowledge, so that new users who need to set
up their own server can begin their exploration with this part.
The rest of this part is about tuning and management; that material
assumes that the reader is familiar with the general use of
the <span class="productname">PostgreSQL</span> database system. Readers are
encouraged to look at <a href="tutorial.html" title="PartI.Tutorial">PartI, “Tutorial”</a> and <a href="sql.html" title="PartII.The SQL Language">PartII, “The SQL Language”</a> for additional information.
</p>
<div class="toc">
<p><b>Table of Contents</b></p>
<dl>
<dt><span class="chapter"><a href="installation.html">14. Installation Instructions</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="installation.html#install-short">14.1. Short Version</a></span></dt>
<dt><span class="sect1"><a href="install-requirements.html">14.2. Requirements</a></span></dt>
<dt><span class="sect1"><a href="install-getsource.html">14.3. Getting The Source</a></span></dt>
<dt><span class="sect1"><a href="install-upgrading.html">14.4. If You Are Upgrading</a></span></dt>
<dt><span class="sect1"><a href="install-procedure.html">14.5. Installation Procedure</a></span></dt>
<dt><span class="sect1"><a href="install-post.html">14.6. Post-Installation Setup</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="install-post.html#id637172">14.6.1. Shared Libraries</a></span></dt>
<dt><span class="sect2"><a href="install-post.html#id637545">14.6.2. Environment Variables</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="supported-platforms.html">14.7. Supported Platforms</a></span></dt>
</dl></dd>
<dt><span class="chapter"><a href="install-win32.html">15. Client-Only Installation on <span class="productname">Windows</span></a></span></dt>
<dt><span class="chapter"><a href="runtime.html">16. Operating System Environment</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="runtime.html#postgres-user">16.1. The <span class="productname">PostgreSQL</span> User Account</a></span></dt>
<dt><span class="sect1"><a href="creating-cluster.html">16.2. Creating a Database Cluster</a></span></dt>
<dt><span class="sect1"><a href="postmaster-start.html">16.3. Starting the Database Server</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="postmaster-start.html#postmaster-start-failures">16.3.1. Server Start-up Failures</a></span></dt>
<dt><span class="sect2"><a href="postmaster-start.html#client-connection-problems">16.3.2. Client Connection Problems</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="kernel-resources.html">16.4. Managing Kernel Resources</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="kernel-resources.html#sysvipc">16.4.1. Shared Memory and Semaphores</a></span></dt>
<dt><span class="sect2"><a href="kernel-resources.html#id643652">16.4.2. Resource Limits</a></span></dt>
<dt><span class="sect2"><a href="kernel-resources.html#id643848">16.4.3. Linux Memory Overcommit</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="postmaster-shutdown.html">16.5. Shutting Down the Server</a></span></dt>
<dt><span class="sect1"><a href="encryption-options.html">16.6. Encryption Options</a></span></dt>
<dt><span class="sect1"><a href="ssl-tcp.html">16.7. Secure TCP/IP Connections with SSL</a></span></dt>
<dt><span class="sect1"><a href="ssh-tunnels.html">16.8. Secure TCP/IP Connections with <span class="application">SSH</span> Tunnels</a></span></dt>
</dl></dd>
<dt><span class="chapter"><a href="runtime-config.html">17. Server Configuration</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="runtime-config.html#config-setting">17.1. Setting Parameters</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-file-locations.html">17.2. File Locations</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-connection.html">17.3. Connections and Authentication</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-connection.html#runtime-config-connection-settings">17.3.1. Connection Settings</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-connection.html#runtime-config-connection-security">17.3.2. Security and Authentication</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-resource.html">17.4. Resource Consumption</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-resource.html#runtime-config-resource-memory">17.4.1. Memory</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-resource.html#runtime-config-resource-fsm">17.4.2. Free Space Map</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-resource.html#runtime-config-resource-kernel">17.4.3. Kernel Resource Usage</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-resource.html#runtime-config-resource-vacuum-cost">17.4.4. Cost-Based Vacuum Delay
</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-resource.html#runtime-config-resource-background-writer">17.4.5. Background Writer</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-wal.html">17.5. Write Ahead Log</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-wal.html#runtime-config-wal-settings">17.5.1. Settings</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-wal.html#runtime-config-wal-checkpoints">17.5.2. Checkpoints</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-wal.html#runtime-config-wal-archiving">17.5.3. Archiving</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-query.html">17.6. Query Planning</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-query.html#runtime-config-query-enable">17.6.1. Planner Method Configuration</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-query.html#runtime-config-query-constants">17.6.2. Planner Cost Constants
</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-query.html#runtime-config-query-geqo">17.6.3. Genetic Query Optimizer</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-query.html#runtime-config-query-other">17.6.4. Other Planner Options</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-logging.html">17.7. Error Reporting and Logging</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-logging.html#runtime-config-logging-where">17.7.1. Where To Log</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-logging.html#runtime-config-logging-when">17.7.2. When To Log</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-logging.html#runtime-config-logging-what">17.7.3. What To Log</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-statistics.html">17.8. Run-Time Statistics</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-statistics.html#runtime-config-statistics-monitor">17.8.1. Statistics Monitoring</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-statistics.html#runtime-config-statistics-collector">17.8.2. Query and Index Statistics Collector</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-autovacuum.html">17.9. Automatic Vacuuming</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-client.html">17.10. Client Connection Defaults</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-client.html#runtime-config-client-statement">17.10.1. Statement Behavior</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-client.html#runtime-config-client-format">17.10.2. Locale and Formatting</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-client.html#runtime-config-client-other">17.10.3. Other Defaults</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-locks.html">17.11. Lock Management</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-compatible.html">17.12. Version and Platform Compatibility</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="runtime-config-compatible.html#runtime-config-compatible-version">17.12.1. Previous PostgreSQL Versions</a></span></dt>
<dt><span class="sect2"><a href="runtime-config-compatible.html#runtime-config-compatible-clients">17.12.2. Platform and Client Compatibility</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="runtime-config-preset.html">17.13. Preset Options</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-custom.html">17.14. Customized Options</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-developer.html">17.15. Developer Options</a></span></dt>
<dt><span class="sect1"><a href="runtime-config-short.html">17.16. Short Options</a></span></dt>
</dl></dd>
<dt><span class="chapter"><a href="user-manag.html">18. Database Roles and Privileges</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="user-manag.html#database-roles">18.1. Database Roles</a></span></dt>
<dt><span class="sect1"><a href="role-attributes.html">18.2. Role Attributes</a></span></dt>
<dt><span class="sect1"><a href="privileges.html">18.3. Privileges</a></span></dt>
<dt><span class="sect1"><a href="role-membership.html">18.4. Role Membership</a></span></dt>
<dt><span class="sect1"><a href="perm-functions.html">18.5. Functions and Triggers</a></span></dt>
</dl></dd>
<dt><span class="chapter"><a href="managing-databases.html">19. Managing Databases</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="managing-databases.html#manage-ag-overview">19.1. Overview</a></span></dt>
<dt><span class="sect1"><a href="manage-ag-createdb.html">19.2. Creating a Database</a></span></dt>
<dt><span class="sect1"><a href="manage-ag-templatedbs.html">19.3. Template Databases</a></span></dt>
<dt><span class="sect1"><a href="manage-ag-config.html">19.4. Database Configuration</a></span></dt>
<dt><span class="sect1"><a href="manage-ag-dropdb.html">19.5. Destroying a Database</a></span></dt>
<dt><span class="sect1"><a href="manage-ag-tablespaces.html">19.6. Tablespaces</a></span></dt>
</dl></dd>
<dt><span class="chapter"><a href="client-authentication.html">20. Client Authentication</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="client-authentication.html#auth-pg-hba-conf">20.1. The <code class="filename">pg_hba.conf</code> file</a></span></dt>
<dt><span class="sect1"><a href="auth-methods.html">20.2. Authentication methods</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="auth-methods.html#auth-trust">20.2.1. Trust authentication</a></span></dt>
<dt><span class="sect2"><a href="auth-methods.html#auth-password">20.2.2. Password authentication</a></span></dt>
<dt><span class="sect2"><a href="auth-methods.html#kerberos-auth">20.2.3. Kerberos authentication</a></span></dt>
<dt><span class="sect2"><a href="auth-methods.html#auth-ident">20.2.4. Ident-based authentication</a></span></dt>
<dt><span class="sect2"><a href="auth-methods.html#auth-pam">20.2.5. PAM authentication</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="client-authentication-problems.html">20.3. Authentication problems</a></span></dt>
</dl></dd>
<dt><span class="chapter"><a href="charset.html">21. Localization</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="charset.html#locale">21.1. Locale Support</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="charset.html#id663047">21.1.1. Overview</a></span></dt>
<dt><span class="sect2"><a href="charset.html#id663399">21.1.2. Behavior</a></span></dt>
<dt><span class="sect2"><a href="charset.html#id663578">21.1.3. Problems</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="multibyte.html">21.2. Character Set Support</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="multibyte.html#multibyte-charset-supported">21.2.1. Supported Character Sets</a></span></dt>
<dt><span class="sect2"><a href="multibyte.html#id664688">21.2.2. Setting the Character Set</a></span></dt>
<dt><span class="sect2"><a href="multibyte.html#id664905">21.2.3. Automatic Character Set Conversion Between Server and Client</a></span></dt>
<dt><span class="sect2"><a href="multibyte.html#id666163">21.2.4. Further Reading</a></span></dt>
</dl></dd>
</dl></dd>
<dt><span class="chapter"><a href="maintenance.html">22. Routine Database Maintenance Tasks</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="maintenance.html#routine-vacuuming">22.1. Routine Vacuuming</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="maintenance.html#vacuum-for-space-recovery">22.1.1. Recovering disk space</a></span></dt>
<dt><span class="sect2"><a href="maintenance.html#vacuum-for-statistics">22.1.2. Updating planner statistics</a></span></dt>
<dt><span class="sect2"><a href="maintenance.html#vacuum-for-wraparound">22.1.3. Preventing transaction ID wraparound failures</a></span></dt>
<dt><span class="sect2"><a href="maintenance.html#autovacuum">22.1.4. The auto-vacuum daemon</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="routine-reindex.html">22.2. Routine Reindexing</a></span></dt>
<dt><span class="sect1"><a href="logfile-maintenance.html">22.3. Log File Maintenance</a></span></dt>
</dl></dd>
<dt><span class="chapter"><a href="backup.html">23. Backup and Restore</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="backup.html#backup-dump">23.1. <acronym class="acronym">SQL</acronym> Dump</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="backup.html#backup-dump-restore">23.1.1. Restoring the dump</a></span></dt>
<dt><span class="sect2"><a href="backup.html#backup-dump-all">23.1.2. Using <span class="application">pg_dumpall</span></a></span></dt>
<dt><span class="sect2"><a href="backup.html#backup-dump-large">23.1.3. Handling large databases</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="backup-file.html">23.2. File system level backup</a></span></dt>
<dt><span class="sect1"><a href="backup-online.html">23.3. On-line backup and point-in-time recovery (PITR)</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="backup-online.html#backup-archiving-wal">23.3.1. Setting up WAL archiving</a></span></dt>
<dt><span class="sect2"><a href="backup-online.html#backup-base-backup">23.3.2. Making a Base Backup</a></span></dt>
<dt><span class="sect2"><a href="backup-online.html#backup-pitr-recovery">23.3.3. Recovering with an On-line Backup</a></span></dt>
<dt><span class="sect2"><a href="backup-online.html#backup-timelines">23.3.4. Timelines</a></span></dt>
<dt><span class="sect2"><a href="backup-online.html#backup-online-caveats">23.3.5. Caveats</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="migration.html">23.4. Migration Between Releases</a></span></dt>
</dl></dd>
<dt><span class="chapter"><a href="monitoring.html">24. Monitoring Database Activity</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="monitoring.html#monitoring-ps">24.1. Standard Unix Tools</a></span></dt>
<dt><span class="sect1"><a href="monitoring-stats.html">24.2. The Statistics Collector</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="monitoring-stats.html#monitoring-stats-setup">24.2.1. Statistics Collection Configuration</a></span></dt>
<dt><span class="sect2"><a href="monitoring-stats.html#monitoring-stats-views">24.2.2. Viewing Collected Statistics</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="monitoring-locks.html">24.3. Viewing Locks</a></span></dt>
</dl></dd>
<dt><span class="chapter"><a href="diskusage.html">25. Monitoring Disk Usage</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="diskusage.html#disk-usage">25.1. Determining Disk Usage</a></span></dt>
<dt><span class="sect1"><a href="disk-full.html">25.2. Disk Full Failure</a></span></dt>
</dl></dd>
<dt><span class="chapter"><a href="wal.html">26. Reliability and the Write-Ahead Log</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="wal.html#wal-reliability">26.1. Reliability</a></span></dt>
<dt><span class="sect1"><a href="wal-intro.html">26.2. Write-Ahead Logging (<acronym class="acronym">WAL</acronym>)</a></span></dt>
<dt><span class="sect1"><a href="wal-configuration.html">26.3. <acronym class="acronym">WAL</acronym> Configuration</a></span></dt>
<dt><span class="sect1"><a href="wal-internals.html">26.4. WAL Internals</a></span></dt>
</dl></dd>
<dt><span class="chapter"><a href="regress.html">27. Regression Tests</a></span></dt>
<dd><dl>
<dt><span class="sect1"><a href="regress.html#regress-run">27.1. Running the Tests</a></span></dt>
<dt><span class="sect1"><a href="regress-evaluation.html">27.2. Test Evaluation</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="regress-evaluation.html#id674902">27.2.1. Error message differences</a></span></dt>
<dt><span class="sect2"><a href="regress-evaluation.html#id674925">27.2.2. Locale differences</a></span></dt>
<dt><span class="sect2"><a href="regress-evaluation.html#id674939">27.2.3. Date and time differences</a></span></dt>
<dt><span class="sect2"><a href="regress-evaluation.html#id674969">27.2.4. Floating-point differences</a></span></dt>
<dt><span class="sect2"><a href="regress-evaluation.html#id675046">27.2.5. Row ordering differences</a></span></dt>
<dt><span class="sect2"><a href="regress-evaluation.html#id675124">27.2.6. Insufficient stack depth</a></span></dt>
<dt><span class="sect2"><a href="regress-evaluation.html#id675168">27.2.7. The “<span class="quote">random</span>” test</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="regress-variant.html">27.3. Variant Comparison Files</a></span></dt>
</dl></dd>
</dl>
</div>
</div>
</div></body>
</html>
|
