Pgsql backup pg_dump and restore Pg_restore

A sample that can jump directly to the last face to view

Really did not think, has been the user of PostgreSQL, suddenly need a library transplant has become the first trick! Originally it was quite different from the MySQL command line operation.
Don't be afraid, but be careful, because database operation is the core of the site, once there is damage or loss, the consequences are very serious.
I wrote the steps first, then the plan, and although there was a mistake, it was eventually safe to transplant. It's on the record for later use.
Backup Restore Method:Pg_dump and Pg_restore, first carefully explain the two commands, and then record my method of operation.
Pg_dump--Extract a PostgreSQL database into a script file or other archive file
pg_dump [option ...] [dbname]
options option ...
The following command-line parameters control the content and format of the output.
dbname
Declares the name of the database that will be dumped. If this parameter is not declared, then the environment variable pgdatabase is used. If the environment variable is not declared, then use the user name that originated the connection.
-A
--data-only
Only output data, no output mode (data definition).
This option is only meaningful for plain text formatting. For the archive format, you can declare options when calling Pg_restore.
-B
--blobs
Contains large objects in the dump. You must select a non-text output format.
-C
--clean
Outputs the command to clean (delete) The database object before creating the database Creation command.
This option is only meaningful for plain text formatting. For the archive format, you can declare options when calling Pg_restore.
-C
--create
Output from the beginning of a command that creates the database itself and joins the database. (If this is the form of a script, it doesn't matter which database you join before you run the script.) ）
This option is only meaningful for plain text formatting. For the archive format, you can declare this option when calling Pg_restore.
-D
--inserts
Output the data as an Insert command (not COPY). This can result in a very slow recovery. This option is primarily used to create dumps that can be used for other non-PostgreSQL databases. Note that if you rearrange the order of the fields, recovery may fail completely. -D is more secure, but slower.
-D
--column-inserts
--attribute-inserts
Dumps the data as an INSERT command with an explicit field name. (INSERT into table (column, ...) VALUES ... ）。 This can result in very slow recovery, which is primarily used to make dumps that can be used for other non-PostgreSQL databases.
-F File
--file=file
Sends the output to the specified file. If these are omitted, standard output is used.
-F format
--format=format
Select the format of the output. Format can be one of the following:
P
Output plain Text SQL script file (default)
T
The output is suitable for the TAR archive file entered into the Pg_restore. Using this archive allows you to reorder and/or exclude database objects when you restore the database. It is also possible to limit which data can be recovered at the time of recovery.
C
Outputs a customized archive suitable for use with Pg_restore. This is the most flexible format that allows you to rearrange the data and object definitions that are loaded. This format is compressed by default.
-I.
--ignore-version
Ignores version differences between the Pg_dump and the database server.
Pg_dump can process databases from previous versions of PostgreSQL, but the older versions are not supported (currently supported to 7.0). Use this option if you need to cross the version check (and if Pg_dump fails, don't say I didn't warn you).
-N Namespace
--schema=schema
Only the contents of the schema are dumped. If this option is not declared, all non-system patterns in the target database will be dumped.
Note: In this mode, Pg_dump does not attempt to dump any database objects that may be dependent on any other selected mode. Therefore, the system does not guarantee that a single mode of dump can be successfully restored to a clean database.
-O
--oids
As part of the data, the object identity (OID) is output for each table. If your app refers to the OID field to some extent (for example, used in a foreign key constraint). Then use this option. Otherwise, this option should not be used.
-O
--no-owner
The ownership of the object is not set to the corresponding source database. Typically, Pg_dump emits (psql) the ALTER owner or set SESSION AUTHORIZATION statement to set ownership of the created database object. See also the-R and-X use-set-session-authorization options. Note-O does not prevent all re-joins to the database, only the exclusive join that is being made to adjust permissions.
This option is only meaningful for plain text formatting. For the archive format, you can declare this option when you call Pg_restore.
-R
--no-reconnect
This option is obsolete, but it is still acceptable for backwards compatibility reasons.
-S
--schema-only
Only the object definition (schema) is output, and no data is output.
-S username
--superuser=username
Declares the super user name to use when closing the trigger. It only has a relationship when the--disable-triggers is used. (In general, we'd better not enter this parameter, but instead start the generated script with a super user.) ）
-T table
--table=table
Only the data for table tables is output. It is possible that there are multiple tables with the same name in different modes, and if so, all matching tables will be dumped. Declare both--schema and--table to select only one table.
Note: In this mode, Pg_dump does not attempt to dump any database objects that may be dependent on any other selected table. Therefore, the system does not guarantee that a single table dump can be successfully restored to a clean database.
-V
--verbose
Declares redundancy mode. This will allow the Pg_dump to output detailed object commentary and the dump file's start and stop time and progress information to the standard output.
-X
--no-privileges
--no-acl
Avoid outputting ACL (give/REVOKE command) and table owner relationship information.
-X disable-dollar-quoting
--disable-dollar-quoting
This option turns off using the dollar symbol around the function body. Forces them to enclose in quotation marks of the SQL standard string syntax.
-X Disable-triggers
--disable-triggers
This option is only relevant for creating a dump with only data. It tells Pg_dump to include commands to temporarily close the trigger on the target table when recovering data. If you have referential integrity checks or other triggers on the table and you don't want to reload them when recovering the data, you should use this option.
Currently, commands issued for--disable-triggers must be done by Superuser. Therefore, you should either declare a Superuser name with-s or, preferably, start the generated script with the identity of a superuser.
This option is only meaningful for plain text formatting. For the archive format, you can declare this option when calling Pg_restore.
-X Use-set-session-authorization
--use-set-session-authorization
The output SQL standard SET SESSION AUTHORIZATION command instead of the OWNER to command. Such dumps result in a more complex standard, but depending on the history of the objects in the dump, they may not be restored correctly.
-Z 0..9
--compress=0..9
Declares the level of compression used in those formats that support compression. (Currently only the client-side format supports compression).
The following command-line parameters control the database as a join parameter.
-H Host
--host=host
Declares the host name of the machine on which the server is running. If the value starts with a slash, it is used as the path to the Unix domain socket. The default is obtained from the PGHOST environment variable, if the environment variable is set, otherwise, try a Unix domain socket connection.
-P Port
--port=port
Declares that the server is listening and waits for a joined TCP port or a local Unix master socket file handle. The default is to use the value of the environment variable PGPORT, if one exists, or the default value at compile time.
-u username
To give a user identity join.
-W
Force a password prompt. If the server requires password authentication, then this action should happen automatically.
Pg_restore--Recovers the PostgreSQL database from a backup file created by Pg_dump.
Pg_restore accepts the following command-line arguments.
FileName
Declares the location of the backup file to be recovered. If there is no declaration, standard input is used.
-A
--data-only
Restores only the data, not the table schema (data definition).
-C
--clean
Clean (delete) The database objects before you create them.
-C
--create
Create the database before you restore it. (If this option is present, the database name with-D is only used to issue the original CREATE DATABASE command.) All data is restored to the database where the name appears in the archive. ）
-D dbname
--dbname=dbname
Joins the database dbname and restores it directly to the database.
-E
--exit-on-error
If an error is encountered while sending the SQL command to the database, exit. The default is to continue execution and display an error count at the end of the restore.
-F filename
--file=filename
Declares the output file of the generated script, or the file used for the list when the-l option appears, the default is standard output.
-F format
--format=format
Declares the format of the backup file. Because Pg_restore automatically determines the format, if it is declared, it can be one of the following:
T
A backup is a tar archive. Use this format to allow reordering and/or exclusion of table schema elements when recovering a database. It is also possible to limit the data that is loaded at the time of recovery.
C
The format of the backup is a customized format from Pg_dump. This is the most flexible format, because it allows you to reorder the data and also overload the table schema elements. By default, this format is compressed.
-I.
--ignore-version
Ignores database version checking.
-I. Index
--index=index
Restores only the named index.
-L
--list
Lists the contents of the backup. The output of this operation can limit and rearrange the recovered items with the-l option.
-L List-file
--use-list=list-file
Restores only the elements inside the list-file, in the order in which they appear in the file. You can move individual lines and annotate them by putting '; ' In the beginning of the line. (see below for an example.) ）
-O
--no-owner
Do not export the command that sets the object's permissions so that it matches the original database. By default, Pg_restore emits the ALTER owner or set SESSION AUTHORIZATION statement to set the ownership rights of the created pattern element. If the initial database connection is not initiated by the superuser (or the same user who owns all of the created objects), the statements will fail. With-O, any user can be used for the initial connection, and the user will have all the objects created.
-P Function-name (Argtype [, ...])
--function=function-name (Argtype [, ...])
Restores only the specified named function. Be careful to spell the function name and its arguments exactly as it should be in the dump's list of contents.
-R
--no-reconnect
This option has been deprecated, but is still acceptable in order to keep backwards compatibility.
-S
--schema-only
Restores only the table structure (data definition). If the data is not recovered, the sequence values are reset.
-S username
--superuser=username
Sets the user name of the super user to be declared when the trigger is closed. It is only useful when--disable-triggers is set.
-T table
--table=table
Restores only the definitions and/or data of tables specified by the table.
-T Trigger
--trigger=trigger
Restores only the specified trigger.
-V
--verbose
Declares redundancy mode.
-X
--no-privileges
--no-acl
Avoid the ACL recovery (grant/revoke command).
-X Use-set-session-authorization
--use-set-session-authorization
Output the SQL standard SET SESSION AUTHORIZATION command instead of the OWNER to command. This makes the dump better compatible with the standard, but depending on the history of the objects in the dump, this dump may not be properly restored.
-X Disable-triggers
--disable-triggers
This option is only relevant if you are performing recovery data only. It tells Pg_restore to execute some commands to temporarily close the trigger on the target table when loading the data. You can use this option if you have integrity checks or other triggers on the table, and you don't want to activate them when you load the data.
Currently, the command issued for--disable-triggers must be issued as a superuser. Therefore, you should also use-s to declare a super user name, or better to set up--use-set-session-authorization and run Pg_restore as the PostgreSQL superuser.
Pg_restore also accepts the following command-line arguments as join parameters:
-H Host
--host=host
Declares the host name of the machine that the server is running on. If the value starts 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 will be attempted.
-P Port
--port=port
Declares the TCP port on which the server listens or the local Unix domain socket file extension. The default is the value of the environment variable PGPORT (if set), otherwise the compiler defaults.
-u username
To give a user identity join.
-W
Force a hint to the export order. If the server requires password authentication, this should happen automatically.
The theory is finished, with the above knowledge to do the actual combat becomes easy:
Dbコッピ
/usr/local/pgsql/bin/pg_dump-ft-b zhoz >/home/zhoz/db_zhoz_081121.tar
Move
Scp-v/home/zhoz/db_zhoz_081121.tar [Email protected]:/home/zhoz/
The SCP is also newly learned, very powerful! The parameters are also collected:
-V is the same as-V in most Linux commands to show progress. Can be used to view connections, authentication, or configuration errors.
-C Enable compression option.
-R Copy Folder
-P Select the port. Note-P has been used by RCP.
-4 forcibly use the IPV4 address.
-6 forcibly use the IPV6 address.
エクスポート
/usr/local/pgsql/bin/pg_restore-d zhoz-u zhoz-w/home/zhoz/logs/db_zhoz_081121.tar
If you do not specify-U, it is dangerous to be prompted that the database does not exist or is imported into a non-specified library.
At this point, finish the call! Also mastered a kind of combat technology.
"2009/06/23 added:"
Pg_dumpall > outfile
The generated dump can be restored with Psql:
Psql Template1 < infile
(In fact, you can declare any existing database to be connected, but if you are loading to an empty database, then template1 is your only option.) Restoring Pg_dumpall dumps usually requires database superuser privileges because we need it to recover user and group information.
working with large databases
Because the size of the PostgreSQL allow table is larger than the maximum file size allowed by your system, it may be problematic to dump the table to a file because the resulting file is likely to be larger than the largest file allowed by your system. Because the pg_dump output to standard output, you can bypass this problem with standard Unix tools:
Use a compressed dump. Use your familiar compression program, such as Gzip.

Pg_dump dbname | gzip > filename.gz
Use the following command to restore:
Createdb dbname
Gunzip-c filename.gz | Psql dbname
Or
Cat Filename.gz | Gunzip | Psql dbname

The split command allows you to break the output down to the acceptable size of the operating system in the following way. For example, let each block size be 1 megabytes:
Pg_dump dbname | Split-b 1m-filename
Use the following command to restore:
Createdb dbname
Cat filename* | Psql dbname
Use the customer dump format. If PostgreSQL was made on a system with a zlib compression library installed, the custom dump format would compress the data when it was written to the output file. It generates and uses gzip-like dump files, but it also has an advantage: you can selectively restore the tables in the library. The following command dumps a database in the custom dump format:
PG_DUMP-FC dbname > FileName
The dump of the custom format is not a script, it cannot be used for psql, but it requires a pg_restore dump. Please refer to Pg_dump and Pg_restore's manual for details.

Backup: Pg_dump-h localhost-p 5432-u tradesns-w-F c-b-v-f "/home/tradeworkwangbin/us2010.backup" us2010
Recovery: pg_restore-h 192.168.0.100-p 5432-u postgres-w-D us2011-v "/root/us2010.backup"

PS: "D-us2011" in the "us2011" database needs to be established beforehand, or will be error.

Note
In a backward-compatible consideration, the default pg_dump does not dump large objects. To dump large objects, you must use the custom or TAR output format and use the-B option in Pg_dump.

Original address: http://www.cnblogs.com/wangbin/archive/2009/08/14/1546009.html

Pgsql backup pg_dump and restore Pg_restore (EXT)