<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.107 2007/10/16 19:44:18 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.108 2007/11/28 15:42:30 petere Exp $ -->

<chapter id="backup">

<title>Backup and Restore</title>

(The path name is relative to the working directory of the server,

i.e., the cluster's data directory.)

Any <literal>%r</> is replaced by the name of the file containing the

last validrestartpoint. That is the earliest file that must be kept

last validrestart point. That is the earliest file that must be kept

to allow a restore to be restartable, so this information can be used

to truncate the archive to just the minimum required to support

restart of the current restore. <literal>%r</> would only be used in a

<para>

The size of the WAL archive can be minimized by using the <literal>%r</>

option of the <varname>restore_command</>. This option specifies the

last archivefilename that needs to be kept to allow the recovery to

last archivefile name that needs to be kept to allow the recovery to

restart correctly. This can be used to truncate the archive once

files are no longer required, if the archive is writable from the

standby server.

<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.157 2007/11/2805:01:24 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.158 2007/11/2815:42:30 petere Exp $ -->

<chapter Id="runtime-config">

<title>Server Configuration</title>

</indexterm>

<listitem>

<para>

Sets the realm to match Kerberos, GSSAPI and SSPIusernames against.

Sets the realm to match Kerberos, GSSAPI and SSPIuser names against.

See <xref linkend="kerberos-auth">, <xref linkend="gssapi-auth"> or

<xref linkend="sspi-auth"> for details. This parameter can only be

set at server start.

Including <literal>csvlog</> in the <varname>log_destination</> list

provides a convenient way to import log files into a database table.

This option emits log lines in comma-separated-value format,

with these columns: timestamp with milliseconds,username, database

name, sessionid, host:port number, processid, per-process line

number, command tag, session start time, virtual transactionid,

with these columns: timestamp with milliseconds,user name, database

name, sessionID, host:port number, processID, per-process line

number, command tag, session start time, virtual transactionID,

regular transaction id, error severity, SQL state code, error message.

Here is a sample table definition for storing CSV-format log output:

</para>

<programlisting>

CREATE TABLE postgres_log

PRIMARY KEY (session_id, process_line_num)

);

</programlisting>

</para>

<para>

To import a log file into this table, use the <command>COPY FROM</>

command:

</para>

<programlisting>

COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;

</programlisting>

</para>

<para>

There are a few things you need to do to simplify importing CSV log

<listitem>

<para>

Set <varname>log_rotation_size</varname> to 0 to disable

size-based log rotation, as it makes the logfilename difficult

size-based log rotation, as it makes the logfile name difficult

to predict.

</para>

</listitem>

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.76 2007/06/20 23:11:38 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.77 2007/11/28 15:42:31 petere Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

range tests for range partitioning, as illustrated in the preceding

examples. A good rule of thumb is that partitioning constraints should

contain only comparisons of the partitioning column(s) to constants

usingbtree-indexable operators.

usingB-tree-indexable operators.

</para>

</listitem>

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.413 2007/11/2805:13:41 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.414 2007/11/2815:42:31 petere Exp $ -->

<chapter id="functions">

<title>Functions and Operators</title>

<entry>

<acronym>ASCII</acronym> code of the first character of the

argument. For <acronym>UTF8</acronym> returns the Unicode code

point of the character. For othermulti-byte encodings. the

point of the character. For othermultibyte encodings. the

argument must be a strictly <acronym>ASCII</acronym> character.

</entry>

<entry><literal>ascii('x')</literal></entry>

<entry><type>text</type></entry>

<entry>

Character with the given code. For <acronym>UTF8</acronym> the

argument is treated as a Unicode code point. For othermulti-byte

argument is treated as a Unicode code point. For othermultibyte

encodings the argument must designate a strictly

<acronym>ASCII</acronym> character.

</entry>

<parameter>src_encoding</parameter>. The

<parameter>string</parameter> must be valid in this encoding.

Conversions can be defined by <command>CREATE CONVERSION</command>.

Also there are somepre-defined conversions. See <xref

Also there are somepredefined conversions. See <xref

linkend="conversion-names"> for available conversions.

</entry>

<entry><literal>convert('text_in_utf8', 'UTF8', 'LATIN1')</literal></entry>

<para>

Notice that except for the two-argument form of <function>enum_range</>,

these functions disregard the specific value passed to them; they care

only about its declareddatatype. EitherNULL or a specific value of

only about its declareddata type. Eithernull or a specific value of

the type can be passed, with the same result. It is more common to

apply these functions to a table column or function argument than to

a hardwired type name as suggested by the examples.

...

]]></screen>

If no table name isavaible, that is, when mapping a query or a

