PostgreSQL教程FG192-PG客户端应用详解：psql/pg_dump/pg_restore全参数

本文档详细介绍PostgreSQL客户端应用工具psql、pg_dump和pg_restore的使用方法和参数说明，风哥教程参考PostgreSQL官方文档内容，适合数据库管理员和开发人员在生产环境中使用这些工具进行数据库管理和备份恢复操作。

Part01-基础概念与理论知识

1.1 PostgreSQL客户端应用概述

PostgreSQL提供了一系列客户端应用工具，用于数据库管理、备份恢复、性能监控等操作。这些工具是PostgreSQL生态系统的重要组成部分，是数据库管理员日常工作的必备工具。更多视频教程www.fgedu.net.cn

常用客户端应用工具：

psql：命令行交互工具，用于执行SQL语句和管理数据库
pg_dump：数据库备份工具，用于创建数据库的逻辑备份
pg_restore：数据库恢复工具，用于从备份文件恢复数据库
pg_basebackup：物理备份工具，用于创建数据库的物理备份
pg_controldata：控制数据查看工具，用于查看数据库集群的控制信息

1.2 psql命令行工具

psql是PostgreSQL的命令行交互工具，是最常用的PostgreSQL客户端工具之一。它允许用户执行SQL语句、管理数据库对象、查看数据库状态等操作。学习交流加群风哥微信: itpux-com

1.3 pg_dump备份工具

pg_dump是PostgreSQL的逻辑备份工具，用于创建数据库的逻辑备份。它可以生成SQL脚本或自定义格式的备份文件，支持全库备份和单表备份。学习交流加群风哥QQ113257174

1.4 pg_restore恢复工具

pg_restore是PostgreSQL的恢复工具，用于从pg_dump创建的备份文件中恢复数据库。它支持从自定义格式的备份文件中选择性地恢复数据库对象。

风哥提示：pg_dump和pg_restore是PostgreSQL中最常用的备份恢复工具，掌握它们的使用方法对于数据库管理员来说非常重要。

Part02-生产环境规划与建议

2.1 客户端应用最佳实践

— 客户端应用最佳实践

— 1. psql最佳实践
— – 使用 .psqlrc 文件定制psql环境
— – 使用 \watch 命令监控查询结果
— – 使用 \timing 命令查看查询执行时间
— – 使用 \x 命令美化输出格式

— 2. pg_dump最佳实践
— – 定期执行备份，建立备份策略
— – 使用 -F c 选项创建自定义格式备份，便于恢复
— – 使用 -j 选项启用并行备份，提高备份速度
— – 备份时使用 -v 选项查看详细信息

— 3. pg_restore最佳实践
— – 恢复前验证备份文件的完整性
— – 使用 -j 选项启用并行恢复，提高恢复速度
— – 恢复时使用 -v 选项查看详细信息
— – 对于大型数据库，考虑使用 –single-transaction 选项

2.2 备份策略建议

备份策略建议：

全库备份：每周执行一次全库备份
增量备份：每天执行一次增量备份
备份存储：备份文件应存储在不同的物理位置
备份验证：定期验证备份文件的完整性
备份保留：根据业务需求设置备份保留期限

风哥教程针对风哥教程针对生产环境建议：制定完善的备份策略，确保数据安全。from PostgreSQL视频:www.itpux.com

2.3 恢复策略建议

恢复策略建议：

测试恢复：定期测试备份文件的恢复过程
恢复计划：制定详细的恢复计划，包括步骤和时间估计
恢复演练：定期进行恢复演练，提高应对故障的能力
恢复验证：恢复后验证数据的完整性和一致性
恢复时间目标：根据业务需求设置恢复时间目标（RTO）

Part03-生产环境项目实施方案

3.1 psql命令使用详解

3.1.1 psql命令基本语法

— psql命令基本语法
psql [OPTIONS] [DBNAME [USERNAME]]

