印刻数据库

外观

PostgreSQL 工具命令语法 ​

客户端工具 ​

psql - PostgreSQL交互式终端 ​

功能：PostgreSQL的交互式终端，用于执行SQL命令、管理数据库和执行脚本。

语法格式：

bash
psql [OPTION]... [DBNAME [USERNAME]]

常用选项：

选项描述
-h, --host=HOSTNAME数据库服务器主机名或IP地址
-p, --port=PORT数据库服务器端口号
-U, --username=USERNAME连接数据库的用户名
-d, --dbname=DBNAME要连接的数据库名称
-c, --command=COMMAND执行指定的SQL命令并退出
-f, --file=FILENAME执行指定文件中的SQL命令
-l, --list列出所有数据库
-v, --set=, --variable=NAME=VALUE设置psql变量
-X, --no-psqlrc不读取psqlrc文件
-1, --single-transaction将整个脚本作为单个事务执行
-V, --version显示版本信息并退出
-?, --help显示帮助信息并退出

示例：

bash
# 连接到本地数据库
psql -h localhost -p 5432 -U postgres -d mydb

# 执行单个SQL命令
psql -c "SELECT * FROM users" -d mydb -U postgres

# 执行SQL脚本文件
psql -f script.sql -d mydb -U postgres

# 列出所有数据库
psql -l -U postgres

备份与恢复工具 ​

pg_dump - 数据库备份工具 ​

功能：创建PostgreSQL数据库的备份，可以生成SQL脚本或归档文件。

语法格式：

bash
pg_dump [OPTION]... [DBNAME]

常用选项：

选项描述
-h, --host=HOSTNAME数据库服务器主机名或IP地址
-p, --port=PORT数据库服务器端口号
-U, --username=USERNAME连接数据库的用户名
-d, --dbname=DBNAME要备份的数据库名称
-F, --format=cdtp输出格式：c=自定义, d=目录, t=tar, p=纯文本
-a, --data-only只备份数据，不备份schema
-s, --schema-only只备份schema，不备份数据
-c, --clean在创建对象前先删除对象
-C, --create在备份中包含创建数据库语句
-E, --encoding=ENCODING备份的编码格式
-v, --verbose详细输出模式
-f, --file=FILENAME输出文件名
-j, --jobs=NUM并行备份作业数
-Z, --compress=0-9压缩级别（0-9）

示例：

bash
# 创建纯文本备份
pg_dump -h localhost -p 5432 -U postgres -d mydb -f mydb_backup.sql

# 创建自定义格式备份（支持压缩和并行恢复）
pq_dump -h localhost -p 5432 -U postgres -d mydb -F c -Z 5 -f mydb_backup.dump

# 只备份数据
pg_dump -h localhost -p 5432 -U postgres -d mydb -a -f mydb_data.sql

# 只备份schema
pg_dump -h localhost -p 5432 -U postgres -d mydb -s -f mydb_schema.sql

pg_restore - 数据库恢复工具 ​

功能：从pg_dump创建的备份文件中恢复PostgreSQL数据库。

语法格式：

bash
pg_restore [OPTION]... [FILE]

常用选项：

选项描述
-h, --host=HOSTNAME数据库服务器主机名或IP地址
-p, --port=PORT数据库服务器端口号
-U, --username=USERNAME连接数据库的用户名
-d, --dbname=DBNAME要恢复到的数据库名称
-c, --clean在恢复前先删除数据库对象
-C, --create先创建数据库，然后恢复到该数据库
-e, --exit-on-error遇到错误时退出
-F, --format=cdt输入格式：c=自定义, d=目录, t=tar
-j, --jobs=NUM并行恢复作业数
-v, --verbose详细输出模式
-x, --no-privileges不恢复权限
-O, --no-owner不恢复对象所有者

示例：

bash
# 从自定义格式备份恢复数据库
pg_restore -h localhost -p 5432 -U postgres -d mydb -v mydb_backup.dump

# 恢复前先创建数据库
pg_restore -h localhost -p 5432 -U postgres -C -d postgres -v mydb_backup.dump

# 并行恢复（使用4个作业）
pg_restore -h localhost -p 5432 -U postgres -d mydb -j 4 -v mydb_backup.dump

pg_dumpall - 备份所有数据库 ​

功能：备份PostgreSQL服务器上的所有数据库，包括全局对象（如角色和表空间）。

语法格式：

bash
pg_dumpall [OPTION]...

常用选项：

选项描述
-h, --host=HOSTNAME数据库服务器主机名或IP地址
-p, --port=PORT数据库服务器端口号
-U, --username=USERNAME连接数据库的用户名
-c, --clean在创建对象前先删除对象
-g, --globals-only只备份全局对象（角色和表空间）
-r, --roles-only只备份角色
-s, --schema-only只备份schema，不备份数据
-t, --tablespaces-only只备份表空间
-v, --verbose详细输出模式
-f, --file=FILENAME输出文件名

示例：

bash
# 备份所有数据库和全局对象
pg_dumpall -h localhost -p 5432 -U postgres -f all_databases.sql

# 只备份角色
pg_dumpall -h localhost -p 5432 -U postgres -r -f roles.sql

# 只备份表空间
pg_dumpall -h localhost -p 5432 -U postgres -t -f tablespaces.sql

管理工具 ​

pg_ctl - 数据库服务器控制工具 ​

功能：用于启动、停止和重启PostgreSQL服务器，以及查看服务器状态。

语法格式：

bash
pg_ctl [OPTION]... COMMAND [DATADIR]

常用命令：

命令描述
start启动PostgreSQL服务器
stop停止PostgreSQL服务器
restart重启PostgreSQL服务器
reload重新加载配置文件
status查看PostgreSQL服务器状态
promote将热备服务器提升为主服务器

常用选项：

选项描述
-D, --pgdata=DATADIR数据库集群的数据目录
-l, --log=FILENAME服务器日志文件
-W, --wait等待服务器启动或停止
-t, --timeout=SECS等待超时时间（秒）
-s, --silent减少输出信息
-o, --options=OPTIONS传递给postgres命令的选项
-V, --version显示版本信息

示例：

bash
# 启动PostgreSQL服务器
pg_ctl -D /var/lib/postgresql/14/main start

# 停止PostgreSQL服务器
pg_ctl -D /var/lib/postgresql/14/main stop -m fast

# 重启PostgreSQL服务器
pg_ctl -D /var/lib/postgresql/14/main restart

# 重新加载配置文件
pg_ctl -D /var/lib/postgresql/14/main reload

# 查看服务器状态
pg_ctl -D /var/lib/postgresql/14/main status

createdb - 创建数据库 ​

功能：创建新的PostgreSQL数据库。

语法格式：

bash
createdb [OPTION]... [DBNAME [OWNER]]

常用选项：

选项描述
-h, --host=HOSTNAME数据库服务器主机名或IP地址
-p, --port=PORT数据库服务器端口号
-U, --username=USERNAME连接数据库的用户名
-O, --owner=OWNER新数据库的所有者
-T, --template=TEMPLATE用于创建新数据库的模板
-E, --encoding=ENCODING新数据库的编码格式
-l, --locale=LOCALE新数据库的区域设置
-C, --lc-collate=LOCALE新数据库的排序规则
-c, --lc-ctype=LOCALE新数据库的字符分类
-v, --verbose详细输出模式

示例：

bash
# 创建名为mydb的数据库
createdb -h localhost -p 5432 -U postgres mydb

# 创建指定所有者的数据库
createdb -h localhost -p 5432 -U postgres -O myuser mydb

# 使用特定模板创建数据库
createdb -h localhost -p 5432 -U postgres -T template0 mydb

dropdb - 删除数据库 ​

功能：删除PostgreSQL数据库。

语法格式：

bash
dropdb [OPTION]... DBNAME

常用选项：

选项描述
-h, --host=HOSTNAME数据库服务器主机名或IP地址
-p, --port=PORT数据库服务器端口号
-U, --username=USERNAME连接数据库的用户名
-e, --echo显示执行的命令
-i, --interactive交互式确认
-v, --verbose详细输出模式

示例：

bash
# 删除名为mydb的数据库
dropdb -h localhost -p 5432 -U postgres mydb

# 交互式删除数据库
dropdb -h localhost -p 5432 -U postgres -i mydb

# 显示执行的命令
dropdb -h localhost -p 5432 -U postgres -e mydb

复制工具 ​

pg_basebackup - 基础备份工具 ​

功能：创建PostgreSQL数据库集群的基础备份，用于物理复制。

语法格式：

bash
pg_basebackup [OPTION]... DESTINATION

常用选项：

选项描述
-h, --host=HOSTNAME主服务器主机名或IP地址
-p, --port=PORT主服务器端口号
-U, --username=USERNAME复制用户的用户名
-D, --pgdata=DIRECTORY备份存储目录
-F, --format=pt输出格式：p=plain, t=tar
-X, --wal-method=nonefetchstreamWAL获取方法
-c, --checkpoint=fastspread检查点方法
-z, --gzip使用gzip压缩tar文件
-Z, --compress=0-9压缩级别（0-9）
-P, --progress显示进度信息
-v, --verbose详细输出模式
-w, --no-password不提示输入密码
-W, --password强制提示输入密码

示例：

bash
# 创建基础备份（流式WAL）
pg_basebackup -h master.example.com -p 5432 -U replication_user -D /backup/base -F t -X stream -z -P

# 创建基础备份（fetch WAL）
pg_basebackup -h master.example.com -p 5432 -U replication_user -D /backup/base -F p -X fetch -v

统计与分析工具 ​

pg_stat_statements - 查询统计扩展 ​

功能：PostgreSQL扩展，用于收集和分析SQL查询的执行统计信息。

加载扩展：

sql
-- 在数据库中创建扩展
CREATE EXTENSION pg_stat_statements;

配置参数：

sql
-- 在postgresql.conf中配置
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
pg_stat_statements.track_utility = on

常用查询：

sql
-- 查看执行时间最长的查询
SELECT queryid, query, calls, total_time, mean_time, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

-- 查看调用次数最多的查询
SELECT queryid, query, calls, total_time, mean_time, rows FROM pg_stat_statements ORDER BY calls DESC LIMIT 10;

-- 重置统计信息
SELECT pg_stat_statements_reset();

常见问题（FAQ） ​

Q1：如何获取psql命令的帮助？ ​

A1：可以使用以下命令获取psql命令的帮助：

bash
psql --help
# 或在psql交互模式下
\?

Q2：如何在脚本中使用pg_dump和pg_restore？ ​

A2：在脚本中使用pg_dump和pg_restore的示例：

bash
#!/bin/bash
# 备份脚本
BACKUP_DIR="/backup"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
USER="postgres"
HOST="localhost"
PORT="5432"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
pg_dump -h $HOST -p $PORT -U $USER -d $DB_NAME -F c -Z 5 -f $BACKUP_DIR/$DB_NAME_$DATE.dump

# 保留最近7天的备份
find $BACKUP_DIR -name "$DB_NAME_*.dump" -type f -mtime +7 -delete

Q3：如何使用psql执行多条命令？ ​

A3：可以使用分号分隔多条命令，或使用-e选项执行多个-c选项：

bash
# 分号分隔多条命令
psql -c "SELECT * FROM table1; SELECT * FROM table2;" -d mydb

# 多个-c选项
psql -c "SELECT * FROM table1" -c "SELECT * FROM table2" -d mydb

Q4：如何在psql中执行命令并将结果导出到文件？ ​

A4：可以使用以下方法：

bash
# 使用-o选项
psql -c "SELECT * FROM table1" -o output.txt -d mydb

# 使用重定向
psql -c "SELECT * FROM table1" -d mydb > output.txt

# 在psql交互模式下
\o output.txt
SELECT * FROM table1;
\o

Q5：如何使用pg_ctl管理PostgreSQL服务？ ​

A5：pg_ctl是PostgreSQL服务器控制工具，常用命令包括：

bash
# 启动服务
pg_ctl -D /var/lib/postgresql/14/main start

# 停止服务（快速模式）
pg_ctl -D /var/lib/postgresql/14/main stop -m fast

# 重启服务
pg_ctl -D /var/lib/postgresql/14/main restart

# 重新加载配置
pg_ctl -D /var/lib/postgresql/14/main reload

# 查看状态
pg_ctl -D /var/lib/postgresql/14/main status

Q6：如何使用pg_basebackup创建复制备份？ ​

A6：使用pg_basebackup创建复制备份的示例：

bash
# 在从服务器上执行
pg_basebackup -h master.example.com -p 5432 -U replication -D /var/lib/postgresql/14/main -P -X stream -C -S replica_slot_name -R

其中：

  • -X stream：流式传输WAL文件
  • -C：创建复制槽
  • -S：指定复制槽名称
  • -R：创建recovery.conf文件