If no table name isavailable, that is, when mapping a query or a

cursor, the string <literal>table</literal> is used in the first

format, <literal>row</literal> in the second format.

</para>

<!-- $PostgreSQL: pgsql/doc/src/sgml/high-availability.sgml,v 1.28 2007/11/2810:10:14 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/high-availability.sgml,v 1.29 2007/11/2815:42:31 petere Exp $ -->

<chapter id="high-availability">

<title>High Availability, Load Balancing, and Replication</title>

<para>

Performance must be considered in any choice. There is usually a

tradeoff between functionality and

trade-off between functionality and

performance. For example, a full synchronous solution over a slow

network might cut performance by more than half, while an asynchronous

one might have a minimal performance impact.

</varlistentry>

<varlistentry>

<term>AsynchronousMulti-Master Replication</term>

<term>AsynchronousMultimaster Replication</term>

<listitem>

<para>

For servers that are not regularly connected, like laptops or

remote servers, keeping data consistent among servers is a

challenge. Using asynchronousmulti-master replication, each

challenge. Using asynchronousmultimaster replication, each

server works independently, and periodically communicates with

the other servers to identify conflicting transactions. The

conflicts can be resolved by users or conflict resolution rules.

</varlistentry>

<varlistentry>

<term>SynchronousMulti-Master Replication</term>

<term>SynchronousMultimaster Replication</term>

<listitem>

<para>

In synchronousmulti-master replication, each server can accept

In synchronousmultimaster replication, each server can accept

write requests, and modified data is transmitted from the

original server to every other server before each transaction

commits. Heavy write activity can cause excessive locking,

leading to poor performance. In fact, write performance is

often worse than that of a single server. Read requests can

be sent to any server. Some implementations use shared disk

to reduce the communication overhead. Synchronousmulti-master

to reduce the communication overhead. Synchronousmultimaster

replication is best for mostly read workloads, though its big

advantage is that any server can accept write requests &mdash;

there is no need to partition workloads between master and

<entry>Warm Standby Using PITR</entry>

<entry>Master-Slave Replication</entry>

<entry>Statement-Based Replication Middleware</entry>

<entry>AsynchronousMulti-Master Replication</entry>

<entry>SynchronousMulti-Master Replication</entry>

<entry>AsynchronousMultimaster Replication</entry>

<entry>SynchronousMultimaster Replication</entry>

</row>

</thead>

</varlistentry>

<varlistentry>

<term>Multi-Server Parallel Query Execution</term>

<term>Multiple-Server Parallel Query Execution</term>

<listitem>

<para>

<!-- $PostgreSQL: pgsql/doc/src/sgml/install-win32.sgml,v 1.41 2007/08/03 10:47:10 mha Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/install-win32.sgml,v 1.42 2007/11/28 15:42:31 petere Exp $ -->

<chapter id="install-win32">

<title>Installation on <productname>Windows</productname></title>

<term><productname>ActiveState Perl</productname></term>

<listitem><para>

ActiveState Perl is required to run the build generation scripts. MinGW

or Cygwinperl will not work. It must also be present in the PATH.

or CygwinPerl will not work. It must also be present in the PATH.

Binaries can be downloaded from

<ulink url="http://www.activestate.com"></>.

</para></listitem>

</userinput>

</screen>

To change the default build configuration to debug, put the following

in the buildenv.bat file:

in the<filename>buildenv.bat</filename> file:

<screen>

<userinput>

set CONFIG=Debug

<para>

To run the regression tests, make sure you have completed the build of all

required parts first. Also, make sure that the DLLs required to load all

parts of the system (such as theperl andpython DLLs for the procedural

languages) are present in the systemPATH. If they are not, set it through

parts of the system (such as thePerl andPython DLLs for the procedural

languages) are present in the systempath. If they are not, set it through

the <filename>buildenv.bat</filename> file. To run the tests, run one of

the following commands from the <filename>src\tools\msvc</filename>

directory:

</screen>

To change the schedule used (default is the parallel), append it to the

commandline like:

command line like:

<screen>

<userinput>

vcregress check serial

</varlistentry>

<varlistentry>

<term>DocBookDSSL 1.79</term>

<term>DocBookDSSSL 1.79</term>

<listitem><para>

Download from

<ulink url="http://sourceforge.net/project/downloading.php?groupname=docbook&amp;filename=docbook-dsssl-1.79.zip"></>

<!-- $PostgreSQL: pgsql/doc/src/sgml/installation.sgml,v 1.297 2007/11/05 17:43:20 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/installation.sgml,v 1.298 2007/11/28 15:42:31 petere Exp $ -->

<chapter id="installation">