— 常用选项
— -h, –host=HOSTNAME 数据库服务器主机名
— -p, –port=PORT 数据库服务器端口号
— -U, –username=USERNAME 数据库用户名
— -d, –dbname=DBNAME 数据库名称
— -c, –command=COMMAND 执行指定的SQL命令
— -f, –file=FILENAME 执行指定文件中的SQL命令
— -v, –set=NAME=VALUE 设置psql变量
— -X, –no-psqlrc 不读取psqlrc文件
— -1, –single-transaction 在单个事务中执行所有命令

3.1.2 psql命令执行示例

— psql命令执行示例

— 1. 连接到数据库
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb

— 输出：
— psql (18.3)
— Type “help” for help.
—
— fgedudb=>

— 2. 执行单个SQL命令
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT * FROM fgedu_users LIMIT 5;”

— 3. 执行SQL文件
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -f create_tables.sql

— 4. 设置变量并执行命令
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -v table_name=fgedu_users -c “SELECT * FROM :table_name LIMIT 5;”

3.1.3 psql内部命令

— psql内部命令

— 1. 常用内部命令
\h [命令] — 查看SQL命令的帮助
\? — 查看psql内部命令的帮助
\l — 列出所有数据库
\c [数据库] — 连接到指定数据库
\d [表名] — 查看表结构
\d+ [表名] — 查看表结构（详细）
\dt [模式.] — 列出表
\dv [模式.] — 列出视图
\df [模式.] — 列出函数
\di [模式.] — 列出索引
\du — 列出角色
\x — 切换扩展输出模式
\timing — 切换执行时间显示
\watch [秒数] — 定期执行当前查询
\q — 退出psql

3.2 pg_dump命令使用详解

3.2.1 pg_dump命令基本语法

— pg_dump命令基本语法
pg_dump [OPTIONS] DBNAME

— 常用选项
— -h, –host=HOSTNAME 数据库服务器主机名
— -p, –port=PORT 数据库服务器端口号
— -U, –username=USERNAME 数据库用户名
— -d, –dbname=DBNAME 数据库名称
— -F, –format=c|d|t|p 输出格式：c=自定义, d=目录, t=tar, p=纯文本
— -f, –file=FILENAME 输出文件
— -j, –jobs=NUM 并行备份的工作线程数
— -v, –verbose 详细模式
— -Z, –compress=0-9 压缩级别
— -n, –schema=SCHEMA 只备份指定的模式
— -t, –table=TABLE 只备份指定的表
— -a, –data-only 只备份数据，不备份 schema
— -s, –schema-only 只备份 schema，不备份数据
— –inserts 使用INSERT语句而不是COPY语句
— –column-inserts 使用带有列名的INSERT语句
— –no-owner 不备份对象所有权
— –no-privileges 不备份权限
— –clean 在重新创建对象之前先删除它们
— –if-exists 当与 –clean 一起使用时，只删除存在的对象

3.2.2 pg_dump命令执行示例

— pg_dump命令执行示例

— 1. 备份整个数据库（纯文本格式）
$ pg_dump -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -f fgedudb.sql

— 2. 备份整个数据库（自定义格式，压缩）
$ pg_dump -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -F c -Z 5 -f fgedudb.backup

— 3. 备份指定模式
$ pg_dump -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -n sales -F c -f sales_schema.backup

— 4. 备份指定表
$ pg_dump -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -t fgedu_users -t fgedu_orders -F c -f tables.backup

— 5. 只备份数据
$ pg_dump -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -a -F c -f data_only.backup

— 6. 只备份schema
$ pg_dump -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -s -F c -f schema_only.backup

— 7. 并行备份
$ pg_dump -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -F d -j 4 -f fgedudb_dir

— 8. 备份并显示详细信息
$ pg_dump -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -F c -v -f fgedudb_backup.log

3.3 pg_restore命令使用详解

3.3.1 pg_restore命令基本语法

— pg_restore命令基本语法
pg_restore [OPTIONS] [FILE]

