Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
app-pgrestore.html504 linesDownload Raw Back to html
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 &gt; 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 &gt; 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>
codekingpro/portable-devtools · Team Ai