Avatar

Organizations

2 results for Database
Clear filter

  • PostgreSQL维护手册

    PostgreSQL 频繁读写数据库的定期维护

    是的，频繁读写的 PostgreSQL 数据库绝对需要定期维护。这是保证数据库长期稳定运行、维持高性能和避免数据损坏的关键措施。

    🔧 必须的定期维护任务

    1. VACUUM 维护（最重要）

    -- 常规VACUUM（推荐每日执行）
VACUUM ANALYZE;

-- 针对特定表的VACUUM
VACUUM ANALYZE your_busiest_table;

-- 彻底的VACUUM（建议每周或每月）
VACUUM FULL VERBOSE;

    2. ANALYZE 统计信息更新

    -- 更新统计信息（查询优化器需要）
ANALYZE VERBOSE;

-- 针对特定表
ANALYZE VERBOSE your_busiest_table;

    3. 索引维护

    -- 重建碎片化严重的索引
REINDEX INDEX your_index_name;
REINDEX TABLE your_table_name;

-- 并发重建（PostgreSQL 12+）
REINDEX INDEX CONCURRENTLY your_index_name;

    📅 推荐维护计划

    每日维护

    # 自动VACUUM通常足够，但可以补充
VACUUM ANALYZE;

    每周维护

    # 更彻底的维护
VACUUM FULL ANALYZE;
CHECKPOINT;

    每月维护

    # 全面维护
REINDEX DATABASE your_database;
VACUUM FULL VERBOSE;
ANALYZE VERBOSE;

    ⚙️ 自动化维护配置

    1. 调整 autovacuum 设置（postgresql.conf）

    # 针对频繁读写数据库的优化设置
autovacuum = on
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

    2. 使用 pg_cron 扩展定时任务

    -- 安装pg_cron
CREATE EXTENSION pg_cron;

-- 每天凌晨3点执行VACUUM
SELECT cron.schedule('0 3 * * *', 'VACUUM ANALYZE');

-- 每周日凌晨2点执行维护
SELECT cron.schedule('0 2 * * 0', 'VACUUM FULL ANALYZE');

    3. 操作系统定时任务（crontab）

    # 每天执行维护
0 2 * * * psql -d your_db -c "VACUUM ANALYZE"

# 每周执行全面维护
0 3 * * 0 psql -d your_db -c "VACUUM FULL ANALYZE; REINDEX DATABASE your_db"

    📊 监控和维护检查

    1. 监控数据库状态

    -- 检查表膨胀情况
SELECT schemaname, tablename, 
       n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric/n_live_tup::numeric*100, 2) as dead_ratio
FROM pg_stat_user_tables
ORDER BY dead_ratio DESC;

-- 检查索引状态
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes;

    2. 检查 autovacuum 效果

    -- 查看autovacuum统计信息
SELECT relname, last_vacuum, last_autovacuum, 
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables;

    🚨 维护注意事项

    1. 维护时间窗口：选择业务低峰期进行
    2. VACUUM FULL 谨慎使用：会锁表，影响业务
    3. 监控维护影响：使用 VERBOSE 选项查看详细情况
    4. 备份优先：重要维护前先备份数据

    🎯 针对频繁读写数据库的特殊维护

    -- 针对热点表增加维护频率
ALTER TABLE your_busy_table SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

-- 监控长事务和锁
SELECT * FROM pg_locks WHERE granted = false;
SELECT * FROM pg_stat_activity WHERE state <> 'idle';

    💡 维护最佳实践

    1. 定期检查：每周检查数据库状态
    2. 自动化：尽可能自动化维护任务
    3. 文档化：记录维护历史和问题
    4. 测试：在测试环境验证维护脚本
    5. 容量规划：定期评估存储增长趋势

    总结：对于频繁读写的 PostgreSQL 数据库，定期维护不是可选项，而是必需品。通过合理的维护计划，可以确保数据库的性能稳定性和数据完整性。

    postgresql database Created Sat, 20 Sep 2025 22:26:06 +0800

  • Centos7 安装 Postgresql

    安装

    $ yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

# 安装服务端包
$ yum install postgresql10-server postgresql10

    配置

    # 初始化数据库，设置自启动
$ /usr/pgsql-10/bin/postgresql-10-setup initdb
$ systemctl enable postgresql-10
$ systemctl start postgresql-10

# 设置防火墙规则
$ iptables -A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT              #开放Postgresql 5432端口

$ service iptables save   # 保存防火墙规则

    配置远程访问

    # 切换至用户
$ su - postgres

    ; ./10/data/postgres.conf
- #listen_address
+ listen_address

; ./10/data/pg_hba.conf
; 允许所有IPv4地址
+ host all all 0.0.0.0/0 scram-sha-256

    登录数据库

    $ psql -U postgres

    -- 设置数据运行参数
ALTER SYSTEM SET listen_addresses = '*';
ALTER SYSTEM SET port = 5432;
ALTER SYSTEM SET password_encryption = 'scram-sha-256';

--  修改默认用户密码
ALTER USER postgres with encrypted password '你的密码';

-- 退出数据库
\q

-- 退出用户
exit

    重启服务

    systemctl restart postgresql-10
    postgresql centos database Created Fri, 19 Jan 2024 13:37:15 +0800