# ========================================
# 文档信息
# ========================================
文档编号：PG-ARCH-001
文档名称：PostgreSQL数据库架构设计文档
版本号：V1.0
编制日期：2026-04-07
编制人员：XXX

# ========================================
# 1. 项目概述
# ========================================
1.1 项目背景
本项目为XX公司电商平台PostgreSQL数据库部署项目，旨在构建高可用、高性能的数据库系统，支撑业务系统稳定运行。

1.2 项目目标
– 部署3节点PostgreSQL高可用集群
– 实现数据平滑迁移，数据量500GB
– 确保系统可用性达到99.9%
– 提供完善的监控告警机制

1.3 项目范围
包含：
– 数据库服务器部署
– 高可用架构搭建
– 数据迁移
– 性能优化
– 监控告警配置
– 运维培训

# ========================================
# 2. 架构设计
# ========================================
2.1 整体架构

┌─────────────┐
│ 应用层 │
└──────┬──────┘
│
┌──────┴──────┐
│ Pgpool-II │
│ 读写分离 │
└──────┬──────┘
│
┌───────────────────┼───────────────────┐
│ │ │
▼ ▼ ▼
┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ PostgreSQL │────▶│ PostgreSQL │ │ PostgreSQL │
│ 主库 │ │ 备库1 │ │ 备库2 │
│192.168.1.100│ │192.168.1.101│ │192.168.1.102│
└─────────────┘ └─────────────┘ └─────────────┘
│ │ │
└───────────────────┼───────────────────┘
│
┌──────┴──────┐
│ etcd │
│ 集群配置 │
└─────────────┘

2.2 技术选型
– 数据库版本：PostgreSQL 18.0
– 高可用方案：Patroni + etcd
– 读写分离：Pgpool-II
– 监控方案：Prometheus + Grafana
– 备份方案：pg_basebackup + WAL归档

2.3 部署架构
节点规划：
– node1：192.168.1.100，主库
– node2：192.168.1.101，备库
– node3：192.168.1.102，备库
– backup：192.168.1.103，备份服务器
– monitor：192.168.1.104，监控服务器

# ========================================
# 3. 数据库设计
# ========================================
3.1 表空间设计
– fgedutbs_data：数据表空间，存储业务数据
– fgedutbs_index：索引表空间，存储索引数据
– fgedutbs_temp：临时表空间，存储临时数据

3.2 用户权限设计
– fgedu：超级用户，用于管理
– fgedu_fgapp：应用用户，读写权限
– fgedu_read：只读用户，查询权限
– fgedu_repl：复制用户，复制权限

3.3 参数配置设计
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 256MB
max_connections = 300
wal_level = replica
max_wal_senders = 10

# ========================================
# 4. 高可用设计
# ========================================
4.1 故障切换设计
– Patroni监控数据库状态
– etcd存储集群配置
– 自动故障检测和切换
– 切换时间小于30秒

4.2 数据同步设计
– 流复制实现数据同步
– 同步模式保证数据一致性
– 复制延迟监控

# ========================================
# 5. 备份设计
# ========================================
5.1 备份策略
– 全量备份：每天凌晨2:00
– WAL归档：持续
– 备份保留：30天
– 备份验证：每周

5.2 恢复策略
– 全量恢复：pg_basebackup恢复
– 时间点恢复：WAL归档恢复
– 恢复测试：每月

# ========================================
# 6. 监控设计
# ========================================
6.1 监控指标
– 系统资源：CPU、内存、磁盘、网络
– 数据库指标：连接数、TPS、缓存命中率
– 复制指标：复制延迟、复制状态
– 备份指标：备份状态、备份大小

6.2 告警规则
– CPU使用率 > 80%
– 内存使用率 > 85%
– 磁盘使用率 > 85%
– 复制延迟 > 10MB
– 备份失败

# ========================================
# 文档信息
# ========================================
文档编号：PG-DEPLOY-001
文档名称：PostgreSQL数据库部署实施文档
版本号：V1.0
编制日期：2026-04-07

