Pg_dump Example Detailed

first, the use of Pg_dump:
Import and export of databases is one of the most commonly used features, each of which provides tools such as Oracle's Exp/imp,informix Dbexp/dbimp,mysql mysqldump, and PostgreSQL provides a corresponding tool for Pg_ Dump and Pg_restore.
Pg_dump is a tool for backing up the PostgreSQL database. It can perform a full and consistent backup while the database is in use, without blocking access to the database by other users.
The dump format can be either a script or an archive file. The format of the dump script is plain text and contains a number of SQL commands that can be used to rebuild the database and restore it to its state when the script is saved. You can use Psql to recover from such a script. They can even be used to rebuild a database on other machines and even on other hardware systems, by making some modifications to the script and even rebuilding the database on other SQL database products.
The archive file format must be used with Pg_restore to rebuild the database. They allow Pg_restore to choose what to recover, even to reorder the items that need to be recovered before they are restored. Archive files can also be ported across platforms.
D:\Program Files\powercmd>pg_dump--help
Pg_dump dumps a database to a plain text file or to a different format.
Usage: pg_dump [options] ... [Database name]
General Options:
-F,--file=filename output file or directory name
-F,--format=c|d|t|p output file format (custom, directory, tar, plain text)
-V,--verbose verbose mode
-Z, compression level of--compress=0-9 compressed format
--lock-wait-timeout=timeout operation failed after waiting for table lock timeout
--HELP displays this help information, and then exits
--versoin output version information, and then exit
Control output Content options:
-A,--data-only only dumps data, excluding patterns
-B,--blobs includes large objects in the dump
-C,--clean to clear (delete) database objects before recreating them
-C,--create includes commands in the dump to create the database
-E,--encoding=encoding dumps data encoded in encoding form
-N,--schema=schema only dumps the pattern of the specified name
-N,--exclude-schema=schema does not dump named schemas
-O,--oids including OIDs in dumps
-O,--no-owner in clear text format, ignoring the owner of the recovery object
-S,--schema-only only dump mode, excluding data
-S,--superuser=name in the dump, the specified super user name
-T,--table=table only dumps the table with the specified name
-T,--exclude-table=table only dumps the table with the specified name
-X,--no-privileges do not dump permissions (Grant/revoke)
--binary-upgrade can only be used by the upgrade tool
--column-inserts dump data in the form of an insert command with a column name
--disable-dollar-quoting Cancel Dollar (symbol) quotes, using SQL standard quotes
--disable-triggers disable triggers in the process of recovering data only
--inserts dump data in the form of the Insert command instead of the copy command
--no-security-labels don't dump security label assignments
--no-tablespaces do not dump table space allocation information
--no-unlogged-table-data do not dump unlogged table data
--quote-all-identifiers quote All identifiers, even if not key words
--serializable-deferrable wait until the dump can run without anomalies
--use-set-session-authorization
Use the session AUTHORIZATION command instead of the alter OWNER command to set ownership
Join options:
-H,--host= hostname or socket directory of the host name database server
-P, port number of the--port= port number database server
-U,--username= name to join the specified database user
-W,--no-password never prompts to enter a password
-W,--password force password prompt (automatic)
--role=rolename do SET role before dump
If no database name is provided, the value of the PGDATABASE environment variable is used.

Second, pg_dump usages
1, creating two databases
Create DATABASE "TESTDB1"
  with OWNER = "TestRole1"
& nbsp;      ENCODING = ' UTF8 '
       tablespace = ' TESTTBS1 ";
CREATE DATABASE "TestDb2"
  with OWNER = "TestRole1"
       ENCODING = ' UT F8 '
       tablespace = "TESTTBS1";
Create table Csm_bill, Cfg_public_int_ in TESTDB1 Transport inserts several records and creates an index that uses the index tablespace testtbsindex.

2. Migrate only the database structure:
E:\>pg_dump-u testrole1-s-F testdb1.sql TestDb1
Password:
-U TestRole1 and Super User-U postgres the results are exactly the same:
E:\>pg_dump-u postgres-s-F testdb11.sql TestDb1
E:\>psql-u testrole2-f testdb1.sql TestDb2 >a.txt 2>&1
Password for user TestRole2:
When importing, using-u TestRole2 often has a lot of permissions, to successfully import the owner who needs to modify the related database objects, it is best to use the Superuser-u postgres:
E:\>psql-u postgres-f testdb1.sql TestDb2 >a.txt 2>&1
Do not dump permissions option:-X
E:\>pg_dump-u postgres-x-s-f Testdb12.sql TestDb1
Testdb12.sql a few lines less than Testdb1.sql:

In order to run Testdb1.sql multiple times, you can start the next two lines at the file:
Drop schema public cascade;
Create schema public;
or use the-C option:
E:\>pg_dump-u postgres-c-x-s-F testdb13.sql TestDb1
Testdb13.sql the following lines more than Testdb1.sql:

