<!--

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

$PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.54 2004/12/28 19:08:58 tgl Exp $

-->

<chapter id="backup">

<title>Backup and Restore</title>

<indexterm zone="backup"><primary>backup</></>

<para>

As everything that contains valuable data, <productname>PostgreSQL</>

Aswitheverything that contains valuable data, <productname>PostgreSQL</>

databases should be backed up regularly. While the procedure is

essentially simple, it is important to have a basic understanding of

the underlying techniques and assumptions.

client application (albeit a particularly clever one). This means

that you can do this backup procedure from any remote host that has

access to the database. But remember that <application>pg_dump</>

does not operate with special permissions. In particular,you must

does not operate with special permissions. In particular,it must

have read access to all tables that you want to back up, so in

practice you almost always have tobe a database superuser.

practice you almost always have torun it as a database superuser.

</para>

<para>

command, you must create it yourself from <literal>template0</> before executing

<application>psql</> (e.g., with <literal>createdb -T template0

<replaceable class="parameter">dbname</></literal>).

<application>psql</> supportssimilaroptions to <application>pg_dump</>

<application>psql</> supports options similar to <application>pg_dump</>

for controlling the database server location and the user name. See

its reference page for more information.

<xref linkend="app-psql">'s reference page for more information.

</para>

<para>

If the objects in the original database were owned by different

users, then the dump will instruct <application>psql</> to connect

as each affected user in turn and then create the relevant

objects. This way the original ownership is preserved. This also

means, however, that all these users must already exist, and

furthermore that you must be allowed to connect as each of them.

It might therefore be necessary to temporarily relax the client

authentication settings.

Not only must the target database already exist before starting to

run the restore, but so must all the users who own objects in the

dumped database or were granted permissions on the objects. If they

do not, then the restore will fail to recreate the objects with the

original ownership and/or permissions. (Sometimes this is what you want,

but usually it is not.)

</para>

<para>

Once restored, it is wise to run <xref linkend="sql-analyze"

endterm="sql-analyze-title"> on each database so the optimizer has

useful statistics. You can also run <command>vacuumdb -a -z</> to

useful statistics. An easy way to do this is to run

<command>vacuumdb -a -z</> to

<command>VACUUM ANALYZE</> all databases; this is equivalent to

running <command>VACUUM ANALYZE</command> manually.

</para>

</sect2>

<sect2 id="backup-dump-large">

<title>Large Databases</title>

<title>Handling large databases</title>

<para>

Since <productname>PostgreSQL</productname> allows tables larger

<formalpara>

<title>Use the custom dump format.</title>

<para>

If <productname>PostgreSQL</productname> was built on a system with the <application>zlib</> compression library

installed, the custom dump format will compress data as it writes it

sizes to using <command>gzip</command>, buthas the added advantage that the tables can be

custom dump format:

If <productname>PostgreSQL</productname> was built on a system with the

<application>zlib</> compression libraryinstalled, the custom dump

produce dump filesizessimilarto using <command>gzip</command>, butit

following command dumps a database using thecustom dump format:

<programlisting>

pg_dump -Fc <replaceable class="parameter">dbname</replaceable> > <replaceable class="parameter">filename</replaceable>

</programlisting>

A custom-format dump is not a script for <application>psql</>, but

instead must be restored with <application>pg_restore</>.

See the <xref linkend="app-pgdump"> and <xref

linkend="app-pgrestore"> reference pages for details.

</para>

object</primary><secondary>backup</secondary></indexterm> To dump

large objects you must use either the custom or the tar output

format, and use the <option>-b</> option in

<application>pg_dump</>. See the reference pages for details. The

<application>pg_dump</>. See the <xref linkend="app-pgdump"> reference

page for details. The

directory <filename>contrib/pg_dumplo</> of the

<productname>PostgreSQL</> source tree also contains a program

that can dump large objects.

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 your file system documentation very carefully before trusting

to the consistent-snapshot technique in such situations.

to the consistent-snapshot technique in such situations. The safest

approach is to shut down the database server for long enough to

establish all the frozen snapshots.

</para>

<para>

modifications made to the data in your <productname>PostgreSQL</> database

it will not restore changes made to configuration files (that is,

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

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

<filename>pg_ident.conf</>), since those are edited manually rather

than through SQL operations.

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

be backed up by your regular file system backup procedures.

be backed up by your regular file system backup procedures. See

<xref linkend="runtime-config-file-locations"> for how to relocate the

configuration files.

</para>

</sect2>

in the command.

</para>

<para>

It is important for the command to return a zero exit statusonly

if it succeeds. The command <emphasis>will</> be asked for file

It is important for the command to return a zero exit statusif and

onlyif it succeeds. The command <emphasis>will</> be asked for file

names that are not present in the archive; it must return nonzero

when so asked. Examples:

<programlisting>

that was current when the base backup was taken. If you want to recover

into some child timeline (that is, you want to return to some state that

was itself generated after a recovery attempt), you need to specify the

target timeline in <filename>recovery.conf</>. You cannot recover into

target timelineIDin <filename>recovery.conf</>. You cannot recover into

timelines that branched off earlier than the base backup.

</para>

</sect2>

<secondary>compatibility</secondary>

</indexterm>

<para>

This section discusses how to migrate your database data from one

<productname>PostgreSQL</> release to a newer one.

The software installation procedure <foreignphrase>per se</> is not the

subject of this section; those details are in <xref linkend="installation">.

</para>

<para>

As a general rule, the internal data storage format is subject to

