一、本篇目标上一篇完成了 Nginx 源码编译。本篇处理 JeecgBoot 的核心依赖PostgreSQL 17。JeecgBoot 3.9.5 官方默认使用 MySQL但我们选择 PostgreSQL原因信创环境推荐使用国产化或开源数据库PostgreSQL 功能强大兼容性好配合 pgvector 可支持 AI 知识库功能避免 MySQL 授权问题本篇完成PostgreSQL 17.10 源码编译pgvector 0.8.6 向量扩展集成数据库初始化与权限配置JeecgBoot SQL 脚本转换引用独立文章二、为什么源码编译PostgreSQL 有三种安装方式方式优点缺点系统包dnf安装快依赖自动解决版本受源限制位置固定二进制 tar 包版本灵活需手动管理依赖源码编译完全可控自包含编译耗时选择源码编译的理由二进制和配置自包含在/usr/local/pgsql重装系统后从/data/packages解压即恢复编译参数可精确控制ICU、SSL、systemd 等三、安装编译依赖dnfinstall-y\readline-devel libicu-devel zlib-devel openssl-devel\bison flex perl perl-ExtUtils-Embed\libxml2-devel libxslt-devel openldap-devel\pam-devel systemd-devel gettext-devel关键依赖说明包用途readline-develpsql 命令行编辑libicu-devel国际化排序–with-icuopenssl-develSSL/TLS 加密bison/flexSQL 解析器生成systemd-develsystemd 通知集成pam-develPAM 认证libxml2-develXML 类型支持四、下载源码mkdir-p/data/packages/postgresql17cd/data/packages/postgresql17# 下载源码和校验文件wgethttps://ftp.postgresql.org/pub/source/v17.10/postgresql-17.10.tar.bz2wgethttps://ftp.postgresql.org/pub/source/v17.10/postgresql-17.10.tar.bz2.sha256# 验证完整性sha256sum-cpostgresql-17.10.tar.bz2.sha256期望postgresql-17.10.tar.bz2: 成功解压到源码目录cd/usr/local/srctar-xjf/data/packages/postgresql17/postgresql-17.10.tar.bz2五、configure 配置cd/usr/local/src/postgresql-17.10 ./configure\--prefix/usr/local/pgsql\--with-openssl\--with-zlib\--with-icu\--with-libxml\--with-libxslt\--with-ldap\--with-pam\--with-systemd\--with-perl\--with-python\--enable-nls21|tee/tmp/pg-configure.log关键参数说明参数作用--prefix/usr/local/pgsql安装路径--with-opensslSSL/TLS 加密连接--with-icu国际化排序--with-systemdsystemd 通知--with-pythonPL/Python 存储过程--enable-nls多语言支持注意PG 17 已默认线程安全不需要--enable-thread-safety这个参数已废弃。常见坑如果缺少 Python 头文件会报header file Python.h is required需要额外安装dnfinstall-ypython3-devel六、编译安装cd/usr/local/src/postgresql-17.10# 编译用满 CPU 核make-j$(nproc)21|tee/tmp/pg-build.log# 安装makeinstall21|tee/tmp/pg-install.log# 验证/usr/local/pgsql/bin/postgres--version期望postgres (PostgreSQL) 17.106.1 编译 contrib 扩展PG 源码的contrib/包含大量常用扩展pg_stat_statements、uuid-ossp、hstore 等必须编译cd/usr/local/src/postgresql-17.10/contribmake-j$(nproc)21|tee/tmp/pg-contrib-build.logmakeinstall21|tee/tmp/pg-contrib-install.log七、初始化数据库注意PostgreSQL 拒绝以 root 运行initdb必须切换到pgsql用户。7.1 设置属主chown-Rpgsql:pgsql /usr/local/pgsql /data/pgdata7.2 执行 initdbsu-s/bin/bash pgsql-c /usr/local/pgsql/bin/initdb \ -D /data/pgdata \ -E UTF8 \ --localeC \ --auth-localtrust \ --auth-hostscram-sha-256 参数说明参数说明-D /data/pgdata数据目录-E UTF8默认编码--localeC排序规则C 兼容性最好--auth-localtrust本地 socket 免密--auth-hostscram-sha-256TCP 连接用密码7.3 日志目录常见坑pg_ctl启动时写日志到/data/logs但该目录属主是 root会报Permission denied。mkdir-p/data/logs/pgsqlchown-Rpgsql:pgsql /data/logs/pgsql八、systemd 服务cat/etc/systemd/system/postgresql-17.serviceEOF [Unit] DescriptionPostgreSQL 17 database server Afternetwork.target [Service] Typeforking Userpgsql Grouppgsql EnvironmentPGDATA/data/pgdata EnvironmentPGLOG/data/logs/pgsql/pg.log ExecStart/usr/local/pgsql/bin/pg_ctl -D ${PGDATA} -l ${PGLOG} start ExecStop/usr/local/pgsql/bin/pg_ctl -D ${PGDATA} stop -m fast ExecReload/usr/local/pgsql/bin/pg_ctl -D ${PGDATA} reload TimeoutSec300 Restarton-failure RestartSec5 [Install] WantedBymulti-user.target EOFsystemctl daemon-reload systemctlenable--nowpostgresql-17 systemctl status postgresql-17验证ss-lntp|grep5432/usr/local/pgsql/bin/psql-h/tmp-Upgsql-dpostgres-cSELECT version();九、pgvector 向量扩展pgvector 为 PG 提供向量存储和相似度检索能力JeecgBoot 的 AI 知识库功能依赖它。9.1 下载源码注意华为云的 pgvector 目录是 SPA 页面不是真实文件只能用 GitHub。mkdir-p/data/packages/pgvectorcd/data/packages/pgvectorwget--content-disposition --trust-server-names\https://github.com/pgvector/pgvector/archive/refs/tags/v0.8.6.tar.gzcd/usr/local/srctar-xzf/data/packages/pgvector/pgvector-0.8.6.tar.gz9.2 编译安装cd/usr/local/src/pgvector-0.8.6exportPG_CONFIG/usr/local/pgsql/bin/pg_configmake-j$(nproc)PG_CONFIG$PG_CONFIGmakeinstallPG_CONFIG$PG_CONFIG9.3 设置属主chown-Rpgsql:pgsql /usr/local/pgsql/lib/vector.sochown-Rpgsql:pgsql /usr/local/pgsql/share/extension/vector*chown-Rpgsql:pgsql /usr/local/pgsql/include/server/extension/vector/9.4 启用扩展su-s/bin/bash pgsql-c/usr/local/pgsql/bin/psql -h /tmp -U pgsql -d postgres -c CREATE EXTENSION IF NOT EXISTS vector;su-s/bin/bash pgsql-c/usr/local/pgsql/bin/psql -h /tmp -U pgsql -d postgres -c \dx期望看到vector | 0.8.6。十、SQL 脚本转换与数据库初始化JeecgBoot 官方只提供 MySQL 脚本PostgreSQL 需要自行转换。转换方案详见独立文章 JeecgBoot 3.9.5 MySQL 脚本一键转 PostgreSQL 完整方案该文章提供了完整的 Python 转换脚本和 Gitee 开源仓库导入错误数 0143 张表全部生成。10.1 快速导入# 1. 创建数据库和用户su-s/bin/bash pgsql-c/usr/local/pgsql/bin/psql -h /tmp -U pgsql -d postgres -c\CREATE USER jeecg WITH PASSWORD yourpwd;\su-s/bin/bash pgsql-c/usr/local/pgsql/bin/psql -h /tmp -U pgsql -d postgres -c\CREATE DATABASE jeecg_boot OWNER jeecg ENCODING UTF8 LC_COLLATE C LC_CTYPE C TEMPLATE template0;\# 2. 导入 PG 脚本su-s/bin/bash pgsql-c/usr/local/pgsql/bin/psql -h /tmp -U pgsql -d jeecg_boot -f /path/to/jeecgboot-postgresql.sql# 3. 启用 pgvectorsu-s/bin/bash pgsql-c/usr/local/pgsql/bin/psql -h /tmp -U pgsql -d jeecg_boot -c CREATE EXTENSION IF NOT EXISTS vector;10.2 PostgreSQL 15 授权关键PostgreSQL 15 默认不再对普通用户开放publicschema 的访问权限。导入后必须授权su-s/bin/bash pgsql-c/usr/local/pgsql/bin/psql -h /tmp -U pgsql -d jeecg_bootSQL_EOF GRANT USAGE ON SCHEMA public TO jeecg; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jeecg; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO jeecg; GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO jeecg; GRANT CREATE ON SCHEMA public TO jeecg; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO jeecg; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO jeecg; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO jeecg; SQL_EOF如果跳过这步应用启动时会报relation xxx does not exist。10.3 验证echo 表数量 su-s/bin/bash pgsql-c/usr/local/pgsql/bin/psql -h /tmp -U pgsql -d jeecg_boot -c\SELECT count(*) FROM pg_tables WHERE schemanamepublic;\echoecho jeecg 用户连接测试 PGPASSWORDyourpwd /usr/local/pgsql/bin/psql-h127.0.0.1-p5432-Ujeecg-djeecg_boot-cSELECT count(*) FROM sys_user;期望表数量 143sys_user 有数据。十一、总结本篇完成项目状态PostgreSQL 17.10 源码编译✅contrib 扩展编译✅pgvector 0.8.6 集成✅initdb 初始化✅systemd 服务✅SQL 脚本转换✅引用独立文章jeecg 用户授权✅关键坑提醒PG 17 不需要--enable-thread-safety已废弃缺python3-devel会导致 configure 失败initdb必须用pgsql用户不能用 root日志目录属主必须是pgsql否则启动失败PostgreSQL 15 必须授权publicschema否则应用报relation does not exist下一篇开始编译 JeecgBoot 后端。项目地址https://gitee.com/dboru/jeecgboot-pg-converter本系列文章第 1 篇为什么从 3.8.3 升级到 3.9.5第 2 篇环境准备与数据盘规划第 3 篇Redis 7.4 源码编译与内核参数调优第 4 篇Nginx 1.28 源码编译与反向代理配置第 5 篇PostgreSQL 17 源码编译 pgvector SQL 转换本文第 6 篇JeecgBoot 后端编译与 systemd 部署第 7 篇JeecgBoot 前端编译与 Nginx 部署第 8 篇部署归档与一键恢复方案配套文章JeecgBoot 3.9.5 MySQL 脚本一键转 PostgreSQL 完整方案