<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.40 2004/11/27 21:27:05 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.41 2004/12/13 18:05:07 petere Exp $ -->

<sect1 id="arrays">

<title>Arrays</title>

equal to one, an additional decoration that indicates the actual

array dimensions will precede the array structure decoration.

The decoration consists of square braces (<literal>[</> and <literal>]</>)

around each array dimension's lower and upper boundindicies, plus

a colon (<literal>:</>) delimiter characterinbetween. Delimiting the

around each array dimension's lower and upper boundindices, plus

a colon (<literal>:</>) delimiter characterin between. Delimiting the

array dimension decoration from the array structure decoration is a

single assignment operator (<literal>=</>). For example:

<programlisting>

</para>

<para>

In a similar fashion, an array with non-defaultindicies may be specified

In a similar fashion, an array with non-defaultindices may be specified

using the same literal syntax. For example:

<programlisting>

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2

<!--

$PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.52 2004/11/17 18:29:02 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.53 2004/12/13 18:05:07 petere Exp $

-->

<chapter id="backup">

<title>Backup and Restore</title>

get a usable backup. Half-way measures such as disallowing all

connections will <emphasis>not</emphasis> work

(mainly because <command>tar</command> and similar tools do not take an

atomic snapshot of the state of thefilesystem at a point in

atomic snapshot of the state of thefile system at a point in

time). Information about stopping the server can be found in

<xref linkend="postmaster-shutdown">. Needless to say that you

also need to shut down the server before restoring the data.

If your database is spread across multiple volumes (for example,

data files and WAL log on different disks) there may not be any way

to obtain exactly-simultaneous frozen snapshots of all the volumes.

Read yourfilesystem documentation very carefully before trusting

Read yourfile system documentation very carefully before trusting

to the consistent-snapshot technique in such situations.

</para>

database can be restored to consistency by <quote>replaying</> the

log entries made since the last checkpoint. However, the existence

of the log makes it possible to use a third strategy for backing up

databases: we can combine afilesystem-level backup with backup of

databases: we can combine afile-system-level backup with backup of

the WAL files. If recovery is needed, we restore the backup and

then replay from the backed-up WAL files to bring the backup up to

current time. This approach is more complex to administer than

We do not need a perfectly consistent backup as the starting point.

Any internal inconsistency in the backup will be corrected by log

replay (this is not significantly different from what happens during

crash recovery). So we don't needfilesystem snapshot capability,

crash recovery). So we don't needfile system snapshot capability,

just <application>tar</> or a similar archiving tool.

</para>

</listitem>

</para>

<para>

As with the plainfilesystem-backup technique, this method can only

As with the plainfile-system-backup technique, this method can only

support restoration of an entire database cluster, not a subset.

Also, it requires a lot of archival storage: the base backup may be bulky,

and a busy system will generate many megabytes of WAL traffic that

<filename>postgresql.conf</>, <filename>pg_hba.conf</> and

<filename>pg_ident.conf</>) after the initial base backup.

You may wish to keep the configuration files in a location that will

be backed up by your regularfilesystem backup procedures.

be backed up by your regularfile system backup procedures.

</para>

</sect2>

</listitem>

<listitem>

<para>

Perform the backup, using any convenientfilesystem-backup tool

Perform the backup, using any convenientfile-system-backup tool

such as <application>tar</> or <application>cpio</>. It is neither

necessary nor desirable to stop normal operation of the database

while you do this.

It is important that the command return nonzero exit status on failure.

The command <emphasis>will</> be asked for log files that are not present

in the archive; it must return nonzero when so asked. This is not an

error condition. Be aware also that thebasename of the <literal>%p</>

error condition. Be aware also that thebase name of the <literal>%p</>

path will be different from <literal>%f</>; do not expect them to be

interchangeable.

</para>

</term>

<listitem>

<para>

This parameter specifies thetimestamp up to which recovery

This parameter specifies thetime stamp up to which recovery

will proceed.

At most one of <varname>recovery_target_time</> and

<xref linkend="recovery-target-xid"> can be specified.

<itemizedlist>

<listitem>

<para>

Operations on non-btree indexes (hash, R-tree, and GiST indexes) are

Operations on non-B-tree indexes (hash, R-tree, and GiST indexes) are

not presently WAL-logged, so replay will not update these index types.

The recommended workaround is to manually <command>REINDEX</> each

such index after completing a recovery operation.

<para>

You will always need a SQL dump (<application>pg_dump</> dump) for

