tests, it cannot guarantee that the database will not reach a state

in which the constraint condition is false (due to subsequent changes

of the other row(s) involved). This would cause a database dump and

reload to fail. Thereload could fail even when the complete

restore to fail. Therestore could fail even when the complete

database state is consistent with the constraint, due to rows not

being loaded in an order that will satisfy the constraint. If

possible, use <literal>UNIQUE</literal>, <literal>EXCLUDE</literal>,

If what you desire is a one-time check against other rows at row

insertion, rather than a continuously-maintained consistency

guarantee, a custom <link linkend="triggers">trigger</link> can be used

to implement that. (This approach avoids the dump/reload problem because

to implement that. (This approach avoids the dump/restore problem because

<application>pg_dump</application> does not reinstall triggers until after

reloading data, so that the check will not be enforced during a

dump/reload.)

restoring data, so that the check will not be enforced during a

dump/restore.)

</para>

</note>

function. <productname>PostgreSQL</productname> does not disallow

that, but it will not notice if there are rows in the table that now

violate the <literal>CHECK</literal> constraint. That would cause a

subsequent database dump andreload to fail.

subsequent database dump andrestore to fail.

The recommended way to handle such a change is to drop the constraint

(using <command>ALTER TABLE</command>), adjust the function definition,

and re-add the constraint, thereby rechecking it against all table rows.

<application>pg_dump</application>. But that behavior is undesirable for a

configuration table; any data changes made by the user need to be

included in dumps, or the extension will behave differently after a dump

andreload.

andrestore.

</para>

<indexterm>

<para>

Dump scripts generated by <application>pg_dump</application> automatically apply

several, but not all, of the above guidelines. Toreload a

several, but not all, of the above guidelines. Torestore a

<application>pg_dump</application> dump as quickly as possible, you need to

do a few extra things manually. (Note that these points apply while

<emphasis>restoring</emphasis> a dump, not while <emphasis>creating</emphasis> it.

attached to a function when <varname>check_function_bodies</varname> is on.

Therefore, checks whose results might be affected by GUC parameters

definitely should be skipped when <varname>check_function_bodies</varname> is

off, to avoid false failures whenreloading a dump.

off, to avoid false failures whenrestoring a dump.

</para>

<para>

around</quote> since the original creation of the enum type). The slowdown is

usually insignificant; but if it matters, optimal performance can be

regained by dropping and recreating the enum type, or by dumping and

reloading the database.

restoring the database.

</para>

</refsect1>

function. <productname>PostgreSQL</productname> does not disallow that,

but it will not notice if there are stored values of the domain type that

now violate the <literal>CHECK</literal> constraint. That would cause a

subsequent database dump andreload to fail. The recommended way to

subsequent database dump andrestore to fail. The recommended way to

handle such a change is to drop the constraint (using <command>ALTER

DOMAIN</command>), adjust the function definition, and re-add the

constraint, thereby rechecking it against stored data.

...</literal>). This will make restoration very slow; it is mainly

useful for making dumps that can be loaded into

non-<productname>PostgreSQL</productname> databases.

Any error duringreloading will cause only rows that are part of the

Any error duringrestoring will cause only rows that are part of the

problematic <command>INSERT</command> to be lost, rather than the

entire table contents.

</para>

This option is relevant only when creating a data-only dump.

It instructs <application>pg_dump</application> to include commands

to temporarily disable triggers on the target tables while

the data isreloaded. Use this if you have referential

the data isrestored. Use this if you have referential

integrity checks or other triggers on the tables that you

do not want to invoke during datareload.

do not want to invoke during datarestore.

</para>

<para>

than <command>COPY</command>). This will make restoration very slow;

it is mainly useful for making dumps that can be loaded into

non-<productname>PostgreSQL</productname> databases.

Any error duringreloading will cause only rows that are part of the

Any error duringrestoring will cause only rows that are part of the

problematic <command>INSERT</command> to be lost, rather than the

entire table contents. Note that the restore might fail altogether if

you have rearranged column order. The

target the root of the partitioning hierarchy that contains it, rather

than the partition itself. This causes the appropriate partition to

be re-determined for each row when the data is loaded. This may be

useful whenreloading data on a server where rows do not always fall

useful whenrestoring data on a server where rows do not always fall

into the same partitions as they did on the original server. That

could happen, for example, if the partitioning column is of type text

and the two systems have different definitions of the collation used

with this option, because <application>pg_restore</application> will

not know exactly which partition(s) a given archive data item will

load data into. This could result in inefficiency due to lock

conflicts between parallel jobs, or perhaps evenreload failures due

conflicts between parallel jobs, or perhaps evenrestore failures due

to foreign key constraints being set up before all the relevant data

is loaded.

</para>

Dump data as <command>INSERT</command> commands (rather than

<command>COPY</command>). Controls the maximum number of rows per

