1、安装和启用EPEL、CRB、PostgreSQL仓库
dnf config-manager --set-enabled crb
dnf -y install epel-release
dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
备注:若提示没有config-manager命令,请执行命令:
dnf install 'dnf-command(config-manager)' -y
2、禁用系统自带PostgreSQL模块
dnf -qy module disable postgresql
3、查找PostgreSQL和PostGIS的版本情况
# dnf list postgresql12 --showduplicates | sort -r
postgresql12.x86_64 12.9-6PGDG.rhel9 pgdg12
postgresql12.x86_64 12.9-2PGDG.rhel9 pgdg12
postgresql12.x86_64 12.9-2PGDG.rhel9 @pgdg12
postgresql12.x86_64 12.8-3PGDG.rhel9 pgdg12
postgresql12.x86_64 12.13-1PGDG.rhel9 pgdg12
postgresql12.x86_64 12.12-1PGDG.rhel9 pgdg12
postgresql12.x86_64 12.11-1PGDG.rhel9 pgdg12
postgresql12.x86_64 12.10-1PGDG.rhel9 pgdg12
# dnf list postgis30_12 --showduplicates | sort -r
postgis30_12.x86_64 3.0.7-1.rhel9 pgdg12
postgis30_12.x86_64 3.0.6-1.rhel9 pgdg12
postgis30_12.x86_64 3.0.5-1.rhel9 pgdg12
postgis30_12.x86_64 3.0.5-1.rhel9 @pgdg12
4、安装PostgreSQL和PostGIS的需求版本
dnf -y install postgresql12-12.9-2PGDG.rhel9 postgresql12-libs-12.9-2PGDG.rhel9 postgresql12-server-12.9-2PGDG.rhel9 postgresql12-contrib-12.9-2PGDG.rhel9 postgis30_12-3.0.5-1.rhel9
5、初始化数据库
/usr/pgsql-12/bin/postgresql-12-setup initdb
6、配置文件修改
# vim /var/lib/pgsql/12/data/postgresql.conf
listen_addresses = '*'
# vim /var/lib/pgsql/12/data/pg_hba.conf
host all all 0.0.0.0/0 md5 #最后一行增加
7、启动服务
systemctl enable --now postgresql-12
8、其他配置
# 切换到postgres用户
su - postgres
# 创建用户
createuser cent
# 配置用户密码
psql -c "alter user cent with password 'password';"
# 创建数据库
createdb testdb -O cent
# 验证用户连接数据库权限
psql -h 10.10.10.10 -d testdb -U cent
# 查看用户
psql -c "select usename from pg_user;"
# 查看数据库
psql -l
# 还原备份数据
psql -d testdb -U postgres -f dump.sql
# 连接数据库
psql testdb
testdb=> \du #查看用户角色
testdb=> \dt #查看数据库表
testdb=> \q #退出
# 删除数据库
dropdb testdb