Manager Handbook for Centralized AntDB-T - P5–5
Published: 2023-08-21 06:08:59

pg_restore
pg_restore — restore an AntDB database from an archive file created by pg_dump.
Outline

```
pg_restore [connection-option...] [option...] [filename]
```
Description
pg_restore is a tool used to restore an AntDB database from a non-text format archive created by pg_dump. It will issue the necessary commands to rebuild that database to the state it was in when it was saved. These archives also allow pg_restore to choose what to recover or to reorder the recovered items prior to recovery. These archives are designed to be migrated between different architectures.
pg_restore can operate in two modes. If a database name is specified, pg_restore connects to that database and restores the archive contents directly to that database. Otherwise, a script is created containing the SQL commands necessary to rebuild the database, which is written to a file or to standard output. This script output is equivalent to the plain text output format of pg_dump. Therefore, some of the options to control the output are similar to those of pg_dump.
Obviously, pg_restore cannot recover information that is not in the archive file. For example, if the archive was created with the "dump data by INSERTcommand" option, pg_restore will not be able to load the data using the COPYstatement.

```
pg_restore --help
    General options:
       -d, --dbname=NAME        connect to database name
       -f, --file=FILENAME      output file name (- for stdout)
       -F, --format=c|d|t       backup file format (should be automatic)
       -l, --list               print summarized TOC of the archive
    Options controlling the restore:
       -a, --data-only              restore only the data, no schema
       -e, --exit-on-error          exit on error, default is to continue
       -j, --jobs=NUM               use this many parallel jobs to restore
       -L, --use-list=FILENAME      use table of contents from this file for  selecting/ordering output
       -n, --schema=NAME            restore only objects in this schema
```
option
pg_restore accepts the following command line arguments.

