pg_dumpall 与pg_dump实际使用 postgresSQL client 备份方法

最新推荐文章于 2024-07-15 15:10:32 发布

abka

最新推荐文章于 2024-07-15 15:10:32 发布

阅读量4.6k

点赞数

分类专栏：数据库文章标签： postgresql

本文链接：https://blog.csdn.net/asdcls/article/details/121631813

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

postgresSQL 备份数据的命令，可以使用pg_dumpall 和pg_dump

pg_dumpall 可以备份全库数据，但是不建议每天使用pg_dumpall备份。因为pg_dumpall仅支持导出为SQL文本格式，而这种庞大的文本备份来进行全库级别的数据库恢复时及其耗时，另外也更加占用空间，所以，一般只建议使用pg_dumpall来备份全局对象，而非全库数据。

pg_dumpall命令的可选参数和功能如下：

pg_dumpall --help
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.

Usage:
  pg_dumpall [OPTION]...

General options:
  -f, --file=FILENAME          output file name
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -c, --clean                  clean (drop) databases before recreating
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -g, --globals-only           dump only global objects, no databases
  -O, --no-owner               skip restoration of object ownership
  -r, --roles-only             dump only roles, no databases or tablespaces
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in the dump
  -t, --tablespaces-only       dump only tablespaces, no databases or roles
  -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
  --exclude-database=PATTERN   exclude databases whose name matches PATTERN
  --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-role-passwords          do not dump passwords for roles
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-sync                    do not wait for changes to be written safely to disk
  --no-tablespaces             do not dump tablespace assignments
  --no-toast-compression       do not dump TOAST compression methods
  --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
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=CONNSTR     connect using connection string
  -h, --host=HOSTNAME      database server host or socket directory
  -l, --database=DBNAME    alternative default database
  -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

If -f/--file is not used, then the SQL script will be written to the standard
output.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

pg_dumpall可实现仅备份角色和表空间定义：

pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only

如果仅需备份角色定义而无需备份表空间，那么可以加上--roles-only选项：

pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --roles-only

如果需要备份表结构:

 pg_dumpall -h localhost -U postgres --port=5432 -f myglobals_schema.sql -s

如果导出时不希望输入密码，可以设置环境变量PGPASSWORD来设置密码

PGPASSWORD="Somepassword" pg_dumpall -h hostnameOrIp -U postgres --port=5432 -f myglobals.sql -s

pg_dump

pg_dump --help
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, --extension=PATTERN      dump the specified extension(s) only
  -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
  --include-foreign-data=PATTERN
                               include data of foreign tables on foreign
                               servers matching PATTERN
  --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-toast-compression       do not dump TOAST compression methods
  --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

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

pg_dump仅导出数据库结构：

pg_dump -U TestRole1 -s -f TestDb1.sql TestDb1

备份某个database，备份结果以自定义压缩格式输出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb

备份某个database，备份结果以SQL文本方式输出，输出结果中需包括CREATE DATABASE语句：

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

备份某个database中所有名称以“pay”开头的表，备份结果以自定义压缩个数输出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *.pay* -f pay.backup mydb

备份某个database中所有名称为“test,abc"的表，备份结果以自定义压缩个数输出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t "(tests|abc)" -f pay.backup mydb

备份某个database中hr和payroll这两个schema中的所有数据，备份结果以自定义压缩格式输出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -n hr -n payroll -f hr_payroll.backup mydb

备份某个database中除了public schema中的数据以外的所有数据，备份结果以自定义压缩格式输出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -N public -f all_sch_except_pub.backup mydb

将数据备份为SQL文本文件，且生成的INSERT语句是带有字段名列表的标准格式，该文件可以用于将数据导入到低于当前版本的PostgreSQL或者其他支出SQL的非PostgreSQL数据库中（之所有能够实现这种数据移植过程，是因为标准的SQL文本可以在任何支持SQL标准的数据库中执行）：

pg_dump -h localhost -p 5432 -U someuser -F p --column-inserts -f select_tables.backup mydb

注：如果输出文件路径中含空格或者其他可能影响命令行正常处理的字符，请在路径两侧加上双引号，比如："/path with spaces/mydb.backup"。请注意着在PostgreSQL中是一个通用的原则，即当你不确定某段文本是否能正常处理时，都可以加双引号。

从9.1版本开始支持目录格式选项，该选项会将每个表备份为某个文件夹下的一个单独的文件，这样就解决了以其他备份格式备份时可能存在的单个文件大小超出操作系统限制的问题。该选项是生成多个文件的唯一pg_dump备份格式选项。备份时会先创建一个新目录，然后逐个表将一个gzip格式的压缩文件和一个列出所有包含结构的文件填充到该目录中。如果备份开始时发现指定的目录已存在，那么该命令会报错并退出。
目录格式备份：

pg_dump -h localhost -p 5432 -U someuser -F d -f /somepath/a_directory mydb

从9.3版本开始支持并行备份选项--jobs (-j)。如果将其设定为--jobs=3，则后台会有三个线程并行执行当前备份任务。此选项只有在按目录格式进行备份时才会生效，每个写线程只负责写一个单独的文件，因此一定是输出结果为多个独立的文件时才可以并行。
目录格式并行备份：

pg_dump -h localhost -p 5432 -U someuser -j 3 -Fd -f /somepath/a_directory mydb

pg_dump 也可以使用 PGPASSWORD="Somepassword" 在命令行前面的设置环境变量的方式执行。

eg：

PGPASSWORD="Somepassword" pg_dump -h localhost -p 5432 -U someuser -j 3 -Fd -f /somepath/a_directory mydb

abka

关注

0
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
pg_dumpall 与pg_dump实际使用 postgresSQL client 备份方法

postgresSQL 备份数据的命令，可以使用pg_dumpall 和pg_dumppg_dumpall 可以备份全库数据，但是不建议每天使用pg_dumpall备份。因为pg_dumpall仅支持导出为SQL文本格式，而这种庞大的文本备份来进行全库级别的数据库恢复时及其耗时，另外也更加占用空间，所以，一般只建议使用pg_dumpall来备份全局对象，而非全库数据。pg_dumpall命令的可选参数和功能如下：pg_dumpall --helppg_dumpall extracts a P
复制链接

扫一扫

专栏目录