# ========================================
# 1. 环境准备
# ========================================
1.1 硬件要求
– CPU：16核及以上
– 内存：64GB及以上
– 磁盘：2TB SSD
– 网络：万兆网卡

1.2 软件要求
– 操作系统：Oracle Linux 9.3
– 数据库：PostgreSQL 18.0
– 高可用：Patroni 3.2.0
– 配置中心：etcd 3.5.12

# ========================================
# 2. 系统配置
# ========================================
2.1 关闭防火墙
$ systemctl stop firewalld
$ systemctl disable firewalld

Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

2.2 关闭SELinux
$ setenforce 0
$ sed -i ‘s/SELINUX=enforcing/SELINUX=disabled/’ /etc/selinux/config

2.3 配置内核参数
$ vi /etc/sysctl.conf
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 4294967296
kernel.shmmax = 34359738368
vm.swfgappiness = 1
vm.dirty_ratio = 15
vm.dirty_background_ratio = 3

$ sysctl -p

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 4294967296
kernel.shmmax = 34359738368
vm.swfgappiness = 1
vm.dirty_ratio = 15
vm.dirty_background_ratio = 3

2.4 配置资源限制
$ vi /etc/security/limits.conf
pgsql soft nofile 65536
pgsql hard nofile 65536
pgsql soft nproc 65536
pgsql hard nproc 65536

# ========================================
# 3. 用户和目录创建
# ========================================
3.1 创建用户
$ groupadd -g 1000 fgedudb
$ useradd -u 1000 -g fgedudb pgsql

3.2 创建目录
$ mkdir -p /postgresql/fgapp
$ mkdir -p /postgresql/fgdata
$ mkdir -p /postgresql/archive
$ mkdir -p /postgresql/backup
$ chown -R pgsql:fgedudb /postgresql

# ========================================
# 4. PostgreSQL安装
# ========================================
4.1 解压安装包
$ tar -xzf postgresql-18.0.tar.gz -C /postgresql/src

4.2 编译安装
$ cd /postgresql/src/postgresql-18.0
$ ./configure –prefix=/postgresql/fgapp –with-openssl –with-libxml

checking build system type… x86_64-pc-linux-gnu
checking host system type… x86_64-pc-linux-gnu
configure: using CFLAGS=’-O2 -Wall’
configure: using LDFLAGS=’-Wl,–as-needed’
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
configure: success

$ make && make install

make[1]: Entering directory ‘/postgresql/src/postgresql-18.0/src’
make[2]: Entering directory ‘/postgresql/src/postgresql-18.0/src/common’
…
make[1]: Leaving directory ‘/postgresql/src/postgresql-18.0’
PostgreSQL installation complete.

4.3 配置环境变量
$ vi /home/pgsql/.bash_profile
export PGHOME=/postgresql/fgapp
export PGDATA=/postgresql/fgdata
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

$ source /home/pgsql/.bash_profile

# ========================================
# 5. 数据库初始化
# ========================================
5.1 初始化数据库
$ su – pgsql
$ initdb -D /postgresql/fgdata -E UTF8 –locale=en_US.UTF-8

The files belonging to this database system will be owned by user “pgsql”.
This user must also own the server process.

The database cluster will be initialized with locale “en_US.UTF-8”.
The default text search configuration will be set to “english”.

Data page checksums are disabled.

creating directory /postgresql/fgdata … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … Asia/Shanghai
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok

Success. You can now start the database server using:

pg_ctl -D /postgresql/fgdata -l logfile start

5.2 启动数据库
$ pg_ctl -D /postgresql/fgdata start

waiting for server to start…. done
server started

# ========================================
# 6. 参数配置
# ========================================
6.1 修改postgresql.conf
$ vi /postgresql/fgdata/postgresql.conf
listen_addresses = ‘*’
port = 5432
max_connections = 300
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 256MB
maintenance_work_mem = 1GB
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

6.2 修改pg_hba.conf
$ vi /postgresql/fgdata/pg_hba.conf
host all all 192.168.1.0/24 md5
host replication fgedu_repl 192.168.1.0/24 md5

6.3 重启数据库
$ pg_ctl -D /postgresql/fgdata restart

