yum安装PostgreSQL15+PostGIS3.3

1、安装 pgdg 官方 repo rpm

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2、只启用 pgdg15，禁用其他所有 pgdg 版本源

yum install -y yum-utils yum-plugin-versionlock
yum-config-manager --enable pgdg15
yum-config-manager --disable pgdg12 pgdg13 pgdg14 pgdg16

3、清理重建 yum 缓存

yum clean all
yum makecache

4、安装软件包

yum install -y postgresql15-server postgresql15-contrib postgis33_15 postgis33_15-utils

5、版本锁定，防止 yum update 自动升级

yum versionlock postgresql15* postgis33_15*
yum versionlock list

6、创建目录并授权

mkdir -p /data/postgresql15/{data,log}
chown -R postgres:postgres /data/postgresql15
chmod 700 /data/postgresql15/data

7、初始化数据库集群

su -s /bin/bash postgres -c "
/usr/pgsql-15/bin/initdb \
-D /data/postgresql15/data \
-W \
--encoding=UTF8 \
--lc-collate=en_US.UTF-8 \
--lc-ctype=en_US.UTF-8
"

执行后输入两次postgres 超级用户密码

8、修改 postgresql.conf

vi /data/postgresql15/data/postgresql.conf

listen_addresses = '*'
port = 5432
logging_collector = on
log_directory = '/data/postgresql15/log'

9、修改 pg_hba.conf 访问控制

vi /data/postgresql15/data/pg_hba.conf

host    all             all             0.0.0.0/0            scram-sha-256
host    all             all             ::/0                 scram-sha-256

10、配置 systemd drop‑in

mkdir -p /etc/systemd/system/postgresql-15.service.d

cat > /etc/systemd/system/postgresql-15.service.d/custom.conf <<'EOF'
[Service]
Environment=PGDATA=/data/postgresql15/data
EOF

11、启动服务，设置开机自启

systemctl daemon-reload
systemctl enable --now postgresql-15
systemctl status postgresql-15

12、创建 PostGIS 扩展

su -s /bin/bash postgres -c "psql -h 127.0.0.1 -p 5432 -d postgres -c 'CREATE EXTENSION IF NOT EXISTS postgis;CREATE EXTENSION IF NOT EXISTS postgis_topology;'"

13、全套验证命令

echo "====psql客户端版本===="
psql --version

echo "====数据库内核版本===="
su -s /bin/bash postgres -c "psql -h 127.0.0.1 -p 5432 -c 'SELECT version();'"

echo "====RPM包版本===="
rpm -qa | grep -E "postgresql15|postgis33"

echo "====PostGIS版本===="
su -s /bin/bash postgres -c "psql -h 127.0.0.1 -p 5432 -d postgres -c 'SELECT PostGIS_Version();'"

echo "====端口监听===="
ss -tlnp | grep 5432

echo "====versionlock锁定列表===="
yum versionlock list

附录 A 小版本补丁升级（停机，无需 dump 恢复）

#全库备份
su -s /bin/bash postgres -c "pg_dumpall -f /data/pg_all_backup.sql"

#解除版本锁
yum versionlock delete postgresql15* postgis33_15*

#停止服务
systemctl stop postgresql-15

#更新rpm包
yum update -y postgresql15-server postgresql15-contrib postgis33_15 postgis33_15-utils

#启动
systemctl start postgresql-15

#重新锁定当前实际版本
yum versionlock postgresql15* postgis33_15*

#执行上面全套验证，确认rpm、数据库内核、versionlock三者版本一致

附录 B 完整卸载

systemctl stop postgresql-15
systemctl disable postgresql-15

yum versionlock delete postgresql15* postgis33_15*
yum remove -y postgresql15* postgis33_15*

rm -rf /etc/systemd/system/postgresql-15.service.d
systemctl daemon-reload

yum remove -y pgdg-redhat-repo
rm -f /etc/yum.repos.d/pgdg*.repo
yum clean all

#可选删除postgres用户，无其他pg实例才执行
#userdel -r postgres

#谨慎！确认不需要数据再执行
#rm -rf /data/postgresql15