# 1. 环境准备
– 操作系统：Oracle Linux 9.3
– 硬件：8核CPU、32GB内存、1TB SSD
– 网络：10Gbps网络
– PostgreSQL版本：14.0

# 2. 安装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

# 3. 初始化数据库
$ sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

# 4. 启动PostgreSQL
$ sudo systemctl start postgresql-14
$ sudo systemctl enable postgresql-14

# 5. 创建用户和数据库
$ sudo -u postgres psql -c “CREATE USER fgedu WITH PASSWORD ‘password’;”
$ sudo -u postgres psql -c “CREATE DATABASE fgedudb OWNER fgedu;”
$ sudo -u postgres psql -c “GRANT ALL PRIVILEGES ON DATABASE fgedudb TO fgedu;”

# 6. 配置远程访问
$ sudo vi /postgresql/fgapp/14/data/pg_hba.conf
# 添加以下行
host all all 192.168.1.0/24 md5

$ sudo vi /postgresql/fgapp/14/data/postgresql.conf
# 修改以下参数
listen_addresses = ‘*’

# 7. 重启PostgreSQL
$ sudo systemctl restart postgresql-14

# 8. 验证环境
$ psql -h fgedu.localhost -U fgedu -d fgedudb -c “SELECT 1;”

# 1. 安装pgbench
$ sudo yum install postgresql14-contrib

# 2. 安装pgTop
$ sudo yum install pg_top

# 3. 安装pgBadger
$ sudo yum install perl-ExtUtils-MakeMaker
$ wget https://github.com/darold/pgbadger/archive/master.zip
$ unzip master.zip
$ cd pgbadger-master
$ perl Makefile.PL
$ make
$ sudo make install

# 4. 安装Prometheus和Grafana
$ sudo yum install prometheus grafana

# 5. 安装PostgreSQL exporters
$ 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/

# 6. 配置PostgreSQL exporters
$ sudo vi /etc/postgres_exporter.yml
data_source_name: “postgresql://fgedu:password@fgedu.localhost:5432/fgedudb?sslmode=disable”

# 7. 启动PostgreSQL exporters
$ sudo systemctl start postgres_exporter
$ sudo systemctl enable postgres_exporter

# 8. 验证工具安装
$ pgbench –help
$ pg_top –help
$ pgbadger –help
$ curl http://fgedu.localhost:9187/metrics

# 1. 配置pgbench
$ pgbench -i -s 10 fgedudb

# 2. 配置Prometheus
$ sudo vi /etc/prometheus/prometheus.yml

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

# 3. 配置Grafana
# 登录Grafana界面：http://fgedu.localhost:3000
# 导入PostgreSQL监控面板

# 4. 配置测试脚本
$ sudo vi /usr/local/bin/postgresql_acceptance_test.sh

#!/bin/bash

# PostgreSQL验收测试脚本

# 设置变量
HOST=”fgedu.localhost”
PORT=”5432″
USER=”fgedu”
DB=”fgedudb”
DATE=$(date +”%Y-%m-%d_%H-%M-%S”)
LOG_DIR=”/var/log/postgresql/acceptance”
REPORT_DIR=”/var/report/postgresql/acceptance”

# 创建目录
mkdir -p $LOG_DIR
mkdir -p $REPORT_DIR

# 开始验收测试
echo “开始验收测试: $(date)” >> $LOG_DIR/acceptance_test_$DATE.log

# 功能测试
echo “功能测试” >> $LOG_DIR/acceptance_test_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “CREATE TABLE test_table (id SERIAL PRIMARY KEY, name VARCHAR(100));” >> $LOG_DIR/acceptance_test_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “INSERT INTO test_table (name) VALUES (‘Test’);” >> $LOG_DIR/acceptance_test_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “SELECT * FROM test_table;” >> $LOG_DIR/acceptance_test_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “DROP TABLE test_table;” >> $LOG_DIR/acceptance_test_$DATE.log

# 性能测试
echo “性能测试” >> $LOG_DIR/acceptance_test_$DATE.log
pgbench -h $HOST -p $PORT -U $USER -d $DB -c 10 -j 2 -T 60 >> $LOG_DIR/acceptance_test_$DATE.log

# 安全测试
echo “安全测试” >> $LOG_DIR/acceptance_test_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “SELECT rolname, rolsuper FROM pg_roles;” >> $LOG_DIR/acceptance_test_$DATE.log

# 可靠性测试
echo “可靠性测试” >> $LOG_DIR/acceptance_test_$DATE.log
psql -h $HOST -p $PORT -U $USER -d $DB -c “SELECT pg_is_in_recovery();” >> $LOG_DIR/acceptance_test_$DATE.log

# 结束验收测试
echo “结束验收测试: $(date)” >> $LOG_DIR/acceptance_test_$DATE.log

# 生成报告
cat $LOG_DIR/acceptance_test_$DATE.log > $REPORT_DIR/acceptance_test_$DATE.report

# 5. 赋予脚本执行权限
$ sudo chmod +x /usr/local/bin/postgresql_acceptance_test.sh

# 6. 测试脚本执行
$ /usr/local/bin/postgresql_acceptance_test.sh

# 1. 配置Prometheus告警规则
$ sudo vi /etc/prometheus/rules/acceptance_alerts.yml
groups:
– name: acceptance_alerts
rules:
– alert: PostgresqlDown
expr: pg_up == 0
for: 5m
labels:
severity: critical
annotations:
summary: “PostgreSQL down on {{ $labels.instance }}”
description: “PostgreSQL instance {{ $labels.instance }} has been down for 5 minutes”

– alert: HighConnectionCount
expr: pg_stat_activity_count > 100
for: 5m
labels:
severity: warning
annotations:
summary: “High connection count on {{ $labels.instance }}”
description: “Connection count is {{ $value }}”

– alert: SlowQueries
expr: pg_stat_statements_max_duration > 10000
for: 5m
labels:
severity: warning
annotations:
summary: “Slow queries on {{ $labels.instance }}”
description: “Maximum query duration is {{ $value }}ms”

# 2. 配置Grafana dashboard
# 登录Grafana界面：http://fgedu.localhost:3000
# 创建验收测试监控dashboard

# 3. 实时监控验收测试
$ curl http://fgedu.localhost:9090/targets
$ curl http://fgedu.localhost:3000

# 4. 收集监控数据
$ psql -h fgedu.localhost -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_activity;”
$ psql -h fgedu.localhost -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_statements ORDER BY duration DESC LIMIT 10;”