PG_备份还原系列之逻辑备份

基本概念

pg_dump — 把PostgreSQL数据库抽取为一个脚本文件或其他归档文件。

pg_dumpall — 将一个PostgreSQL数据库集簇抽取到一个脚本文件中

区别在于：后者是将postgresql整个转储到一个脚本文件中。

pg_restore –恢复pg_dump自定义备份或tar类型的备份，可以选择性地恢复部分数据。

实例

1. 单个库备份
pg_dump -h 127.0.0.1 testdb_old -U postgres -F c >testdb_old.dump

2. 备份整库表结构
pg_dump –s –U postgres auth > auth.sql

3. 备份单个schema
pg_dump testdb_old -F c -n test_dump -v -h 127.0.0.1 > test_dump.dump

4. 备份单个schema的表结构
pg_dump testdb_old -F c -n test_dump -s -v -h 127.0.0.1 > test_dump_str.dump

5.备份多张表
pg_dump testdb_old -F c  -t test_dump.test111 -t public.nb_tab_engine -h 127.0.0.1 -v > test_dump_test111.dump

6. pg_restore还原
pg_restore -d duanlei test_dump_test111.dump -h 127.0.0.1

实际案例：

--备份还原整个库
方法1：
--1.备份整个数据库
pg_dump -U duanlei -d duanlei >duanlei.dump
--2.还原(pg_dump生成的转储包不包含建库、建用户、建表空间语句；需要先建用户、库、指定表空间)
--2.1建用户
create user duanlei inherit password 'duanlei';
--2.2postgres用户登录建库 
create database duanlei template template0 owner duanlei;
--2.3执行还原
psql -U duanlei -d duanlei -1 -f duanlei.dump
--3.收集统计信息
psql -U duanlei -d duanlei -c 'analyze;'


方法2：跨服务器转储
--1.建用户
create user duanlei inherit password 'duanlei';
--2.postgres用户登录建库 
create database duanlei template template0 owner duanlei;
--3.psql+pg_dump转储
pg_dump -h host1 dunalei |psql -h host2　duanlei
--4.收集统计信息
psql -U duanlei -d duanlei -h host2 -c 'analyze;'

 

 

参数：

pg_dump dumps a database as a text file or to other formats.

 

Usage:

  pg_dump [OPTION]... [DBNAME]

 

General options:

  -f, --file=FILENAME          output file or directory name

  -F, --format=c|d|t|p         output file format (custom, directory, tar,

                               plain text (default))

  -j, --jobs=NUM               use this many parallel jobs to dump

  -v, --verbose                verbose mode

  -V, --version                output version information, then exit

  -Z, --compress=0-9           compression level for compressed formats

  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock

  --no-sync                    do not wait for changes to be written safely to disk

  -?, --help                   show this help, then exit

 

Options controlling the output content:

  -a, --data-only              dump only the data, not the schema

  -b, --blobs                  include large objects in dump

  -B, --no-blobs               exclude large objects in dump

  -c, --clean                  clean (drop) database objects before recreating

  -C, --create                 include commands to create database in dump

  -E, --encoding=ENCODING      dump the data in encoding ENCODING

  -n, --schema=PATTERN         dump the specified schema(s) only

  -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)

  -O, --no-owner               skip restoration of object ownership in

                               plain-text format

  -s, --schema-only            dump only the schema, no data

  -S, --superuser=NAME         superuser user name to use in plain-text format

  -t, --table=PATTERN          dump the specified table(s) only

  -T, --exclude-table=PATTERN  do NOT dump the specified table(s)

  -x, --no-privileges          do not dump privileges (grant/revoke)

  --binary-upgrade             for use by upgrade utilities only

  --column-inserts             dump data as INSERT commands with column names

  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting

  --disable-triggers           disable triggers during data-only restore

  --enable-row-security        enable row security (dump only content user has access to)

  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)

  --extra-float-digits=NUM     override default setting for extra_float_digits

  --if-exists                  use IF EXISTS when dropping objects

  --inserts                    dump data as INSERT commands, rather than COPY

  --load-via-partition-root    load partitions via the root table

  --no-comments                do not dump comments

  --no-publications            do not dump publications

  --no-security-labels         do not dump security label assignments

  --no-subscriptions           do not dump subscriptions

  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs

  --no-tablespaces             do not dump tablespace assignments

  --no-unlogged-table-data     do not dump unlogged table data

  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands

  --quote-all-identifiers      quote all identifiers, even if not key words

  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts

  --section=SECTION            dump named section (pre-data, data, or post-data)

  --serializable-deferrable    wait until the dump can run without anomalies

  --snapshot=SNAPSHOT          use given snapshot for the dump

  --strict-names               require table and/or schema include patterns to

                               match at least one entity each

  --use-set-session-authorization

                               use SET SESSION AUTHORIZATION commands instead of

                               ALTER OWNER commands to set ownership

 