waiting for server to shut down…. done
server stopped
waiting for server to start…. done
server started

# ========================================
# 7. 用户创建
# ========================================
7.1 创建管理用户
$ psql -U pgsql -d postgres -c ”
CREATE USER fgedu WITH SUPERUSER PASSWORD ‘Fgedu@2026’;
”

CREATE ROLE

7.2 创建应用用户
$ psql -U fgedu -d postgres -c ”
CREATE USER fgedu_fgapp WITH PASSWORD ‘FgeduApp@2026’;
GRANT ALL PRIVILEGES ON DATABASE fgedudb TO fgedu_fgapp;
”

CREATE ROLE
GRANT

7.3 创建复制用户
$ psql -U fgedu -d postgres -c ”
CREATE USER fgedu_repl WITH REPLICATION PASSWORD ‘FgeduRepl@2026’;
”

CREATE ROLE

# ========================================
# 8. 数据库创建
# ========================================
8.1 创建数据库
$ psql -U fgedu -d postgres -c ”
CREATE DATABASE fgedudb WITH ENCODING ‘UTF8’ OWNER fgedu;
”

CREATE DATABASE

8.2 创建表空间
$ psql -U fgedu -d fgedudb -c ”
CREATE TABLESPACE fgedutbs_data OWNER fgedu LOCATION ‘/postgresql/tbs_data’;
CREATE TABLESPACE fgedutbs_index OWNER fgedu LOCATION ‘/postgresql/tbs_index’;
”

CREATE TABLESPACE
CREATE TABLESPACE

# ========================================
# 9. 高可用配置
# ========================================
9.1 配置Patroni
$ vi /etc/patroni/fgedudb.yml
scope: fgedudb_cluster
namespace: /db/
name: node1

restapi:
listen: 192.168.1.100:8008
connect_address: 192.168.1.100:8008

etcd:
hosts: 192.168.1.100:2379,192.168.1.101:2379,192.168.1.102:2379

bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
max_connections: 300
shared_buffers: 16GB
wal_level: replica
max_wal_senders: 10
max_replication_slots: 10

initdb:
– encoding: UTF8
– locale: en_US.UTF-8

postgresql:
listen: 192.168.1.100:5432
connect_address: 192.168.1.100:5432
data_dir: /postgresql/fgdata
authentication:
replication:
username: fgedu_repl
password: FgeduRepl@2026
superuser:
username: fgedu
password: Fgedu@2026

9.2 启动Patroni
$ systemctl start patroni
$ systemctl enable patroni

Created symlink /etc/systemd/system/multi-user.target.wants/patroni.service → /etc/systemd/system/patroni.service.

9.3 验证集群状态
$ 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 |
+——–+—————+———+———+—-+———–+

# ========================================
# 文档信息
# ========================================
文档编号：PG-OPS-001
文档名称：PostgreSQL数据库运维管理文档
版本号：V1.0
编制日期：2026-04-07

# ========================================
# 1. 日常运维
# ========================================
1.1 服务管理
# 启动服务
$ systemctl start patroni

# 停止服务
$ systemctl stop patroni

# 重启服务
$ systemctl restart patroni

# 查看状态
$ systemctl status patroni

● patroni.service – PostgreSQL High Availability
Loaded: loaded (/etc/systemd/system/patroni.service; enabled)
Active: active (running) since Mon 2026-04-07 10:00:00 CST; 1h ago
Main PID: 12345 (patroni)
Tasks: 5 (limit: 4915)
Memory: 50.0M
CGroup: /system.slice/patroni.service
└─12345 /usr/bin/python3 /usr/local/bin/patroni /etc/patroni/fgedudb.yml

1.2 连接管理
# 查看当前连接
$ psql -U fgedu -d fgedudb -c ”
SELECT pid, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = ‘active’;
”

pid | usename | client_addr | state | query
——+———-+————–+——–+—————————
1234 | fgedu_fgapp| 192.168.1.50 | active | SELECT * FROM fgedu_orders
1235 | fgedu_fgapp| 192.168.1.51 | active | UPDATE fgedu_orders SET…
(2 rows)

# 终止空闲连接
$ psql -U fgedu -d fgedudb -c ”
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = ‘idle’
AND now() – state_change > interval ’10 minutes’;
”

pg_terminate_backend
———————-
t
t
(2 rows)

1.3 日志管理
# 查看数据库日志
$ tail -100 /postgresql/fgdata/pg_log/postgresql-$(date +%Y-%m-%d).log

2026-04-07 10:00:00.123 CST [12345] LOG: database system is ready to accept connections
2026-04-07 10:00:01.234 CST [12346] LOG: checkpoint starting: time
2026-04-07 10:00:05.345 CST [12346] LOG: checkpoint complete

# 查看Patroni日志
$ journalctl -u patroni -f

Apr 07 10:00:00 node1 patroni[12345]: INFO: Lock owner: node1; I am node1
Apr 07 10:00:00 node1 patroni[12345]: INFO: no action. I am the leader with the lock

# ========================================
# 2. 监控管理
# ========================================
2.1 系统监控
# CPU监控
$ top -bn1 | head -20

top – 10:00:00 up 30 days, 2:30, 2 users, load average: 0.52, 0.58, 0.59
Tasks: 156 total, 1 running, 155 sleeping, 0 stopped, 0 zombie
%Cpu(s): 5.2 us, 2.1 sy, 0.0 ni, 92.3 id, 0.0 wa, 0.4 hi, 0.0 si
MiB Mem : 65536.0 total, 12345.6 free, 45000.0 used, 8190.4 buff/cache

# 内存监控
$ free -h

total used free shared buff/cache available
Mem: 64Gi 45Gi 12Gi 2.0Gi 8.0Gi 20Gi
Swap: 8.0Gi 192Mi 7.8Gi

# 磁盘监控
$ df -h

Filesystem Size Used Avail Use% Mounted on
/dev/sda1 2.0T 500G 1.5T 25% /postgresql
/dev/sdb1 1.0T 200G 800G 20% /postgresql/backup

2.2 数据库监控
# 连接数监控
$ psql -U fgedu -d fgedudb -c ”
SELECT count(*) as total_connections,
count(*) FILTER (WHERE state = ‘active’) as active,
count(*) FILTER (WHERE state = ‘idle’) as idle
FROM pg_stat_activity;
”

total_connections | active | idle
——————-+——–+——
150 | 10 | 140
(1 row)

# TPS监控
$ psql -U fgedu -d fgedudb -c ”
SELECT xact_commit, xact_rollback,
xact_commit + xact_rollback as total
FROM pg_stat_database
WHERE datname = ‘fgedudb’;
”

xact_commit | xact_rollback | total
————-+—————+———
1500000 | 1000 | 1501000
(1 row)

# 缓存命中率
$ psql -U fgedu -d fgedudb -c ”
SELECT
round(blks_hit::numeric / (blks_hit + blks_read) * 100, 2) as cache_hit_ratio
FROM pg_stat_database
WHERE datname = ‘fgedudb’;
”

cache_hit_ratio
—————–
99.50
(1 row)

# ========================================
# 3. 备份恢复
# ========================================
3.1 全量备份
# 执行全量备份
$ pg_basebackup -h fgedu.localhost -U fgedu_repl -D /postgresql/backup/base_$(date +%Y%m%d) -Fp -Xs -P

24567/24567 kB (100%), 1/1 tablespace

3.2 WAL归档
# 查看归档状态
$ psql -U fgedu -d fgedudb -c ”
SELECT name, setting FROM pg_settings WHERE name = ‘archive_mode’;
”

name | setting
—————+———
archive_mode | on
(1 row)

# 查看归档文件
$ ls -lh /postgresql/archive/ | tail -10

-rw——- 1 pgsql fgedudb 16M Apr 7 10:00 000000010000000000000001
-rw——- 1 pgsql fgedudb 16M Apr 7 10:05 000000010000000000000002
-rw——- 1 pgsql fgedudb 16M Apr 7 10:10 000000010000000000000003

