codekingpro/portable-devtools
115k
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 16.3 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.18.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 an4 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.18.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 a7 <span class="productname">PostgreSQL</span> database from an archive8 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-text9 formats. It will issue the commands necessary to reconstruct the10 database to the state it was in at the time it was saved. The11 archive files also allow <span class="application">pg_restore</span> to12 be selective about what is restored, or even to reorder the items13 prior to being restored. The archive files are designed to be14 portable across architectures.15 </p><p>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 into19 the database. Otherwise, a script containing the SQL20 commands necessary to rebuild the database is created and written21 to a file or standard output. This script output is equivalent to22 the plain text output format of <span class="application">pg_dump</span>.23 Some of the options controlling the output are therefore analogous to24 <span class="application">pg_dump</span> options.25 </p><p>26 Obviously, <span class="application">pg_restore</span> cannot restore information27 that is not present in the archive file. For instance, if the28 archive was made using the <span class="quote">“<span class="quote">dump data as29 <code class="command">INSERT</code> commands</span>”</span> option,30 <span class="application">pg_restore</span> will not be able to load the data31 using <code class="command">COPY</code> statements.32 </p></div><div class="refsect1" id="APP-PGRESTORE-OPTIONS"><h2>Options</h2><p>33 <span class="application">pg_restore</span> accepts the following command34 line arguments.35 36 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>filename</code></em></span></dt><dd><p>37 Specifies the location of the archive file (or directory, for a38 directory-format archive) to be restored.39 If not specified, the standard input is used.40 </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>41 Restore only the data, not the schema (data definitions).42 Table data, large objects, and sequence values are restored,43 if present in the archive.44 </p><p>45 This option is similar to, but for historical reasons not identical46 to, specifying <code class="option">--section=data</code>.47 </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>48 Before restoring database objects, issue commands49 to <code class="command">DROP</code> all the objects that will be restored.50 This option is useful for overwriting an existing database.51 If any of the objects do not exist in the destination database,52 ignorable error messages will be reported,53 unless <code class="option">--if-exists</code> is also specified.54 </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>55 Create the database before restoring into it.56 If <code class="option">--clean</code> is also specified, drop and57 recreate the target database before connecting to it.58 </p><p>59 With <code class="option">--create</code>, <span class="application">pg_restore</span>60 also restores the database's comment if any, and any configuration61 variable settings that are specific to this database, that is,62 any <code class="command">ALTER DATABASE ... SET ...</code>63 and <code class="command">ALTER ROLE ... IN DATABASE ... SET ...</code>64 commands that mention this database.65 Access privileges for the database itself are also restored,66 unless <code class="option">--no-acl</code> is specified.67 </p><p>68 When this option is used, the database named with <code class="option">-d</code>69 is used only to issue the initial <code class="command">DROP DATABASE</code> and70 <code class="command">CREATE DATABASE</code> commands. All data is restored into the71 database name that appears in the archive.72 </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>73 Connect to database <em class="replaceable"><code>dbname</code></em> and restore directly74 into the database. The <em class="replaceable"><code>dbname</code></em> can75 be a <a class="link" href="libpq-connect.html#LIBPQ-CONNSTRING" title="34.1.1. Connection Strings">connection string</a>.76 If so, connection string parameters will override any conflicting77 command line options.78 </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>79 Exit if an error is encountered while sending SQL commands to80 the database. The default is to continue and to display a count of81 errors at the end of the restoration.82 </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>83 Specify output file for generated script, or for the listing84 when used with <code class="option">-l</code>. Use <code class="literal">-</code>85 for <span class="systemitem">stdout</span>.86 </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>87 Specify format of the archive. It is not necessary to specify88 the format, since <span class="application">pg_restore</span> will89 determine the format automatically. If specified, it can be90 one of the following:91 92 </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>93 The archive is in the custom format of94 <span class="application">pg_dump</span>.95 </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>96 The archive is a directory archive.97 </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>98 The archive is a <code class="command">tar</code> archive.99 </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>100 Restore definition of named index only. Multiple indexes101 may be specified with multiple <code class="option">-I</code> switches.102 </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>103 Run the most time-consuming steps104 of <span class="application">pg_restore</span> — those that load data,105 create indexes, or create constraints — concurrently, using up106 to <em class="replaceable"><code>number-of-jobs</code></em>107 concurrent sessions. This option can dramatically reduce the time108 to restore a large database to a server running on a109 multiprocessor machine. This option is ignored when emitting a script110 rather than connecting directly to a database server.111 </p><p>112 Each job is one process or one thread, depending on the113 operating system, and uses a separate connection to the114 server.115 </p><p>116 The optimal value for this option depends on the hardware117 setup of the server, of the client, and of the network.118 Factors include the number of CPU cores and the disk setup. A119 good place to start is the number of CPU cores on the server,120 but values larger than that can also lead to faster restore121 times in many cases. Of course, values that are too high will122 lead to decreased performance because of thrashing.123 </p><p>124 Only the custom and directory archive formats are supported125 with this option.126 The input must be a regular file or directory (not, for example, a127 pipe or standard input). Also, multiple128 jobs cannot be used together with the129 option <code class="option">--single-transaction</code>.130 </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>131 List the table of contents of the archive. The output of this operation132 can be used as input to the <code class="option">-L</code> option. Note that133 if filtering switches such as <code class="option">-n</code> or <code class="option">-t</code> are134 used with <code class="option">-l</code>, they will restrict the items listed.135 </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>136 Restore only those archive elements that are listed in <em class="replaceable"><code>list-file</code></em>, and restore them in the137 order they appear in the file. Note that138 if filtering switches such as <code class="option">-n</code> or <code class="option">-t</code> are139 used with <code class="option">-L</code>, they will further restrict the items restored.140 </p><p><em class="replaceable"><code>list-file</code></em> is normally created by141 editing the output of a previous <code class="option">-l</code> operation.142 Lines can be moved or removed, and can also143 be commented out by placing a semicolon (<code class="literal">;</code>) at the144 start of the line. See below for examples.145 </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>146 Restore only objects that are in the named schema. Multiple schemas147 may be specified with multiple <code class="option">-n</code> switches. This can be148 combined with the <code class="option">-t</code> option to restore just a149 specific table.150 </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>151 Do not restore objects that are in the named schema. Multiple schemas152 to be excluded may be specified with multiple <code class="option">-N</code> switches.153 </p><p>154 When both <code class="option">-n</code> and <code class="option">-N</code> are given for the same155 schema name, the <code class="option">-N</code> switch wins and the schema is excluded.156 </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>157 Do not output commands to set158 ownership of objects to match the original database.159 By default, <span class="application">pg_restore</span> issues160 <code class="command">ALTER OWNER</code> or161 <code class="command">SET SESSION AUTHORIZATION</code>162 statements to set ownership of created schema elements.163 These statements will fail unless the initial connection to the164 database is made by a superuser165 (or the same user that owns all of the objects in the script).166 With <code class="option">-O</code>, any user name can be used for the167 initial connection, and this user will own all the created objects.168 </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>169 Restore the named function only. Be careful to spell the function170 name and arguments exactly as they appear in the dump file's table171 of contents. Multiple functions may be specified with multiple172 <code class="option">-P</code> switches.173 </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>174 This option is obsolete but still accepted for backwards175 compatibility.176 </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>177 Restore only the schema (data definitions), not data,178 to the extent that schema entries are present in the archive.179 </p><p>180 This option is the inverse of <code class="option">--data-only</code>.181 It is similar to, but for historical reasons not identical to,182 specifying183 <code class="option">--section=pre-data --section=post-data</code>.184 </p><p>185 (Do not confuse this with the <code class="option">--schema</code> option, which186 uses the word <span class="quote">“<span class="quote">schema</span>”</span> in a different meaning.)187 </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>188 Specify the superuser user name to use when disabling triggers.189 This is relevant only if <code class="option">--disable-triggers</code> is used.190 </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>191 Restore definition and/or data of only the named table.192 For this purpose, <span class="quote">“<span class="quote">table</span>”</span> includes views, materialized views,193 sequences, and foreign tables. Multiple tables194 can be selected by writing multiple <code class="option">-t</code> switches.195 This option can be combined with the <code class="option">-n</code> option to196 specify table(s) in a particular schema.197 </p><div class="note"><h3 class="title">Note</h3><p>198 When <code class="option">-t</code> is specified, <span class="application">pg_restore</span>199 makes no attempt to restore any other database objects that the200 selected table(s) might depend upon. Therefore, there is no201 guarantee that a specific-table restore into a clean database will202 succeed.203 </p></div><div class="note"><h3 class="title">Note</h3><p>204 This flag does not behave identically to the <code class="option">-t</code>205 flag of <span class="application">pg_dump</span>. There is not currently206 any provision for wild-card matching in <span class="application">pg_restore</span>,207 nor can you include a schema name within its <code class="option">-t</code>.208 And, while <span class="application">pg_dump</span>'s <code class="option">-t</code>209 flag will also dump subsidiary objects (such as indexes) of the210 selected table(s),211 <span class="application">pg_restore</span>'s <code class="option">-t</code>212 flag does not include such subsidiary objects.213 </p></div><div class="note"><h3 class="title">Note</h3><p>214 In versions prior to <span class="productname">PostgreSQL</span> 9.6, this flag215 matched only tables, not any other type of relation.216 </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>217 Restore named trigger only. Multiple triggers may be specified with218 multiple <code class="option">-T</code> switches.219 </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>220 Specifies verbose mode. This will cause221 <span class="application">pg_restore</span> to output detailed object222 comments and start/stop times to the output file, and progress223 messages to standard error.224 Repeating the option causes additional debug-level messages225 to appear on standard error.226 </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>227 Print the <span class="application">pg_restore</span> version and exit.228 </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>229 Prevent restoration of access privileges (grant/revoke commands).230 </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>231 Execute the restore as a single transaction (that is, wrap the232 emitted commands in <code class="command">BEGIN</code>/<code class="command">COMMIT</code>). This233 ensures that either all the commands complete successfully, or no234 changes are applied. This option implies235 <code class="option">--exit-on-error</code>.236 </p></dd><dt><span class="term"><code class="option">--disable-triggers</code></span></dt><dd><p>237 This option is relevant only when performing a data-only restore.238 It instructs <span class="application">pg_restore</span> to execute commands239 to temporarily disable triggers on the target tables while240 the data is restored. Use this if you have referential241 integrity checks or other triggers on the tables that you242 do not want to invoke during data restore.243 </p><p>244 Presently, the commands emitted for245 <code class="option">--disable-triggers</code> must be done as superuser. So you246 should also specify a superuser name with <code class="option">-S</code> or,247 preferably, run <span class="application">pg_restore</span> as a248 <span class="productname">PostgreSQL</span> superuser.249 </p></dd><dt><span class="term"><code class="option">--enable-row-security</code></span></dt><dd><p>250 This option is relevant only when restoring the contents of a table251 which has row security. By default, <span class="application">pg_restore</span> will set252 <a class="xref" href="runtime-config-client.html#GUC-ROW-SECURITY">row_security</a> to off, to ensure253 that all data is restored in to the table. If the user does not have254 sufficient privileges to bypass row security, then an error is thrown.255 This parameter instructs <span class="application">pg_restore</span> to set256 <a class="xref" href="runtime-config-client.html#GUC-ROW-SECURITY">row_security</a> to on instead, allowing the user to attempt to restore257 the contents of the table with row security enabled. This might still258 fail if the user does not have the right to insert the rows from the259 dump into the table.260 </p><p>261 Note that this option currently also requires the dump be in <code class="command">INSERT</code>262 format, as <code class="command">COPY FROM</code> does not support row security.263 </p></dd><dt><span class="term"><code class="option">--if-exists</code></span></dt><dd><p>264 Use <code class="literal">DROP ... IF EXISTS</code> commands to drop objects265 in <code class="option">--clean</code> mode. This suppresses <span class="quote">“<span class="quote">does not266 exist</span>”</span> errors that might otherwise be reported. This267 option is not valid unless <code class="option">--clean</code> is also268 specified.269 </p></dd><dt><span class="term"><code class="option">--no-comments</code></span></dt><dd><p>270 Do not output commands to restore comments, even if the archive271 contains them.272 </p></dd><dt><span class="term"><code class="option">--no-data-for-failed-tables</code></span></dt><dd><p>273 By default, table data is restored even if the creation command274 for the table failed (e.g., because it already exists).275 With this option, data for such a table is skipped.276 This behavior is useful if the target database already277 contains the desired table contents. For example,278 auxiliary tables for <span class="productname">PostgreSQL</span> extensions279 such as <span class="productname">PostGIS</span> might already be loaded in280 the target database; specifying this option prevents duplicate281 or obsolete data from being loaded into them.282 </p><p>283 This option is effective only when restoring directly into a284 database, not when producing SQL script output.285 </p></dd><dt><span class="term"><code class="option">--no-publications</code></span></dt><dd><p>286 Do not output commands to restore publications, even if the archive287 contains them.288 </p></dd><dt><span class="term"><code class="option">--no-security-labels</code></span></dt><dd><p>289 Do not output commands to restore security labels,290 even if the archive contains them.291 </p></dd><dt><span class="term"><code class="option">--no-subscriptions</code></span></dt><dd><p>292 Do not output commands to restore subscriptions, even if the archive293 contains them.294 </p></dd><dt><span class="term"><code class="option">--no-table-access-method</code></span></dt><dd><p>295 Do not output commands to select table access methods.296 With this option, all objects will be created with whichever297 access method is the default during restore.298 </p></dd><dt><span class="term"><code class="option">--no-tablespaces</code></span></dt><dd><p>299 Do not output commands to select tablespaces.300 With this option, all objects will be created in whichever301 tablespace is the default during restore.302 </p></dd><dt><span class="term"><code class="option">--section=<em class="replaceable"><code>sectionname</code></em></code></span></dt><dd><p>303 Only restore the named section. The section name can be304 <code class="option">pre-data</code>, <code class="option">data</code>, or <code class="option">post-data</code>.305 This option can be specified more than once to select multiple306 sections. The default is to restore all sections.307 </p><p>308 The data section contains actual table data as well as large-object309 definitions.310 Post-data items consist of definitions of indexes, triggers, rules311 and constraints other than validated check constraints.312 Pre-data items consist of all other data definition items.313 </p></dd><dt><span class="term"><code class="option">--strict-names</code></span></dt><dd><p>314 Require that each schema315 (<code class="option">-n</code>/<code class="option">--schema</code>) and table316 (<code class="option">-t</code>/<code class="option">--table</code>) qualifier match at317 least one schema/table in the backup file.318 </p></dd><dt><span class="term"><code class="option">--use-set-session-authorization</code></span></dt><dd><p>319 Output SQL-standard <code class="command">SET SESSION AUTHORIZATION</code> commands320 instead of <code class="command">ALTER OWNER</code> commands to determine object321 ownership. This makes the dump more standards-compatible, but322 depending on the history of the objects in the dump, might not restore323 properly.324 </p></dd><dt><span class="term"><code class="option">-?</code><br /></span><span class="term"><code class="option">--help</code></span></dt><dd><p>325 Show help about <span class="application">pg_restore</span> command line326 arguments, and exit.327 </p></dd></dl></div><p>328 </p><p>329 <span class="application">pg_restore</span> also accepts330 the following command line arguments for connection parameters:331 332 </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>333 Specifies the host name of the machine on which the server is334 running. If the value begins with a slash, it is used as the335 directory for the Unix domain socket. The default is taken336 from the <code class="envar">PGHOST</code> environment variable, if set,337 else a Unix domain socket connection is attempted.338 </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>339 Specifies the TCP port or local Unix domain socket file340 extension on which the server is listening for connections.341 Defaults to the <code class="envar">PGPORT</code> environment variable, if342 set, or a compiled-in default.343 </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>344 User name to connect as.345 </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>346 Never issue a password prompt. If the server requires347 password authentication and a password is not available by348 other means such as a <code class="filename">.pgpass</code> file, the349 connection attempt will fail. This option can be useful in350 batch jobs and scripts where no user is present to enter a351 password.352 </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>353 Force <span class="application">pg_restore</span> to prompt for a354 password before connecting to a database.355 </p><p>356 This option is never essential, since357 <span class="application">pg_restore</span> will automatically prompt358 for a password if the server demands password authentication.359 However, <span class="application">pg_restore</span> will waste a360 connection attempt finding out that the server wants a password.361 In some cases it is worth typing <code class="option">-W</code> to avoid the extra362 connection attempt.363 </p></dd><dt><span class="term"><code class="option">--role=<em class="replaceable"><code>rolename</code></em></code></span></dt><dd><p>364 Specifies a role name to be used to perform the restore.365 This option causes <span class="application">pg_restore</span> to issue a366 <code class="command">SET ROLE</code> <em class="replaceable"><code>rolename</code></em>367 command after connecting to the database. It is useful when the368 authenticated user (specified by <code class="option">-U</code>) lacks privileges369 needed by <span class="application">pg_restore</span>, but can switch to a role with370 the required rights. Some installations have a policy against371 logging in directly as a superuser, and use of this option allows372 restores to be performed without violating the policy.373 </p></dd></dl></div><p>374 </p></div><div class="refsect1" id="id-1.9.4.18.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>375 Default connection parameters376 </p></dd><dt><span class="term"><code class="envar">PG_COLOR</code></span></dt><dd><p>377 Specifies whether to use color in diagnostic messages. Possible values378 are <code class="literal">always</code>, <code class="literal">auto</code> and379 <code class="literal">never</code>.380 </p></dd></dl></div><p>381 This utility, like most other <span class="productname">PostgreSQL</span> utilities,382 also uses the environment variables supported by <span class="application">libpq</span>383 (see <a class="xref" href="libpq-envars.html" title="34.15. Environment Variables">Section 34.15</a>). However, it does not read384 <code class="envar">PGDATABASE</code> when a database name is not supplied.385 </p></div><div class="refsect1" id="APP-PGRESTORE-DIAGNOSTICS"><h2>Diagnostics</h2><p>386 When a direct database connection is specified using the387 <code class="option">-d</code> option, <span class="application">pg_restore</span>388 internally executes <acronym class="acronym">SQL</acronym> statements. If you have389 problems running <span class="application">pg_restore</span>, make sure390 you are able to select information from the database using, for391 example, <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a>. Also, any default connection392 settings and environment variables used by the393 <span class="application">libpq</span> front-end library will apply.394 </p></div><div class="refsect1" id="APP-PGRESTORE-NOTES"><h2>Notes</h2><p>395 If your installation has any local additions to the396 <code class="literal">template1</code> database, be careful to load the output of397 <span class="application">pg_restore</span> into a truly empty database;398 otherwise you are likely to get errors due to duplicate definitions399 of the added objects. To make an empty database without any local400 additions, copy from <code class="literal">template0</code> not <code class="literal">template1</code>, for example:401</p><pre class="programlisting">402CREATE DATABASE foo WITH TEMPLATE template0;403</pre><p>404 </p><p>405 The limitations of <span class="application">pg_restore</span> are detailed below.406 407 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>408 When restoring data to a pre-existing table and the option409 <code class="option">--disable-triggers</code> is used,410 <span class="application">pg_restore</span> emits commands411 to disable triggers on user tables before inserting the data, then emits commands to412 re-enable them after the data has been inserted. If the restore is stopped in the413 middle, the system catalogs might be left in the wrong state.414 </p></li><li class="listitem"><p><span class="application">pg_restore</span> cannot restore large objects415 selectively; for instance, only those for a specific table. If416 an archive contains large objects, then all large objects will be417 restored, or none of them if they are excluded via <code class="option">-L</code>,418 <code class="option">-t</code>, or other options.419 </p></li></ul></div><p>420 </p><p>421 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 on422 limitations of <span class="application">pg_dump</span>.423 </p><p>424 Once restored, it is wise to run <code class="command">ANALYZE</code> on each425 restored table so the optimizer has useful statistics; see426 <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-STATISTICS" title="25.1.3. Updating Planner Statistics">Section 25.1.3</a> and427 <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">Section 25.1.6</a> for more information.428 </p></div><div class="refsect1" id="APP-PGRESTORE-EXAMPLES"><h2>Examples</h2><p>429 Assume we have dumped a database called <code class="literal">mydb</code> into a430 custom-format dump file:431 432</p><pre class="screen">433<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -Fc mydb > db.dump</code></strong>434</pre><p>435 </p><p>436 To drop the database and recreate it from the dump:437 438</p><pre class="screen">439<code class="prompt">$</code> <strong class="userinput"><code>dropdb mydb</code></strong>440<code class="prompt">$</code> <strong class="userinput"><code>pg_restore -C -d postgres db.dump</code></strong>441</pre><p>442 443 The database named in the <code class="option">-d</code> switch can be any database existing444 in the cluster; <span class="application">pg_restore</span> only uses it to issue the445 <code class="command">CREATE DATABASE</code> command for <code class="literal">mydb</code>. With446 <code class="option">-C</code>, data is always restored into the database name that appears447 in the dump file.448 </p><p>449 To restore the dump into a new database called <code class="literal">newdb</code>:450 451</p><pre class="screen">452<code class="prompt">$</code> <strong class="userinput"><code>createdb -T template0 newdb</code></strong>453<code class="prompt">$</code> <strong class="userinput"><code>pg_restore -d newdb db.dump</code></strong>454</pre><p>455 456 Notice we don't use <code class="option">-C</code>, and instead connect directly to the457 database to be restored into. Also note that we clone the new database458 from <code class="literal">template0</code> not <code class="literal">template1</code>, to ensure it is459 initially empty.460 </p><p>461 To reorder database items, it is first necessary to dump the table of462 contents of the archive:463</p><pre class="screen">464<code class="prompt">$</code> <strong class="userinput"><code>pg_restore -l db.dump > db.list</code></strong>465</pre><p>466 The listing file consists of a header and one line for each item, e.g.:467</p><pre class="programlisting">468;469; Archive created at Mon Sep 14 13:55:39 2009470; dbname: DBDEMOS471; TOC Entries: 81472; Compression: 9473; Dump Version: 1.10-0474; Format: CUSTOM475; Integer: 4 bytes476; Offset: 8 bytes477; Dumped from database version: 8.3.5478; Dumped by pg_dump version: 8.3.8479;480;481; Selected TOC Entries:482;4833; 2615 2200 SCHEMA - public pasha4841861; 0 0 COMMENT - SCHEMA public pasha4851862; 0 0 ACL - public pasha486317; 1247 17715 TYPE public composite pasha487319; 1247 25899 DOMAIN public domain0 pasha488</pre><p>489 Semicolons start a comment, and the numbers at the start of lines refer to the490 internal archive ID assigned to each item.491 </p><p>492 Lines in the file can be commented out, deleted, and reordered. For example:493</p><pre class="programlisting">49410; 145433 TABLE map_resolutions postgres495;2; 145344 TABLE species postgres496;4; 145359 TABLE nt_header postgres4976; 145402 TABLE species_records postgres498;8; 145416 TABLE ss_old postgres499</pre><p>500 could be used as input to <span class="application">pg_restore</span> and would only restore501 items 10 and 6, in that order:502</p><pre class="screen">503<code class="prompt">$</code> <strong class="userinput"><code>pg_restore -L db.list db.dump</code></strong>504</pre></div><div class="refsect1" id="id-1.9.4.18.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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> <span class="application">pg_verifybackup</span></td></tr></table></div></body></html>