PostgreSQL 16 生产环境部署手册 (CentOS 9)
PostgreSQL 16 生产环境部署手册 (CentOS 9)
0.环境配置
机器配置: 4C16G 操作系统:CENTOS 9 postgres 16 二进制源码安装 目录分别是: /data/postgres/app /data/postgres/pgdata /data/postgres/archivelog /data/postgres/backup
1. 关闭防火墙和selinux
# 停止并禁用防火墙 systemctl stop firewalld systemctl disable firewalld # 使用sed过滤防火墙状态信息 systemctl status firewalld | sed -n '/Active:/p' # 临时关闭SELinux setenforce 0 # 查看当前状态 getenforce # 确认配置文件已修改（使用sed过滤关键配置） sed -n '/^SELINUX=/p' /etc/selinux/config # 使用sed命令修改SELinux配置 sed -i 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config # 验证修改结果 grep SELINUX= /etc/selinux/config
2. 环境准备与依赖安装
首先，安装编译 PostgreSQL 和后续运行所需的依赖包。 # 更新系统 sudo dnf update -y # 安装编译工具和依赖库 sudo dnf -y groupinstall "Development Tools" sudo dnf -y install readline-devel zlib-devel libicu-devel libxml2-devel libxslt-devel openssl-devel systemd-devel libuuid-devel lrzsz
3. 创建目录结构并设置权限
# 创建PostgreSQL系统用户（禁止登录，用于管理进程） sudo useradd -s /bin/bash -m -d /home/postgres postgres #sudo passwd postgres # 请设置一个强密码并妥善保管 sudo echo 'postgres'| passwd --stdin postgres # 请设置一个强密码并妥善保管
根据您的要求创建所有目录，并授予 postgres用户适当的权限。 # 创建所有目录 sudo mkdir -p /data/postgres/pgdata/log sudo mkdir -p /data/postgres/archivelog sudo mkdir -p /data/postgres/backup sudo mkdir -p /data/postgres/app # 将目录所有权赋予 postgres 用户 sudo chown -R postgres:postgres /data/postgres # 设置权限（生产环境建议限制目录权限） sudo chmod 700 /data/postgres/pgdata # 数据目录权限应最为严格 sudo chmod 700 /data/postgres/archivelog sudo chmod 750 /data/postgres/backup sudo chmod 755 /data/postgres/app
4. 以 postgres 用户身份进行操作
#后续操作应使用 postgres用户执行，以确保文件权限正确。 # 切换到 postgres 用户 sudo su - postgres
5. 编译与安装 PostgreSQL
#假设软件包已上传至 /home/postgres/soft。 mkdir -p /home/postgres/soft # 进入软件包目录 cd /home/postgres/soft # 解压源码包 (请将 'postgresql-16.x.tar.gz' 替换为实际的完整文件名) tar -xzf postgresql-16.10.tar.gz cd postgresql-16.10 # 配置编译选项 # --prefix=/data/postgres/app 指定安装目录 # 其他选项用于支持常用的功能 ./configure --prefix=/data/postgres/app \ --with-icu \ --with-libxml \ --with-libxslt \ --with-ssl=openssl \ --with-systemd \ --with-uuid=e2fs # 编译并安装 (根据CPU核心数，4核可使用-j4参数加速编译) make -j4 world make install-world # 安装贡献模块 (如 pg_stat_statements, 非常重要) cd contrib make -j4 all make install
6. 配置环境变量
#为 postgres用户设置环境变量，方便日常管理。 # 编辑 postgres 用户的 bashrc 配置文件 vi ~/.bashrc # PostgreSQL Environment Variables export PGHOME=/data/postgres/app export PGDATA=/data/postgres/pgdata export PATH=$PGHOME/bin:$PATH export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export MANPATH=$PGHOME/share/man:$MANPATH export PGPORT=5432 使环境变量立即生效： source ~/.bashrc
7. 初始化数据库集群
# 初始化数据库集群 (-D 指定数据目录) initdb -D $PGDATA -U postgres -E UTF8 --locale=C -W # 执行此命令时，会提示为数据库超级用户 'postgres' 设置密码。请务必设置一个极其复杂的密码并妥善保管。
8. 配置数据库参数 (生产环境优化)
#编辑主配置文件 postgresql.conf。 vi $PGDATA/postgresql.conf
################################### begin ################################### #------------------------------------------------------------------------------ # 基础连接配置（安全+兼容性优化） #------------------------------------------------------------------------------ # 监听地址：生产环境建议明确指定业务网段（而非通配符*） listen_addresses = '0.0.0.0' # 示例：内网场景改为 '192.168.1.0/24,10.0.0.0/8' port = 5432 # 如需安全加固可修改为非默认端口（需同步更新应用配置） max_connections = 200 # 4C16G合理值（100-200），需与应用连接池匹配 superuser_reserved_connections = 3 # 保留超级用户连接，避免运维受阻 unix_socket_directories = '/tmp/' # 本地socket路径 unix_socket_permissions = 0770 # 限制socket权限，仅postgres用户及组可访问 tcp_keepalives_idle = 60 # 连接空闲60秒后发送保活包 tcp_keepalives_interval = 10 # 保活包发送间隔 tcp_keepalives_count = 10 # 无响应断开连接的尝试次数 #------------------------------------------------------------------------------ # 内存配置（16GB内存精准适配） #------------------------------------------------------------------------------ shared_buffers = 4GB # 系统内存1/4（16G*25%），核心数据缓存 work_mem = 16MB # 单查询操作内存：(16G-4G)/200连接/3并发 ≈ 16MB maintenance_work_mem = 1GB # 维护操作内存（VACUUM/建索引），不超过系统内存10% dynamic_shared_memory_type = posix # 兼容Linux系统的共享内存类型 effective_cache_size = 12GB # 系统缓存估计值（16G*75%），帮助优化器生成计划 temp_buffers = 8MB # 临时表缓存，默认值足够 shared_memory_type = mmap # 内存映射方式，提升大内存访问效率（16+支持） #------------------------------------------------------------------------------ # WAL配置（数据安全+性能平衡） #------------------------------------------------------------------------------ wal_level = replica # 支持归档和流复制，为容灾备份预留扩展 fsync = on # 强制WAL写入磁盘后返回成功，防止断电丢数据 synchronous_commit = on # 事务提交等待WAL刷盘（可改为remote_write平衡性能） full_page_writes = on # 防止断电导致部分页损坏，生产环境必开 wal_buffers = 16MB # 适配4GB shared_buffers，避免WAL频繁刷盘 max_wal_size = 4GB # 减少检查点频率，降低IO波动 min_wal_size = 2GB # 避免WAL文件频繁创建/删除 checkpoint_completion_target = 0.9 # 平滑IO压力 checkpoint_timeout = 5min # 检查点间隔，平衡IO和一致性 wal_writer_delay = 10ms # WAL写入延迟，兼顾性能和及时性 wal_compression = on # 开启WAL压缩（14+支持），减少磁盘占用 max_wal_senders = 5 # 允许的最大WAL发送进程数（为流复制预留） #------------------------------------------------------------------------------ # 归档配置（容灾备份+可追溯） #------------------------------------------------------------------------------ archive_mode = on # 开启WAL归档，支持时间点恢复（PITR） archive_command = 'test ! -f /data/postgres/archivelog/%f && (cp %p /data/postgres/archivelog/%f && chmod 600 /data/postgres/archivelog/%f) || echo "Archive failed for %f" >> /data/postgres/archivelog/archive_failed.log' archive_timeout = 600 # 每10分钟强制归档，控制单文件大小 archive_cleanup_command = 'pg_archivecleanup /data/postgres/archivelog %r' # 自动清理旧归档 #------------------------------------------------------------------------------ # 日志配置（可溯源+无冲突） #------------------------------------------------------------------------------ logging_collector = on # 开启日志收集，便于问题排查 log_directory = 'log' # 日志目录，相对于PGDATA #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # 按时间命名，便于归档 log_filename = 'postgresql-%Y-%m-%d.log' # 按时间命名，便于归档 #log_filename = 'postgresql-%a.log' # 按周生成日志文件：Mon, Tue... log_rotation_age = 1d # 每天生成一个新的日志文件 log_rotation_size = 0 # 禁用按大小切换，只按时间 #log_retention_days = 14 # 保留14天日志（PostgreSQL 13+支持） # 日志行前缀：包含关键溯源信息 log_line_prefix = '%t [%p]: [%c] %h %u %d %a ' # 时间 进程ID 会话ID 客户端IP 用户 数据库 应用名 #log_destination = 'stderr,csvlog' # 同时输出到标准错误和CSV格式 log_destination = 'stderr' # 同时输出到标准错误和CSV格式 # 关键日志开关 log_checkpoints = on # 记录检查点信息，分析IO瓶颈 log_lock_waits = on # 记录锁等待（超过1秒），排查阻塞 #log_min_error_statement = 'warning' # 记录警告及以上错误（含SQL） log_min_error_statement = 'info' # 轻微提示（如 NOTICE: identifier "test" will be truncated to "tes") log_min_duration_statement = 1000 # 记录执行超1秒的慢查询 log_connections = on # 记录所有连接尝试（含失败） log_disconnections = on # 记录连接断开，分析连接泄漏 log_autovacuum_min_duration = 0 # 记录所有自动清理操作 log_statement = 'ddl' # 记录DDL操作，审计schema变更 log_replication_commands = on # 记录复制相关命令（为流复制准备） # 可设置为具体时区（如 'Asia/Shanghai'）或 'localtime' 自动跟随系统时区 # 核心修复：使用系统标准时区名称（替换为你服务器的实际时区，如Asia/Shanghai） log_timezone = 'Asia/Shanghai' #------------------------------------------------------------------------------ # 性能优化（4C CPU+SSD适配） #------------------------------------------------------------------------------ effective_io_concurrency = 200 # 适配SSD硬盘，提升IO并发效率 max_worker_processes = 4 # 等于CPU核心数，避免进程过多切换 max_parallel_workers_per_gather = 2 # 单查询并行进程数（CPU核心50%） max_parallel_workers = 4 # 全局最大并行进程数（等于CPU核心数） max_parallel_maintenance_workers = 2 # 维护操作并行数，避免影响业务 random_page_cost = 1.1 # 适配SSD硬盘，优化器更倾向索引扫描 seq_page_cost = 1.0 # 顺序扫描成本（SSD场景降低） cpu_tuple_cost = 0.01 # 调整CPU成本估算，适配现代处理器 cpu_index_tuple_cost = 0.005 cpu_operator_cost = 0.0025 #------------------------------------------------------------------------------ # 统计与监控配置（运维友好） #------------------------------------------------------------------------------ autovacuum = on # 自动清理死元组，防止表膨胀 autovacuum_max_workers = 3 # 自动清理最大进程数（CPU核心75%） autovacuum_naptime = 1min # 清理检查间隔，及时处理小表膨胀 autovacuum_vacuum_scale_factor = 0.02 # 表膨胀2%即触发清理（默认5%） autovacuum_analyze_scale_factor = 0.01 # 数据变化1%即更新统计信息 track_io_timing = on # 跟踪IO耗时，帮助定位瓶颈 track_activity_query_size = 4096 # 记录完整SQL语句（避免截断） shared_preload_libraries = 'pg_stat_statements,auto_explain' # 预加载监控扩展 pg_stat_statements.track = 'all' # 跟踪所有SQL（包括函数内） pg_stat_statements.max = 10000 # 保留10000条SQL统计 pg_stat_statements.track_utility = on # 跟踪DDL/DCL等工具语句 auto_explain.log_min_duration = 1000 # 自动记录超1秒查询的执行计划 #------------------------------------------------------------------------------ # 安全配置（生产环境强化） #------------------------------------------------------------------------------ password_encryption = 'scram-sha-256' # 更安全的密码加密算法 #ssl = on # 开启SSL加密，防止数据传输被窃听 #ssl_ciphers = 'HIGH:!aNULL:!MD5:!3DES' # 禁用弱加密套件 #ssl_prefer_server_ciphers = on # 优先使用服务器端加密套件 #ssl_cert_file = 'server.crt' # SSL证书文件 #ssl_key_file = 'server.key' # SSL私钥文件（权限600） #ssl_ca_file = '' # 如需客户端证书验证，填写CA证书路径 client_encoding = 'UTF8' # 客户端默认编码，避免乱码 default_transaction_isolation = 'read committed' # 默认事务隔离级别 ################################### end ###################################
9. 配置客户端认证
编辑 pg_hba.conf文件，设置访问规则。这是重要的安全配置。在文件末尾添加一些规则示例，请根据您的实际网络和应用需求进行调整： vi $PGDATA/pg_hba.conf # PostgreSQL Client Authentication Configuration File
# ===================================================
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow connections from your application server network
host all all 192.168.43.0/24 scram-sha-256
# Replication connections (if needed)
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
# Allow replication from specific network (if needed)
# host replication all 192.168.43.0/24 scram-sha-256
# Deny all other connections (default secure stance)
host all all 0.0.0.0/0 reject
# Additional custom rules can be added below 注意：scram-sha-256是比 md5更安全的认证方式，PostgreSQL 10及以上版本推荐使用。
10. 配置系统服务 (Systemd)
#退出 postgres用户回到 rootshell，然后创建系统服务文件。 sudo vi /etc/systemd/system/postgresql.service [Unit] Description=PostgreSQL 16 Database Server Documentation=https://www.postgresql.org/docs/16/static/ After=syslog.target After=network.target [Service] Type=notify User=postgres Group=postgres Environment=PGDATA=/data/postgres/pgdata Environment=PGPORT=5432 OOMScoreAdjust=-1000 ExecStart=/data/postgres/app/bin/postgres -D ${PGDATA} ExecReload=/bin/kill -HUP $MAINPID KillMode=mixed KillSignal=SIGINT TimeoutSec=infinity # Pre-configure the victim OOM score adjustment to -1000 when the OOM killer is invoked. # The victim is likely to be a backend rather than the postmaster, but we set it for # everything so that the policy applies to any process started by the unit. OOMScoreAdjust=-1000 [Install] WantedBy=multi-user.target 重新加载 systemd 并启动服务： sudo systemctl daemon-reload sudo systemctl start postgresql sudo systemctl enable postgresql # 设置开机自启
11. 检查服务状态与连接验证
# 检查服务状态 sudo systemctl status postgresql # 查看日志输出（确保没有ERROR级别的错误） sudo tail -f /data/postgres/pgdata/log/postgresql-*.log # 连接到数据库进行验证 sudo su - postgres psql -d postgres # 在psql命令行中，执行以下命令： SELECT name, setting FROM pg_settings WHERE name IN ('port', 'listen_addresses', 'shared_buffers', 'archive_mode'); # 应返回您配置的值。 \q # 退出psql 监控扩展：在需要监控的数据库中创建 pg_stat_statements扩展。 psql -d postgres -c "CREATE EXTENSION pg_stat_statements;" # 日常维护命令 # 切换到postgres用户 su - postgres # 查看数据库状态 pg_ctl status # 启动数据库 pg_ctl start # 停止数据库 pg_ctl stop # 重启数据库 pg_ctl restart # 重新加载配置 pg_ctl reload # 查看数据库连接情况 psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;" # 查看数据库大小 psql -U postgres -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;" # 查看表空间大小 psql -U postgres -c "SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace;"
浙公网安备 33010602011771号