# 1. 服务启动验收
# 检查数据库服务状态
$ pg_isready -h fgedu.localhost -p 5432 -U fgedu -d fgedudb

fgedu.localhost:5432 – accepting connections

# 检查数据库进程
$ ps -ef | grep postgres | grep -v grep

pgsql 12345 1 0 Apr01 ? 00:20:00 /postgresql/fgapp/bin/postgres -D /postgresql/fgdata
pgsql 12346 12345 0 Apr01 ? 00:15:00 postgres: checkpointer
pgsql 12347 12345 0 Apr01 ? 00:10:00 postgres: background writer
pgsql 12348 12345 0 Apr01 ? 00:05:00 postgres: walwriter
pgsql 12349 12345 0 Apr01 ? 00:08:00 postgres: autovacuum launcher

# 验收结论：[通过] 数据库服务正常启动

# 2. 连接测试验收
# 测试本地连接
$ psql -h fgedu.localhost -p 5432 -U fgedu -d fgedudb -c “SELECT version();”

version
—————————————————————————————————————-
PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.3.1 20221121 (Red Hat 11.3.1-2), 64-bit
(1 row)

# 测试远程连接
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT current_database();”

current_database
——————
fgedudb
(1 row)

# 验收结论：[通过] 数据库连接正常

# 3. 基本操作验收
# 创建表测试
$ psql -U fgedu -d fgedudb -c ”
CREATE TABLE fgedu_acceptance_test (
id serial PRIMARY KEY,
name varchar(100),
created_at timestamp default now()
);
”

CREATE TABLE

# 插入数据测试
$ psql -U fgedu -d fgedudb -c ”
INSERT INTO fgedu_acceptance_test (name) VALUES (‘验收测试’);
”

INSERT 0 1

# 查询数据测试
$ psql -U fgedu -d fgedudb -c ”
SELECT * FROM fgedu_acceptance_test;
”

id | name | created_at
—-+————+—————————-
1 | 验收测试 | 2026-04-07 10:00:00.123456
(1 row)

# 更新数据测试
$ psql -U fgedu -d fgedudb -c ”
UPDATE fgedu_acceptance_test SET name = ‘验收测试更新’ WHERE id = 1;
”

UPDATE 1

# 删除数据测试
$ psql -U fgedu -d fgedudb -c ”
DELETE FROM fgedu_acceptance_test WHERE id = 1;
”

DELETE 1

# 清理测试表
$ psql -U fgedu -d fgedudb -c “DROP TABLE fgedu_acceptance_test;”

DROP TABLE

# 验收结论：[通过] 数据库基本操作正常

# 1. 主从复制验收
# 检查主库状态
$ psql -U fgedu -d fgedudb -c ”
SELECT pg_is_in_recovery();
”

pg_is_in_recovery
——————-
f
(1 row)

# 检查备库状态
$ psql -h 192.168.1.101 -U fgedu -d fgedudb -c ”
SELECT pg_is_in_recovery();
”

pg_is_in_recovery
——————-
t
(1 row)

# 检查复制状态
$ psql -U fgedu -d fgedudb -c ”
SELECT
client_addr,
state,
sent_lsn,
replay_lsn,
pg_wal_lsn_diff(sent_lsn, replay_lsn) as lag_bytes
FROM pg_stat_replication;
”

client_addr | state | sent_lsn | replay_lsn | lag_bytes
—————+———-+———–+————+———–
192.168.1.101 | streaming| 0/65000128| 0/65000128 | 0
192.168.1.102 | streaming| 0/65000128| 0/65000128 | 0
(2 rows)

# 验收结论：[通过] 主从复制正常

# 2. 故障切换验收
# 记录当前主库
$ patronictl -c /etc/patroni/fgedudb.yml list
+ Cluster: fgedudb_cluster ——-+—-+———–+
| Member | Host | Role | State | TL | Lag in MB |
+——–+—————+———+———+—-+———–+
| node1 | 192.168.1.100 | Leader | running | 1 | |
| node2 | 192.168.1.101 | Replica | running | 1 | 0 |
| node3 | 192.168.1.102 | Replica | running | 1 | 0 |
+——–+—————+———+———+—-+———–+

# 模拟主库故障
$ ssh 192.168.1.100 “systemctl stop patroni”

# 观察切换过程
$ sleep 30
$ patronictl -c /etc/patroni/fgedudb.yml list
+ Cluster: fgedudb_cluster ——-+—-+———–+
| Member | Host | Role | State | TL | Lag in MB |
+——–+—————+———+———+—-+———–+
| node2 | 192.168.1.101 | Leader | running | 2 | |
| node3 | 192.168.1.102 | Replica | running | 2 | 0 |
+——–+—————+———+———+—-+———–+

# 验证新主库
$ psql -h 192.168.1.101 -U fgedu -d fgedudb -c “SELECT pg_is_in_recovery();”

pg_is_in_recovery
——————-
f
(1 row)

# 恢复原主库
$ ssh 192.168.1.100 “systemctl start patroni”

# 验收结论：[通过] 故障切换正常，切换时间约15秒

# 3. 数据一致性验收
# 在主库插入测试数据
$ psql -h 192.168.1.101 -U fgedu -d fgedudb -c ”
INSERT INTO fgedu_acceptance_test (name) VALUES (‘一致性测试’);
SELECT * FROM fgedu_acceptance_test;
”

id | name | created_at
—-+————–+—————————-
1 | 一致性测试 | 2026-04-07 10:05:00.123456
(1 row)

# 在备库查询数据
$ psql -h 192.168.1.102 -U fgedu -d fgedudb -c ”
SELECT * FROM fgedu_acceptance_test;
”

id | name | created_at
—-+————–+—————————-
1 | 一致性测试 | 2026-04-07 10:05:00.123456
(1 row)

# 验收结论：[通过] 数据一致性正常

# 1. TPS测试
# 使用pgbench进行TPS测试
$ pgbench -h 192.168.1.100 -U fgedu -d fgedudb -i -s 100

dropping old tables…
NOTICE: table “pgbench_accounts” does not exist, skipping
NOTICE: table “pgbench_branches” does not exist, skipping
NOTICE: table “pgbench_history” does not exist, skipping
NOTICE: table “pgbench_tellers” does not exist, skipping
creating tables…
generating data (client-side)…
10000000 of 10000000 tuples (100%) done (elapsed 120.56 s, remaining 0.00 s)
vacuum…
creating primary keys…
done in 150.23 s (drop tables 0.01 s, create tables 0.01 s, client-side generate 120.56 s, vacuum 25.12 s, primary keys 4.53 s).

# 执行TPS测试
$ pgbench -h 192.168.1.100 -U fgedu -d fgedudb -c 100 -j 10 -T 300

pgbench (18.0)
transaction type:
scaling factor: 100
query mode: simple
number of clients: 100
number of threads: 10
duration: 300 s
number of transactions actually processed: 450000
latency average = 66.667 ms
initial connection time = 1234.567 ms
tps = 1500.123456 (without initial connection time)

# 验收结论：[通过] TPS = 1500，超过验收标准1000

# 2. 查询响应时间测试
# 简单查询测试
$ psql -U fgedu -d fgedudb -c ”
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_id = 12345;
”

QUERY PLAN
————————————————————————————————————————–
Index Scan using fgedu_orders_pkey on fgedu_orders (cost=0.43..8.45 rows=1 width=200) (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (order_id = 12345)
Planning Time: 0.050 ms
Execution Time: 0.030 ms
(4 rows)

# 验收结论：[通过] 简单查询响应时间0.030ms，小于验收标准10ms

# 复杂查询测试
$ psql -U fgedu -d fgedudb -c ”
EXPLAIN ANALYZE
SELECT o.order_id, c.customer_name, p.product_name, o.amount
FROM fgedu_orders o
JOIN fgedu_customers c ON o.customer_id = c.customer_id
JOIN fgedu_products p ON o.product_id = p.product_id
WHERE o.created_at >= ‘2026-01-01’
ORDER BY o.created_at DESC
LIMIT 100;
”

QUERY PLAN
—————————————————————————————————————————————
Limit (cost=50.12..52.34 rows=100 width=200) (actual time=15.234..15.456 rows=100 loops=1)
-> Sort (cost=50.12..52.34 rows=100 width=200) (actual time=15.233..15.345 rows=100 loops=1)
Sort Key: o.created_at DESC
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.86..48.90 rows=100 width=200) (actual time=0.123..12.345 rows=100 loops=1)
-> Nested Loop (cost=0.43..25.56 rows=100 width=150) (actual time=0.089..8.234 rows=100 loops=1)
-> Index Scan using idx_orders_created_at on fgedu_orders o (cost=0.43..10.23 rows=100 width=100) (actual time=0.045..2.123 rows=100 loops=1)
Index Cond: (created_at >= ‘2026-01-01’::date)
-> Index Scan using fgedu_customers_pkey on fgedu_customers c (cost=0.00..0.15 rows=1 width=50) (actual time=0.056..0.058 rows=1 loops=100)
Index Cond: (customer_id = o.customer_id)
-> Index Scan using fgedu_products_pkey on fgedu_products p (cost=0.43..0.23 rows=1 width=50) (actual time=0.034..0.036 rows=1 loops=100)
Index Cond: (product_id = o.product_id)
Planning Time: 1.234 ms
Execution Time: 15.567 ms
(12 rows)

# 验收结论：[通过] 复杂查询响应时间15.567ms，小于验收标准100ms

# 3. 并发连接测试
# 测试300并发连接
$ pgbench -h 192.168.1.100 -U fgedu -d fgedudb -c 300 -j 10 -T 60

pgbench (18.0)
transaction type:
scaling factor: 100
query mode: simple
number of clients: 300
number of threads: 10
duration: 60 s
number of transactions actually processed: 90000
latency average = 200.000 ms
initial connection time = 5678.901 ms
tps = 1500.000000 (without initial connection time)

# 验收结论：[通过] 并发连接300正常工作

# 1. 用户权限验收
# 检查用户列表
$ psql -U fgedu -d fgedudb -c “\du”

List of roles
Role name | Attributes | Member of
————–+————————————————————+—————–
fgedu | Superuser, Create role, Create DB | {}
fgedu_fgapp | | {}
fgedu_read | Cannot login | {}
fgedu_repl | Replication | {}
pgsql | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

# 验证权限分离
# 应用用户权限测试
$ psql -U fgedu_fgapp -d fgedudb -c “SELECT * FROM fgedu_orders LIMIT 1;”

order_id | customer_id | product_id | amount | status | created_at
———-+————-+————+——–+——–+—————————-
1 | 100 | 1000 | 500.00 | active | 2026-01-01 10:00:00.123456
(1 row)

# 应用用户不能创建表
$ psql -U fgedu_fgapp -d fgedudb -c “CREATE TABLE test (id int);”

ERROR: permission denied for schema public

# 验收结论：[通过] 用户权限配置正确

# 2. 连接加密验收
# 检查SSL配置
$ psql -U fgedu -d fgedudb -c ”
SELECT ssl, version, cipher
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();
”

ssl | version | cipher
—–+———+———————————–
t | TLSv1.3 | TLS_AES_256_GCM_SHA384
(1 row)

# 验收结论：[通过] 连接加密正常

# 3. 审计日志验收
# 检查审计配置
$ psql -U fgedu -d fgedudb -c ”
SHOW log_connections;
SHOW log_disconnections;
SHOW log_statement;
”

log_connections
—————–
on
(1 row)

log_disconnections
——————–
on
(1 row)

log_statement
—————
ddl
(1 row)

# 检查审计日志
$ grep “connection” /postgresql/fgdata/pg_log/postgresql-$(date +%Y-%m-%d).log | tail -5

2026-04-07 10:00:00.123 CST [12345] LOG: connection received: host=192.168.1.50 port=54321
2026-04-07 10:00:00.234 CST [12345] LOG: connection authorized: user=fgedu_fgapp database=fgedudb
2026-04-07 10:05:00.123 CST [12346] LOG: connection received: host=192.168.1.51 port=54322
2026-04-07 10:05:00.234 CST [12346] LOG: connection authorized: user=fgedu_read database=fgedudb

# 验收结论：[通过] 审计日志正常