Backup and recovery of PostgreSQL pg_dump&psql data

Source: Internet
Author: User
Tags postgresql psql postgres database server port

Usage:
Pg_dump [OPTION] ... [DBNAME] DatabaseLast name, do not specify the 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, compress backup and restore:
Process Big Data library:
1. Use compressed dumps. 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 look is   VENDAA 100k
   vendab 100k
   vendac 100k
   vendad 16k
 . Recovery:
  cat/vend/vend* | psql-u postgres bk02

Backup and restore of PostgreSQL pg_dump&psql data

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.