At this point, you can run multiple times:
E:\>psql-u postgres-f testdb13.sql TestDb2 >a.txt 2>&1
However, if two libraries have different tables or indexes, the first method should be used, because the second method will complain when no database objects are found.

3, migrate the database structure and data (can realize the database backup and restore)
Copy of data using the Copy command:
E:\>pg_dump-u Postgres Testdb1>testdb14.sql

Copy of data using INSERT statement:
E:\>pg_dump-u Postgres--column-inserts Testdb1>testdb15.sql

4, the remote Linux on the PostgreSQL on the CPOST database structure to migrate to the local PostgreSQL
(1) Building a very similar environment locally
Create user "Cpost" Inherit createdb;
Create Tablespace "Pis_data" owner Cpost location ' E:\postgresql/data/pis_data ';
Create Tablespace "Pis_index" owner Cpost location ' E:\postgresql/data/pis_index ';
The remote database Cpost still uses the default table space:
CREATE DATABASE Cpost
With OWNER = Cpost
--encoding = ' LATIN9 '
Tablespace = Pg_default
--lc_collate = ' C '
--lc_ctype = ' C '
CONNECTION LIMIT =-1;
Using the above three parameters error, the completed database is as follows:
CREATE DATABASE Cpost
With OWNER = Cpost
ENCODING = ' UTF8 '
Tablespace = Pg_default
Lc_collate = ' Chinese (Simplified) _people ' s Republic of china.936 '
Lc_ctype = ' Chinese (Simplified) _people ' s Republic of china.936 '
CONNECTION LIMIT =-1;
(2) Using pg_dump migration table structure
With the-h option, the exported SQL file is stored directly locally:
E:\>pg_dump-h 132.10.10.11-p 1234-u cpost-x-s-f cpost.sql
E:\>psql-u postgres-f Cpost.sql
The import was successful, but an error was reported:
Psql:cpost.sql:22:error:character 0xe99499 of encoding "UTF8" has no equivalent in "LATIN9"
Character set error, character set problem see my other blog: from the PostgreSQL area and character set.

third, the use of Pg_dump and Pg_restore to implement database backup and recovery
E:\>pg_restore--help
Pg_restore restores a PostgreSQL database created by Pg_dump from an archive.
Usage:
Pg_restore [Options] ... FileName
General Options:
-D,--dbname= name connection database name
-F,--file= filename output file name
-F,--format=c|d|t backup file format (should be automatic)
-L,--list Print archive overview of TOC
-V,--verbose verbose mode
--HELP displays this help information, and then exits
--version output version information, and then exit the recovery control option:
-A,--data-only only restores data, excluding patterns
-C,--clean need to clear (remove) database objects before recreating them
-C,--create to create the target database
-E,--exit-on-error error exits, default to continue
-I,--index= name to restore the index of the specified name
-j,--jobs=num can perform multiple tasks to restore work in parallel
-L,--use-list= file name to sort output from this file using the specified table of contents
-N,--schema=name only restores objects in this mode
-O,--no-owner ignores the person who owns the recovery object
-P,--function= name (parameter) restore function with specified name
-S,--schema-only only recovery mode, excluding data
-S,--superuser=name uses the specified superuser to disable the trigger
-T,--table=name restores the table with the specified command word
-T,--trigger=name the trigger to restore the specified word
-X,--no-privileges Bypass processing permission recovery (grant/revoke)
-1,--single-transaction recovery as a single transaction
--disable-triggers disable triggers in the process of recovering data only
--no-data-for-failed-tables did not recover data that could not create the table
--no-security-labels Don't restore security labels
--no-tablespaces do not restore allocation information for tablespace
--use-set-session-authorization to set object ownership using the session Authorization command in place of the alter owner command
Join options:
-H,--host= hostname or socket directory of the host name database server
-P, port number of the--port= port number database server
-U,--username= name to join the specified database user
-W,--no-password never prompts to enter a password
-W,--password force password prompt (automatic)
--role=rolename performs a set role operation before resuming
If no input file name is provided, standard input is used.
1. Backup and restore using the dump format:
E:\>pg_dump-u POSTGRES-FC TestDb1 >testdb1.dump
postgres=# drop Database "TESTDB2";
DROP DATABASE
postgres=# CREATE DATABASE "TESTDB2"
postgres-# with owner= "TestRole2"
postgres-# tablespace= "TESTTBS2";
CREATE DATABASE
E:\>pg_restore-u postgres-d TestDb2 testdb1.dump >a.txt 2>&1
2. Backup and restore using the TAR format:
E:\>pg_dump-u postgres-ft Testdb1>testdb1.tar
postgres=# drop Database "TESTDB2";
DROP DATABASE
postgres=# CREATE DATABASE "TESTDB2"
postgres-# with owner= "TestRole2"
postgres-# tablespace= "TESTTBS2";
CREATE DATABASE
E:\>pg_restore-u postgres-d TestDb2 Testdb1.tar >a.txt 2>&1