Go Backup and recovery of PostgreSQL pg_dump&psql data

Tag: User CTI SSI create cancels VAR use SSE byte

Transferred from: https://www.cnblogs.com/chjbbs/p/6480687.html

Usage:
Pg_dump [OPTION] ... [DBNAME] The database name is last, not specified by default is the database specified by the system variable pgdatabase.

General Options: (generic option)
-F,--file=filename output file or directory nameFile name saved after export
-F,--format=c|d|t|p output file format (custom, directory, tar,Format of the exported file
Plain text (default))
-j,--jobs=num Use this many parallel jobs to dumpNumber of parallel
-V,--verbose verbose modeVerbose mode
-V,--version output version information, then exitOutput version information, and then exit
-Z,--compress=0-9 compression level for compressed formatsCompression level in compressed format
--lock-wait-timeout=timeout fail after waiting timeout for a table lockOperation failed after waiting for table lock timeout
-?,--Help show this help and then exitDisplay this help message, and then exit

Options controlling the Output content: (option to control outputs)
-A,--data-only dump only the data, not the schemaExport data only, excluding patterns
-B,--blobs include large objects in dumpInclude large objects in a dump
-C,--clean Clean (drop) database objects before recreatingClear (delete) database objects before re-creation
-C,--create include commands to the CREATE database in dumpInclude commands in the dump to create the database (including the build database statement without having to build the databases before importing)
-E,--encoding=encoding dump the data in encoding encodingDumps data encoded in encoding form
-N,--schema=schema dump the named schema (s) onlyDumps only the pattern of the specified name
-N,--exclude-schema=schema do not dump the named schema (s)Do not dump a named pattern
-O,--oids include OIDs in dumpInclude OID in dump
-O,--no-owner skip restoration of object ownership inIn clear text format, the owner of the recovery object is ignored
Plain-text format
-S,--schema-only dump only the schema, no dataDump mode only, excluding data (do not export data)
-S,--superuser=name superuser user NAME to use in plain-text formatIn the dump, the specified super user name
-T,--table=table dump the named table (s) onlyDumps only tables with the specified name
-T,--exclude-table=table do not dump the named table (s)Dumps only tables with the specified name
-X,--no-privileges do not dump privileges (Grant/revoke)Do not dump permissions (Grant/revoke)
--binary-upgrade for use by upgrade utilities onlyCan only be used by the upgrade tool
--column-inserts dump data as INSERT commands with column namesDump data in the form of an insert command with a column name
--disable-dollar-quoting Disable dollar quoting, use SQL standard quotingCancel Dollar (sign) quotation marks, using SQL standard quotation marks
--disable-triggers disable triggers during data-only restoreDisabling triggers during data recovery only
--exclude-table-data=table do not dump data for the named table (s)Dump data in the form of an insert command instead of a copy command
--inserts dump data as INSERT commands, rather than COPY
--no-security-labels do not dump security label assignments
--no-synchronized-snapshots don't use synchronized snapshots in parallel jobs
--no-tablespaces do not dump tablespace assignmentsDo 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
--section=section Dump named section (Pre-data, data, or Post-data)
--serializable-deferrable wait until the dump can run without anomalies
--use-set-session-authorization
Use SET SESSION AUTHORIZATION commands instead of
ALTER OWNER commands to set ownership

Connection options: (option to control connections)
-D,--dbname=dbname database to dumpDatabase name
-H,--host=hostname database server host or socket directoryThe host name or socket directory of the database server
-p,--port=port database server port numberPort number of the database server
-U,--username=name Connect as specified database userJoins with the specified database user
-W,--no-password never prompt for passwordNever prompt to enter a password
-W,--password force password prompt (should happen automatically)Mandatory password prompt (automatic)
--role=rolename do SET role before dump

If No database name is supplied and then the pgdatabase environmentIf the database name is not provided, the value of the PGDATABASE environment variable is used.
Variable value is used.

Report bugs to <[email Protected]>.

One: script in plain file format:
Example:
1. Export only data from the Postgres database, not including mode-s
Pg_dump-u postgres-f/postgres.sql-s postgres (database name)
2. Export the Postgres database (including data)
Pg_dump-u postgres-f/postgres.sql postgres (database name)
3. Export the data from the table test01 in the Postgres database
Create DATABASE "test01" with owner= "Postgres" encoding= ' utf-8 ';(single quotation marks, double quotes cannot be wrong)
Pg_dump-u postgres-f/postgres.sql-t test01 postgres (database name)
4. Export the data from the table test01 in the Postgres database, in the form of an INSERT statement
Pg_dump-u postgres-f/postgres.sql-t test01--column-inserts postgres (database name)
5. Recovering data to the BK01 database
Psql-u postgres-f/postgres.sql bk01

second, use the archive file format:
Pg_restore
Use Pg_restore Plain text to recover scripts in plain text format, unable to recover
[Email protected] postgres-9.3.5]# pg_restore-u postgres-d bk01/mnt/hgfs/window\&ubuntu\ shared\ Folder/vendemo. Sql
Pg_restore: [archiver] Input file appears to BES a text format dump. Please use Psql.

Pg_restore and archive file formats use the Rebuild database together.

1. Backup First:
Pg_dump-u postgres-f t-f/vendemo.tar Vendemo back up with more than 800 K
. Recovery:
Pg_restore-u postgres-d Bk01/vendemo.tar
2. Backup First:
Pg_dump-u postgres-f c-f/vendemo.tar Vendemo back up with more than 300 K
. Recovery:
Pg_restore-u postgres-d Bk01/vendemo.tar

three, compressed backup and recovery:
Processing large database:
1. Use a compressed dump. Use your familiar compression program, such as Gzip.
  Backup first:
   pg_dump-u postgres Vendemo | gzip >/vendemo.gz back up only 30 k
 . Recovery:
  & Nbsp;gunzip-c/vendemo.gz | Psql-u postgres bk02
  or
   cat/vendemo.gz | gunzip | psql-u postgres bk02
2. Using split: Split The command allows you to use the following method to decompose the output into The operating system can accept the size. For example, let each block size be 1 megabytes:  
  First backup:
   pg_dump-u postgres-d Vendemo | split-b 100k-/vend/vend
&nb Sp   The appearance is   VENDAA 100k
  &NBSP;VENDAC 100k
   vendad 16k
  restore:
  CAT/ vend/vend* | Psql-u postgres bk02

(GO) PostgreSQL pg_dump&psql data backup and recovery