References:
PostgreSQL 自带了两个备份工具:
C:\Program Files\PostgreSQL\17\bin\pg_dump.exe C:\Program Files\PostgreSQL\17\bin\pg_dumpall.exe /bin/pg_dump /bin/pg_dumpall
如果已经设置
联接选项:
我们以例子说明其他选项:
pg_dump -Uhaife -F c -b -v -f sowya.backup sowya
上面
PS D:\> pg_dump -Uhaife -F c -b -v -f sowya.backup sowya 口令: pg_dump: 最后的内置 OID 是 16383 pg_dump: 读扩展 pg_dump: 识别扩展成员 pg_dump: 读取模式 pg_dump: 读取用户定义表 pg_dump: 读取用户定义函数 pg_dump: 读取用户定义类型 pg_dump: 读取过程语言 pg_dump: 读取用户定义聚集函数 pg_dump: 读取用户定义操作符 pg_dump: 读取用户定义的访问方法 pg_dump: 读取用户定义操作符集 pg_dump: 读取用户定义操作符 pg_dump: 读取用户定义的文本搜索解析器 pg_dump: 读取用户定义的文本搜索模板 pg_dump: 读取用户定义的文本搜索字典 pg_dump: 读取用户定义的文本搜索配置 pg_dump: 读取用户定义外部数据封装器 pg_dump: 读取用户定义的外部服务器 pg_dump: 正在读取缺省权限 pg_dump: 读取用户定义的校对函数 pg_dump: 读取用户定义的字符集转换 pg_dump: 读取类型转换 pg_dump: 读取转换 pg_dump: 读取表继承信息 pg_dump: 读取事件触发器 pg_dump: 查找扩展表 pg_dump: 正在查找关系继承 pg_dump: 正在读取感兴趣表的列信息 pg_dump: 在子表里标记继承字段 pg_dump: reading partitioning data pg_dump: 读取索引 pg_dump: 在分区表中标记索引 pg_dump: 读取扩展统计信息 pg_dump: 读取约束 pg_dump: 读取触发器 pg_dump: 读取重写规则 pg_dump: 读取策略 pg_dump: reading row-level security policies pg_dump: 读取发布 pg_dump: reading publication membership of tables pg_dump: reading publication membership of schemas pg_dump: 读取订阅 pg_dump: reading subscription membership of tables pg_dump: 正在读取大对象 pg_dump: 读取从属数据 pg_dump: 正在保存encoding = UTF8 pg_dump: saving "standard_conforming_strings = on" pg_dump: saving "search_path = " pg_dump: 保存数据库定义 pg_dump: 正在转储表"public.finitesumformula"的内容
查看该文件
D:\>dir *.backup
驱动器 D 中的卷是 计算机
卷的序列号是 9C68-468A
D:\ 的目录
2025/09/17 17:21 1,530 sowya.backup
1 个文件 1,530 字节
0 个目录 50,290,683,904 可用字节
注意: 使用
使用
PS D:\work\cs\Database\postgres\sowya> pg_restore -l .\sowya.backup ; ; Archive created at 2025-09-17 17:21:04 ; dbname: sowya ; TOC Entries: 6 ; Compression: gzip ; Dump Version: 1.16-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 17.6 ; Dumped by pg_dump version: 17.6 ; ; ; Selected TOC Entries: ; 217; 1259 16390 TABLE public finitesumformula haife 4785; 0 16390 TABLE DATA public finitesumformula haife
当然可使用命令
命令行选项一般有两种写法,
这两种写法是完全等价的. 比如这里
D:\>pg_dump --help
pg_dump 把一个数据库转储为纯文本文件或者是其它格式.
使用方法:
pg_dump [选项]... [数据库名字]
一般选项:
-f, --file=FILENAME 输出文件或目录名
-F, --format=c|d|t|p 输出文件格式 (定制, 目录, tar
明文 (默认值))
-j, --jobs=NUM 执行多个并行任务进行备份转储工作
-v, --verbose 详细模式
-V, --version 输出版本信息, 然后退出
-Z, --compress=METHOD[:DETAIL]
compress as specified
--lock-wait-timeout=TIMEOUT 在等待表锁超时后操作失败
--no-sync 不用等待变化安全写入磁盘
--sync-method=METHOD set method for syncing files to disk
-?, --help 显示此帮助, 然后退出
控制输出内容选项:
-a, --data-only 只转储数据,不包括模式
-b, --large-objects include large objects in dump
--blobs (same as --large-objects, deprecated)
-B, --no-large-objects exclude large objects in dump
--no-blobs (same as --no-large-objects, deprecated)
-c, --clean 在重新创建之前，先清除（删除）数据库对象
-C, --create 在转储中包括命令,以便创建数据库
-e, --extension=PATTERN 仅转储指定的扩展名
-E, --encoding=ENCODING 转储以ENCODING形式编码的数据
-n, --schema=PATTERN 只转储指定的模式
-N, --exclude-schema=PATTERN 不转储指定的模式
-O, --no-owner 在明文格式中, 忽略恢复对象所属者
-s, --schema-only 只转储模式, 不包括数据
-S, --superuser=NAME 在明文格式中使用指定的超级用户名
-t, --table=PATTERN dump only the specified table(s)
-T, --exclude-table=PATTERN 不转储指定的表
-x, --no-privileges 不要转储权限 (grant/revoke)
--binary-upgrade 只能由升级工具使用
--column-inserts 以带有列名的INSERT命令形式转储数据
--disable-dollar-quoting 取消美元 (符号) 引号, 使用 SQL 标准引号
--disable-triggers 在只恢复数据的过程中禁用触发器
--enable-row-security 启用行安全性（只转储用户能够访问的内容）
--exclude-extension=PATTERN do NOT dump the specified extension(s)
--exclude-table-and-children=PATTERN
do NOT dump the specified table(s), including
child and partition tables
--exclude-table-data=PATTERN 不转储指定的表中的数据
--exclude-table-data-and-children=PATTERN
do NOT dump data for the specified table(s),
including child and partition tables
--extra-float-digits=NUM 覆盖extra_float_digits的默认设置
--filter=FILENAME include or exclude objects and data from dump
based on expressions in FILENAME
--if-exists 当删除对象时使用IF EXISTS
--include-foreign-data=PATTERN
包含外部服务器上与模式匹配的
外部表的数据
--inserts 以INSERT命令，而不是COPY命令的形式转储数据 --load-via-partition-root 通过根表加载分区
--no-comments 不转储注释
--no-publications 不转储发布
--no-security-labels 不转储安全标签的分配
--no-subscriptions 不转储订阅
--no-table-access-method do not dump table access methods
--no-tablespaces 不转储表空间分配信息
--no-toast-compression 不转储TOAST压缩方法
--no-unlogged-table-data 不转储没有日志的表数据
--on-conflict-do-nothing 将ON CONFLICT DO NOTHING添加到INSERT命令
--quote-all-identifiers 所有标识符加引号，即使不是关键字
--restrict-key=RESTRICT_KEY use provided string as psql \restrict key
--rows-per-insert=NROWS 每个插入的行数；意味着--inserts
--section=SECTION 备份命名的节 (数据前, 数据, 及 数据后)
--serializable-deferrable 等到备份可以无异常运行
--snapshot=SNAPSHOT 为转储使用给定的快照
--strict-names 要求每个表和(或)schema包括模式以匹配至少一个实体
--table-and-children=PATTERN dump only the specified table(s), including
child and partition tables
--use-set-session-authorization
使用 SESSION AUTHORIZATION 命令代替
ALTER OWNER 命令来设置所有权
联接选项:
-d, --dbname=DBNAME 对数据库 DBNAME备份
-h, --host=主机名 数据库服务器的主机名或套接字目录
-p, --port=端口号 数据库服务器的端口号
-U, --username=名字 以指定的数据库用户联接
-w, --no-password 永远不提示输入口令
-W, --password 强制口令提示 (自动)
--role=ROLENAME 在转储前运行SET ROLE
如果没有提供数据库名字, 那么使用 PGDATABASE 环境变量
的数值.
臭虫报告至.
PostgreSQL 主页:
具体操作时将用户名
备份数据库
pg_dump -h localhost -p5432 -U someuser -C -F p -b -v -f sowya.backup sowya
备份数据库
pg_dump -h localhost -p5432 -U someuser -F t -b -f sowya.backup sowya
注意: 使用
PS D:\> pg_dump -h localhost -p5432 -U haife -F t -b -f sowya.backup.tar sowya 口令:
将这个 sowya.backup.tar 文件解压缩, 会发现其中包含了三个文件, 例如:
注意:
下面的命令可实现备份角色和表空间定义:
pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only
如果仅需备份角色定义而无需表空间定义, 那么加上
pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --roles-only
由于
PostgreSQL 支持以下两种数据恢复方法:
为实验数据恢复, 我们删除之前创建的数据库 sowya.
mydb=> drop database sowya; DROP DATABASE
注意无法删除当前使用的数据库.
现在退出 PostgreSQL, 使用
PS D:\work\cs\Database\postgres\sowya\backup> psql -Uhaife -f restore.sql 用户 haife 的口令： psql: 错误: 连接到"localhost" (::1)上的服务器，端口5432失败：致命错误: 数据库 "haife" 不存在
这个命令默认需要连接与用户 haife 同名的数据库, 但该数据库 haife 不存在. 由于之前还以 haife 账户创建了一个数据库 mydb. 因此可以尝试连接 mydb.
PS D:\work\cs\Database\postgres\sowya\backup> psql -Uhaife -f restore.sql -d mydb 用户 haife 的口令： SET SET SET SET SET SET set_config ------------ (1 行记录) SET SET SET SET CREATE DATABASEl:27: 閿欒: 鏁版嵁搴?"sowya" 涓嶅瓨鍦? ALTER DATABASE psql:restore.sql:37: 错误: \unrestrict: not currently in restricted mode 您现在已经连接到数据库 "sowya",用户 "haife". SET SET SET SET SET SET set_config ------------ (1 行记录) SET SET SET SET SET SET CREATE TABLE ALTER TABLE COPY 0 psql:restore.sql:79: 閿欒(转换出错): permission denied to COPY from a file 描述: Only roles with privileges of the "pg_read_server_files" role may COPY from a file. 提示: 浠讳綍浜哄彲浠?COPY 鍒版爣鍑嗚緭鍑烘垨鏉ヨ嚜鏍囧噯杈撳叆鐨?COPY. 浠讳綍浜轰篃鍙互浣跨敤 Psql 鐨?\copy 鍛戒护. (任何人可�?COPY 到标准输出或来自标准输入�?COPY. 任何人也可以使用 Psql �?\copy 命令.)
现在可以连接 sowya 数据库了.
PS D:\work\cs\Database\postgres\sowya\backup> psql -Uhaife -d sowya 用户 haife 的口令： psql (17.6) 输入 "help" 来获取帮助信息. sowya=>
sowya=> \l
数据库列表
名称 | 拥有者 | 字元编码 | Locale Provider | 校对规则 | Ctype | Locale | ICU Rules | 存取权限
-----------+----------+----------+-----------------+----------+-------+--------+-----------+-----------------------
mydb | haife | UTF8 | libc | zh-CN | zh-CN | | |
postgres | postgres | UTF8 | libc | zh-CN | zh-CN | | |
sowya | haife | UTF8 | libc | zh-CN | zh-CN | | |
template0 | postgres | UTF8 | libc | zh-CN | zh-CN | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | zh-CN | zh-CN | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(5 行记录)
使用
sowya=> \dn+
架构模式列表
名称 | 拥有者 | 存取权限 | 描述
--------+-------------------+----------------------------------------+------------------------
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
| | =U/pg_database_owner |
(1 行记录)
查看其中的表
sowya=> \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+------------------+--------+--------
public | finitesumformula | 数据表 | haife
(1 行记录)
如果只希望显示记录(也称元组 tuple), 则可以使用
sowya=> \t 开启只显示元组.
此时再输入
sowya=> \d public | finitesumformula | 数据表 | haife
查看
sowya=> \t 关闭只显示元组. sowya=> SELECT * FROM finitesumformula; item | var | first | difference | last | thesum ------+-----+-------+------------+------+-------- (0 行记录)
发现是空的, 原因是尚未恢复其中的数据. 数据保存在
如果备份文件是 tar 格式或者 custom(自定义压缩格式) 或者 directory (目录格式, 为每张表上此一个文件, 输出到指定路径中), 那么可以使用功能强大的
首先先删除 sowya 数据库.
PS D:\work\cs\Database\postgres\sowya> pg_restore --dbname=mydb -Uhaife --verbose sowya.backup.tar pg_restore: 为恢复数据库与数据库联接 口令: pg_restore: 创建TABLE "public.finitesumformula" pg_restore: 为表"public.finitesumformula"处理数据
上面的命令将 sowya.backup.tar 中的内容恢复到数据库 mydb, 也就是说不再是之前的 sowya.
PS D:\work\cs\Database\postgres\sowya> psql -Uhaife -d mydb
用户 haife 的口令：
psql (17.6)
输入 "help" 来获取帮助信息.
mydb=> \l
数据库列表
名称 | 拥有者 | 字元编码 | Locale Provider | 校对规则 | Ctype | Locale | ICU Rules | 存取权限
-----------+----------+----------+-----------------+----------+-------+--------+-----------+-----------------------
mydb | haife | UTF8 | libc | zh-CN | zh-CN | | |
postgres | postgres | UTF8 | libc | zh-CN | zh-CN | | |
template0 | postgres | UTF8 | libc | zh-CN | zh-CN | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | zh-CN | zh-CN | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(4 行记录)
mydb=> \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+------------------+--------+--------
public | finitesumformula | 数据表 | haife
(1 行记录)
mydb=> select * from finitesumformula;
item | var | first | difference | last | thesum
-------------+-----+-------+------------+------+---------
1/(i*(i+1)) | i | 1 | 1 | n | n/(n+1)
(1 行记录)
因此, 若使用用户 haife 进行恢复数据, 首先创建数据库 sowya, 然后再使用
pg_restore --dbname=sowya -Uhaife --verbose sowya.backup.tar
使用
PS D:\work\cs\Database\postgres\sowya> pg_restore --help
pg_restore 从一个归档中恢复一个由 pg_dump 创建的 PostgreSQL 数据库.
使用方法:
pg_restore [选项]... [文件名]
一般选项:
-d, --dbname=名字 连接数据库名字
-f, --file=文件名 输出文件名(- 对于stdout)
-F, --format=c|d|t 备份文件格式(应该自动进行)
-l, --list 打印归档文件的 TOC 概述
-v, --verbose 详细模式
-V, --version 输出版本信息, 然后退出
-?, --help 显示此帮助, 然后退出
恢复控制选项:
-a, --data-only 只恢复数据, 不包括模式
-c, --clean 在重新创建之前，先清除（删除）数据库对象
-C, --create 创建目标数据库
-e, --exit-on-error 发生错误退出, 默认为继续
-I, --index=NAME 恢复指定名称的索引
-j, --jobs=NUM 执行多个并行任务进行恢复工作
-L, --use-list=FILENAME 从这个文件中使用指定的内容表排序
输出
-n, --schema=NAME 在这个模式中只恢复对象
-N, --exclude-schema=NAME 不恢复此模式中的对象
-O, --no-owner 不恢复对象所属者
-P, --function=NAME(args) 恢复指定名字的函数
-s, --schema-only 只恢复模式, 不包括数据
-S, --superuser=NAME 使用指定的超级用户来禁用触发器
-t, --table=NAME 恢复命名关系（表、视图等）
-T, --trigger=NAME 恢复指定名字的触发器
-x, --no-privileges 跳过处理权限的恢复 (grant/revoke)
-1, --single-transaction 作为单个事务恢复
--disable-triggers 在只恢复数据的过程中禁用触发器
--enable-row-security 启用行安全性
--filter=FILENAME restore or skip objects based on expressions
in FILENAME
--if-exists 当删除对象时使用IF EXISTS
--no-comments 不恢复注释
--no-data-for-failed-tables 对那些无法创建的表不进行
数据恢复
--no-publications 不恢复发行
--no-security-labels 不恢复安全标签信息
--no-subscriptions 不恢复订阅
--no-table-access-method do not restore table access methods
--no-tablespaces 不恢复表空间的分配信息
--restrict-key=RESTRICT_KEY use provided string as psql \restrict key
--section=SECTION 恢复命名节 (数据前、数据及数据后)
--strict-names 要求每个表和(或)schema包括模式以匹配至少一个实体
--transaction-size=N commit after every N objects
--use-set-session-authorization
使用 SESSION AUTHORIZATION 命令代替
ALTER OWNER 命令来设置所有权
联接选项:
-h, --host=主机名 数据库服务器的主机名或套接字目录
-p, --port=端口号 数据库服务器的端口号
-U, --username=名字 以指定的数据库用户联接
-w, --no-password 永远不提示输入口令
-W, --password 强制口令提示 (自动)
--role=ROLENAME 在恢复前执行SET ROLE操作
选项 -I, -n, -N, -P, -t, -T, 以及 --section 可以组合使用和指定
多次用于选择多个对象.
如果没有提供输入文件名, 则使用标准输入.
臭虫报告至<pgsql-bugs@lists.postgresql.org>.
PostgreSQL 主页: <https://www.postgresql.org/>
Linux 下在 用户当前目录 (~) 下编辑文件 .pgpass 内容为 主机或IP地址:端口号:用户名:数据库名:密码
例如:
localhost:5432:postgres:postgres:yourpassword
将此文件的权限改为 600, 否则会提示错误.
chmod 600 .pgpass
这样就可以不用输入密码就可以连接了.
Windows 上, 首先新建环境变量 PGPASSFILE, 其值是存储登录信息的文件的绝对路径. 比如在 C:\Program Files\PostgreSQL\17\data 目录下新建一个密码文件, 名为 pgpass.conf, 那么环境变量 PGPASSFILE 的值为 C:\Program Files\PostgreSQL\17\data\pgpass.conf pgpass.conf 的内容与上面的一致.
Linux 下对于密码文件 .pgpass 的文件权限要求很严格, 比如是 600, 如果不是会提示下面的错误:
WARNING: password file "/home/haifeng/.pgpass" has group or world access; permissions should be u=rw (0600) or less psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "haifeng" does not exist
Windows 对于文件权限的规定没有那么严格, 但也要注意密码文件应该存放在一个安全的地方.
注意:
使用下面的命令可以直接打开环境变量编辑窗口.
rundll32 sysdm.cpl,EditEnvironmentVariables
如果需要修改系统环境变量, 则需要管理员运行命令行终端.