migrating to a new release.Filesystem-level backups (including

migrating to a new release.File-system-level backups (including

on-line backups) will not work, for the same reason that you can't

just do the update in-place: the file formats won't necessarily be

compatible across major releases.

<!--

Documentation of the system catalogs, directed toward PostgreSQL developers

$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.93 2004/12/01 19:00:27 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.94 2004/12/13 18:05:07 petere Exp $

-->

<chapter id="catalogs">

represents converting from one type to another and applying a length

coercion in a single step. When no such entry is available, coercion

to a type that uses a type modifier involves two steps, one to

convert betweendatatypes and a second to apply the modifier.

convert betweendata types and a second to apply the modifier.

</para>

</sect1>

<entry><type>oid</type></entry>

<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>

<entry>

The OID of the data type that corresponds to this table'srowtype,

The OID of the data type that corresponds to this table'srow type,

if any (zero for indexes, which have no <structname>pg_type</> entry)

</entry>

</row>

<row>

<entry><structfield>indclass</structfield></entry>

<entry><type>oidvector</type></entry>

<entry><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</entry>

<entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>

<entry>

For each column in the index key this contains the OID of

the operator class to use. See

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.33 2004/11/27 21:27:06 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.34 2004/12/13 18:05:08 petere Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

<literal>UNIQUE</> or a <literal>PRIMARY KEY</>, this would not stop the

<structname>capitals</> table from having rows with names duplicating

rows in <structname>cities</>. And those duplicate rows would by

default show up inSELECTs from <structname>cities</>. In fact, by

default show up inqueries from <structname>cities</>. In fact, by

default <structname>capitals</> would have no unique constraint at all,

and so could contain multiple rows with the same name.

You could add a unique constraint to <structname>capitals</>, but this

<!--

$PostgreSQL: pgsql/doc/src/sgml/external-projects.sgml,v 1.4 2004/12/03 06:30:40 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/external-projects.sgml,v 1.5 2004/12/13 18:05:08 petere Exp $

-->

<chapter id="external-projects">

popular interfaces are:

<variablelist>

<varlistentry>

<term>ODBC</term>

<listitem>

<para>

This is the most common interface for <application>Windows</>

applications.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>psqlODBC</term>

<listitem>

separate projects. <application>pgFoundry</> at <ulink

url="http://pgfoundry.org">http://pgfoundry.org</ulink> contains

even more projects. Other projects are not even hosted on these

servers and you will have to do aninternet search to find them.

servers and you will have to do anInternet search to find them.

</para>

</sect1>

<productname>PostgreSQL</> was designed from the start to be

extensible. For this reason, extensions loaded into the database can

function just like features that are packaged with the database. The

<filename>/contrib</> directory shipped with the source code

<filename>contrib/</> directory shipped with the source code

contains a large number of extensions. The <filename>README</> file

in that directory contains a summary. They include conversion tools,

full-text indexing, <acronym>XML</> tools, and additional data types

and indexing methods. Other extensions are developed independently,

like <application>PostGIS</>. Even <application>PostgreSQL</>

replication solutions are developed externally. For example,

<application>Slony</> is a popular master/slave replication solution

<application>Slony-I</> is a popular master/slave replication solution

that is developed independently from the main projects.

</para>

<para>

There are several administration tools available for

<productname>PostgreSQL</>. The most popular is

<application>pgadmin</>, and there are several commercially

<application>pgAdmin</>, and there are several commercially

available ones.

</para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.229 2004/12/03 18:34:31 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.230 2004/12/13 18:05:08 petere Exp $

PostgreSQL documentation

-->

<row>

<entry><literal><function>pg_get_serial_sequence</function>(<parameter>table_name</parameter>, <parameter>column_name</parameter>)</literal></entry>

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

<entry>get name of the sequence that a serial or bigserial column

<entry>get name of the sequence that a<type>serial</type> or<type>bigserial</type> column

uses</entry>

</row>

<row>

This function returns 1 if successful, 0 if not successful.

The process ID (<literal>pid</literal>) of an active backend can be found

from the <structfield>procpid</structfield> column in the

<structname>pg_stat_activity</structname> view, or by listing the postgres

<structname>pg_stat_activity</structname> view, or by listing the<command>postgres</command>

processes on the server with <application>ps</>.

</para>

<!-- $PostgreSQL: pgsql/doc/src/sgml/installation.sgml,v 1.211 2004/12/11 20:54:14 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/installation.sgml,v 1.212 2004/12/13 18:05:08 petere Exp $ -->

<chapter id="installation">

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

<para>

To build the <application>PL/Python</> server programming

language, you need a <productname>Python</productname>

installation with the header files and the distutils module.

The distutils module is included by default with

installation with the header files and the<application>distutils</application> module.

The<application>distutils</application> module is included by default with

<productname>Python</productname> 1.6 and later; users of

earlier versions of <productname>Python</productname> will need

to install it.

<!--

$PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.170 2004/12/02 15:32:52 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.171 2004/12/13 18:05:08 petere Exp $

-->

<chapter id="libpq">

</para>

<para>

At present, there is no way to determine the actualdatatype inferred for

At present, there is no way to determine the actualdata type inferred for

any parameters whose types are not specified in <parameter>paramTypes[]</>.

This is a <application>libpq</> omission that will probably be rectified

in a future release.

<!--

$PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.38 2004/11/15 06:32:14 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.39 2004/12/13 18:05:08 petere Exp $

-->

<chapter id="maintenance">

basis, consider doing it with <command>TRUNCATE</command> rather

than using <command>DELETE</command> followed by

<command>VACUUM</command>. <command>TRUNCATE</command> removes the

entire content of the table immediately, withoutrecquiring a

entire content of the table immediately, withoutrequiring a

subsequent <command>VACUUM</command> or <command>VACUUM

FULL</command> to reclaim the now-unused disk space.

</para>

just when you need them the most. Also, on <productname>linux</>,

<application>syslog</> will sync each message to disk, yielding poor

performance. (You can use a <literal>-</> at the start of the file name

in the <application>syslog</>config file to disable this behavior.)

in the <application>syslog</>configuration file to disable this behavior.)

</para>

<para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.37 2004/12/02 19:28:48 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.38 2004/12/13 18:05:08 petere Exp $

-->

<chapter id="managing-databases">

<note>

<para>

There is usually not much point in making more than one

tablespace per logicalfilesystem, since you cannot control the location

of individual files within a logicalfilesystem. However,

tablespace per logicalfile system, since you cannot control the location

of individual files within a logicalfile system. However,

<productname>PostgreSQL</> does not enforce any such limitation, and

indeed it is not directly aware of thefilesystem boundaries on your

indeed it is not directly aware of thefile system boundaries on your

system. It just stores files in the directories you tell it to use.

</para>

</note>