POSTGRESS database backup and recovery

Pg_dump
Name
Pg_dump -- extract a PostgreSQL database to a script file or other archive files.
Synopsis
Pg_dump [options...] [dbname]

Description
Pg_dump is a tool that saves a PostgreSQL database to a script or archive file. the script file is in plain text format and contains many SQL commands that can be used to reconstruct the database and restore it to the state when it is saved as a script. to restore these scripts, use psql. They can even be used to reconstruct the database on machines on other machines or even on machines with other hardware systems. By modifying the script, you can even reconstruct the database on other SQL database products.

In addition, there are also candidate archive file formats that can be used together with pg_restore to recreate the database, and they also allow pg_restore to choose what to recover, or even sort the items to be restored before restoration. archive files are also designed to be portable across platforms.

Pg_dump stores the information required for recreating all user-defined types, functions, tables, index aggregation, and operators. in addition, all data is copied in text format, so it can be easily copied back and easily edited using tools.

If a candidate file format is combined with pg_restore, pg_dump provides a flexible archiving and transmission mechanism. pg_dump can be used to back up the entire database, and then you can use pg_restore to check the archive and/or select the database to be restored. the most flexible output file format is "M (custom)" format (-Fc ). it allows you to select and re-arrange archive elements. The compressed .tar format (-Ft) is not compressed and cannot be re-arranged during loading. However, it is flexible, it can be processed using other tools, such as tar.

When running pg_dump, we should check the output to see if any warning exists (printed on a standard error), especially the restrictions listed below.

Even when the database is used in parallel, pg_dump also produces a consistent backup. pg_dump does not block other users from accessing the database (read or write ).

Option
The following command line parameters are used to control the output format.

Dbname
Declare the name of the database to be dumped. If this parameter is not declared, use the environment variable PGDATABASE. If the environment variable is not declared, use the username that initiates the connection.

-
-- Data-only
Only output data, no output structure (table definition ).

This option is only valid for plain text format. For other formats, you can declare the option when calling pg_restore.

-B
-- Blobs
Dump data and BLOB data.

-C
-- Clean
Output the command to clear (delete) The database object before creating the database creation command.

This option is only valid for plain text format. For other formats, you can declare the option when calling pg_restore.

-C
-- Create
The output starts with a command for creating the database and connecting to the database. (if it is a script in this form, it does not matter which database you connect to before running the script .)

This option is only valid for plain text. For other formats, you can declare this option when calling pg_restore.

-D
-- Inserts
INSERT command (instead of COPY) to output data. This will lead to slow recovery, but makes archiving easier to transplant to other SQL databases.

-D
-- Column-inserts
-- Attribute-inserts
Dumping data into an INSERT command with a specific field name will lead to slow recovery, but it is required if you want to rearrange the field order.

-F file
-- File = file
Sends the output to the specified file. If this is ignored, the standard output is used.

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

P
Output plain text SQL script file (default)

T
Output tar archive files suitable for input to pg_restore. this archive allows the database to be restored and/or the table structure is excluded. at the same time, you may also be able to limit the data to be restored during restoration.

C
The output is suitable for custom archiving for pg_restore. This is the most flexible format and allows you to rearrange the loaded data and outline elements. This format is compressed by default.

-I
-- Ignore-version
Ignore version differences between pg_dump and database server.

Pg_dump can be used to process databases from previous PostgreSQL versions, but older versions are not supported (up to 7.0 currently ). If you need to use this option only when cross-version check is required (and, for example, pg_dump is invalid, don't say I didn't warn you ).

-O
-- Oids
Output the Object ID (OID) for each table ). if your application references the OID field to some extent (for example, used in foreign key constraints ). use this option. otherwise, this option should not be used.

-O
-- No-owner
Do not set the object ownership to the corresponding source database. generally, pg_dump issues (psql exclusive) \ connect statements to set the ownership of outline elements. see the-R and-X use-set-session-authorization options. note that-O does not prevent all database reconnections, but only prevents the connections for adjusting permissions.

This option is only valid for plain text format. For other formats, you can declare this option when calling pg_restore.

-R
-- No-reconnect
Disable pg_dump from issuing any \ connect statements.

In plain text output mode, disable pg_dump to output the script that needs to be re-connected to the database during database recovery. generally, the recovery script needs to be connected multiple times with different users to set the Initial ownership of the object. this option is a rather rough command because it causes pg_dump to lose this permission information unless you use the-X use-set-session-authorization option.

