# 1. 硬件配置
– 服务器：2U机架服务器
– CPU：8核Intel Xeon
– 内存：32GB RAM
– 存储：4×1TB SSD，RAID 10

# 2. 存储分区
– /boot：500MB
– /：50GB
– /swap：8GB
– /postgresql/data：剩余空间（约3.5TB）

# 3. 表空间设计
– fgedu_data：存储表数据
– fgedu_index：存储索引
– fgedu_wal：存储WAL日志

# 4. 配置步骤
# 创建表空间目录
$ sudo mkdir -p /postgresql/tablespaces/data
$ sudo mkdir -p /postgresql/tablespaces/index
$ sudo mkdir -p /postgresql/tablespaces/wal
$ sudo chown -R postgres:postgres /postgresql/tablespaces

# 创建表空间
$ psql -U postgres -c “CREATE TABLESPACE fgedu_data OWNER fgedu LOCATION ‘/postgresql/tablespaces/data’;”
$ psql -U postgres -c “CREATE TABLESPACE fgedu_index OWNER fgedu LOCATION ‘/postgresql/tablespaces/index’;”
$ psql -U postgres -c “CREATE TABLESPACE fgedu_wal OWNER fgedu LOCATION ‘/postgresql/tablespaces/wal’;”

# 配置WAL目录
$ sudo vi /postgresql/fgdata/postgresql.conf
wal_directory = ‘/postgresql/tablespaces/wal’

# 重启PostgreSQL
$ sudo systemctl restart postgresql

# 验证配置
$ psql -U fgedu -d fgedudb -c “\db”
$ psql -U fgedu -d fgedudb -c “SHOW wal_directory;”

# 1. 优化文件系统
$ sudo vi /etc/fstab
/dev/md0 /postgresql/data xfs defaults,noatime,nodiratime,logbufs=8,logbsize=256k 0 0

# 2. 优化PostgreSQL参数
$ sudo vi /postgresql/fgdata/postgresql.conf
shared_buffers = 8GB
work_mem = 64MB
maintenance_work_mem = 2GB
random_page_cost = 1.0
seq_page_cost = 1.0
effective_cache_size = 24GB
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB

# 3. 优化表空间布局
# 将热点表和索引存储到SSD
$ psql -U fgedu -d fgedudb -c “ALTER TABLE fgedu_users SET TABLESPACE fgedu_data;”
$ psql -U fgedu -d fgedudb -c “ALTER INDEX fgedu_users_email_idx SET TABLESPACE fgedu_index;”

# 4. 监控存储性能
$ iostat -x 1
Linux 5.4.0-80-generic (server) 04/06/2026 _x86_64_ (8 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.00 0.00 0.00 100.00

device r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
md0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

# 5. 测试存储性能
$ pgbench -i -s 10 fgedudb
$ pgbench -c 10 -j 2 -T 60 fgedudb

# 输出示例
starting vacuum…end.
transaction type:
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 2
duration: 60 s
number of transactions actually processed: 15480
latency average = 38.767 ms
tps = 257.991519 (including connections establishing)
tps = 258.046233 (excluding connections establishing)

# 1. 配置Prometheus监控
$ sudo vi /etc/prometheus/prometheus.yml
– job_name: ‘node’
static_configs:
– targets: [‘fgedu.localhost:9100’]

– job_name: ‘postgresql’
static_configs:
– targets: [‘fgedu.localhost:9187’]

# 2. 启动监控服务
$ sudo systemctl start prometheus
$ sudo systemctl start node_exporter
$ sudo systemctl start prometheus-postgres-exporter

# 3. 配置Grafana面板
# 导入PostgreSQL和节点监控面板

# 4. 设置告警规则
$ sudo vi /etc/prometheus/rules/storage_alerts.yml
groups:
– name: storage_alerts
rules:
– alert: DiskSpaceLow
expr: (node_filesystem_size_bytes{mountpoint=”/postgresql/data”} – node_filesystem_free_bytes{mountpoint=”/postgresql/data”}) / node_filesystem_size_bytes{mountpoint=”/postgresql/data”} * 100 > 80
for: 5m
labels:
severity: warning
annotations:
summary: “Disk space low on {{ $labels.instance }}”
description: “Disk space usage on {{ $labels.instance }} is {{ $value | humanizePercentage }}”

# 5. 验证监控
$ curl http://fgedu.localhost:9090/targets
$ curl http://fgedu.localhost:3000/dashboards