# 1. 环境准备
– 硬件：8核CPU、32GB内存、1TB SSD（RAID 10）
– 操作系统：Oracle Linux 9.3
– PostgreSQL版本：14.0
– 监控工具：Prometheus、Grafana
– 高可用方案：主从复制

# 2. 硬件和操作系统配置
$ sudo vi /etc/sysctl.conf

# 内核参数优化
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_keepalive_intvl = 15

# 文件系统参数
vm.swfgappiness = 10
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10

# 应用参数
kernel.sem = 250 32000 100 128
kernel.shmmax = 68719476736
kernel.shmall = 4294967296

$ sudo sysctl -p

# 3. PostgreSQL安装和配置
$ sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
$ sudo yum install -y postgresql14 postgresql14-server postgresql14-contrib
$ sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

$ sudo vi /postgresql/fgapp/14/data/postgresql.conf

# 内存参数
shared_buffers = 8GB
work_mem = 32MB
maintenance_work_mem = 1GB
effective_cache_size = 24GB

# 连接参数
max_connections = 200

# 写入性能参数
wal_buffers = 16MB
checkpoint_timeout = 30min
max_wal_size = 4GB
checkpoint_completion_target = 0.9

# 其他参数
random_page_cost = 1.1
effective_io_concurrency = 200

$ sudo vi /postgresql/fgapp/14/data/pg_hba.conf
host all all 192.168.1.0/24 md5

$ sudo systemctl start postgresql-14
$ sudo systemctl enable postgresql-14

# 4. 高可用配置
# 主节点配置
$ sudo vi /postgresql/fgapp/14/data/postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on

$ sudo vi /postgresql/fgapp/14/data/pg_hba.conf
host replication replicator 192.168.1.0/24 md5

$ sudo -u postgres psql -c “CREATE ROLE replicator REPLICATION LOGIN PASSWORD ‘password’;”
$ sudo systemctl restart postgresql-14

# 从节点配置
$ sudo systemctl stop postgresql-14
$ sudo rm -rf /postgresql/fgapp/14/data/*
$ pg_basebackup -h 192.168.1.100 -U replicator -D /postgresql/fgapp/14/data -F p -X stream -P
$ sudo vi /postgresql/fgapp/14/data/recovery.conf
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.1.100 port=5432 user=replicator password=password’

$ sudo systemctl start postgresql-14

# 5. 监控配置
$ wget https://github.com/prometheus/prometheus/releases/download/v2.37.0/prometheus-2.37.0.linux-amd64.tar.gz
$ tar -xzf prometheus-2.37.0.linux-amd64.tar.gz
$ cd prometheus-2.37.0.linux-amd64
$ sudo cp prometheus /usr/local/bin/
$ sudo cp promtool /usr/local/bin/

$ sudo yum install -y grafana

$ wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
$ tar -xzf postgres_exporter-0.15.0.linux-amd64.tar.gz
$ cd postgres_exporter-0.15.0.linux-amd64
$ sudo cp postgres_exporter /usr/local/bin/

$ sudo vi /etc/postgres_exporter.yml
data_source_name: “postgresql://fgedu:password@fgedu.localhost:5432/fgedudb?sslmode=disable”

$ sudo vi /etc/prometheus/prometheus.yml

global:
scrape_interval: 15s
evaluation_interval: 15s

scrape_configs:
– job_name: ‘postgres’
static_configs:
– targets: [‘fgedu.localhost:9187’]
labels:
instance: ‘postgres’

$ sudo systemctl start postgres_exporter
$ sudo systemctl enable postgres_exporter
$ sudo systemctl start prometheus
$ sudo systemctl enable prometheus
$ sudo systemctl start grafana-server
$ sudo systemctl enable grafana-server

# 6. 验证最佳实践
$ psql -h fgedu.localhost -U fgedu -d fgedudb -c “SELECT version();”
$ psql -h 192.168.1.101 -U fgedu -d fgedudb -c “SELECT pg_is_in_recovery();”
$ curl http://fgedu.localhost:9090/targets
$ curl http://fgedu.localhost:3000

# 1. 问题分析
– 数据库响应时间长
– 查询执行缓慢
– 系统负载高

# 2. 优化措施
– 内存参数优化
– 查询优化
– 索引优化
– 存储优化

# 3. 优化实施
# 内存参数优化
$ sudo vi /postgresql/fgapp/14/data/postgresql.conf

shared_buffers = 8GB # 增加共享缓冲区
work_mem = 32MB # 调整工作内存
maintenance_work_mem = 1GB # 调整维护工作内存
effective_cache_size = 24GB # 增加有效缓存大小

# 查询优化
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE email = ‘test@fgedu.net.cn’;”

# 创建索引
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_users_email_idx ON fgedu_users(email);”

# 存储优化
$ sudo vi /etc/fstab
/dev/md0 /postgresql/data xfs defaults,noatime 0 0

# 4. 验证优化效果
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE email = ‘test@fgedu.net.cn’;”
$ top

# 优化前响应时间：100ms
# 优化后响应时间：10ms

# 1. 定期备份
$ pg_basebackup -h fgedu.localhost -U fgedu -D /postgresql/backup/full_backup -F t -X stream -P

# 2. 定期清理
$ psql -U fgedu -d fgedudb -c “VACUUM ANALYZE;”

# 3. 定期检查
$ psql -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_activity;”
$ psql -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_statements ORDER BY duration DESC LIMIT 10;”
$ psql -U fgedu -d fgedudb -c “SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace;”

# 4. 定期优化
$ psql -U fgedu -d fgedudb -c “REINDEX TABLE fgedu_users;”
$ psql -U fgedu -d fgedudb -c “ANALYZE fgedu_users;”

# 5. 监控告警
$ curl http://fgedu.localhost:9090/alerts
$ cat /var/log/alertmanager/alertmanager.log

# 6. 故障处理
$ sudo systemctl status postgresql-14
$ sudo journalctl -u postgresql-14
$ psql -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_activity WHERE state = ‘active’;”

# 7. 案例演示
$ ls -la /postgresql/backup/
$ psql -U fgedu -d fgedudb -c “SELECT count(*) FROM pg_stat_activity;”
$ psql -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_statements ORDER BY duration DESC LIMIT 1;”