<title><![%standalone-include[<productname>PostgreSQL</>]]>

<term><envar>TCLSH</envar></term>

<listitem>

<para>

Full path to the Tcl interpreter. Thiswil be used to

Full path to the Tcl interpreter. Thiswill be used to

determine the dependencies for building PL/Tcl.

</para>

</listitem>

<!-- $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.246 2007/09/26 08:45:50 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.247 2007/11/28 15:42:31 petere Exp $ -->

<chapter id="libpq">

<title><application>libpq</application> - C Library</title>

used. (Therefore, put more-specific entries first when you are using

wildcards.) If an entry needs to contain <literal>:</literal> or

<literal>\</literal>, escape this character with <literal>\</literal>.

A host name of <literal>localhost</> matches both TCP (hostname

A host name of <literal>localhost</> matches both TCP (host name

<literal>localhost</>) and Unix domain socket (<literal>pghost</> empty

or the default socket directory) connections coming from the local

machine.

<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.84 2007/10/07 01:16:42 alvherre Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.85 2007/11/28 15:42:31 petere Exp $ -->

<chapter id="maintenance">

<title>Routine Database Maintenance Tasks</title>

<command>UPDATE</> or <command>DELETE</> of a row does not

immediately remove the old version of the row.

This approach is necessary to gain the benefits of multiversion

concurrency control (see <xref linkend="mvcc">): the rowversion

concurrency control (see <xref linkend="mvcc">): the rowversions

must not be deleted while it is still potentially visible to other

transactions. But eventually, an outdated or deleted row version is no

longer of interest to any transaction. The space it occupies must be

<para>

Beginning in <productname>PostgreSQL</productname> 8.3, autovacuum has a

multi-process architecture:there is a daemon process, called the

multiprocess architecture: There is a daemon process, called the

<firstterm>autovacuum launcher</firstterm>, which is in charge of starting

an <firstterm>autovacuum worker</firstterm> process on each database every

<xref linkend="guc-autovacuum-naptime"> seconds. On each run, the worker

<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.54 2007/09/25 20:03:37 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.55 2007/11/28 15:42:31 petere Exp $ -->

<chapter id="monitoring">

<title>Monitoring Database Activity</title>

<entry>One row only, showing cluster-wide statistics from the

background writer: number of scheduled checkpoints, requested

checkpoints, buffers written by checkpoints and cleaning scans,

and the number of times thebgwriter stopped a cleaning scan

and the number of times thebackground writer stopped a cleaning scan

because it had written too many buffers. Also includes

statistics about the shared buffer pool, including buffers written

by backends (that is, not by the background writer) and total buffers

<entry><literal><function>pg_stat_get_bgwriter_timed_checkpoints</function>()</literal></entry>

<entry><type>bigint</type></entry>

<entry>

The number of times thebgwriter has started timed checkpoints

The number of times thebackground writer has started timed checkpoints

(because the <varname>checkpoint_timeout</varname> time has expired)

</entry>

</row>

<entry><literal><function>pg_stat_get_bgwriter_requested_checkpoints</function>()</literal></entry>

<entry><type>bigint</type></entry>

<entry>

The number of times thebgwriter has started checkpoints based

The number of times thebackground writer has started checkpoints based

on requests from backends because the <varname>checkpoint_segments</varname>

has been exceeded or because the <command>CHECKPOINT</command>

command has been issued

<entry><literal><function>pg_stat_get_bgwriter_buf_written_checkpoints</function>()</literal></entry>

<entry><type>bigint</type></entry>

<entry>

The number of buffers written by thebgwriter during checkpoints

The number of buffers written by thebackground writer during checkpoints

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_bgwriter_buf_written_clean</function>()</literal></entry>

<entry><type>bigint</type></entry>

<entry>

The number of buffers written by thebgwriter for routine cleaning of

The number of buffers written by thebackground writer for routine cleaning of

dirty pages

</entry>

</row>

<entry><literal><function>pg_stat_get_bgwriter_maxwritten_clean</function>()</literal></entry>

<entry><type>bigint</type></entry>

<entry>

The number of times thebgwriter has stopped its cleaning scan because

The number of times thebackground writer has stopped its cleaning scan because

it has written more buffers than specified in the

<varname>bgwriter_lru_maxpages</varname> parameter

</entry>

<para>

You should take care that the data types specified for the probe arguments

match thedatatypes of the variables used in the <literal>PG_TRACE</>

match thedata types of the variables used in the <literal>PG_TRACE</>

macro. This is not checked at compile time. You can check that your newly

added trace point is available by recompiling, then running the new binary,

and as root, executing a DTrace command such as: