58-数据库运维(主从+备份+优化)

56 - 数据库运维（主从+备份+优化）

生产环境的数据库运维不仅仅是安装和建表。主从复制保障高可用和读写分离，逻辑与物理备份形成多层数据保护，配置调优让查询在毫秒级完成，连接池管理成千上万的客户端——这些是 DBA 和运维工程师的日常核心任务。本章在 27-数据库管理 基础上深入 MySQL/MariaDB 和 PostgreSQL 的复制、备份、优化和监控。

---

56.1 MySQL/MariaDB 主从复制

复制原理

Master（主库） Slave（从库）
┌──────────────┐ ┌──────────────┐
│ Client 写入 │ │ Client 只读 │
│ │ │ │ ▲ │
│ ▼ │ │ │ │
│ Binlog │──── 网络 ─────▶│ Relay Log │
│ (二进制日志) │ I/O Thread │ → SQL Thread │
│ │ │ (应用变更) │
└──────────────┘ └──────────────┘

基于 Binlog 位置的传统复制

主库配置：

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf # Debian/Ubuntu
sudo vim /etc/my.cnf.d/mariadb-server.cnf # RHEL/Fedora

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 500M
binlog_do_db = myapp # 只复制指定库（可选）
# binlog_ignore_db = test # 排除指定库（可选）

sudo systemctl restart mariadb
 
# 在 Master 上创建复制用户
sudo mysql -e "
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;
 
-- 查看当前 binlog 位置（锁表后执行）
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
"
 
# 记录输出中的 File 和 Position 值
# +------------------+----------+
# | File | Position |
# +------------------+----------+
# | mysql-bin.000001 | 623 |
# +------------------+----------+

从库配置：

[mysqld]
server-id = 2
relay_log = /var/log/mysql/relay-bin.log
read_only = 1

sudo systemctl restart mariadb
 
# 在 Slave 上导入初始数据（如果是全新从库）
# 方法 1：从主库导出并导入
# 在主库上：mysqldump -u root -p --all-databases --master-data=2 > dump.sql
# 拷贝到从库：mysql -u root -p < dump.sql
 
# 配置复制
sudo mysql -e "
CHANGE MASTER TO
 MASTER_HOST='192.168.1.10',
 MASTER_USER='repl',
 MASTER_PASSWORD='ReplPass123!',
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=623;
 
START SLAVE;
SHOW SLAVE STATUS\G
"
 
# 关键字段检查：
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# Seconds_Behind_Master: 0

GTID 复制（推荐）

GTID（Global Transaction Identifier）简化了复制管理，切换主库更方便。

# 主库 server-id=1，从库 server-id=2,3,...
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

# 在从库上使用 GTID 配置复制
sudo mysql -e "
CHANGE MASTER TO
 MASTER_HOST='192.168.1.10',
 MASTER_USER='repl',
 MASTER_PASSWORD='ReplPass123!',
 MASTER_AUTO_POSITION = 1;
 
START SLAVE;
"

复制监控与管理

# 查看从库状态
mysql -e "SHOW SLAVE STATUS\G" | grep -E "Running|Behind|Error"
 
# 查看从库延迟
mysql -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master
 
# 跳过错误（谨慎使用）
# mysql -e "SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;"
 
# 重置复制
# mysql -e "STOP SLAVE; RESET SLAVE ALL;"
 
# 查看 binlog 事件
mysqlbinlog /var/log/mysql/mysql-bin.000001 --base64-output=DECODE-ROWS
 
# 查看连接的从库
mysql -e "SHOW SLAVE HOSTS;"

主主复制（双主）

# 在两台服务器上都配置 server-id（不同值）
[mysqld]
server-id = 1
auto_increment_increment = 2
auto_increment_offset = 1 # 另一台用 2
log_bin = /var/log/mysql/mysql-bin.log

主主复制需谨慎处理冲突。通常配合 Keepalived 或 HAProxy 做 Active-Passive 模式，只往一台写入。

---

56.2 PostgreSQL 复制

流复制（Streaming Replication）

Primary Standby（Replica）
┌──────────────┐ ┌──────────────┐
│ Client 写入 │ │ 只读查询 │
│ │ │ │ ▲ │
│ ▼ │ │ │ │
│ WAL │──── WAL ──────▶│ 接收并应用 │
│ (预写日志) │ Sender │ WAL │
│ │ Process │ (WAL Replay)│
└──────────────┘ └──────────────┘

