PostgreSQL: Backup and Restoration

Logical backup and Restoration

Pg_dump.exe logical backup

• Is a client Tool
• Standard SQL files are generated.
• You can select different output formats for packaging, compression, and custom formats.
• Data Consistency, because PostgreSQL databases are MVC-specific.

 

Restoration corresponding to pg_dump.exe output backup

• What kind of output requires? What kind of reverse operation is to obtain standard SQL input.

 

 

Pg_dump.exe OptionPg_dump[Option]... [database name]

• Pg_dump.exe -- no-owner -- no-privileges -- create
• Pg_dump.exe -- no-owner -- no-privileges -- create -- Inserts

General options:
-F, -- file = file name output file name
-F, -- format = c | T | P output file format (custom, tar, plaintext)
-I, -- ignore-version: the server continues to run when the server version does not match the pg_dump version.
-V, -- verbose detailed mode
-Z, -- compress = 0-9 compression level of the compressed format
-- help displays this help information, then exit
-- version to output version information, and then exit

Control output content options:
-A, -- Data-onlyDump only the data, not the schema -- only backup data. When the structure of the restored database is exactly the same as that of the backup database!

-S, -- Schema-only Dump only the schema, no data -- only backup structure. Used to generate structure scripts

 

 -C, -- cleanClean (drop) schema prior to create -- regenerate the target table. And -- create are incompatible options. The restored database should exist !!
 -C, -- create Include commands to create database in dump -- create a database to create a full backup. And -- clean are incompatible options. In general, this option does not work !!

 

-O, -- no-ownerSkip restoration of object ownershipin plain text format
-X, -- no-PrivilegesDo not dump privileges (grant/revoke)

 

-T, -- table = tableDump the named table (s) only
-T, -- exclude-table = tableDo not dump the named table (s)

-N, -- schema = SchemaDump the named Schema (s) only -- specify the schema to export
-N, -- exclude-schema = SchemaDo not dump the named Schema (s) -- specify the schema to be excluded

 

-B, -- blobs include large objects in dump
 -D, -- Inserts Dump data as insert commands, rather than copy
 -D, -- column-InsertsDump data as insert commands with column names
-E, -- encoding = encoding dump the data in encoding Encoding

 

-O, -- oids include oids in dump

-S, -- superuser = Name specify the superuser user name to use in plain text format

-- Disable-dollar-quoting disable dollar quoting, use SQL standard quoting
-- Disable-triggers disable triggers during data-only restore
-- Use-set-session-Authorization use session authorization commands instead of alter owner commands to set ownership

Join options:
-H, -- Host = Host Name the host name or socket directory of the Database Server
-P, -- Port = port number: database server port number
-U, -- username = Name is connected to the specified database user
-W, -- password forced password prompt (automatic)

If the database name is not provided, use the pgdatabase environment variable
.