2010-09-20 22:08:53 +02:00
|
|
|
<!-- doc/src/sgml/postgres.sgml -->
|
2000-03-31 05:26:21 +02:00
|
|
|
|
2019-08-13 08:38:21 +02:00
|
|
|
<!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN"
|
|
|
|
"http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd"
|
2017-11-23 15:39:47 +01:00
|
|
|
[
|
1998-05-13 07:33:32 +02:00
|
|
|
|
2011-04-04 22:07:53 +02:00
|
|
|
<!ENTITY % version SYSTEM "version.sgml">
|
2000-11-24 18:44:22 +01:00
|
|
|
%version;
|
2011-04-04 22:07:53 +02:00
|
|
|
<!ENTITY % filelist SYSTEM "filelist.sgml">
|
2000-11-24 18:44:22 +01:00
|
|
|
%filelist;
|
2000-07-21 02:44:13 +02:00
|
|
|
|
2011-04-04 22:07:53 +02:00
|
|
|
<!ENTITY reference SYSTEM "reference.sgml">
|
2000-07-21 02:44:13 +02:00
|
|
|
|
2020-04-12 20:03:24 +02:00
|
|
|
<!--
|
|
|
|
Zero-width space. Use this to allow line breaks at desirable places in
|
|
|
|
table cells, examples, etc. without causing an unwanted space when the
|
|
|
|
break is not needed in a wider output rendering.
|
|
|
|
-->
|
|
|
|
<!ENTITY zwsp "​">
|
|
|
|
|
1998-03-01 09:16:16 +01:00
|
|
|
]>
|
2000-05-02 22:02:03 +02:00
|
|
|
|
2003-03-25 17:15:44 +01:00
|
|
|
<book id="postgres">
|
2000-11-24 18:44:22 +01:00
|
|
|
<title>PostgreSQL &version; Documentation</title>
|
|
|
|
|
2003-03-25 17:15:44 +01:00
|
|
|
<bookinfo>
|
2000-11-24 18:44:22 +01:00
|
|
|
<corpauthor>The PostgreSQL Global Development Group</corpauthor>
|
2009-08-05 00:04:37 +02:00
|
|
|
<productname>PostgreSQL</productname>
|
|
|
|
<productnumber>&version;</productnumber>
|
2001-02-03 20:03:27 +01:00
|
|
|
&legal;
|
2003-03-25 17:15:44 +01:00
|
|
|
</bookinfo>
|
|
|
|
|
2003-09-09 01:02:28 +02:00
|
|
|
&intro;
|
2003-03-25 17:15:44 +01:00
|
|
|
|
|
|
|
<part id="tutorial">
|
|
|
|
<title>Tutorial</title>
|
|
|
|
|
|
|
|
<partintro>
|
|
|
|
<para>
|
2003-09-13 00:17:24 +02:00
|
|
|
Welcome to the <productname>PostgreSQL</productname> Tutorial. The
|
2023-11-08 22:48:43 +01:00
|
|
|
tutorial is intended to give an introduction
|
2003-03-25 17:15:44 +01:00
|
|
|
to <productname>PostgreSQL</productname>, relational database
|
2023-11-10 11:56:52 +01:00
|
|
|
concepts, and the SQL language. We assume some general knowledge about
|
2023-11-08 22:48:43 +01:00
|
|
|
how to use computers and no particular Unix or programming experience is
|
2023-11-10 11:56:52 +01:00
|
|
|
required. This tutorial is intended to provide hands-on experience with
|
|
|
|
important aspects of the <productname>PostgreSQL</productname> system.
|
2023-11-08 22:48:43 +01:00
|
|
|
It makes no attempt to be a comprehensive treatment of the topics it covers.
|
2003-03-25 17:15:44 +01:00
|
|
|
</para>
|
|
|
|
|
|
|
|
<para>
|
2023-11-08 22:48:43 +01:00
|
|
|
After you have successfully completed this tutorial you will want to
|
|
|
|
read the <xref linkend="sql"/> section to gain a better understanding
|
2017-11-23 15:39:47 +01:00
|
|
|
of the SQL language, or <xref linkend="client-interfaces"/> for
|
2023-11-08 22:48:43 +01:00
|
|
|
information about developing applications with
|
|
|
|
<productname>PostgreSQL</productname>. Those who provision and
|
|
|
|
manage their own PostgreSQL installation should also read <xref linkend="admin"/>.
|
2003-03-25 17:15:44 +01:00
|
|
|
</para>
|
|
|
|
</partintro>
|
|
|
|
|
|
|
|
&start;
|
|
|
|
&query;
|
|
|
|
&advanced;
|
|
|
|
|
|
|
|
</part>
|
|
|
|
|
|
|
|
<part id="sql">
|
|
|
|
<title>The SQL Language</title>
|
|
|
|
|
|
|
|
<partintro>
|
|
|
|
<para>
|
|
|
|
This part describes the use of the <acronym>SQL</acronym> language
|
|
|
|
in <productname>PostgreSQL</productname>. We start with
|
|
|
|
describing the general syntax of <acronym>SQL</acronym>, then
|
2023-11-10 11:56:52 +01:00
|
|
|
how to create tables, how to populate the database, and how to
|
|
|
|
query it. The middle part lists the available data types and
|
2023-11-08 22:48:43 +01:00
|
|
|
functions for use in <acronym>SQL</acronym> commands. Lastly,
|
|
|
|
we address several aspects of importance for tuning a database.
|
2003-03-25 17:15:44 +01:00
|
|
|
</para>
|
|
|
|
|
|
|
|
<para>
|
2023-11-08 22:48:43 +01:00
|
|
|
The information is arranged so that a novice user can
|
|
|
|
follow it from start to end and gain a full understanding of the topics
|
2003-03-25 17:15:44 +01:00
|
|
|
without having to refer forward too many times. The chapters are
|
|
|
|
intended to be self-contained, so that advanced users can read the
|
2023-11-10 11:56:52 +01:00
|
|
|
chapters individually as they choose. The information is presented
|
|
|
|
in narrative form with topical units. Readers looking for a complete
|
|
|
|
description of a particular command are encouraged to review
|
2023-11-08 22:48:43 +01:00
|
|
|
the <xref linkend="reference"/>.
|
2003-03-25 17:15:44 +01:00
|
|
|
</para>
|
|
|
|
|
|
|
|
<para>
|
2023-11-08 22:48:43 +01:00
|
|
|
Readers should know how to connect to a
|
2023-11-10 11:56:52 +01:00
|
|
|
<productname>PostgreSQL</productname> database and issue
|
2003-03-25 17:15:44 +01:00
|
|
|
<acronym>SQL</acronym> commands. Readers that are unfamiliar with
|
2017-11-23 15:39:47 +01:00
|
|
|
these issues are encouraged to read <xref linkend="tutorial"/>
|
2003-03-25 17:15:44 +01:00
|
|
|
first. <acronym>SQL</acronym> commands are typically entered
|
2017-10-09 03:44:17 +02:00
|
|
|
using the <productname>PostgreSQL</productname> interactive terminal
|
2003-03-25 17:15:44 +01:00
|
|
|
<application>psql</application>, but other programs that have
|
|
|
|
similar functionality can be used as well.
|
|
|
|
</para>
|
|
|
|
</partintro>
|
|
|
|
|
|
|
|
&syntax;
|
|
|
|
&ddl;
|
|
|
|
&dml;
|
|
|
|
&queries;
|
|
|
|
&datatype;
|
|
|
|
&func;
|
|
|
|
&typeconv;
|
|
|
|
&indices;
|
2007-08-21 23:08:47 +02:00
|
|
|
&textsearch;
|
2003-03-25 17:15:44 +01:00
|
|
|
&mvcc;
|
|
|
|
&perform;
|
2016-09-21 14:37:02 +02:00
|
|
|
∥
|
2003-03-25 17:15:44 +01:00
|
|
|
|
|
|
|
</part>
|
|
|
|
|
|
|
|
<part id="admin">
|
|
|
|
<title>Server Administration</title>
|
|
|
|
|
|
|
|
<partintro>
|
|
|
|
<para>
|
|
|
|
This part covers topics that are of interest to a
|
2023-11-08 22:48:43 +01:00
|
|
|
<productname>PostgreSQL</productname> administrator. This includes
|
2023-11-10 11:56:52 +01:00
|
|
|
installation, configuration of the server, management of users
|
|
|
|
and databases, and maintenance tasks. Anyone running
|
2023-11-08 22:48:43 +01:00
|
|
|
<productname>PostgreSQL</productname> server, even for
|
2003-03-25 17:15:44 +01:00
|
|
|
personal use, but especially in production, should be familiar
|
2023-11-08 22:48:43 +01:00
|
|
|
with these topics.
|
2003-03-25 17:15:44 +01:00
|
|
|
</para>
|
|
|
|
|
|
|
|
<para>
|
2023-11-10 11:56:52 +01:00
|
|
|
The information attempts to be in the order in which
|
|
|
|
a new user should read it. The chapters are self-contained and
|
|
|
|
can be read individually as desired. The information is presented
|
|
|
|
in a narrative form in topical units. Readers looking for a complete
|
|
|
|
description of a command are encouraged to review the
|
2023-11-08 22:48:43 +01:00
|
|
|
<xref linkend="reference"/>.
|
2003-03-25 17:15:44 +01:00
|
|
|
</para>
|
|
|
|
|
|
|
|
<para>
|
2009-04-27 18:27:36 +02:00
|
|
|
The first few chapters are written so they can be understood
|
|
|
|
without prerequisite knowledge, so new users who need to set
|
2023-11-10 11:56:52 +01:00
|
|
|
up their own server can begin their exploration. The rest of this
|
2023-11-08 22:48:43 +01:00
|
|
|
part is about tuning and management; that material
|
2004-12-24 20:12:37 +01:00
|
|
|
assumes that the reader is familiar with the general use of
|
2017-10-09 03:44:17 +02:00
|
|
|
the <productname>PostgreSQL</productname> database system. Readers are
|
2023-11-08 22:48:43 +01:00
|
|
|
encouraged review the <xref linkend="tutorial"/> and <xref
|
|
|
|
linkend="sql"/> parts for additional information.
|
2003-03-25 17:15:44 +01:00
|
|
|
</para>
|
|
|
|
</partintro>
|
|
|
|
|
2020-10-06 14:15:32 +02:00
|
|
|
&installbin;
|
2003-03-25 17:15:44 +01:00
|
|
|
&installation;
|
|
|
|
&runtime;
|
2005-09-13 00:11:38 +02:00
|
|
|
&config;
|
2008-05-07 18:36:43 +02:00
|
|
|
&client-auth;
|
2003-03-25 17:15:44 +01:00
|
|
|
&user-manag;
|
|
|
|
&manage-ag;
|
|
|
|
&charset;
|
|
|
|
&maintenance;
|
|
|
|
&backup;
|
2006-11-17 17:38:44 +01:00
|
|
|
&high-availability;
|
2003-03-25 17:15:44 +01:00
|
|
|
&monitoring;
|
|
|
|
&wal;
|
2017-01-19 18:00:00 +01:00
|
|
|
&logical-replication;
|
2018-03-28 23:22:42 +02:00
|
|
|
&jit;
|
2003-03-25 17:15:44 +01:00
|
|
|
®ress;
|
|
|
|
|
|
|
|
</part>
|
|
|
|
|
|
|
|
<part id="client-interfaces">
|
|
|
|
<title>Client Interfaces</title>
|
|
|
|
|
|
|
|
<partintro>
|
|
|
|
<para>
|
|
|
|
This part describes the client programming interfaces distributed
|
2017-10-09 03:44:17 +02:00
|
|
|
with <productname>PostgreSQL</productname>. Each of these chapters can be
|
2023-11-08 22:48:43 +01:00
|
|
|
read independently. There are many external programming
|
2023-11-10 11:56:52 +01:00
|
|
|
interfaces for client programs that are distributed separately. They
|
2017-11-23 15:39:47 +01:00
|
|
|
contain their own documentation (<xref linkend="external-projects"/>
|
2004-12-30 00:36:47 +01:00
|
|
|
lists some of the more popular ones). Readers of this part should be
|
2023-11-08 22:48:43 +01:00
|
|
|
familiar with using <acronym>SQL</acronym> to manipulate
|
2017-11-23 15:39:47 +01:00
|
|
|
and query the database (see <xref linkend="sql"/>) and of course
|
2023-11-08 22:48:43 +01:00
|
|
|
with the programming language of their choice.
|
2003-03-25 17:15:44 +01:00
|
|
|
</para>
|
|
|
|
</partintro>
|
|
|
|
|
|
|
|
&libpq;
|
|
|
|
&lobj;
|
|
|
|
&ecpg;
|
2003-05-18 22:55:57 +02:00
|
|
|
&infoschema;
|
2003-03-25 17:15:44 +01:00
|
|
|
|
|
|
|
</part>
|
|
|
|
|
|
|
|
<part id="server-programming">
|
|
|
|
<title>Server Programming</title>
|
|
|
|
|
|
|
|
<partintro>
|
|
|
|
<para>
|
|
|
|
This part is about extending the server functionality with
|
|
|
|
user-defined functions, data types, triggers, etc. These are
|
2023-11-08 22:48:43 +01:00
|
|
|
advanced topics which should be approached only after all
|
2017-10-09 03:44:17 +02:00
|
|
|
the other user documentation about <productname>PostgreSQL</productname> has
|
2004-12-30 04:13:56 +01:00
|
|
|
been understood. Later chapters in this part describe the server-side
|
2003-03-25 17:15:44 +01:00
|
|
|
programming languages available in the
|
|
|
|
<productname>PostgreSQL</productname> distribution as well as
|
2023-11-08 22:48:43 +01:00
|
|
|
general issues concerning server-side programming. It
|
2004-12-30 04:13:56 +01:00
|
|
|
is essential to read at least the earlier sections of <xref
|
2017-11-23 15:39:47 +01:00
|
|
|
linkend="extend"/> (covering functions) before diving into the
|
2023-11-08 22:48:43 +01:00
|
|
|
material about server-side programming.
|
2003-03-25 17:15:44 +01:00
|
|
|
</para>
|
|
|
|
</partintro>
|
|
|
|
|
|
|
|
&extend;
|
|
|
|
&trigger;
|
2012-07-18 16:16:16 +02:00
|
|
|
&event-trigger;
|
2004-12-30 04:13:56 +01:00
|
|
|
&rules;
|
2003-03-25 17:15:44 +01:00
|
|
|
|
|
|
|
&xplang;
|
|
|
|
&plsql;
|
|
|
|
&pltcl;
|
|
|
|
&plperl;
|
|
|
|
&plpython;
|
|
|
|
|
2003-10-23 00:28:10 +02:00
|
|
|
&spi;
|
Background worker processes
Background workers are postmaster subprocesses that run arbitrary
user-specified code. They can request shared memory access as well as
backend database connections; or they can just use plain libpq frontend
database connections.
Modules listed in shared_preload_libraries can register background
workers in their _PG_init() function; this is early enough that it's not
necessary to provide an extra GUC option, because the necessary extra
resources can be allocated early on. Modules can install more than one
bgworker, if necessary.
Care is taken that these extra processes do not interfere with other
postmaster tasks: only one such process is started on each ServerLoop
iteration. This means a large number of them could be waiting to be
started up and postmaster is still able to quickly service external
connection requests. Also, shutdown sequence should not be impacted by
a worker process that's reasonably well behaved (i.e. promptly responds
to termination signals.)
The current implementation lets worker processes specify their start
time, i.e. at what point in the server startup process they are to be
started: right after postmaster start (in which case they mustn't ask
for shared memory access), when consistent state has been reached
(useful during recovery in a HOT standby server), or when recovery has
terminated (i.e. when normal backends are allowed).
In case of a bgworker crash, actions to take depend on registration
data: if shared memory was requested, then all other connections are
taken down (as well as other bgworkers), just like it were a regular
backend crashing. The bgworker itself is restarted, too, within a
configurable timeframe (which can be configured to be never).
More features to add to this framework can be imagined without much
effort, and have been discussed, but this seems good enough as a useful
unit already.
An elementary sample module is supplied.
Author: Álvaro Herrera
This patch is loosely based on prior patches submitted by KaiGai Kohei,
and unsubmitted code by Simon Riggs.
Reviewed by: KaiGai Kohei, Markus Wanner, Andres Freund,
Heikki Linnakangas, Simon Riggs, Amit Kapila
2012-12-06 18:57:52 +01:00
|
|
|
&bgworker;
|
2014-03-18 18:20:01 +01:00
|
|
|
&logicaldecoding;
|
Introduce replication progress tracking infrastructure.
When implementing a replication solution ontop of logical decoding, two
related problems exist:
* How to safely keep track of replication progress
* How to change replication behavior, based on the origin of a row;
e.g. to avoid loops in bi-directional replication setups
The solution to these problems, as implemented here, consist out of
three parts:
1) 'replication origins', which identify nodes in a replication setup.
2) 'replication progress tracking', which remembers, for each
replication origin, how far replay has progressed in a efficient and
crash safe manner.
3) The ability to filter out changes performed on the behest of a
replication origin during logical decoding; this allows complex
replication topologies. E.g. by filtering all replayed changes out.
Most of this could also be implemented in "userspace", e.g. by inserting
additional rows contain origin information, but that ends up being much
less efficient and more complicated. We don't want to require various
replication solutions to reimplement logic for this independently. The
infrastructure is intended to be generic enough to be reusable.
This infrastructure also replaces the 'nodeid' infrastructure of commit
timestamps. It is intended to provide all the former capabilities,
except that there's only 2^16 different origins; but now they integrate
with logical decoding. Additionally more functionality is accessible via
SQL. Since the commit timestamp infrastructure has also been introduced
in 9.5 (commit 73c986add) changing the API is not a problem.
For now the number of origins for which the replication progress can be
tracked simultaneously is determined by the max_replication_slots
GUC. That GUC is not a perfect match to configure this, but there
doesn't seem to be sufficient reason to introduce a separate new one.
Bumps both catversion and wal page magic.
Author: Andres Freund, with contributions from Petr Jelinek and Craig Ringer
Reviewed-By: Heikki Linnakangas, Petr Jelinek, Robert Haas, Steve Singer
Discussion: 20150216002155.GI15326@awork2.anarazel.de,
20140923182422.GA15776@alap3.anarazel.de,
20131114172632.GE7522@alap2.anarazel.de
2015-04-29 19:30:53 +02:00
|
|
|
&replication-origins;
|
2022-02-03 19:57:27 +01:00
|
|
|
&archive-modules;
|
2003-10-23 00:28:10 +02:00
|
|
|
|
2003-03-25 17:15:44 +01:00
|
|
|
</part>
|
2000-11-24 18:44:22 +01:00
|
|
|
|
|
|
|
&reference;
|
2003-03-25 17:15:44 +01:00
|
|
|
|
|
|
|
<part id="internals">
|
|
|
|
<title>Internals</title>
|
|
|
|
|
|
|
|
<partintro>
|
|
|
|
<para>
|
Update documentation on may/can/might:
Standard English uses "may", "can", and "might" in different ways:
may - permission, "You may borrow my rake."
can - ability, "I can lift that log."
might - possibility, "It might rain today."
Unfortunately, in conversational English, their use is often mixed, as
in, "You may use this variable to do X", when in fact, "can" is a better
choice. Similarly, "It may crash" is better stated, "It might crash".
Also update two error messages mentioned in the documenation to match.
2007-01-31 21:56:20 +01:00
|
|
|
This part contains assorted information that might be of use to
|
2017-10-09 03:44:17 +02:00
|
|
|
<productname>PostgreSQL</productname> developers.
|
2003-03-25 17:15:44 +01:00
|
|
|
</para>
|
|
|
|
</partintro>
|
|
|
|
|
|
|
|
&arch-dev;
|
|
|
|
&catalogs;
|
2022-07-14 22:07:12 +02:00
|
|
|
&system-views;
|
2003-03-25 17:15:44 +01:00
|
|
|
&protocol;
|
2003-10-23 00:28:10 +02:00
|
|
|
&sources;
|
|
|
|
&nls;
|
|
|
|
&plhandler;
|
2011-02-20 06:17:18 +01:00
|
|
|
&fdwhandler;
|
Redesign tablesample method API, and do extensive code review.
The original implementation of TABLESAMPLE modeled the tablesample method
API on index access methods, which wasn't a good choice because, without
specialized DDL commands, there's no way to build an extension that can
implement a TSM. (Raw inserts into system catalogs are not an acceptable
thing to do, because we can't undo them during DROP EXTENSION, nor will
pg_upgrade behave sanely.) Instead adopt an API more like procedural
language handlers or foreign data wrappers, wherein the only SQL-level
support object needed is a single handler function identified by having
a special return type. This lets us get rid of the supporting catalog
altogether, so that no custom DDL support is needed for the feature.
Adjust the API so that it can support non-constant tablesample arguments
(the original coding assumed we could evaluate the argument expressions at
ExecInitSampleScan time, which is undesirable even if it weren't outright
unsafe), and discourage sampling methods from looking at invisible tuples.
Make sure that the BERNOULLI and SYSTEM methods are genuinely repeatable
within and across queries, as required by the SQL standard, and deal more
honestly with methods that can't support that requirement.
Make a full code-review pass over the tablesample additions, and fix
assorted bugs, omissions, infelicities, and cosmetic issues (such as
failure to put the added code stanzas in a consistent ordering).
Improve EXPLAIN's output of tablesample plans, too.
Back-patch to 9.5 so that we don't have to support the original API
in production.
2015-07-25 20:39:00 +02:00
|
|
|
&tablesample-method;
|
2015-03-13 12:55:39 +01:00
|
|
|
&custom-scan;
|
2003-03-25 17:15:44 +01:00
|
|
|
&geqo;
|
2019-04-04 02:37:00 +02:00
|
|
|
&tableam;
|
2005-02-13 04:04:15 +01:00
|
|
|
&indexam;
|
2024-04-15 21:46:19 +02:00
|
|
|
&wal-for-extensions;
|
2024-03-20 16:51:53 +01:00
|
|
|
&indextypes;
|
2005-01-10 01:04:43 +01:00
|
|
|
&storage;
|
2022-11-30 02:49:52 +01:00
|
|
|
&transaction;
|
2003-06-22 18:16:44 +02:00
|
|
|
&bki;
|
2005-02-27 01:49:28 +01:00
|
|
|
&planstats;
|
2020-04-14 19:41:32 +02:00
|
|
|
&backup-manifest;
|
2003-03-25 17:15:44 +01:00
|
|
|
|
|
|
|
</part>
|
|
|
|
|
|
|
|
<part id="appendixes">
|
|
|
|
<title>Appendixes</title>
|
|
|
|
|
2003-10-17 20:57:01 +02:00
|
|
|
&errcodes;
|
2003-03-25 17:15:44 +01:00
|
|
|
&datetime;
|
|
|
|
&keywords;
|
|
|
|
&features;
|
|
|
|
&release;
|
2007-11-14 03:36:43 +01:00
|
|
|
&contrib;
|
|
|
|
&external-projects;
|
2010-09-22 20:10:28 +02:00
|
|
|
&sourcerepo;
|
2003-03-25 17:15:44 +01:00
|
|
|
&docguide;
|
2018-11-29 13:58:28 +01:00
|
|
|
&limits;
|
2007-11-01 18:00:46 +01:00
|
|
|
&acronyms;
|
2020-04-03 18:23:20 +02:00
|
|
|
&glossary;
|
2020-03-29 11:13:27 +02:00
|
|
|
&color;
|
2021-03-31 22:23:25 +02:00
|
|
|
&obsolete;
|
2003-03-25 17:15:44 +01:00
|
|
|
|
|
|
|
</part>
|
|
|
|
|
|
|
|
&biblio;
|
2017-04-07 04:03:52 +02:00
|
|
|
<index id="bookindex"></index>
|
2003-03-25 17:15:44 +01:00
|
|
|
|
|
|
|
</book>
|