主库配置：

sudo vim /etc/postgresql/15/main/postgresql.conf # Debian/Ubuntu
sudo vim /var/lib/pgsql/15/data/postgresql.conf # RHEL/Fedora

listen_addresses = '*'
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1024 # MB（或 wal_keep_segments）
hot_standby = on

# 创建复制用户
sudo -u postgres psql -c "
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'ReplPass123!';
"
 
# 配置 pg_hba.conf 允许复制连接
sudo vim /etc/postgresql/15/main/pg_hba.conf

host replication replicator 192.168.1.0/24 md5

sudo systemctl restart postgresql

从库配置（使用 pg_basebackup 初始化）：

# 停止从库 PostgreSQL
sudo systemctl stop postgresql
 
# 清空数据目录（全新从库）
sudo rm -rf /var/lib/postgresql/15/main/*
 
# 从主库拉取基础备份
sudo -u postgres pg_basebackup -h 192.168.1.10 -p 5432 \
 -U replicator -D /var/lib/postgresql/15/main/ \
 -Fp -Xs -P -R
 
# -R 参数会自动创建 standby.signal 和配置
# -X stream 背景流式传输 WAL

# 检查生成的配置（pg_basebackup -R 自动生成）
cat /var/lib/postgresql/15/main/postgresql.auto.conf
# primary_conninfo = 'user=replicator password=ReplPass123! host=192.168.1.10 port=5432'

sudo systemctl start postgresql
 
# 验证复制状态（主库上）
sudo -u postgres psql -c "
SELECT pid, application_name, client_addr,
 state, sync_state,
 pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_lag,
 pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_lag
FROM pg_stat_replication;
"

逻辑复制（Logical Replication）

逻辑复制按表粒度复制，不要求二进制兼容，可用于跨版本迁移。

# 主库（发布端）
sudo -u postgres psql -c "
-- 调整 wal_level（如需要）
-- ALTER SYSTEM SET wal_level = logical;
 
CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- 或发布所有表：CREATE PUBLICATION my_pub FOR ALL TABLES;
"
 
# 从库（订阅端）
sudo -u postgres psql -c "
CREATE SUBSCRIPTION my_sub
 CONNECTION 'host=192.168.1.10 dbname=myapp user=replicator password=ReplPass123!'
 PUBLICATION my_pub;
"
 
# 监控
sudo -u postgres psql -c "SELECT * FROM pg_stat_subscription;"

---

56.3 连接池

PgBouncer（PostgreSQL 轻量级连接池）

# 安装
sudo apt install pgbouncer -y # Debian/Ubuntu
sudo dnf install pgbouncer -y # RHEL/Fedora
 
# 配置
sudo vim /etc/pgbouncer/pgbouncer.ini

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp
 
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
log_connections = 1
log_disconnections = 1

# 用户认证文件
sudo vim /etc/pgbouncer/userlist.txt

"myapp_user" "user_password"

sudo systemctl enable --now pgbouncer
 
# 验证（应用连接改为 6432 端口）
psql -h 127.0.0.1 -p 6432 -U myapp_user -d myapp

ProxySQL（MySQL 协议代理）

# 安装（官方仓库）
wget https://github.com/sysown/proxysql/releases/download/v2.6/proxysql_2.6.0-ubuntu22_amd64.deb
sudo dpkg -i proxysql_2.6.0-ubuntu22_amd64.deb
 
sudo systemctl enable --now proxysql
 
# 管理接口（默认端口 6032）
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- 配置后端
INSERT INTO mysql_servers(hostgroup_id, hostname, port)
 VALUES (0, '192.168.1.10', 3306),
 (0, '192.168.1.11', 3306),
 (1, '192.168.1.20', 3306),
 (1, '192.168.1.21', 3306);
 
-- 配置监控用户
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor_pass' WHERE variable_name='mysql-monitor_password';
 
-- 配置读写分离规则
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply)
 VALUES (1, 1, '^SELECT', 1, 1),
 (2, 1, '.*', 0, 1);
 
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

---

56.4 备份策略

逻辑备份

MySQL/MariaDB — mysqldump：

# 全库备份
mysqldump -u root -p --all-databases --single-transaction \
 --routines --triggers --events > /backup/full_$(date +%Y%m%d).sql
 
# 单库备份
mysqldump -u root -p --databases myapp --single-transaction \
 > /backup/myapp_$(date +%Y%m%d).sql
 
# 仅表结构（无数据）
mysqldump -u root -p --no-data myapp > /backup/myapp_schema.sql
 
# 压缩备份
mysqldump -u root -p --all-databases --single-transaction | gzip > /backup/full.sql.gz

参数	作用
--single-transaction	事务一致性快照（InnoDB），不锁表
--routines	包含存储过程和函数
--triggers	包含触发器
--events	包含事件调度器
--master-data=2	注释化记录 binlog 位置
--ignore-table	排除指定表

PostgreSQL — pg_dump：

# 单库备份
pg_dump -U postgres -d myapp -Fc -f /backup/myapp_$(date +%Y%m%d).dump
 
# 全库备份（pg_dumpall）
pg_dumpall -U postgres -f /backup/all_$(date +%Y%m%d).sql
 
# 仅模式备份
pg_dump -U postgres -d myapp --schema-only -f /backup/myapp_schema.sql
 
# 自定义格式（支持并行恢复）
pg_dump -U postgres -d myapp -Fc -j 4 -f /backup/myapp_parallel.dump
 
# 恢复
# pg_restore -U postgres -d myapp /backup/myapp.dump
# pg_restore -U postgres -d myapp -j 4 /backup/myapp_parallel.dump

物理备份

MySQL — XtraBackup：

# Percona XtraBackup 可在线热备份 InnoDB 表
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo apt update && sudo apt install percona-xtrabackup-84 -y
 
# 全量备份
sudo xtrabackup --backup --target-dir=/backup/full_$(date +%Y%m%d) \
 --user=root --password=xxx
 
# 准备备份（应用 redo log，使备份一致）
sudo xtrabackup --prepare --target-dir=/backup/full_20260724
 
# 恢复
# sudo systemctl stop mysql
# sudo rsync -av /backup/full_20260724/ /var/lib/mysql/
# sudo chown -R mysql:mysql /var/lib/mysql
# sudo systemctl start mysql

PostgreSQL — pg_basebackup：

# 全量物理备份
pg_basebackup -U postgres -D /backup/base_$(date +%Y%m%d) \
 -Ft -z -P -X stream
 
# -Ft: tar 格式；-z: gzip 压缩；-P: 显示进度；-X stream: 流式包含 WAL

PITR（Point-in-Time Recovery）

MySQL PITR（Binlog 恢复）：

# 恢复到特定时间点
mysqlbinlog mysql-bin.000001 mysql-bin.000002 \
 --start-datetime="2026-07-24 10:00:00" \
 --stop-datetime="2026-07-24 10:05:00" \
 | mysql -u root -p
 
# 恢复到特定位置
mysqlbinlog mysql-bin.000001 \
 --start-position=100 --stop-position=2000 \
 | mysql -u root -p

PostgreSQL PITR：

# postgresql.conf 中启用 WAL 归档
# archive_mode = on
# archive_command = 'cp %p /backup/wal_archive/%f'
 
# 恢复到特定时间点
sudo systemctl stop postgresql
 
# 使用备份 + WAL 恢复
cat > /var/lib/postgresql/15/data/recovery.signal << 'EOF'
# 空文件，触发生效
EOF
 
sudo vim /var/lib/postgresql/15/data/postgresql.auto.conf

restore_command = 'cp /backup/wal_archive/%f %p'
recovery_target_time = '2026-07-24 10:05:00'
recovery_target_action = 'promote'

sudo systemctl start postgresql
# PostgreSQL 将恢复指定时间点后自动 promoted 为 主库

自动化备份脚本

#!/bin/bash
# /usr/local/bin/db-backup.sh
set -euo pipefail
 
BACKUP_DIR="/backup/db"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/db-backup.log"
 
log() { echo "[$(date '+%F %T')] $*" >> "$LOG_FILE"; }
 
mkdir -p "$BACKUP_DIR"
 
# MySQL/MariaDB 备份
log "Starting MySQL backup..."
mysqldump -u root --all-databases --single-transaction --routines --triggers \
 | gzip > "$BACKUP_DIR/mysql_full_${DATE}.sql.gz"
log "MySQL backup completed: $(du -h $BACKUP_DIR/mysql_full_${DATE}.sql.gz | cut -f1)"
 
# PostgreSQL 备份
log "Starting PostgreSQL backup..."
sudo -u postgres pg_dumpall -c | gzip > "$BACKUP_DIR/pg_all_${DATE}.sql.gz"
log "PostgreSQL backup completed: $(du -h $BACKUP_DIR/pg_all_${DATE}.sql.gz | cut -f1)"
 
# 清理旧备份
log "Cleaning backups older than $RETENTION_DAYS days..."
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete
 
log "Backup job finished."

# 添加到 cron（每天凌晨 2 点）
sudo chmod +x /usr/local/bin/db-backup.sh
echo "0 2 * * * root /usr/local/bin/db-backup.sh" | sudo tee /etc/cron.d/db-backup

---

56.5 性能调优

MySQL/MariaDB 关键参数

[mysqld]
# === 内存 ===
innodb_buffer_pool_size = 4G # 核心参数！设为总内存的 50-70%
innodb_log_file_size = 512M # redo log 大小
innodb_buffer_pool_instances = 4 # 大 buffer pool 时分片
key_buffer_size = 64M # MyISAM 索引缓冲
 
# === 连接 ===
max_connections = 200
thread_cache_size = 32
 
# === 查询 ===
query_cache_type = 0 # 8.0 已移除；MariaDB 建议关闭
query_cache_size = 0
sort_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 64M
max_heap_table_size = 64M
 
# === InnoDB ===
innodb_flush_log_at_trx_commit = 2 # 1=最安全, 2=折中, 0=最快
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000 # SSD 可设更高
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_per_table = 1

# 查看当前 InnoDB buffer pool 命中率
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A5 "BUFFER POOL AND MEMORY"
 
# 更精确的查看
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
# Innodb_buffer_pool_reads（磁盘读） / Innodb_buffer_pool_read_requests（总读）
# 目标：< 1%

PostgreSQL 关键参数

sudo vim /var/lib/postgresql/15/data/postgresql.conf # 或 /etc/postgresql/15/main/

# === 内存（设总 RAM 的 25%）===
shared_buffers = 2GB # 建议 RAM 的 25%
effective_cache_size = 6GB # 建议 RAM 的 50-75%（OS 页缓存）
work_mem = 64MB # 每个排序/哈希操作可用内存
maintenance_work_mem = 512MB # VACUUM 等维护操作内存
wal_buffers = 16MB
 
# === 写入 ===
wal_level = replica
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9 # 分散写入
 
# === 规划器 ===
random_page_cost = 1.1 # SSD 设为 1.1, HDD 保持 4.0
effective_io_concurrency = 200 # SSD 的并发 I/O
default_statistics_target = 100
 
# === 连接 ===
max_connections = 200

# 查看 buffer 命中率
sudo -u postgres psql -c "
SELECT
 round((blks_hit * 100.0) / NULLIF(blks_hit + blks_read, 0), 1) AS hit_ratio
FROM pg_stat_bgwriter;
"

慢查询日志配置

MySQL：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1 # 超过 1 秒的记录
log_queries_not_using_indexes = 1

# 分析慢查询日志
mysqldumpslow -s t /var/log/mysql/slow-query.log | head -20
# -s t: 按总时间排序; -s c: 按次数排序
 
# 使用 pt-query-digest 进行深度分析
sudo apt install percona-toolkit -y
pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow_report.txt

PostgreSQL：

sudo vim /var/lib/postgresql/15/data/postgresql.conf

log_min_duration_statement = 1000 # 毫秒
log_lock_waits = on
log_temp_files = 0 # 记录所有临时文件创建

# PostgreSQL 日志通常在
sudo tail -f /var/log/postgresql/postgresql-15-main.log
 
# 分析慢查询
grep "duration:" /var/log/postgresql/postgresql-15-main.log | sort -t: -k3 -rn | head -20

---

56.6 数据库监控核心指标

关键指标清单

# === MySQL/MariaDB ===
# 连接数
mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';"
 
# QPS（每秒查询数）
mysql -e "SHOW GLOBAL STATUS LIKE 'Questions';"
# 两次采样差值 / 间隔秒数
 
# 复制延迟
mysql -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master
 
# Buffer pool 命中率
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
 
# 表锁等待
mysql -e "SHOW GLOBAL STATUS LIKE 'Table_locks_waited';"
 
# 慢查询数
mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"
 
# === PostgreSQL ===
# 当前连接
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"
 
# 事务提交率
sudo -u postgres psql -c "SELECT xact_commit, xact_rollback, \
 round(xact_commit::numeric / NULLIF(xact_commit + xact_rollback, 0) * 100, 1) AS success_rate \
 FROM pg_stat_database WHERE datname = 'myapp';"
 
# 复制延迟（lag bytes）
sudo -u postgres psql -c "SELECT application_name, \
 pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes \
 FROM pg_stat_replication;"
 
# 表膨胀
sudo -u postgres psql -c "SELECT schemaname, relname, \
 n_dead_tup, n_live_tup, \
 round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_ratio \
 FROM pg_stat_user_tables WHERE n_dead_tup > 0 ORDER BY dead_ratio DESC LIMIT 10;"

使用 Prometheus Exporter 监控

# MySQL
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15/mysqld_exporter-0.15.linux-amd64.tar.gz
# 配置 ~/.my.cnf 然后启动
 
# PostgreSQL
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15/postgres_exporter-0.15.linux-amd64.tar.gz
# 设置 DATA_SOURCE_NAME 环境变量后启动

详见 60-监控系统(Prometheus+Grafana)。

---

56.7 数据库安全

网络安全

# MySQL/MariaDB — 绑定地址
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
# bind-address = 127.0.0.1 # 仅本地访问
# bind-address = 0.0.0.0 # 允许远程（配合防火墙）
 
# PostgreSQL — 监听地址
sudo vim /var/lib/postgresql/15/data/postgresql.conf
# listen_addresses = '192.168.1.10' # 仅指定网卡

启用 TLS

MySQL：

# 生成自签名证书（测试用）
openssl req -newkey rsa:2048 -nodes -keyout /etc/mysql/ssl/server-key.pem \
 -x509 -days 365 -out /etc/mysql/ssl/server-cert.pem
 
sudo vim /etc/mysql/my.cnf

[mysqld]
ssl_ca = /etc/mysql/ssl/server-cert.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem
require_secure_transport = ON

# 创建用户要求 TLS
mysql -e "CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;"

PostgreSQL：

sudo vim /var/lib/postgresql/15/data/postgresql.conf
# ssl = on
# ssl_cert_file = 'server.crt'
# ssl_key_file = 'server.key'
 
sudo vim /var/lib/postgresql/15/data/pg_hba.conf
# hostssl all all 192.168.1.0/24 scram-sha-256

---

56.8 数据库维护

MySQL/MariaDB 维护

# 分析表（更新统计信息）
mysqlcheck -u root -p --analyze --all-databases
 
# 优化表（整理碎片）
mysqlcheck -u root -p --optimize myapp users orders
 
# 检查表
mysqlcheck -u root -p --check --all-databases
mysqlcheck -u root -p --check --auto-repair myapp
 
# 查看表大小
mysql -e "SELECT table_schema AS 'DB', 
 table_name AS 'Table', 
 round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
 FROM information_schema.TABLES
 WHERE table_schema = 'myapp' ORDER BY (data_length + index_length) DESC;"

PostgreSQL 维护

# VACUUM（回收死元组空间，日常维护）
sudo -u postgres psql -c "VACUUM VERBOSE;"
 
# ANALYZE（更新统计信息）
sudo -u postgres psql -c "ANALYZE VERBOSE;"
 
# VACUUM FULL（物理回收空间，会锁表！）
sudo -u postgres psql -c "VACUUM FULL users;"
 
# 自动 VACUUM 配置
sudo vim /var/lib/postgresql/15/data/postgresql.conf

autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

# 重建索引
sudo -u postgres psql -c "REINDEX DATABASE myapp;"
 
# 查看索引使用情况
sudo -u postgres psql -c "
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY idx_scan DESC;"

---

56.9 本章总结

运维领域	MySQL/MariaDB	PostgreSQL	参考
主从复制	Binlog 位置 / GTID	流复制 / 逻辑复制	本章
连接池	ProxySQL	PgBouncer	本章
逻辑备份	mysqldump	pg_dump	17-备份与恢复
物理备份	XtraBackup	pg_basebackup	本章
PITR	mysqlbinlog 回放	WAL 归档恢复	本章
性能调优	innodb_buffer_pool_size	shared_buffers / work_mem	27-数据库管理
慢查询	slow_query_log	log_min_duration_statement	本章
监控	Prometheus mysqld_exporter	Prometheus postgres_exporter	60-监控系统(Prometheus+Grafana)
安全	TLS + require_secure_transport	pg_hba.conf + SSL	本章
维护	mysqlcheck + OPTIMIZE TABLE	VACUUM + REINDEX	本章