change between major releases of <productname>PostgreSQL</> (where

storage formats. For example, releases 7.0.1, 7.1.2, and 7.2 are

not compatible, whereas 7.1.1 and 7.1.2 are. When you update

between compatible versions, you can simply replace the executables

and reuse the data area on disk. Otherwise you need to <quote>back

up</> your data and <quote>restore</> it on the new server, using

<application>pg_dump</>. There are checks in place that prevent you

from using a data area with an incompatible version of

<productname>PostgreSQL</productname>, so no harm can be done by

confusing these things. It is recommended that you use the

<application>pg_dump</> program from the newer version of

<productname>PostgreSQL</> to take advantage of any enhancements in

<application>pg_dump</> that may have been made. The precise

installation procedure is not the subject of this section; those

details are in <xref linkend="installation">.

and reuse the data directory on disk. Otherwise you need to back

up your data and restore it on the new server. This has to be done

using <application>pg_dump</>; file system level backup methods

obviously won't work. There are checks in place that prevent you

from using a data directory with an incompatible version of

<productname>PostgreSQL</productname>, so no great harm can be done by

trying to start the wrong server version on a data directory.

</para>

<para>

It is recommended that you use the <application>pg_dump</> and

<application>pg_dumpall</> programs from the newer version of

<productname>PostgreSQL</>, to take advantage of any enhancements

that may have been made in these programs. Current releases of the

dump programs can read data from any server version back to 7.0.

</para>

<para>

to transfer your data. Or use an intermediate file if you want.

Then you can shut down the old server and start the new server at

the port the old one was running at. You should make sure that the

database is not updated after you run <application>pg_dumpall</>,

olddatabase is not updated after you run <application>pg_dumpall</>,

otherwise you will obviously lose that data. See <xref

linkend="client-authentication"> for information on how to prohibit

access. In practice you probably want to test your client

applications on the new setup before switching over.

access.

</para>

<para>

In practice you probably want to test your client

applications on the new setup before switching over completely.

This is another reason for setting up concurrent installations

of old and new versions.

</para>

<para>

you of strategic places to perform these steps.

</para>

<para>

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

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.

</para>

<note>

<para>

When you <quote>move the old installation out of the way</quote>

itis no longer perfectly usable. Somepartsof theinstallation

containinformation about where the other parts are located. This

is usually not a big problem but if you plan on using two

itmay no longerbeperfectly usable. Some of theexecutable programs

containabsolute paths to various installed programs and data files.

Thisis usually not a big problem but if you plan on using two

installations in parallel for a while you should assign them

different installation directories at build time.

different installation directories at build time. (This problem

is rectified in <productname>PostgreSQL</> 8.0 and later, but you

need to be wary of moving older installations.)

</para>

</note>

</sect1>

<!--

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

$PostgreSQL: pgsql/doc/src/sgml/diskusage.sgml,v 1.13 2004/12/28 19:08:58 tgl Exp $

-->

<chapter id="diskusage">

which is used to store values too wide to fit comfortably in the main

table. There will be one index on the

<acronym>TOAST</> table, if present. There may also be indexes associated

with the base table.

with the base table. Each table and index is stored in a separate disk

file &mdash; possibly more than one file, if the file would exceed one

gigabyte. Naming conventions for these files are described in <xref

linkend="file-layout">.

</para>

<para>

not wait until the disk is completely full to take action.

</para>

</tip>

<para>

If your system supports per-user disk quotas, then the database

will naturally be subject to whatever quota is placed on the user

the server runs as. Exceeding the quota will have the same bad

effects as running out of space entirely.

</para>

</sect1>

</chapter>

<!--

$PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.26 2004/03/26 03:18:28 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.27 2004/12/28 19:08:58 tgl Exp $

-->

<chapter id="monitoring">

analyzing performance. Most of this chapter is devoted to describing

<productname>PostgreSQL</productname>'s statistics collector,

but one should not neglect regular Unix monitoring programs such as

<command>ps</> and <command>top</>. Also, once one has identified a

<command>ps</>, <command>top</>, <command>iostat</>, and <command>vmstat</>.

Also, once one has identified a

poorly-performing query, further investigation may be needed using

<productname>PostgreSQL</productname>'s <xref linkend="sql-explain"

endterm="sql-explain-title"> command.

<title>Viewing Collected Statistics</Title>

<para>

Several predefined views are available to show the results of

linkend="monitoring-stats-views-table">. Alternatively, one can

Several predefined views, listed in <xref

of statistics collection. Alternatively, one can

build custom views using the underlying statistics functions.

</para>

<para>

When using the statistics to monitor current activity, it is important

to realize that the information does not update instantaneously.

Each individual server process transmits new access counts to the collector

just beforewaiting for another client command; so a query still in

Each individual server process transmits newblock and rowaccess counts to

the collectorjust beforegoing idle; so a query or transaction still in

progress does not affect the displayed totals. Also, the collector itself

emits new totals at most once per <varname>pgstat_stat_interval</varname> milliseconds

(500 by default). So the displayed totals lag behind actual activity.

emits a new report at most once per <varname>pgstat_stat_interval</varname>

milliseconds (500 by default). So the displayed information lags behind

actual activity. Current-query information is reported to the collector

immediately, but is still subject to the

<varname>pgstat_stat_interval</varname> delay before it becomes visible.

</para>

<para>

Another important point is that when a server process is asked to display

any of these statistics, it first fetches the most recenttotals emitted by

any of these statistics, it first fetches the most recentreport emitted by

the collector process and then continues to use this snapshot for all

statistical views and functions until the end of its current transaction.

So the statistics will appear not to change as long as you continue the

database administrator to view information about the outstanding

locks in the lock manager. For example, this capability can be used

to:

<itemizedlist>

<listitem>

<para>