One possible reason we don't want to re-connect during recovery is that Manual Interference (that is, password) may be required for database access ).

This option only makes sense for plain text format. For other formats, you can declare the option when calling pg_restore.

-S
-- Schema-only
Only output the table outline (Definition) without outputting data.

-S username
-- Superuser = username
In some cases, the scripts or archives created by pg_dump must have the superuser access permission, such as when the trigger is disabled or the outline element or even all attributes. this option declares the username used in these scenarios.

-T table
-- Table = table
Only output table data.

-V
-- Verbose
Declares the redundancy mode. In this way, the pg_dump will print the progress information on the standard error.

-X
-- No-privileges
-- No-acl
This prevents the output of ACL (grant/Revoke command) and Table Owner Relationship information.

-X use-set-session-authorization
-- Use-set-session-authorization
Generally, if a script generated by pg_dump (in plain text mode) must change the current database user (for example, setting the correct object ownership), it uses the psql \ connect command. this command actually opens a new connection. In this case, it may need to receive interference (for example, enter a password ). if you use-X use-set-session-authorization, pg_dump will output the set session authorization command. the results are the same, but the user who needs to use the generated script for database restoration is the database superuser. this option effectively overwrites the-R option.

Because set session authorization is a standard SQL command, and \ connect can only be used for psql, this option also theoretically adds the portability of the output script.

This option is only valid for plain text. For other formats, you can declare this option when you call pg_restore.

-Z 0 .. 9
-- Compress = 0 .. 9
Indicates the compression level used in formats that support compression. (currently, only custom formats support compression ).

The following command line parameter controls the database as the connection parameter.

-H host
-- Host = host
Declare the Host Name of the machine on which the server is running. The local Unix Main Control socket is used by default, instead of an IP connection. If the host name starts with an oblique shoulder, it is used as the path of the Unix domain socket.

-P port
-- Port = port
Declares the TCP/IP Port or local Unix Main Control socket file handle that the server is listening for and waiting for connection. The default port number is 5432, or the environmental variable PGPORT value (if any ).

-U username
To provide user identity connection.

-W
Force password prompt. If the server requires password authentication, this action should automatically occur.

Long options are available only on some platforms.

Environment

PGDATABASE
PGHOST
PGPORT
PGUSER
Default connection Parameters

Diagnosis
Connection to database 'template1 'failed.
ConnectDBStart () -- connect () failed: No such file or directory
Is the postmaster running locally
And accepting connections on Unix socket '/tmp/. s. pgsql.5432 '?
Pg_dump cannot be associated with the PostgreSQL server on the specified host and port. If this information is displayed, confirm that the server is running on the port you declared on the specified host.
Dummies quence (table): SELECT failed
You are not authorized to read the database. Contact your PostgreSQL node administrator.
Note: pg_dump uses the SELECT statement internally. If you encounter problems when running pg_dump, make sure you can use programs like psql to SELECT from the database.

Note:
If your installation adds anything to the template1 database, pay attention to restoring the pg_dump output to a truly empty database; otherwise, you may receive the error message caused by the object appended by repeated definitions. to create a database without any local attachments, you can copy data from template0 instead of template1, for example:

Create database foo with template template0;

Pg_dump has several restrictions:

When dumping a table or as a text dump, pg_dump cannot operate large objects. large objects must be dumped in binary archive format as a whole.

When performing a pure data dump, pg_dump sends out some queries. First, close the triggers on the user table, and then insert data. After the data is inserted, it sends a query to open the trigger. if the recovery operation stops in the middle, the system table may be in an error state.

The size of tar archive members is limited to 8 GB. (This restriction is inherent in the tar file format .) Therefore, this format cannot be used for the original representation where the size of a table exceeds this size. The total size of tar archive and any other output formats is unrestricted, but may be limited by the operating system.

Example
Dump a database:

$ Pg_dump mydb> db. out

Reload this database:

$ Psql-d database-f db. out

Output A database named mydb containing BLOB to a tar file:

$ Pg_dump-Ft-B mydb> db.tar

Restore the database (along with BLOB) to an existing database named newdb:

$ Pg_restore-d newdb db.tar

History
The pg_dump tool was first introduced in ipvs95 0.02. The non-plain text output format was introduced in PostgreSQL 7.1.