3.3 数据恢复
# 恢复全量备份
$ pg_ctl -D /postgresql/fgdata stop
$ rm -rf /postgresql/fgdata/*
$ cp -r /postgresql/backup/base_20260407/* /postgresql/fgdata/

# 配置恢复参数
$ vi /postgresql/fgdata/postgresql.conf
restore_command = ‘cp /postgresql/archive/%f %p’
recovery_target_time = ‘2026-04-07 10:00:00’

# 创建恢复标志
$ touch /postgresql/fgdata/recovery.signal

# 启动恢复
$ pg_ctl -D /postgresql/fgdata start

# ========================================
# 4. 性能优化
# ========================================
4.1 慢查询分析
# 查看慢查询
$ psql -U fgedu -d fgedudb -c ”
SELECT query, calls, total_time/calls as avg_time
FROM pg_stat_statements
ORDER BY avg_time DESC
LIMIT 10;
”

query | calls | avg_time
——————————————–+——-+———-
SELECT * FROM fgedu_orders WHERE … | 1000 | 150.50
UPDATE fgedu_orders SET … WHERE … | 500 | 120.30
(2 rows)

4.2 索引优化
# 查看缺失索引
$ psql -U fgedu -d fgedudb -c ”
SELECT schemaname, relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY seq_scan DESC
LIMIT 10;
”

schemaname | relname | seq_scan | idx_scan
————+—————-+———-+———-
public | fgedu_logs | 50000 | 100
public | fgedu_temp | 30000 | 50
(2 rows)

# 创建索引
$ psql -U fgedu -d fgedudb -c ”
CREATE INDEX idx_fgedu_logs_created_at ON fgedu_logs(created_at);
”

CREATE INDEX

4.3 VACUUM优化
# 查看表膨胀
$ psql -U fgedu -d fgedudb -c ”
SELECT schemaname, relname, n_dead_tup, n_live_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 10;
”

schemaname | relname | n_dead_tup | n_live_tup | dead_ratio
————+—————-+————+————+————
public | fgedu_orders | 500000 | 50000000 | 1.00
public | fgedu_customers| 200000 | 20000000 | 1.00
(2 rows)

# 执行VACUUM
$ psql -U fgedu -d fgedudb -c “VACUUM ANALYZE fgedu_orders;”

VACUUM

# ========================================
# 5. 故障处理
# ========================================
5.1 连接数超限
# 问题现象
FATAL: sorry, too many clients already

# 处理步骤
# 1. 查看连接数
$ psql -U fgedu -d fgedudb -c “SELECT count(*) FROM pg_stat_activity;”

count
——-
300
(1 row)

# 2. 释放空闲连接
$ psql -U fgedu -d fgedudb -c ”
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = ‘idle’ AND usename NOT IN (‘fgedu’, ‘pgsql’);
”

# 3. 调整最大连接数
$ psql -U fgedu -d fgedudb -c “ALTER SYSTEM SET max_connections = 500;”
$ pg_ctl -D /postgresql/fgdata restart

5.2 复制延迟
# 问题现象
复制延迟超过阈值

# 处理步骤
# 1. 查看复制状态
$ psql -U fgedu -d fgedudb -c ”
SELECT client_addr, state,
pg_wal_lsn_diff(sent_lsn, replay_lsn) as lag_bytes
FROM pg_stat_replication;
”

client_addr | state | lag_bytes
—————+———-+———–
192.168.1.101 | streaming| 10485760
(2 row)

# 2. 检查备库状态
$ ssh 192.168.1.101 “systemctl status patroni”

# 3. 重启复制
$ ssh 192.168.1.101 “systemctl restart patroni”

5.3 磁盘空间不足
# 问题现象
磁盘使用率超过85%

# 处理步骤
# 1. 查看磁盘使用
$ df -h

Filesystem Size Used Avail Use% Mounted on
/dev/sda1 2.0T 1.8T 200G 90% /postgresql

# 2. 清理日志
$ find /postgresql/fgdata/pg_log -name “*.log” -mtime +30 -delete

# 3. 清理备份
$ find /postgresql/backup -name “base_*” -mtime +30 -exec rm -rf {} \;

# 4. 清理WAL归档
$ find /postgresql/archive -name “*” -mtime +7 -delete