```
filename
Specifies the location of the archive file to be recovered (or directory for a directory format archive). If not specified, standard input is used.
-a
--data-only
Recover only data, not mode (data definition). If present in the archive, table data, large objects, and sequence values are restored.
This option is similar to specifying --section=data, but for historical reasons the two are not exactly the same.
-c
--clean
Clear (discard) database objects before recreating them (unless --if-exists is used, which may generate some harmless error messages if there are objects that do not exist in the target database).
-C
--create
The county creates a database before restoring it. If --clean is also specified, it is discarded and rebuilt before connecting to the target database.
If you use --create, pg_restore also restore the comments of the database (if any) and the configuration variable settings associated with it, i.e. any ALTER DATABASE that mentions the database... SET ... AND ALTER ROLE ... IN DATABASE ... SET ... Command. Whether --no-ACL is specified or not, access privileges to the database itself are restored.
When using this option, the database mentioned by -d is only used to issue the initial DROP DATABASE and CREATE DATABASE commands. All data to be restored to that database name appears in the archive.
-d dbname
--dbname=dbname
Connect to the database dbname and restore directly to the database. dbname can be a connection string. If so, the connection string parameter overrides any conflicting command-line options.
-e
--exit-on-error
Exit if an error is encountered during sending SQL commands to the database. The default behavior is to continue and display an error count at the end of the recovery.
-f filename
--file=filename
Specify an output file for the generated script or, when used with the -l option, for a list. Use -for stdout.
-F format
--format=format
Specifies the format of the archive. You don't have to specify the format because pg_restore will automatically determine the format. If specified, it can be one of the following:
c
custom
Archiving is a custom format for pg_dump.
d
directory
An archive is a directory archive.
t
tar
The archive is a tar archive.
-I index
--index=index
Only the definition of the mentioned index is restored. Multiple indexes can be specified by writing multiple -I switches.
-j number-of-jobs
--jobs=number-of-jobs
Use concurrent tasks to run the most time-consuming steps in pg_restore—loading data, creating indexes, or creating constraints; At the same time, up to number-of-jobs are used for concurrent sessions. For a server running on a multiprocessor machine, this option can greatly reduce the time to restore a large database. This option is ignored when emitting a script instead of connecting directly to the database server.
Each task is a process or a thread, depending on the operating system, and they all use a separate server connection.
The optimal value for this option depends on the hardware settings of the server, client, and network. Factors include the number of cores and disk settings of the CPU. A good recommendation is the number of CPU cores on the server, but larger values can also result in faster recovery times in many cases. Of course, values that are too high can lead to degraded performance due to overload.
This option only supports custom and directory archive formats. The input must be a regular file or directory (for example, it cannot be a pipe or standard output). Also, multitasking cannot be used with the option--single-transaction.
-l
--list
A table that lists the contents of the archive. The output of this operation can be used as input to the -L option. Note that if you use filter switches such as -n or -t with -l, they will limit the items listed.
-L list-file
--use-list=list-file
Only the archive elements listed in the list-file are restored, and the restore occurs in the order in which they appear in the file. Note that if filter switches such as -n or -t are used with -L, they will further limit the items to be recovered.
list-file is usually created by editing the output of a -l operation. Rows can be moved or removed, and can also be moved or removed by placing a (; Comment it out. See below for examples.
-n shcema
--schema=schema
Only objects in the mentioned mode are restored. Multiple modes can be specified with multiple -n switches. This can be combined with the -t option to restore only one specified table.
-N schema
--exclude-schema=schema
Objects in the mentioned scenario are not restored. Multiple -N switches can be used to specify multiple schemes to be excluded.
If both -n and -N are given for the same scheme name, -N wins and the scheme is excluded.
-O
--no-owner
Do not output commands that set the ownership of objects to match the original database. By default, pg_restore issues an ALTER OWNER or SET SESSION AUTHORIZATION statement to set ownership of the created schema object. Unless the initial connection to the database is established by a superuser (or the same user who owns all the objects in the script), these statements will fail. With -O, any username can be used for the initial connection, and this user will own all created objects.
-P function-name(argtype [, ...])
--function=function-name(argtype [, ...])
Only the mentioned functions are restored. Be careful to spell the names and parameters of the functions so that they are exactly the names and parameters that appear in the contents table of the dump file. Multiple functions can be specified using multiple -P switches.
-R
--no-reconnect
This option has been deprecated, but it is still accepted for backward compatibility purposes.
-s
--schema-only
Restoring only schemas (data definitions) in the archive does not restore data.
This option is the inverse of --data-only. It is similar to specifying --section=pre-data --section=post-data, but not exactly the same for historical reasons.
（Don't confuse this option with the --schema option, which uses the word "schema" for a different meaning）。

-S username
--superuser=username
Specifies the superuser name to use when disabling the trigger. This option is only relevant if --disable-triggers are used.
-t table
--table=table
Only the definitions and data of the mentioned tables are restored. For this purpose, "table" includes views, materialized views, sequences, and external tables. Multiple -t switches can be written to select multiple tables. This option can be combined with the -n option to specify a table in a particular schema.
-T trigger
--trigger=trigger
Only the mentioned triggers are restored. Multiple triggers can be specified with multiple -T switches.
-v
--verbose
Specifies verbose mode.
-V
--version
Print the version of the pg_restore and exit.
-x
--no-privileges
--no-acl
Prevent the restoration of access privileges (grant/reclaim command).
-1
--single-transaction
Execute the restore as a single transaction (i.e., wrap the issued commands in BEGIN/COMMIT). This ensures that either all commands are completely successful or that no changes are applied. This option implies --exit-on-error.
--disable-triggers
This option is only relevant if you are performing a recovery that only recovers data. It instructs pg_restore execute a command to temporarily disable triggers on the target table while loading data. Use this option if you have referential integrity checks or other triggers on the table and you do not want them to be invoked during data loading.
Currently, commands issued for --disable-triggers must be completed as superuser. So you should also specify a superusername with -S, or better yet, run the pg_restore as an AntDB superuser.
--enable-row-security
This option is relevant only if you are restoring the contents of a table with row safety. By default, pg_restore will set the row_security to off to ensure that all data is restored to the table. If the user does not have sufficient privileges to bypass row security, an error is thrown. This parameter instructs pg_restore set row_security to on to allow the user to attempt to recover the contents of a table with row security enabled. If the user does not have permission to insert rows into the table from the dump, this will still fail.
Note that the current option also requires the dump to be in INSERT format, as COPY FROM does not support row safety.
--if-exists
Use a conditional command (that is, add an IF EXISTS clause) to delete database objects. This option only works if --clean is specified.
--no-comments
Even if the archive contains comments, the command to restore comments is not printed.
--no-data-for-failed-tables
By default, even if the table creation command fails (for example, because the table already exists), the table data is restored. With this option, data for such tables will be skipped. This behavior is useful if the target database already contains the table contents you want.
This option is only valid when restoring directly to a database, and has no effect when producing SQL script output.
--no-publications
Even if the archive contains publication, the command to restore publication is not output.
--no-security-labels
Do not output a command to restore a security label, even if the archive contains a security label.
--no-subscriptions
Even if the archive contains subscription, the command to restore subscription is not printed.
--no-tablespaces
Do not output command selection tablespaces. With this option, all objects will be created in the default tablespace at the time of recovery.
--section=sectionname
Only the mentioned subsections are restored. The name of the section can be pre-data, data, or post-data. You can specify this option multiple times to select multiple bars. The default value is to restore all subsections.
The Data subsection contains the actual table data as well as large object definitions. A Post-data item consists of an index definition, triggers, rules, and constraints in addition to the validated check constraints. The Pre-data item consists of all other data definition items.
--strict-names
Each schema (-n/--schema) and table (-t/-table) qualifier is required to match at least one schema/table in the backup file.
--use-set-session-authorization
Output SQL standard SET SESSION AUTHORIZATION COMMAND replaces the ALTER OWNER command to determine object ownership. This makes the dump more standards-compatible, but depending on the history of the objects in the dump, it may not recover correctly.
-?
--help
Displays help for pg_restore command-line arguments, and exits.
pg_restore also accepts the following command-line arguments for connection parameters:
-h host
--host=host
Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as a directory for Unix domain sockets. The default is obtained from the PGHOST environment variable (if set), otherwise a Unix domain socket connection will be attempted.
-p port
--port=port
Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. The default is to put it in the PGPORT environment variable (if set), otherwise use the default value compiled in the program.
-U username
--username=username
Which user to connect as.
-w
--no-password
No password prompt is issued. If the server requires password authentication and there is no other way to provide a password (for example, a .pgpass file), the connection attempt fails. This option is useful for batch tasks and scripts where none of the users enter the password.
-W
--password
Force pg_restore prompt for a password before connecting to a database.
This option is not required because if the server requires password authentication, pg_restore will automatically prompt for a password. However, pg_restore will waste a connection attempt to discover that the server wants a password. In some cases, it is worth typing -W to avoid additional connection attempts.
--role=rolename
Specifies the name of the role used to create the dump. This option causes pg_restore to issue a SET ROLE rolename command after connecting to the database. This option is useful when the authenticated user (specified by -U) lacks the required privileges for pg_restore but is able to switch to a role with the required rights. Some installations have policies for logging in directly as superuser, and this option allows dumps to complete without violating that policy.
```
For example

```
Suppose we have dumped a database called mydb in a custom format:
$ pg_dump -Fc mydb > db.dump

To drop the database and recreate it from the dump:
$ dropdb mydb
$ pg_restore -C -d postgres db.dump

To reload the dump into a new database named newdb:
$ createdb -T template0 newdb
$ pg_restore -d newdb db.dump

To reorder database items, you first need to dump the archived table contents:
$ pg_restore -l db.dump > db.list
```