<command>INSERT</command> command. The value specified must be a

number greater than zero. Any error duringreloading will cause only

number greater than zero. Any error duringrestoring will cause only

rows that are part of the problematic <command>INSERT</command> to be

lost, rather than the entire table contents.

</para>

This option is relevant only when creating a data-only dump.

It instructs <application>pg_dumpall</application> to include commands

to temporarily disable triggers on the target tables while

the data isreloaded. Use this if you have referential

the data isrestored. Use this if you have referential

integrity checks or other triggers on the tables that you

do not want to invoke during datareload.

do not want to invoke during datarestore.

</para>

<para>

target the root of the partitioning hierarchy that contains it, rather

than the partition itself. This causes the appropriate partition to

be re-determined for each row when the data is loaded. This may be

useful whenreloading data on a server where rows do not always fall

useful whenrestoring data on a server where rows do not always fall

into the same partitions as they did on the original server. That

could happen, for example, if the partitioning column is of type text

and the two systems have different definitions of the collation used

Dump data as <command>INSERT</command> commands (rather than

<command>COPY</command>). Controls the maximum number of rows per

<command>INSERT</command> command. The value specified must be a

number greater than zero. Any error duringreloading will cause only

number greater than zero. Any error duringrestoring will cause only

rows that are part of the problematic <command>INSERT</command> to be

lost, rather than the entire table contents.

</para>

</para>

<para>

Toreload database(s) from this file, you can use:

Torestore database(s) from this file, you can use:

<screen>

<prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>

</screen>

After running this command, it should be possible to start the server,

but bear in mind that the database might contain inconsistent data due to

partially-committed transactions. You should immediately dump your data,

run <command>initdb</command>, andreload. Afterreload, check for

run <command>initdb</command>, andrestore. Afterrestore, check for

inconsistencies and repair as needed.

</para>

discussed below. If you are not able to determine correct values for all

these fields, <option>-f</option> can still be used, but

the recovered database must be treated with even more suspicion than

usual: an immediate dump andreload is imperative. <emphasis>Do not</emphasis>

usual: an immediate dump andrestore is imperative. <emphasis>Do not</emphasis>

execute any data-modifying operations in the database before you dump,

as any such action is likely to make the corruption worse.

</para>

This option is relevant only when performing a data-only restore.

It instructs <application>pg_restore</application> to execute commands

to temporarily disable triggers on the target tables while

the data isreloaded. Use this if you have referential

the data isrestored. Use this if you have referential

integrity checks or other triggers on the tables that you

do not want to invoke during datareload.

do not want to invoke during datarestore.

</para>

<para>

</para>

<para>

Toreload the dump into a new database called <literal>newdb</literal>:

Torestore the dump into a new database called <literal>newdb</literal>:

<screen>

<prompt>$</prompt> <userinput>createdb -T template0 newdb</userinput>

<para>

<application>pg_upgrade</application> (formerly called <application>pg_migrator</application>) allows data

stored in <productname>PostgreSQL</productname> data files to be upgraded to a later <productname>PostgreSQL</productname>

major version without the data dump/reload typically required for

major version without the data dump/restore typically required for

major version upgrades, e.g., from 9.5.8 to 9.6.4 or from 10.7 to 11.2.

It is not required for minor version upgrades, e.g., from 9.6.2 to 9.6.3

or from 10.1 to 10.2.

<para>

The <option>--jobs</option> option allows multiple CPU cores to be used

for copying/linking of files and to dump andreload database schemas

for copying/linking of files and to dump andrestore database schemas

in parallel; a good place to start is the maximum of the number of

CPU cores and tablespaces. This option can dramatically reduce the

time to upgrade a multi-database server running on a multiprocessor

For <emphasis>major</emphasis> releases of <productname>PostgreSQL</productname>, the

internal data storage format is subject to change, thus complicating

upgrades. The traditional method for moving data to a new major version

is to dump andreload the database, though this can be slow. A

is to dump andrestore the database, though this can be slow. A

faster method is <xref linkend="pgupgrade"/>. Replication methods are

also available, as discussed below.

</para>

<para>

One upgrade method is to dump data from one major version of

<productname>PostgreSQL</productname> andreload it in another &mdash; to do

<productname>PostgreSQL</productname> andrestore it in another &mdash; to do

this, you must use a <emphasis>logical</emphasis> backup tool like

<application>pg_dumpall</application>; file system

level backup methods will not work. (There are checks in place that prevent

explicitly when creating <type>tsvector</type> values inside triggers,

so that the column's contents will not be affected by changes to

<varname>default_text_search_config</varname>. Failure to do this is likely to

lead to problems such as search results changing after a dump andreload.

lead to problems such as search results changing after a dump andrestore.

</para>

</sect2>