Manager Handbook for Centralized AntDB-T - P5–2
Published: 2023-08-17 07:32:30

option
The following command options control the content and format of the output.

```
dbname
Specify the database name to be dumped. If not specified, the environment variable PGDATABASE will be used. If the environment variable is not set, the username specified for the connection is used.
-a
--data-only
Only dump data, not dump mode (data definition). Table data, large objects, and sequence values are all dumped.

This option is similar to specifying -- section=data, but it is not entirely the same due to historical reasons.

-b
--blobs
Include large objects in the dump. This is the default behavior when -- schema, -- table, or -- schema only is specified. Therefore, the - b switch is only useful for adding large objects to the dump when requesting a specific scheme or table to be dumped. Note that blobs are considered data, so they will be included when using -- data only, but not when using -- schema only.

-B
--no-blobs
Exclude large objects from the dump.

When both - b and - B are given, the behavior is to output large objects when the data is dumped. Please refer to the - b document.

-c
--clean
Before outputting the command to create database objects, output the command to clear (delete) them (unless -- if exists is also specified, recovery may generate some harmful error messages if any objects do not exist in the destination database).

This option only makes sense for plain text format. For the archive format, you can call pg_ Specify this option when restoring.

-C
--create
At the beginning of the output is a command to create the database itself and reconnect to the created database (through a script in this form, it does not matter which database in the target installation you are connecting to before running the script). If -- clean is also specified, the script will delete it before rebuilding before reconnecting to the target database.

By using - create, the output will also include annotations for the database (if any) and any configuration variable settings related to the database, that is, any ALT DATABASE that mentions the database SET Command and Alter ROLE In DATABASE SET Command. The access privileges of the database itself will also be dumped, unless -- no acl is specified.

This option only makes sense for plain text format. For the archive format, you can call pg_ Specify this option when restoring.

-E encoding
--encoding=encoding

Create a dump with the specified character set encoding. By default, the dump will be created in the encoding of the database (another way to obtain the same result is to set the PGCLIENTENCODING environment variable to the desired dump encoding).

-f file
--file=file
Sends the output to the specified file. For files based on output format, this parameter can be ignored, in which case standard output will be used. However, this parameter must be given for the directory output format, which specifies a directory instead of a file. In this case, the directory will be created by pg_ Dump is created and does not need to exist before.

-F format
--format=format
Select the output format. The format can be one of the following:

p
plain
Output a plain text SQL script file (default).

c
custom
Output a suitable pg_ Archive the custom format of restore input. Along with the directory output format, this is the most flexible output format that allows for manual selection and sorting of archived items during recovery. This format is also compressed by default.

d
directory
Output a suitable pg_ Archive the directory format entered by restore. This will create a directory where each dumped table and large object has a file, plus a so-called directory file, which is stored in a pg format_ The machine readable format that restore can read describes the object being dumped. A directory format archive can be manipulated using standard Unix tools, for example, files in an uncompressed archive can be compressed using the gzip tool. This format is compressed by default and also supports parallel dumping.

t
tar
Output a suitable input to pg_ Archive in tar format in restore. The tar format can be compatible with the directory format, and extracting a tar format archive will generate a legal directory format archive. However, the tar format does not support compression. Also, when using tar format, the relative order of table data items cannot be changed during the recovery process.

-j njobs
--jobs=njobs
Run parallel dump by simultaneously archiving njobs tables. This option may reduce the time required to perform the dump, but it also increases the load on the database server. You can only use this option with the directory output format, as it is the only output format that allows multiple processes to write their data at the same time.

-n pattern
--schema=pattern
Only dump patterns that match the pattern, which selects the pattern itself and all the objects it contains. When this option is not specified, all non system schemas in the target database will be dumped. Multiple modes can be selected by writing multiple - n switches.

-N pattern
--exclude-schema=pattern
Do not dump any patterns that match pattern patterns. This pattern is interpreted according to the same rules used by - n- N can be given multiple times to exclude patterns that match any one of several patterns.
When both - n and - N are given, this behavior only dumps a pattern that matches at least one - n switch but does not match the - N switch. If there is only - N without - n, the pattern matching - N will be excluded from a normal dump.

-O
--no-owner
Do not output the command to set the object ownership relationship to match the original database. By default, pg_ Dump will issue an Alter Owner or SET SESSION Authorization statement to set the ownership relationship of the created database object. Unless the script is initiated by a super user (or the same user who owns all objects in the script), these statements will fail. To enable a script to be restored by any user, but to give ownership of all objects to that user, - O can be specified.

This option only makes sense for plain text format. For the archive format, you can call pg_ Specify this option when restoring.

-R
--no-reconnect
This option is obsolete, but it can still be accepted for backward compatibility.

-s
--schema-only
Only dump object definitions (patterns), not data.

This option is the inverse of - data only. It is similar to specifying -- section=pre data -- section=post data, but not entirely the same due to historical reasons.

(Do not confuse this option with the -- schema option, which has different meanings in the use of 'schema').

To exclude table data for a subset of tables in the database, see -- exclude table data.

-S username
--superuser=username
Specify the username of the superuser to use when disabling triggers. This option is only relevant when using -- disable triggers (usually, it is best to omit this option and instead start the result script as a super user).

-t pattern
--table=pattern
Only dump tables with names matching patterns. Multiple tables can be selected by writing multiple - t switches. In addition, the pattern parameter can be interpreted as a pattern written according to the same rules used by the psql's  d command (see Patterns below), so that multiple tables can also be selected by writing wildcard characters in this pattern. 

When using wildcards, if you need to prevent the shell from expanding wildcards, be careful to refer to this pattern, see Examples below.
When - t is used, the - n and - N switches will not have an effect, as the tables selected by - t will be dumped regardless of those switches, and non table objects will not be dumped.

-T pattern
--exclude-table=pattern
Do not dump any tables that match the pattern pattern. This pattern is interpreted according to the same rules used by - t- T can be given multiple times to exclude patterns that match any one of several patterns.

When both - t and - T are given, this behavior only dumps tables that match at least one - t switch but do not match the - T switch. If there is only - T without - t, the table that matches - T will be excluded from a normal dump.

-v
--verbose
Specify verbose mode. This will result in pg_ Dump outputs detailed object annotations to standard errors, as well as the start/stop time of the dump file, as well as progress messages.

-V
--version
Pg_ Dump the version and exit.

-x
--no-privileges
--no-acl
Prevent dump access privileges (grant/revoke commands).

-Z 0..9
--compress=0..9
Specify the compression level to use. Zero means no compression. For custom archive formats, this specifies compression for individual table data segments, and defaults to a medium level of compression. For pure text output, setting a non zero compression level will cause the entire output file to be compressed, as if it had been processed by gzip, but the default is not compressed. The tar archive format currently does not support compression at all.

--binary-upgrade
This option is used for in place upgrade functionality. We do not recommend or support using it for other purposes. This option may be changed without notification in future releases.

--column-inserts
--attribute-inserts
Dump data as an Insert command with explicit column names (Insert INTO table (column,...) VALUES...). This will make the recovery process very slow, mainly used to enable the dump to be loaded into non AntDB databases. Any errors during reloading will result in the loss of problematic Insert related rows, rather than the entire table content.

--disable-dollar-quoting
This option prohibits the use of dollar sign references in function bodies and forces them to be referenced using SQL standard string syntax.

--disable-triggers
This option is only relevant when creating a dump that only dumps data. It indicates pg_ Dump includes commands that temporarily disable triggers on the target table when data is reloaded. If you have referential integrity checks or other triggers on the table and you do not want to call them during data reloading, please use this option.

Currently, the command issued for -- disable triggers must be executed as a super user. Therefore, you should also use - S to specify a super username, or rather start the result script as a super user.

This option only makes sense for plain text format. For the archive format, you can call pg_ Specify this option when restoring.

--enable-row-security
This option is only relevant when dumping the contents of a table with row security. By default, pg_ Dump will remove row_ Set security to off to ensure that all data is stored from the table. If the user does not have sufficient privileges to bypass row security, an error will be thrown indicating that pg_ Dump will row_ Security is set to on, allowing users to only dump the portion of the table that they can access.
Note that if you are currently using this option, you may also want to obtain a dump in the Insert format, as COPY FROM during recovery does not support row security.

--exclude-table-data=pattern
Do not dump data from any table that matches the pattern pattern. This pattern is interpreted according to the same rule as - t-- Exclude table data can be given multiple times to exclude tables that match multiple patterns. This option is useful when you need the definition of a specific table but do not want the data in it.

To exclude data from all tables in the database, see -- schema only.
--extra-float-digits=ndigits

Use the specified extra when dumping floating-point data_ Float_ Digits value, not the maximum available precision. Regular dumps generated for backup purposes do not use this option.
--if-exists

Time conditional commands (i.e. adding an IF EXISTS clause) to clear the database and other objects. This option is only available when -- clean is also specified.

--include-foreign-data=foreignserver

Dump data from any external table using an external server that matches the foreignserver pattern. You can select multiple external servers by writing multiple -- include foreign data switches. Similarly, according to the same rules used by the psql's  d command, interpret the foreignserver parameter as a pattern. (Please refer to the Patterns below), so you can also select multiple external servers by writing wildcards in the pattern. When using wildcards, be careful to refer to this pattern if necessary to prevent Shell from extending wildcards. Please refer to Examples below. The only exception is that empty mode is not allowed.
--inserts

Dump data as an Insert command (instead of COPY). This will make the recovery very slow, mainly used to enable the dump to be loaded into non AntDB databases. Any errors during reloading will result in the loss of problematic Insert related rows, rather than the entire table content. Note that if you have already rearranged the column order, the recovery may fail together-- The column inserts option is safe for column order changes, but it will be slower.

--load-via-partition-root

When dumping data for a partitioned table, have the COPY or Insert statements target the root of the partition hierarchy that contains it instead of the partition itself. This results in re determining the appropriate partition for each row when the data is loaded. This option is useful if rows do not always fall into the same partition as the original server when reloading data on a server. For example, if the partition column is of text type and the sorting rules used to sort partition columns in two systems have different definitions, this situation will occur.
It is best not to use parallelism when recovering from archives created with this option, as pg_ Restore will not be able to accurately determine which partition a given archived data item will load the data into. This can lead to low efficiency, as there may be lock conflicts in parallel tasks, or it may even fail to reload due to foreign key constraints established before all related data is loaded.

--lock-wait-timeout=timeout
Never wait for the shared table lock to be obtained at the beginning of the dump. Instead, it fails when a table cannot be locked within the specified timeout. The timeout duration can be set using SET statement_ Any format specified for timeout (the allowed values vary depending on the server version you are transferring from, but all versions since 7.3 accept an integer representation of the number of milliseconds. If transferring from servers before 7.3, this option will be ignored.).
--no-comments
Do not dump comments.

--no-publications
Do not dump publication.

--no-security-labels
Do not dump security labels.

--no-subscriptions
Do not dump subscriptions.

--no-sync
By default, pg_ Dump will wait for all files to be safely written to disk. This option will make pg_ Dump returns directly without waiting, which will be faster, but it also means that a subsequent operating system crash will cause the dump to be damaged. This option is usually useful for testing, but should not be used when dumping data from a production installation.

--no-synchronized-snapshots
This option allows running pg on servers before 9.2_ Dump - j, please refer to the documentation for the - j parameter.

--no-tablespaces
Do not output commands for selecting tablespaces. With this option, all objects will be created in any default tablespace during the recovery period.
This option only makes sense for plain text format. For the archive format, you can call pg_ Specify this option when restoring.

--no-unlogged-table-data
Do not dump the contents of non logging tables. This option has no effect on whether the table definition (schema) is dumped, it only restricts the dumping of table data. When dumping from a backup server, data in non logging tables is always excluded.

--on-conflict-do-nothing
Add ON CONFLICT DO NOTING to Insert commands. Unless -- inserts, -- column inserts, or -- rows per insert are specified, this option is invalid
--quote-all-identifiers

Force reference to all identifiers. When comparing the main version of AntDB with pg_ This option is recommended when dumping a database on different servers or when the output is ready to be loaded onto a server with different major versions. By default, pg_ Dump only quotes identifiers that are reserved words in their main version. This default behavior can sometimes lead to compatibility issues when dumping other versions of the server, as those versions may have slightly different sets of reserved words. Using -- quote all identifiers can prevent this issue, but the cost is that the dump script is harder to read.

--rows-per-insert=nrows
Dump data as an Insert command (instead of COPY). Control the maximum number of lines for each Insert command. The specified value must be greater than zero. Any errors during reloading will result in the loss of problematic Insert related rows, rather than the entire table content.

--section=sectionname
Dump only named sections. The name of a section can be pre data, data, or post data. This option can be specified multiple times to select multiple sections. The default is to dump all sections.
The data section contains real table data, large object content, and sequence values. The data postterms include definitions of indexes, triggers, rules, and constraints other than verified check constraints. The data header includes all other data definition items.

--serializable-deferrable
Use a serializable transaction for the dump to ensure that the snapshot used is consistent with the subsequent database state. But doing so is waiting for a point in the transaction flow where there cannot be any exceptions, so there will be no dump failures or other transactions with serialization_ The risk of failure rollback. For details on transaction isolation and concurrency control, please refer to Chapter 13.

For a dump that exists only for disaster recovery, this option has little benefit. This option is helpful if a dump is used to load a copy of the database for reports or other read-only workloads during continuous updates to the original database. Without this option, the dump may reflect a state that is inconsistent with any execution sequence of the final commit transaction. For example, if batch processing technology is used, a batch can be displayed as closed in the dump without any items appearing.

If pg_ If there are no read/write transactions active when dump is started, then this option is no different. If there are read and write transactions active, the start of the dump may be delayed for an uncertain period of time. Once running, the performance of this switch is the same.

--snapshot=snapshotname
When performing a database dump, specify a synchronized snapshot (see Table 9.88 for details).

This option can be used when synchronizing the dump with a logical copy slot (see Chapter 48) or a concurrent session.

In the case of parallel dumping, the snapshot name specified by this option will be used instead of taking a new snapshot.

--strict-names
Require each schema (- n/-- schema) and table (- t/-- table) qualifier to match at least one schema/table in the database to be dumped. Note that if no such pattern/table qualifier match is found, even if there is no -- strict names, pg_ Dump will also generate an error.

This option has no effect on - N/-- exclude schema, - T/-- exclude table, or -- exclude table data. Exclusion patterns that cannot match any objects will not be treated as errors.

--use-set-session-authorization
The output SQL - standard SET SESSION Authorization command replaces the Alter Owner command to determine all relationships of objects. This makes the dump more compatible with standards, but depending on the history of the objects in the dump, the dump may not be able to recover properly. Moreover, a dump that uses SET SESSION Authorization will definitely require super user privileges to recover correctly, while Alter Owner requires fewer privileges.

-?
--help
Display information about pg_ Help with dump command line parameters and exit.
The following command-line options control database connection parameters.

-d dbname
--dbname=dbname
Specify the name of the database to connect to. This is equivalent to specifying dbname as the first non option parameter on the command line. Dbname can be a connection string. If so, the connection string parameter will overwrite all conflicting command-line options.

-h host
--host=host
Specify the host name of the machine on which the server is running. If the value starts with a slash, it is used as a directory for a Unix domain socket. The default is obtained from the PGHOST environment variable (if set), otherwise a Unix domain socket connection will be attempted.

-p port
--port=port
Specify the TCP port or local Unix domain socket file extension on which the server is listening for connections. The default is placed in the PGPORT environment variable (if set), otherwise the default value compiled in the program will be used.

-U username
--username=username
Which user do you want to connect as.

-w
--no-password
Never issue a password prompt. If the server requires password authentication and there is no other way to provide a password (such as a. pgpass file), the connection attempt will fail. This option is useful for batch tasks and scripts, as there is no user in it to enter a password.

-W
--password
Force pg_ Dump prompts for a password before connecting to a database.

This option is never mandatory because if the server requires password authentication, pg_ Dump will automatically prompt for a password. However, pg_ Dump will waste one 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
Specify a role name to use to create the dump. This option causes pg_ Dump issues a SET ROLE rolename command after connecting to the database. When an authenticated user (specified by - U) is missing pg_ This option is useful when dumping the required privileges but being able to switch to a role with the required rights. Some installations have a policy for logging in directly as a super user, which allows the dump to be completed without violating this policy.
pg_dump example 1:

$ pg_dump mydb > db.sql             #Dump a database mydb to an SQL script file
$ psql -d newdb -f db.sql           #Reload such a script into a newly created database named newdb
$ pg_dump -Fc mydb > db.dump        #Dump a database to a custom format archive file
$ pg_dump -Fd mydb -f dumpdir       #Dump a database to a directory format archive
$ pg_dump -Fd mydb -j 5 -f dumpdir  #Dump a database into a directory format archive using 5 parallel worker tasks
$ pg_dump -t mytab mydb > db.sql    #To dump a table named mytab

# To dump all tables in the destroy mode with names starting with emp, exclude those named employee_ Table of logs
$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

#To dump all patterns with names starting with east or west and ending with gsm, exclude any patterns with names containing the word test
$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

#To dump, except for the name with ts_ All database objects except for the table at the beginning
$ pg_dump -T 'ts_*' mydb > db.sql

```