openEuler23.09源码编译PostgreSQL16.3实战指南

1. 环境准备与系统配置在openEuler23.09上编译PostgreSQL16.3之前我们需要先做好基础环境配置。我建议使用全新安装的openEuler23.09系统这样可以避免各种依赖冲突问题。通过执行cat /etc/os-release可以确认系统版本输出应该包含VERSION_ID23.09。系统安全策略需要特别注意。我遇到过很多次因为SELinux和防火墙导致的安装失败所以建议先临时关闭它们systemctl stop firewalld systemctl disable firewalld setenforce 0 sed -i s/SELINUXenforcing/SELINUXdisabled/g /etc/selinux/config接下来是依赖包的安装。openEuler的yum源配置比较特殊如果发现某些包找不到可以尝试先挂载安装镜像mount /dev/cdrom /mnt yum install -y cmake make gcc zlib-devel perl readline-devel \ openssl-devel pam-devel libicu-devel python3 tcl-devel2. 源码获取与用户配置PostgreSQL官方源码下载推荐使用国内镜像站加速。我实测清华镜像站速度最快wget https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v16.3/postgresql-16.3.tar.gz数据库运行需要专用用户这是生产环境的基本安全要求。我习惯使用61600作为UID/GIDgroupadd -g 61600 pgsql useradd -u 61600 -g pgsql -m -d /home/pgsql -s /bin/bash pgsql目录结构设计直接影响后期运维效率。我的标准目录方案是mkdir -p /postgresql/{pgdata,backup,pgsql16,soft} chown -R pgsql:pgsql /postgresql chmod -R 750 /postgresql3. 编译安装过程详解解压源码后进入目录开始编译。这里有个小技巧先清理旧编译结果再开始su - pgsql tar zxvf postgresql-16.3.tar.gz -C /postgresql/soft cd /postgresql/soft/postgresql-16.3 make distclean编译配置参数直接影响数据库性能。我推荐的优化配置是./configure --prefix/postgresql/pgsql16 \ --with-icu \ --with-openssl \ --with-pam \ --with-perl \ --with-python \ --with-tcl \ --with-libxml \ --with-libxslt编译过程可能会遇到各种问题。如果报错缺少icu库需要先安装yum install -y libicu-devel编译完成后安装到指定目录make -j$(nproc) make install4. 数据库初始化与配置环境变量配置很关键建议直接写入.bashrcecho export PGHOME/postgresql/pgsql16 export PGDATA/postgresql/pgdata export PATH$PGHOME/bin:$PATH export LD_LIBRARY_PATH$PGHOME/lib:$LD_LIBRARY_PATH ~/.bashrc source ~/.bashrc初始化数据库集群时字符集选择很重要。对于中文环境推荐initdb -D $PGDATA -E UTF8 --localezh_CN.utf8 -U postgres关键配置文件调整包括postgresql.conf主要参数listen_addresses * max_connections 300 shared_buffers 2GB work_mem 16MB maintenance_work_mem 256MBpg_hba.conf访问控制host all all 192.168.1.0/24 md5启动服务后建议设置开机自启pg_ctl start -D $PGDATA echo pg_ctl start -D $PGDATA -l /postgresql/pgdata/logfile /etc/rc.local chmod x /etc/rc.d/rc.local5. 数据库管理实践首次登录后立即修改超级用户密码ALTER USER postgres WITH PASSWORD YourStrongPassword123!;创建应用数据库的标准流程CREATE USER appuser WITH PASSWORD AppUserPass123; CREATE DATABASE appdb WITH OWNER appuser; GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;性能监控常用SQL-- 查看活跃连接 SELECT * FROM pg_stat_activity WHERE state active; -- 检查锁等待 SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid;6. 常见问题排查编译时报错readline library not found的解决方法yum install -y readline-devel启动时报错could not create shared memory segment的处理sysctl -w kernel.shmmax17179869184 sysctl -w kernel.shmall4194304客户端连接超时问题排查步骤检查postgresql.conf中的listen_addresses确认pg_hba.conf中的访问规则排查防火墙规则测试telnet到5432端口7. 性能优化建议根据服务器配置调整的关键参数# 4核8G服务器示例配置 shared_buffers 2GB effective_cache_size 6GB maintenance_work_mem 512MB checkpoint_completion_target 0.9 random_page_cost 1.1 effective_io_concurrency 200定期维护任务设置# 每周日凌晨3点执行vacuum 0 3 * * 0 pgsql /postgresql/pgsql16/bin/vacuumdb -a -z -v扩展插件安装方法-- 安装常用扩展 CREATE EXTENSION pg_stat_statements; CREATE EXTENSION hstore; CREATE EXTENSION pg_trgm;8. 备份恢复策略物理备份完整流程# 基础备份 pg_basebackup -D /postgresql/backup/basebackup -Ft -z -P -U postgres # 归档配置 echo archive_mode on archive_command cp %p /postgresql/backup/wal/%f $PGDATA/postgresql.conf逻辑备份最佳实践# 全库备份 pg_dumpall -U postgres -f /postgresql/backup/full_backup.sql # 单库备份 pg_dump -U appuser -Fc appdb /postgresql/backup/appdb.dump时间点恢复测试方法# 准备恢复配置 echo restore_command cp /postgresql/backup/wal/%f %p recovery_target_time 2024-03-01 12:00:00 $PGDATA/recovery.conf