1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>pg_restore</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="app-pgrecvlogical.html" title="pg_recvlogical" /><link rel="next" href="app-pgverifybackup.html" title="pg_verifybackup" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center"><span class="application">pg_restore</span></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="app-pgrecvlogical.html" title="pg_recvlogical">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="reference-client.html" title="PostgreSQL Client Applications">Up</a></td><th width="60%" align="center">PostgreSQL Client Applications</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="app-pgverifybackup.html" title="pg_verifybackup">Next</a></td></tr></table><hr /></div><div class="refentry" id="APP-PGRESTORE"><div class="titlepage"></div><a id="id-1.9.4.19.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle"><span class="application">pg_restore</span></span></h2><p>pg_restore —
3 restore a <span class="productname">PostgreSQL</span> database from an
4 archive file created by <span class="application">pg_dump</span>
5 </p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><div class="cmdsynopsis"><p id="id-1.9.4.19.4.1"><code class="command">pg_restore</code> [<em class="replaceable"><code>connection-option</code></em>...] [<em class="replaceable"><code>option</code></em>...] [<em class="replaceable"><code>filename</code></em>]</p></div></div><div class="refsect1" id="APP-PGRESTORE-DESCRIPTION"><h2>Description</h2><p>
6 <span class="application">pg_restore</span> is a utility for restoring a
7 <span class="productname">PostgreSQL</span> database from an archive
8 created by <a class="xref" href="app-pgdump.html" title="pg_dump"><span class="refentrytitle"><span class="application">pg_dump</span></span></a> in one of the non-plain-text
9 formats. It will issue the commands necessary to reconstruct the
10 database to the state it was in at the time it was saved. The
11 archive files also allow <span class="application">pg_restore</span> to
12 be selective about what is restored, or even to reorder the items
13 prior to being restored. The archive files are designed to be
14 portable across architectures.
16 <span class="application">pg_restore</span> can operate in two modes.
17 If a database name is specified, <span class="application">pg_restore</span>
18 connects to that database and restores archive contents directly into
19 the database. Otherwise, a script containing the SQL
20 commands necessary to rebuild the database is created and written
21 to a file or standard output. This script output is equivalent to
22 the plain text output format of <span class="application">pg_dump</span>.
23 Some of the options controlling the output are therefore analogous to
24 <span class="application">pg_dump</span> options.
26 Obviously, <span class="application">pg_restore</span> cannot restore information
27 that is not present in the archive file. For instance, if the
28 archive was made using the <span class="quote">“<span class="quote">dump data as
29 <code class="command">INSERT</code> commands</span>”</span> option,
30 <span class="application">pg_restore</span> will not be able to load the data
31 using <code class="command">COPY</code> statements.
32 </p><div class="warning"><h3 class="title">Warning</h3><p>
33 Restoring a dump causes the destination to execute arbitrary code of the
34 source superusers' choice. Partial dumps and partial restores do not limit
35 that. If the source superusers are not trusted, the dumped SQL statements
36 must be inspected before restoring. Non-plain-text dumps can be inspected
37 by using <span class="application">pg_restore</span>'s <code class="option">--file</code>
38 option. Note that the client running the dump and restore need not trust
39 the source or destination superusers.
40 </p></div></div><div class="refsect1" id="APP-PGRESTORE-OPTIONS"><h2>Options</h2><p>
41 <span class="application">pg_restore</span> accepts the following command
44 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>filename</code></em></span></dt><dd><p>
45 Specifies the location of the archive file (or directory, for a
46 directory-format archive) to be restored.
47 If not specified, the standard input is used.
48 </p></dd><dt><span class="term"><code class="option">-a</code><br /></span><span class="term"><code class="option">--data-only</code></span></dt><dd><p>
49 Restore only the data, not the schema (data definitions) or statistics.
50 Table data, large objects, and sequence values are restored,
51 if present in the archive.
53 This option is similar to, but for historical reasons not identical
54 to, specifying <code class="option">--section=data</code>.
55 </p></dd><dt><span class="term"><code class="option">-c</code><br /></span><span class="term"><code class="option">--clean</code></span></dt><dd><p>
56 Before restoring database objects, issue commands
57 to <code class="command">DROP</code> all the objects that will be restored.
58 This option is useful for overwriting an existing database.
59 If any of the objects do not exist in the destination database,
60 ignorable error messages will be reported,
61 unless <code class="option">--if-exists</code> is also specified.
62 </p></dd><dt><span class="term"><code class="option">-C</code><br /></span><span class="term"><code class="option">--create</code></span></dt><dd><p>
63 Create the database before restoring into it.
64 If <code class="option">--clean</code> is also specified, drop and
65 recreate the target database before connecting to it.
67 With <code class="option">--create</code>, <span class="application">pg_restore</span>
68 also restores the database's comment if any, and any configuration
69 variable settings that are specific to this database, that is,
70 any <code class="command">ALTER DATABASE ... SET ...</code>
71 and <code class="command">ALTER ROLE ... IN DATABASE ... SET ...</code>
72 commands that mention this database.
73 Access privileges for the database itself are also restored,
74 unless <code class="option">--no-acl</code> is specified.
76 When this option is used, the database named with <code class="option">-d</code>
77 is used only to issue the initial <code class="command">DROP DATABASE</code> and
78 <code class="command">CREATE DATABASE</code> commands. All data is restored into the
79 database name that appears in the archive.
80 </p></dd><dt><span class="term"><code class="option">-d <em class="replaceable"><code>dbname</code></em></code><br /></span><span class="term"><code class="option">--dbname=<em class="replaceable"><code>dbname</code></em></code></span></dt><dd><p>
81 Connect to database <em class="replaceable"><code>dbname</code></em> and restore directly
82 into the database. The <em class="replaceable"><code>dbname</code></em> can
83 be a <a class="link" href="libpq-connect.html#LIBPQ-CONNSTRING" title="32.1.1. Connection Strings">connection string</a>.
84 If so, connection string parameters will override any conflicting
86 </p></dd><dt><span class="term"><code class="option">-e</code><br /></span><span class="term"><code class="option">--exit-on-error</code></span></dt><dd><p>
87 Exit if an error is encountered while sending SQL commands to
88 the database. The default is to continue and to display a count of
89 errors at the end of the restoration.
90 </p></dd><dt><span class="term"><code class="option">-f <em class="replaceable"><code>filename</code></em></code><br /></span><span class="term"><code class="option">--file=<em class="replaceable"><code>filename</code></em></code></span></dt><dd><p>
91 Specify output file for generated script, or for the listing
92 when used with <code class="option">-l</code>. Use <code class="literal">-</code>
93 for <span class="systemitem">stdout</span>.
94 </p></dd><dt><span class="term"><code class="option">-F <em class="replaceable"><code>format</code></em></code><br /></span><span class="term"><code class="option">--format=<em class="replaceable"><code>format</code></em></code></span></dt><dd><p>
95 Specify format of the archive. It is not necessary to specify
96 the format, since <span class="application">pg_restore</span> will
97 determine the format automatically. If specified, it can be
100 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">c</code><br /></span><span class="term"><code class="literal">custom</code></span></dt><dd><p>
101 The archive is in the custom format of
102 <span class="application">pg_dump</span>.
103 </p></dd><dt><span class="term"><code class="literal">d</code><br /></span><span class="term"><code class="literal">directory</code></span></dt><dd><p>
104 The archive is a directory archive.
105 </p></dd><dt><span class="term"><code class="literal">t</code><br /></span><span class="term"><code class="literal">tar</code></span></dt><dd><p>
106 The archive is a <code class="command">tar</code> archive.
107 </p></dd></dl></div></dd><dt><span class="term"><code class="option">-I <em class="replaceable"><code>index</code></em></code><br /></span><span class="term"><code class="option">--index=<em class="replaceable"><code>index</code></em></code></span></dt><dd><p>
108 Restore definition of named index only. Multiple indexes
109 may be specified with multiple <code class="option">-I</code> switches.
110 </p></dd><dt><span class="term"><code class="option">-j <em class="replaceable"><code>number-of-jobs</code></em></code><br /></span><span class="term"><code class="option">--jobs=<em class="replaceable"><code>number-of-jobs</code></em></code></span></dt><dd><p>
111 Run the most time-consuming steps
112 of <span class="application">pg_restore</span> — those that load data,
113 create indexes, or create constraints — concurrently, using up
114 to <em class="replaceable"><code>number-of-jobs</code></em>
115 concurrent sessions. This option can dramatically reduce the time
116 to restore a large database to a server running on a
117 multiprocessor machine. This option is ignored when emitting a script
118 rather than connecting directly to a database server.
120 Each job is one process or one thread, depending on the
121 operating system, and uses a separate connection to the
124 The optimal value for this option depends on the hardware
125 setup of the server, of the client, and of the network.
126 Factors include the number of CPU cores and the disk setup. A
127 good place to start is the number of CPU cores on the server,
128 but values larger than that can also lead to faster restore
129 times in many cases. Of course, values that are too high will
130 lead to decreased performance because of thrashing.
132 Only the custom and directory archive formats are supported
134 The input must be a regular file or directory (not, for example, a
135 pipe or standard input). Also, multiple
136 jobs cannot be used together with the
137 option <code class="option">--single-transaction</code>.
138 </p></dd><dt><span class="term"><code class="option">-l</code><br /></span><span class="term"><code class="option">--list</code></span></dt><dd><p>
139 List the table of contents of the archive. The output of this operation
140 can be used as input to the <code class="option">-L</code> option. Note that
141 if filtering switches such as <code class="option">-n</code> or <code class="option">-t</code> are
142 used with <code class="option">-l</code>, they will restrict the items listed.
143 </p></dd><dt><span class="term"><code class="option">-L <em class="replaceable"><code>list-file</code></em></code><br /></span><span class="term"><code class="option">--use-list=<em class="replaceable"><code>list-file</code></em></code></span></dt><dd><p>
144 Restore only those archive elements that are listed in <em class="replaceable"><code>list-file</code></em>, and restore them in the
145 order they appear in the file. Note that
146 if filtering switches such as <code class="option">-n</code> or <code class="option">-t</code> are
147 used with <code class="option">-L</code>, they will further restrict the items restored.
148 </p><p><em class="replaceable"><code>list-file</code></em> is normally created by
149 editing the output of a previous <code class="option">-l</code> operation.
150 Lines can be moved or removed, and can also
151 be commented out by placing a semicolon (<code class="literal">;</code>) at the
152 start of the line. See below for examples.
153 </p></dd><dt><span class="term"><code class="option">-n <em class="replaceable"><code>schema</code></em></code><br /></span><span class="term"><code class="option">--schema=<em class="replaceable"><code>schema</code></em></code></span></dt><dd><p>
154 Restore only objects that are in the named schema. Multiple schemas
155 may be specified with multiple <code class="option">-n</code> switches. This can be
156 combined with the <code class="option">-t</code> option to restore just a
158 </p></dd><dt><span class="term"><code class="option">-N <em class="replaceable"><code>schema</code></em></code><br /></span><span class="term"><code class="option">--exclude-schema=<em class="replaceable"><code>schema</code></em></code></span></dt><dd><p>
159 Do not restore objects that are in the named schema. Multiple schemas
160 to be excluded may be specified with multiple <code class="option">-N</code> switches.
162 When both <code class="option">-n</code> and <code class="option">-N</code> are given for the same
163 schema name, the <code class="option">-N</code> switch wins and the schema is excluded.
164 </p></dd><dt><span class="term"><code class="option">-O</code><br /></span><span class="term"><code class="option">--no-owner</code></span></dt><dd><p>
165 Do not output commands to set
166 ownership of objects to match the original database.
167 By default, <span class="application">pg_restore</span> issues
168 <code class="command">ALTER OWNER</code> or
169 <code class="command">SET SESSION AUTHORIZATION</code>
170 statements to set ownership of created schema elements.
171 These statements will fail unless the initial connection to the
172 database is made by a superuser
173 (or the same user that owns all of the objects in the script).
174 With <code class="option">-O</code>, any user name can be used for the
175 initial connection, and this user will own all the created objects.
176 </p></dd><dt><span class="term"><code class="option">-P <em class="replaceable"><code>function-name(argtype [, ...])</code></em></code><br /></span><span class="term"><code class="option">--function=<em class="replaceable"><code>function-name(argtype [, ...])</code></em></code></span></dt><dd><p>
177 Restore the named function only. Be careful to spell the function
178 name and arguments exactly as they appear in the dump file's table
179 of contents. Multiple functions may be specified with multiple
180 <code class="option">-P</code> switches.
181 </p></dd><dt><span class="term"><code class="option">-R</code><br /></span><span class="term"><code class="option">--no-reconnect</code></span></dt><dd><p>
182 This option is obsolete but still accepted for backwards
184 </p></dd><dt><span class="term"><code class="option">-s</code><br /></span><span class="term"><code class="option">--schema-only</code></span></dt><dd><p>
185 Restore only the schema (data definitions), not data,
186 to the extent that schema entries are present in the archive.
188 This option cannot be used with <code class="option">--data-only</code>
189 or <code class="option">--statistics-only</code>.
190 It is similar to, but for historical reasons not identical to,
192 <code class="option">--section=pre-data --section=post-data --no-statistics</code>.
194 (Do not confuse this with the <code class="option">--schema</code> option, which
195 uses the word <span class="quote">“<span class="quote">schema</span>”</span> in a different meaning.)
196 </p></dd><dt><span class="term"><code class="option">-S <em class="replaceable"><code>username</code></em></code><br /></span><span class="term"><code class="option">--superuser=<em class="replaceable"><code>username</code></em></code></span></dt><dd><p>
197 Specify the superuser user name to use when disabling triggers.
198 This is relevant only if <code class="option">--disable-triggers</code> is used.
199 </p></dd><dt><span class="term"><code class="option">-t <em class="replaceable"><code>table</code></em></code><br /></span><span class="term"><code class="option">--table=<em class="replaceable"><code>table</code></em></code></span></dt><dd><p>
200 Restore definition and/or data of only the named table.
201 For this purpose, <span class="quote">“<span class="quote">table</span>”</span> includes views, materialized views,
202 sequences, and foreign tables. Multiple tables
203 can be selected by writing multiple <code class="option">-t</code> switches.
204 This option can be combined with the <code class="option">-n</code> option to
205 specify table(s) in a particular schema.
206 </p><div class="note"><h3 class="title">Note</h3><p>
207 When <code class="option">-t</code> is specified, <span class="application">pg_restore</span>
208 makes no attempt to restore any other database objects that the
209 selected table(s) might depend upon. Therefore, there is no
210 guarantee that a specific-table restore into a clean database will
212 </p></div><div class="note"><h3 class="title">Note</h3><p>
213 This flag does not behave identically to the <code class="option">-t</code>
214 flag of <span class="application">pg_dump</span>. There is not currently
215 any provision for wild-card matching in <span class="application">pg_restore</span>,
216 nor can you include a schema name within its <code class="option">-t</code>.
217 And, while <span class="application">pg_dump</span>'s <code class="option">-t</code>
218 flag will also dump subsidiary objects (such as indexes) of the
220 <span class="application">pg_restore</span>'s <code class="option">-t</code>
221 flag does not include such subsidiary objects.
222 </p></div><div class="note"><h3 class="title">Note</h3><p>
223 In versions prior to <span class="productname">PostgreSQL</span> 9.6, this flag
224 matched only tables, not any other type of relation.
225 </p></div></dd><dt><span class="term"><code class="option">-T <em class="replaceable"><code>trigger</code></em></code><br /></span><span class="term"><code class="option">--trigger=<em class="replaceable"><code>trigger</code></em></code></span></dt><dd><p>
226 Restore named trigger only. Multiple triggers may be specified with
227 multiple <code class="option">-T</code> switches.
228 </p></dd><dt><span class="term"><code class="option">-v</code><br /></span><span class="term"><code class="option">--verbose</code></span></dt><dd><p>
229 Specifies verbose mode. This will cause
230 <span class="application">pg_restore</span> to output detailed object
231 comments and start/stop times to the output file, and progress
232 messages to standard error.
233 Repeating the option causes additional debug-level messages
234 to appear on standard error.
235 </p></dd><dt><span class="term"><code class="option">-V</code><br /></span><span class="term"><code class="option">--version</code></span></dt><dd><p>
236 Print the <span class="application">pg_restore</span> version and exit.
237 </p></dd><dt><span class="term"><code class="option">-x</code><br /></span><span class="term"><code class="option">--no-privileges</code><br /></span><span class="term"><code class="option">--no-acl</code></span></dt><dd><p>
238 Prevent restoration of access privileges (grant/revoke commands).
239 </p></dd><dt><span class="term"><code class="option">-1</code><br /></span><span class="term"><code class="option">--single-transaction</code></span></dt><dd><p>
240 Execute the restore as a single transaction (that is, wrap the
241 emitted commands in <code class="command">BEGIN</code>/<code class="command">COMMIT</code>). This
242 ensures that either all the commands complete successfully, or no
243 changes are applied. This option implies
244 <code class="option">--exit-on-error</code>.
245 </p></dd><dt><span class="term"><code class="option">--disable-triggers</code></span></dt><dd><p>
246 This option is relevant only when performing a restore without schema.
247 It instructs <span class="application">pg_restore</span> to execute commands
248 to temporarily disable triggers on the target tables while
249 the data is restored. Use this if you have referential
250 integrity checks or other triggers on the tables that you
251 do not want to invoke during data restore.
253 Presently, the commands emitted for
254 <code class="option">--disable-triggers</code> must be done as superuser. So you
255 should also specify a superuser name with <code class="option">-S</code> or,
256 preferably, run <span class="application">pg_restore</span> as a
257 <span class="productname">PostgreSQL</span> superuser.
258 </p></dd><dt><span class="term"><code class="option">--enable-row-security</code></span></dt><dd><p>
259 This option is relevant only when restoring the contents of a table
260 which has row security. By default, <span class="application">pg_restore</span> will set
261 <a class="xref" href="runtime-config-client.html#GUC-ROW-SECURITY">row_security</a> to off, to ensure
262 that all data is restored in to the table. If the user does not have
263 sufficient privileges to bypass row security, then an error is thrown.
264 This parameter instructs <span class="application">pg_restore</span> to set
265 <a class="xref" href="runtime-config-client.html#GUC-ROW-SECURITY">row_security</a> to on instead, allowing the user to attempt to restore
266 the contents of the table with row security enabled. This might still
267 fail if the user does not have the right to insert the rows from the
270 Note that this option currently also requires the dump be in <code class="command">INSERT</code>
271 format, as <code class="command">COPY FROM</code> does not support row security.
272 </p></dd><dt><span class="term"><code class="option">--filter=<em class="replaceable"><code>filename</code></em></code></span></dt><dd><p>
273 Specify a filename from which to read patterns for objects excluded
274 or included from restore. The patterns are interpreted according to the
276 <code class="option">-n</code>/<code class="option">--schema</code> for including objects in schemas,
277 <code class="option">-N</code>/<code class="option">--exclude-schema</code> for excluding objects in schemas,
278 <code class="option">-P</code>/<code class="option">--function</code> for restoring named functions,
279 <code class="option">-I</code>/<code class="option">--index</code> for restoring named indexes,
280 <code class="option">-t</code>/<code class="option">--table</code> for restoring named tables
281 or <code class="option">-T</code>/<code class="option">--trigger</code> for restoring triggers.
282 To read from <code class="literal">STDIN</code>, use <code class="filename">-</code> as the
283 filename. The <code class="option">--filter</code> option can be specified in
284 conjunction with the above listed options for including or excluding
285 objects, and can also be specified more than once for multiple filter
288 The file lists one database pattern per row, with the following format:
289 </p><pre class="synopsis">
290 { include | exclude } { function | index | schema | table | trigger } <em class="replaceable"><code>PATTERN</code></em>
293 The first keyword specifies whether the objects matched by the pattern
294 are to be included or excluded. The second keyword specifies the type
295 of object to be filtered using the pattern:
296 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
297 <code class="literal">function</code>: functions, works like the
298 <code class="option">-P</code>/<code class="option">--function</code> option. This keyword
299 can only be used with the <code class="literal">include</code> keyword.
300 </p></li><li class="listitem"><p>
301 <code class="literal">index</code>: indexes, works like the
302 <code class="option">-I</code>/<code class="option">--indexes</code> option. This keyword
303 can only be used with the <code class="literal">include</code> keyword.
304 </p></li><li class="listitem"><p>
305 <code class="literal">schema</code>: schemas, works like the
306 <code class="option">-n</code>/<code class="option">--schema</code> and
307 <code class="option">-N</code>/<code class="option">--exclude-schema</code> options.
308 </p></li><li class="listitem"><p>
309 <code class="literal">table</code>: tables, works like the
310 <code class="option">-t</code>/<code class="option">--table</code> option. This keyword
311 can only be used with the <code class="literal">include</code> keyword.
312 </p></li><li class="listitem"><p>
313 <code class="literal">trigger</code>: triggers, works like the
314 <code class="option">-T</code>/<code class="option">--trigger</code> option. This keyword
315 can only be used with the <code class="literal">include</code> keyword.
316 </p></li></ul></div><p>
318 Lines starting with <code class="literal">#</code> are considered comments and
319 ignored. Comments can be placed after an object pattern row as well.
320 Blank lines are also ignored. See <a class="xref" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns">Patterns</a>
321 for how to perform quoting in patterns.
322 </p></dd><dt><span class="term"><code class="option">--if-exists</code></span></dt><dd><p>
323 Use <code class="literal">DROP ... IF EXISTS</code> commands to drop objects
324 in <code class="option">--clean</code> mode. This suppresses <span class="quote">“<span class="quote">does not
325 exist</span>”</span> errors that might otherwise be reported. This
326 option is not valid unless <code class="option">--clean</code> is also
328 </p></dd><dt><span class="term"><code class="option">--no-comments</code></span></dt><dd><p>
329 Do not output commands to restore comments, even if the archive
331 </p></dd><dt><span class="term"><code class="option">--no-data</code></span></dt><dd><p>
332 Do not output commands to restore data, even if the archive
334 </p></dd><dt><span class="term"><code class="option">--no-data-for-failed-tables</code></span></dt><dd><p>
335 By default, table data is restored even if the creation command
336 for the table failed (e.g., because it already exists).
337 With this option, data for such a table is skipped.
338 This behavior is useful if the target database already
339 contains the desired table contents. For example,
340 auxiliary tables for <span class="productname">PostgreSQL</span> extensions
341 such as <span class="productname">PostGIS</span> might already be loaded in
342 the target database; specifying this option prevents duplicate
343 or obsolete data from being loaded into them.
345 This option is effective only when restoring directly into a
346 database, not when producing SQL script output.
347 </p></dd><dt><span class="term"><code class="option">--no-policies</code></span></dt><dd><p>
348 Do not output commands to restore row security policies, even if
349 the archive contains them.
350 </p></dd><dt><span class="term"><code class="option">--no-publications</code></span></dt><dd><p>
351 Do not output commands to restore publications, even if the archive
353 </p></dd><dt><span class="term"><code class="option">--no-schema</code></span></dt><dd><p>
354 Do not output commands to restore schema (data definitions), even if
355 the archive contains them.
356 </p></dd><dt><span class="term"><code class="option">--no-security-labels</code></span></dt><dd><p>
357 Do not output commands to restore security labels,
358 even if the archive contains them.
359 </p></dd><dt><span class="term"><code class="option">--no-statistics</code></span></dt><dd><p>
360 Do not output commands to restore statistics, even if the archive
362 </p></dd><dt><span class="term"><code class="option">--no-subscriptions</code></span></dt><dd><p>
363 Do not output commands to restore subscriptions, even if the archive
365 </p></dd><dt><span class="term"><code class="option">--no-table-access-method</code></span></dt><dd><p>
366 Do not output commands to select table access methods.
367 With this option, all objects will be created with whichever
368 table access method is the default during restore.
369 </p></dd><dt><span class="term"><code class="option">--no-tablespaces</code></span></dt><dd><p>
370 Do not output commands to select tablespaces.
371 With this option, all objects will be created in whichever
372 tablespace is the default during restore.
373 </p></dd><dt><span class="term"><code class="option">--restrict-key=<em class="replaceable"><code>restrict_key</code></em></code></span></dt><dd><p>
374 Use the provided string as the <span class="application">psql</span>
375 <code class="command">\restrict</code> key in the dump output. This can only be
376 specified for SQL script output, i.e., when the <code class="option">--file</code>
377 option is used. If no restrict key is specified,
378 <span class="application">pg_restore</span> will generate a random one as
379 needed. Keys may contain only alphanumeric characters.
381 This option is primarily intended for testing purposes and other
382 scenarios that require repeatable output (e.g., comparing dump files).
383 It is not recommended for general use, as a malicious server with
384 advance knowledge of the key may be able to inject arbitrary code that
385 will be executed on the machine that runs
386 <span class="application">psql</span> with the dump output.
387 </p></dd><dt><span class="term"><code class="option">--section=<em class="replaceable"><code>sectionname</code></em></code></span></dt><dd><p>
388 Only restore the named section. The section name can be
389 <code class="option">pre-data</code>, <code class="option">data</code>, or <code class="option">post-data</code>.
390 This option can be specified more than once to select multiple
391 sections. The default is to restore all sections.
393 The data section contains actual table data as well as large-object
395 Post-data items consist of definitions of indexes, triggers, rules
396 and constraints other than validated check constraints.
397 Pre-data items consist of all other data definition items.
398 </p></dd><dt><span class="term"><code class="option">--statistics</code></span></dt><dd><p>
399 Output commands to restore statistics, if the archive contains them.
401 </p></dd><dt><span class="term"><code class="option">--statistics-only</code></span></dt><dd><p>
402 Restore only the statistics, not schema (data definitions) or data.
403 </p></dd><dt><span class="term"><code class="option">--strict-names</code></span></dt><dd><p>
404 Require that each schema
405 (<code class="option">-n</code>/<code class="option">--schema</code>) and table
406 (<code class="option">-t</code>/<code class="option">--table</code>) qualifier match at
407 least one schema/table in the file to be restored.
408 </p></dd><dt><span class="term"><code class="option">--transaction-size=<em class="replaceable"><code>N</code></em></code></span></dt><dd><p>
409 Execute the restore as a series of transactions, each processing
410 up to <em class="replaceable"><code>N</code></em> database
411 objects. This option implies <code class="option">--exit-on-error</code>.
413 <code class="option">--transaction-size</code> offers an intermediate choice
414 between the default behavior (one transaction per SQL command)
415 and <code class="option">-1</code>/<code class="option">--single-transaction</code>
416 (one transaction for all restored objects).
417 While <code class="option">--single-transaction</code> has the least
418 overhead, it may be impractical for large databases because the
419 transaction will take a lock on each restored object, possibly
420 exhausting the server's lock table space.
421 Using <code class="option">--transaction-size</code> with a size of a few
422 thousand objects offers nearly the same performance benefits while
423 capping the amount of lock table space needed.
424 </p></dd><dt><span class="term"><code class="option">--use-set-session-authorization</code></span></dt><dd><p>
425 Output SQL-standard <code class="command">SET SESSION AUTHORIZATION</code> commands
426 instead of <code class="command">ALTER OWNER</code> commands to determine object
427 ownership. This makes the dump more standards-compatible, but
428 depending on the history of the objects in the dump, might not restore
430 </p></dd><dt><span class="term"><code class="option">-?</code><br /></span><span class="term"><code class="option">--help</code></span></dt><dd><p>
431 Show help about <span class="application">pg_restore</span> command line
433 </p></dd></dl></div><p>
435 <span class="application">pg_restore</span> also accepts
436 the following command line arguments for connection parameters:
438 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-h <em class="replaceable"><code>host</code></em></code><br /></span><span class="term"><code class="option">--host=<em class="replaceable"><code>host</code></em></code></span></dt><dd><p>
439 Specifies the host name of the machine on which the server is
440 running. If the value begins with a slash, it is used as the
441 directory for the Unix domain socket. The default is taken
442 from the <code class="envar">PGHOST</code> environment variable, if set,
443 else a Unix domain socket connection is attempted.
444 </p></dd><dt><span class="term"><code class="option">-p <em class="replaceable"><code>port</code></em></code><br /></span><span class="term"><code class="option">--port=<em class="replaceable"><code>port</code></em></code></span></dt><dd><p>
445 Specifies the TCP port or local Unix domain socket file
446 extension on which the server is listening for connections.
447 Defaults to the <code class="envar">PGPORT</code> environment variable, if
448 set, or a compiled-in default.
449 </p></dd><dt><span class="term"><code class="option">-U <em class="replaceable"><code>username</code></em></code><br /></span><span class="term"><code class="option">--username=<em class="replaceable"><code>username</code></em></code></span></dt><dd><p>
450 User name to connect as.
451 </p></dd><dt><span class="term"><code class="option">-w</code><br /></span><span class="term"><code class="option">--no-password</code></span></dt><dd><p>
452 Never issue a password prompt. If the server requires
453 password authentication and a password is not available by
454 other means such as a <code class="filename">.pgpass</code> file, the
455 connection attempt will fail. This option can be useful in
456 batch jobs and scripts where no user is present to enter a
458 </p></dd><dt><span class="term"><code class="option">-W</code><br /></span><span class="term"><code class="option">--password</code></span></dt><dd><p>
459 Force <span class="application">pg_restore</span> to prompt for a
460 password before connecting to a database.
462 This option is never essential, since
463 <span class="application">pg_restore</span> will automatically prompt
464 for a password if the server demands password authentication.
465 However, <span class="application">pg_restore</span> will waste a
466 connection attempt finding out that the server wants a password.
467 In some cases it is worth typing <code class="option">-W</code> to avoid the extra
469 </p></dd><dt><span class="term"><code class="option">--role=<em class="replaceable"><code>rolename</code></em></code></span></dt><dd><p>
470 Specifies a role name to be used to perform the restore.
471 This option causes <span class="application">pg_restore</span> to issue a
472 <code class="command">SET ROLE</code> <em class="replaceable"><code>rolename</code></em>
473 command after connecting to the database. It is useful when the
474 authenticated user (specified by <code class="option">-U</code>) lacks privileges
475 needed by <span class="application">pg_restore</span>, but can switch to a role with
476 the required rights. Some installations have a policy against
477 logging in directly as a superuser, and use of this option allows
478 restores to be performed without violating the policy.
479 </p></dd></dl></div><p>
480 </p></div><div class="refsect1" id="id-1.9.4.19.7"><h2>Environment</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="envar">PGHOST</code><br /></span><span class="term"><code class="envar">PGOPTIONS</code><br /></span><span class="term"><code class="envar">PGPORT</code><br /></span><span class="term"><code class="envar">PGUSER</code></span></dt><dd><p>
481 Default connection parameters
482 </p></dd><dt><span class="term"><code class="envar">PG_COLOR</code></span></dt><dd><p>
483 Specifies whether to use color in diagnostic messages. Possible values
484 are <code class="literal">always</code>, <code class="literal">auto</code> and
485 <code class="literal">never</code>.
486 </p></dd></dl></div><p>
487 This utility, like most other <span class="productname">PostgreSQL</span> utilities,
488 also uses the environment variables supported by <span class="application">libpq</span>
489 (see <a class="xref" href="libpq-envars.html" title="32.15. Environment Variables">Section 32.15</a>). However, it does not read
490 <code class="envar">PGDATABASE</code> when a database name is not supplied.
491 </p></div><div class="refsect1" id="APP-PGRESTORE-DIAGNOSTICS"><h2>Diagnostics</h2><p>
492 When a direct database connection is specified using the
493 <code class="option">-d</code> option, <span class="application">pg_restore</span>
494 internally executes <acronym class="acronym">SQL</acronym> statements. If you have
495 problems running <span class="application">pg_restore</span>, make sure
496 you are able to select information from the database using, for
497 example, <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a>. Also, any default connection
498 settings and environment variables used by the
499 <span class="application">libpq</span> front-end library will apply.
500 </p></div><div class="refsect1" id="APP-PGRESTORE-NOTES"><h2>Notes</h2><p>
501 If your installation has any local additions to the
502 <code class="literal">template1</code> database, be careful to load the output of
503 <span class="application">pg_restore</span> into a truly empty database;
504 otherwise you are likely to get errors due to duplicate definitions
505 of the added objects. To make an empty database without any local
506 additions, copy from <code class="literal">template0</code> not <code class="literal">template1</code>, for example:
507 </p><pre class="programlisting">
508 CREATE DATABASE foo WITH TEMPLATE template0;
511 The limitations of <span class="application">pg_restore</span> are detailed below.
513 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
514 When restoring data to a pre-existing table and the option
515 <code class="option">--disable-triggers</code> is used,
516 <span class="application">pg_restore</span> emits commands
517 to disable triggers on user tables before inserting the data, then emits commands to
518 re-enable them after the data has been inserted. If the restore is stopped in the
519 middle, the system catalogs might be left in the wrong state.
520 </p></li><li class="listitem"><p><span class="application">pg_restore</span> cannot restore large objects
521 selectively; for instance, only those for a specific table. If
522 an archive contains large objects, then all large objects will be
523 restored, or none of them if they are excluded via <code class="option">-L</code>,
524 <code class="option">-t</code>, or other options.
525 </p></li></ul></div><p>
527 See also the <a class="xref" href="app-pgdump.html" title="pg_dump"><span class="refentrytitle"><span class="application">pg_dump</span></span></a> documentation for details on
528 limitations of <span class="application">pg_dump</span>.
530 By default, <code class="command">pg_restore</code> will restore optimizer statistics
531 if included in the dump file. If not all statistics were restored, it may
532 be useful to run <code class="command">ANALYZE</code> on each restored table so the
533 optimizer has useful statistics; see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-STATISTICS" title="24.1.3. Updating Planner Statistics">Section 24.1.3</a> and <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">Section 24.1.6</a> for more
535 </p></div><div class="refsect1" id="APP-PGRESTORE-EXAMPLES"><h2>Examples</h2><p>
536 Assume we have dumped a database called <code class="literal">mydb</code> into a
537 custom-format dump file:
539 </p><pre class="screen">
540 <code class="prompt">$</code> <strong class="userinput"><code>pg_dump -Fc mydb > db.dump</code></strong>
543 To drop the database and recreate it from the dump:
545 </p><pre class="screen">
546 <code class="prompt">$</code> <strong class="userinput"><code>dropdb mydb</code></strong>
547 <code class="prompt">$</code> <strong class="userinput"><code>pg_restore -C -d postgres db.dump</code></strong>
550 The database named in the <code class="option">-d</code> switch can be any database existing
551 in the cluster; <span class="application">pg_restore</span> only uses it to issue the
552 <code class="command">CREATE DATABASE</code> command for <code class="literal">mydb</code>. With
553 <code class="option">-C</code>, data is always restored into the database name that appears
556 To restore the dump into a new database called <code class="literal">newdb</code>:
558 </p><pre class="screen">
559 <code class="prompt">$</code> <strong class="userinput"><code>createdb -T template0 newdb</code></strong>
560 <code class="prompt">$</code> <strong class="userinput"><code>pg_restore -d newdb db.dump</code></strong>
563 Notice we don't use <code class="option">-C</code>, and instead connect directly to the
564 database to be restored into. Also note that we clone the new database
565 from <code class="literal">template0</code> not <code class="literal">template1</code>, to ensure it is
568 To reorder database items, it is first necessary to dump the table of
569 contents of the archive:
570 </p><pre class="screen">
571 <code class="prompt">$</code> <strong class="userinput"><code>pg_restore -l db.dump > db.list</code></strong>
573 The listing file consists of a header and one line for each item, e.g.:
574 </p><pre class="programlisting">
576 ; Archive created at Mon Sep 14 13:55:39 2009
580 ; Dump Version: 1.10-0
584 ; Dumped from database version: 8.3.5
585 ; Dumped by pg_dump version: 8.3.8
588 ; Selected TOC Entries:
590 3; 2615 2200 SCHEMA - public pasha
591 1861; 0 0 COMMENT - SCHEMA public pasha
592 1862; 0 0 ACL - public pasha
593 317; 1247 17715 TYPE public composite pasha
594 319; 1247 25899 DOMAIN public domain0 pasha
596 Semicolons start a comment, and the numbers at the start of lines refer to the
597 internal archive ID assigned to each item.
599 Lines in the file can be commented out, deleted, and reordered. For example:
600 </p><pre class="programlisting">
601 10; 145433 TABLE map_resolutions postgres
602 ;2; 145344 TABLE species postgres
603 ;4; 145359 TABLE nt_header postgres
604 6; 145402 TABLE species_records postgres
605 ;8; 145416 TABLE ss_old postgres
607 could be used as input to <span class="application">pg_restore</span> and would only restore
608 items 10 and 6, in that order:
609 </p><pre class="screen">
610 <code class="prompt">$</code> <strong class="userinput"><code>pg_restore -L db.list db.dump</code></strong>
611 </pre></div><div class="refsect1" id="id-1.9.4.19.11"><h2>See Also</h2><span class="simplelist"><a class="xref" href="app-pgdump.html" title="pg_dump"><span class="refentrytitle"><span class="application">pg_dump</span></span></a>, <a class="xref" href="app-pg-dumpall.html" title="pg_dumpall"><span class="refentrytitle"><span class="application">pg_dumpall</span></span></a>, <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="app-pgrecvlogical.html" title="pg_recvlogical">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="reference-client.html" title="PostgreSQL Client Applications">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="app-pgverifybackup.html" title="pg_verifybackup">Next</a></td></tr><tr><td width="40%" align="left" valign="top"><span class="application">pg_recvlogical</span> </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> <span class="application">pg_verifybackup</span></td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob