Logo
ezra-sullivan
发布于 2025-06-07 / 4 阅读
0
0

02 - PostgreSQL 常用的测试数据库

#postgresql

更新时间：2025 年 6 月

使用的 PostgreSQL 版本：17.5

导言

有一些专门为 PostgreSQL 设计的优秀测试数据库，类似于 MySQL 的 test_db。它们通常包含模式、关系、约束和填充好的示例数据，非常适合学习、测试和演示

Pagila

注：支持版本：PostgreSQL 12+

简介

最著名、最接近 MySQL test_db 的 PostgreSQL 测试数据库。它最初由 Devrim Gündüz 基于 MySQL 的 Sakila 示例数据库移植而来，并针对 PostgreSQL 进行了优化和增强。它模拟了一个 DVD 租赁商店

内容

模拟 DVD 租赁商店，包含表 (actor, film, customer, staff, store, rental, payment, inventory, address, city, country 等)、视图、存储过程、函数、触发器

数据量

包含预先生成的数据，规模适中（单表记录 1 万多），非常适合测试

GitHub

https://github.com/devrimgunduz/pagila

导入

Pagila 提供 SQL 脚本文件 (schema, data) 和 Docker 镜像，此处使用 SQL 脚本方式导入

下载 Pagila 文件

直接下载 zip 文件

mkdir -p /usr/local/src/pagila


# 注：http://192.168.111.1:10811 为本地网络代理，没有可以不设置

curl -x http://192.168.111.1:10811 \
    -o /usr/local/src/pagila/pagila-master.zip \
    -L https://github.com/devrimgunduz/pagila/archive/refs/heads/master.zip

解压

unzip -d /usr/local/src/pagila /usr/local/src/pagila/pagila-master.zip

授权

chown -R postgres:postgres /usr/local/src/pagila/

创建数据库

su - postgres -c '
    psql -U postgres -c "CREATE DATABASE pagila;"
'

导入 Schema（表结构）

su - postgres -c '
     psql -U postgres -d pagila -f /usr/local/src/pagila/pagila-master/pagila-schema.sql
'

导入数据

su - postgres -c '
     psql -U postgres -d pagila -f /usr/local/src/pagila/pagila-master/pagila-data.sql
'

检查表

su - postgres -c '
     psql -U postgres -d pagila -c "\dt;"
'

# 输出如下信息
                    List of relations
 Schema |       Name       |       Type        |  Owner   
--------+------------------+-------------------+----------
 public | actor            | table             | postgres
 public | address          | table             | postgres
 public | category         | table             | postgres
 public | city             | table             | postgres
 public | country          | table             | postgres
 public | customer         | table             | postgres
 public | film             | table             | postgres
 public | film_actor       | table             | postgres
 public | film_category    | table             | postgres
 public | inventory        | table             | postgres
 public | language         | table             | postgres
 public | payment          | partitioned table | postgres
 public | payment_p2022_01 | table             | postgres
 public | payment_p2022_02 | table             | postgres
 public | payment_p2022_03 | table             | postgres
 public | payment_p2022_04 | table             | postgres
 public | payment_p2022_05 | table             | postgres
 public | payment_p2022_06 | table             | postgres
 public | payment_p2022_07 | table             | postgres
 public | rental           | table             | postgres
 public | staff            | table             | postgres
 public | store            | table             | postgres
(22 rows)

postgresql-sample-database

参考：PostgreSQL Sample Database

简介

这是 PostgreSQL 官方文档和教程中经常使用的示例数据库。与 Pagila 非常相似（Pagila 就是基于它的概念发展而来）。由 neon 公司（主营产品为一款 Serverless 化的 PG）进行资源维护

内容

与 Pagila 类似，模拟 DVD 租赁商店

数据量

包含预先生成的数据，规模适中（单表记录 1 万多），非常适合测试

官方资源

https://neon.com/postgresql/postgresql-getting-started/postgresql-sample-database (这里提供下载链接和使用说明)

导入

下载

直接下载 zip 文件

mkdir -p /usr/local/src/dvdrental


# 注：http://192.168.111.1:10811 为本地网络代理，没有可以不设置

curl -x http://192.168.111.1:10811 \
    -o /usr/local/src/dvdrental/dvdrental.zip \
    -L https://neon.com/postgresqltutorial/dvdrental.zip

解压

unzip -d /usr/local/src/dvdrental /usr/local/src/dvdrental/dvdrental.zip

授权

chown -R postgres:postgres /usr/local/src/dvdrental/

创建数据库

su - postgres -c '
    psql -U postgres -c "CREATE DATABASE dvdrental;"
'

导入数据（pg_restore）

su - postgres -c '
     pg_restore -U postgres -d dvdrental --format=t -1 /usr/local/src/dvdrental/dvdrental.tar
'

检查表

su - postgres -c '
     psql -U postgres -d dvdrental -c "\dt;"
'

# 输出如下信息
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)

pgbench

参考：PostgreSQL: Documentation: 17: pgbench

pgbench 是 PostgreSQL 自带的基准测试工具，可以用于模拟数据库负载、生成测试数据并进行性能压测

部署

pgbench 随 PostgreSQL 安装包自带

确认 pgbench 已安装（随 PostgreSQL 安装包自带）：

pgbench --version

如果没有安装

# 在基于Debian的系统上
sudo apt-get install postgresql-contrib

# 在基于RHEL的系统上
sudo dnf install postgresql-contrib

常用选项

初始化选项

初始化选项用于生成测试数据

选项说明
-i, --initialize初始化数据库，创建测试表并填充数据
-I, --init-steps=[dtgGvpf]+选择初始化步骤（默认 dtgvp）
• d = 建表
• t = 创建主键
• g = 生成数据
• G = 创建索引
• v = VACUUM
• p = 创建外键
• f = 分析统计
-F, --fillfactor=NUM设置表的填充因子（百分比）
控制表数据页的空闲空间比例
• 默认 100（无空闲空间）
• -F 90 保留10%空闲空间
-n, --no-vacuum初始化时不执行 VACUUM
-q, --quiet安静模式（每 5 秒打印一次进度）
-s NUM, --scale=NUM设置数据规模因子
• 默认 1 = 10万行数据
• -s 100 = 1000万行数据
--foreign-keys创建表之间的外键约束
--index-tablespace=TABLESPACE指定索引的表空间
`--partition-method=(rangehash)`
--partitions=NUM分区数量（默认 0 = 不分区）
--tablespace=TABLESPACE指定表的表空间
--unlogged-tables创建非日志表（不写 WAL）
• 性能更高，但崩溃后数据丢失

压测控制选项

测试脚本选择

选项说明
-b, --builtin=NAME[@W]设置使用的内置测试脚本
• -b tpcb-like（默认 TPC-B 类事务）
• -b select-only（纯查询）
• -b simple-update（简化更新）
• -b list 查看所有内置脚本
• @W 设置权重（如 -b tpcb-like@3 -b select-only@1）
-f, --file=FILENAME[@W]使用自定义SQL脚本
• -f custom.sql
• @W 设置权重
-N, --skip-some-updates跳过部分更新
• 等价于 -b simple-update
-S, --select-only只执行SELECT查询
• 等价于 -b select-only

压测核心参数

选项说明
-c, --client=NUM并发客户端数（模拟用户连接数）
• 默认 1
-C, --connect每个事务建立新连接
• 测试连接开销
-D, --define=VARNAME=VALUE定义脚本变量
• -D user_id=1000
-j, --jobs=NUM工作线程数（应与 CPU 核心线程数匹配）
• 默认 1
-l, --log记录事务耗时到日志文件
-L, --latency-limit=NUM标记超过NUM毫秒的事务为"late"
`-M, --protocol=simpleextended
-n, --no-vacuum测试前不执行 VACUUM
-P, --progress=NUM每 NUM 秒显示进度报告
-r, --report-latencies报告每个命令的延迟统计
-R, --rate=NUM目标TPS（每秒事务数）
• 限流模式
-s, --scale=NUM报告中使用的规模因子
• 可与初始化时的 -s 不同
-t, --transactions=NUM每个客户端运行的事务数
• 默认 10
-T, --time=NUM测试持续时间（秒）
• 优先级高于 -t
-v, --vacuum-all测试前对所有标准表执行 VACUUM
--aggregate-interval=NUM按 NUM 秒间隔聚合报告
--log-prefix=PREFIX事务日志文件前缀
• 默认 pgbench_log
--progress-timestamp使用 Unix 时间戳显示进度
--random-seed=SEED设置随机种子
• time（基于时间，默认）
• rand（真随机）
• 整数（固定种子）
--sampling-rate=NUM事务日志采样率
• 0.01 = 采样1%的事务
--show-script=NAME显示内置脚本源码后退出

通用连接选项

选项说明
-d, --debug输出调试信息
-h, --host=HOSTNAME数据库主机地址
-p, --port=PORT数据库端口
-U, --username=USERNAME数据库用户名
-V, --version输出版本信息
-?, --help显示帮助信息

示例

准备

创建初始化数据库

sudo -i -u postgres  \
    psql -U postgres -c 'CREATE DATABASE pgbench;'

生成数据

# 初始化测试数据（生成 100 万行记录）
sudo -i -u postgres  \
    pgbench -U postgres -i -s 10 pgbench
  • -i: 初始化模式
  • -s: 比例因子（每个单位 = 10万行记录）

测试

测试 TPC-B 类事务（读写）

# 使用 2 个线程，50 个客户端，运行 60 秒
sudo -i -u postgres  \
    pgbench -j 2 -c 50 -T 60 -b tpcb-like pgbench
    
    
# 输出以下信息
pgbench (17.5)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 50
number of threads: 2
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 94177
number of failed transactions: 0 (0.000%)
latency average = 31.875 ms
initial connection time = 145.042 ms
tps = 1568.638411 (without initial connection time)

测试只读

# 使用 2 个线程，50 个客户端，运行 60 秒
sudo -i -u postgres  \
    pgbench -j 2 -c 50 -T 60 -b select-only pgbench
    
    
# 输出以下信息

starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 10
query mode: simple
number of clients: 50
number of threads: 2
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 895843
number of failed transactions: 0 (0.000%)
latency average = 3.353 ms
initial connection time = 112.666 ms
tps = 14913.544218 (without initial connection time)

选择

学习 & 一般测试： Pagila 或 postgresql-sample-database 是最佳选择。结构清晰、数据量适中、功能丰富（包含视图、函数等）

性能基准测试： 使用 pgbench

03 - PostgreSQL 的...
00 - kubernetes 简介

评论