— 常用选项
— -h, –host=HOSTNAME 数据库服务器主机名
— -p, –port=PORT 数据库服务器端口号
— -U, –username=USERNAME 数据库用户名
— -d, –dbname=DBNAME 目标数据库名称
— -F, –format=c|d|t 输入格式：c=自定义, d=目录, t=tar
— -f, –file=FILENAME 输出文件（如果不指定-d）
— -j, –jobs=NUM 并行恢复的工作线程数
— -v, –verbose 详细模式
— -a, –data-only 只恢复数据，不恢复 schema
— -s, –schema-only 只恢复 schema，不恢复数据
— -n, –schema=SCHEMA 只恢复指定的模式
— -t, –table=TABLE 只恢复指定的表
— –clean 在重新创建对象之前先删除它们
— –if-exists 当与 –clean 一起使用时，只删除存在的对象
— –no-owner 不恢复对象所有权
— –no-privileges 不恢复权限
— –single-transaction 在单个事务中执行恢复
— –exit-on-error 遇到错误时退出

3.3.2 pg_restore命令执行示例

— pg_restore命令执行示例

— 1. 恢复整个数据库
$ pg_restore -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb fgedudb.backup

— 2. 恢复到新数据库
$ createdb -h 192.168.1.100 -p 5432 -U fgedu new_db
$ pg_restore -h 192.168.1.100 -p 5432 -U fgedu -d new_db fgedudb.backup

— 3. 只恢复数据
$ pg_restore -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -a fgedudb.backup

— 4. 只恢复schema
$ pg_restore -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -s fgedudb.backup

— 5. 只恢复指定表
$ pg_restore -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -t fgedu_users fgedudb.backup

— 6. 并行恢复
$ pg_restore -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -j 4 fgedudb.backup

— 7. 恢复并显示详细信息
$ pg_restore -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -v fgedudb.backup

— 8. 在单个事务中恢复
$ pg_restore -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb –single-transaction fgedudb.backup

风哥提示：使用pg_restore恢复数据时，目标数据库必须已经存在。如果要恢复到新数据库，需要先创建数据库。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 psql使用案例

— psql使用案例：数据库管理

— 1. 连接到数据库
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb

— 2. 创建表
fgedudb=> CREATE TABLE fgedu_products (
fgedudb(> id SERIAL PRIMARY KEY,
fgedudb(> name VARCHAR(100) NOT NULL,
fgedudb(> price DECIMAL(10,2) NOT NULL,
fgedudb(> stock INTEGER NOT NULL
fgedudb(> );
— 输出：
— CREATE TABLE

— 3. 插入数据
fgedudb=> INSERT INTO fgedu_products (name, price, stock) VALUES
fgedudb-> (‘iPhone 15’, 5999.99, 100),
fgedudb-> (‘MacBook Pro’, 12999.99, 50),
fgedudb-> (‘AirPods Pro’, 1999.99, 200);
— 输出：
— INSERT 0 3

— 4. 查询数据
fgedudb=> \x
— 输出：
— Expanded display is on.

— 5. 更新数据
fgedudb=> UPDATE fgedu_products SET stock = stock – 10 WHERE name = ‘iPhone 15’;
— 输出：
— UPDATE 1

— 6. 删除数据
fgedudb=> DELETE FROM fgedu_products WHERE id = 3;
— 输出：
— DELETE 1

— 8. 退出psql
fgedudb=> \q

4.2 pg_dump使用案例

— pg_dump使用案例：数据库备份

— 1. 全库备份（自定义格式，压缩）
$ pg_dump -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -F c -Z 5 -v -f /backup/fgedudb_$(date +%Y%m%d).backup

— 2. 备份指定表
$ pg_dump -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -t fgedu_users -t fgedu_products -F c -f /backup/tables_backup.backup

— 3. 只备份数据
$ pg_dump -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -a -F c -f /backup/data_only.backup

4.3 pg_restore使用案例

— pg_restore使用案例：数据库恢复

— 1. 恢复到新数据库
$ createdb -h 192.168.1.100 -p 5432 -U fgedu restore_db
$ pg_restore -h 192.168.1.100 -p 5432 -U fgedu -d restore_db /backup/fgedudb_20260407.backup

— 2. 只恢复指定表
$ pg_restore -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -t fgedu_users /backup/fgedudb_20260407.backup

— 3. 并行恢复
$ pg_restore -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -j 4 /backup/fgedudb_20260407.backup

Part05-风哥经验总结与分享

5.1 客户端应用使用技巧

客户端应用使用技巧：

psql技巧：

使用 .psqlrc 文件定制psql环境，设置常用别名和快捷键
使用 \watch 命令定期执行查询，监控数据变化
使用 \timing 命令查看SQL执行时间，优化查询性能
使用 \x 命令美化输出格式，提高可读性
使用 \copy 命令导入导出数据，比SQL COPY命令更灵活

pg_dump技巧：

使用自定义格式（-F c）创建备份，便于后续恢复和操作
使用并行备份（-j）提高备份速度，特别是对于大型数据库
使用压缩选项（-Z）减小备份文件大小，节省存储空间
定期执行备份，建立完善的备份策略
备份时使用 –clean 和 –if-exists 选项，便于恢复到现有数据库

pg_restore技巧：

使用并行恢复（-j）提高恢复速度
使用 –single-transaction 选项确保恢复的原子性
使用 -a 选项只恢复数据，保留现有schema
使用 -s 选项只恢复schema，不恢复数据
恢复前验证备份文件的完整性，确保备份可用

5.2 客户端应用常见问题解决

— 客户端应用常见问题解决

— 1. psql连接问题
— 问题：无法连接到数据库
— 解决：
— – 检查主机名和端口号是否正确
— – 检查用户名和密码是否正确
— – 检查数据库是否存在
— – 检查网络连接是否正常
— – 检查pg_hba.conf文件中的访问控制设置

— 2. pg_dump备份失败
— 问题：备份过程中出现错误
— 解决：
— – 检查数据库连接是否正常
— – 检查用户权限是否足够
— – 检查磁盘空间是否充足
— – 检查备份文件路径是否存在且可写
— – 对于大型数据库，考虑使用并行备份（-j）

— 3. pg_restore恢复失败
— 问题：恢复过程中出现错误
— 解决：
— – 检查备份文件是否完整
— – 检查目标数据库是否存在
— – 检查用户权限是否足够
— – 检查磁盘空间是否充足
— – 对于大型数据库，考虑使用并行恢复（-j）
— – 对于错误，查看详细输出（-v）了解具体原因

5.3 客户端应用性能优化

— 客户端应用性能优化

— 1. psql性能优化
— – 使用服务器端预处理语句，减少网络传输
— – 使用 \copy 命令代替INSERT语句导入大量数据
— – 对于大型结果集，使用FETCH命令分批获取
— – 避免在psql中执行复杂的计算，尽量在服务器端完成

— 2. pg_dump性能优化
— – 使用并行备份（-j），根据CPU核心数设置合适的并行度
— – 使用自定义格式（-F c），提高备份和恢复速度
— – 使用压缩选项（-Z），减小备份文件大小
— – 避免在业务高峰期执行备份，选择低峰期
— – 对于大型数据库，考虑使用增量备份策略

— 3. pg_restore性能优化
— – 使用并行恢复（-j），根据CPU核心数设置合适的并行度
— – 使用 –single-transaction 选项，减少事务开销
— – 对于大型数据库，考虑先恢复schema，再恢复数据
— – 恢复前关闭索引和约束，恢复后再启用
— – 避免在业务高峰期执行恢复，选择低峰期

风哥提示：客户端应用是PostgreSQL数据库管理的重要工具，掌握它们的使用方法和性能优化技巧对于提高工作效率和保障数据安全非常重要。在生产环境中，应该根据实际情况选择合适的工具和参数，制定完善的备份恢复策略。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html