Connection options:

  -d, --dbname=DBNAME      database to dump

  -h, --host=HOSTNAME      database server host or socket directory

  -p, --port=PORT          database server port number

  -U, --username=NAME      connect as specified database user

  -w, --no-password        never prompt for password

  -W, --password           force password prompt (should happen automatically)

  --role=ROLENAME          do SET ROLE before dump

 

pg_restore restores a PostgreSQL database from an archive created by pg_dump.

 

Usage:

  pg_restore [OPTION]... [FILE]

 

General options:

  -d, --dbname=NAME        connect to database name

  -f, --file=FILENAME      output file name (- for stdout)

  -F, --format=c|d|t       backup file format (should be automatic)

  -l, --list               print summarized TOC of the archive

  -v, --verbose            verbose mode

  -V, --version            output version information, then exit

  -?, --help               show this help, then exit

 

Options controlling the restore:

  -a, --data-only              restore only the data, no schema

  -c, --clean                  clean (drop) database objects before recreating

  -C, --create                 create the target database

  -e, --exit-on-error          exit on error, default is to continue

  -I, --index=NAME             restore named index

  -j, --jobs=NUM               use this many parallel jobs to restore

  -L, --use-list=FILENAME      use table of contents from this file for

                               selecting/ordering output

  -n, --schema=NAME            restore only objects in this schema

  -N, --exclude-schema=NAME    do not restore objects in this schema

  -O, --no-owner               skip restoration of object ownership

  -P, --function=NAME(args)    restore named function

  -s, --schema-only            restore only the schema, no data

  -S, --superuser=NAME         superuser user name to use for disabling triggers

  -t, --table=NAME             restore named relation (table, view, etc.)

  -T, --trigger=NAME           restore named trigger

  -x, --no-privileges          skip restoration of access privileges (grant/revoke)

  -1, --single-transaction     restore as a single transaction

  --disable-triggers           disable triggers during data-only restore

  --enable-row-security        enable row security

  --if-exists                  use IF EXISTS when dropping objects

  --no-comments                do not restore comments

  --no-data-for-failed-tables  do not restore data of tables that could not be

                               created

  --no-publications            do not restore publications

  --no-security-labels         do not restore security labels

  --no-subscriptions           do not restore subscriptions

  --no-tablespaces             do not restore tablespace assignments

  --section=SECTION            restore named section (pre-data, data, or post-data)

  --strict-names               require table and/or schema include patterns to

                               match at least one entity each

  --use-set-session-authorization

                               use SET SESSION AUTHORIZATION commands instead of

                               ALTER OWNER commands to set ownership

 

Connection options:

  -h, --host=HOSTNAME      database server host or socket directory

  -p, --port=PORT          database server port number

  -U, --username=NAME      connect as specified database user

  -w, --no-password        never prompt for password

  -W, --password           force password prompt (should happen automatically)

  --role=ROLENAME          do SET ROLE before restore