RHEL8 PostgreSQL源码安装PostGIS并配置主从同步

尝试用二进制yum源安装没有成功,遂尝试源码编译安装。

postgresql

PostgreSQL编译安装

系统环境

Red Hat Enterprise Linux release 8.7 (Ootpa)

PostgreSQL 15.1

yum源

关闭官方订阅
vim /etc/yum/pluginconf.d/subscription-manager.conf
enabled=0

RedHat8更换CentOS阿里云镜像源
mv /etc/yum.repos.d/redhat.repo /etc/yum.repos.d/redhat.repo.bak

wget -O /etc/yum.repos.d/redhat.repo http://mirrors.aliyun.com/repo/Centos-8.repo
#或者
curl -o /etc/yum.repos.d/redhat.repo http://mirrors.aliyun.com/repo/Centos-8.repo

生存缓存
yum clean all && yum makecache

所需安装包

postgresql-15.1.tar.gz
postgis-3.2.4.tar.gz
gdal-3.5.3.tar.gz

编译环境依赖
yum install gcc gcc-c++ make

安装gdal依赖
yum install -y proj proj-devel

安装gdal

tar -zxvf gdal-3.5.3.tar.gz
cd gdal-3.5.3
./configure && make -j4 && make install

编译编译过程比较长

安装postgresql

tar -zxvf postgresql-15.1.tar.gz
cd postgresql-15.1
./configure && make -j4 && make install

默认安装在/usr/local/pgsql

初始化库到指定数据目录

mkdir /data/apps/postgresql/pgdata
chown -R postgres:postgres /data/apps/postgresql/pgdata

/usr/local/pgsql/bin/initdb -D /data/apps/postgresql/pgdata

安装pg_config

yum install postgresql15-devel

#安装官方yum源
#sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装目录:/usr/pgsql-15

ln -s /usr/local/pgsql/bin/pg_config /usr/bin/pg_config

安装postgis

安装依赖
yum install -y libxml2.x86_64 libxml2-devel.x86_64

编译安装

tar -zxvf postgis-3.2.4.tar.gz
cd postgis-3.2.4

./configure之前安装如下依赖

yum install geos311 geos311-devel -y
yum install protobuf-c.x86_64 protobuf-c-devel.x86_64 -y

#ln -s /usr/local/pgsql/bin/pg_config /usr/bin/pg_config
ln -s /usr/geos311/bin/geos-config /usr/bin/geos-config

./configure && make -j4 && make install

数据库启动/停止/重启
/usr/local/pgsql/bin/pg_ctl -D /data/apps/postgresql/pgdata/ start
/usr/local/pgsql/bin/pg_ctl -D /data/apps/postgresql/pgdata/ stop
/usr/local/pgsql/bin/pg_ctl -D /data/apps/postgresql/pgdata/ restart

启用postgis插件

DO NOT INSTALL it in the database called postgres
不要将扩展安装的postgres库上

su - postgres
psql

create database test;
\c test;

\l  列出库名
\c  dbname 切换数据库

\du 当前数据库所有的用户以及对应的权限
\db list tablespaces
\di 查看索引

\conninfo:列出当前数据库和连接的信息。


CREATE EXTENSION postgis;

主从同步

从库和主库安装相同的环境

创建同步数据账号

在主库上创建同步账号

psql -U  postgres
postgres=# CREATE ROLE replica login replication encrypted password '123456';

create role repl login replication encrypted password '123456';

主库 pg_hba.conf 文件增加备库访问控制

host    replication     replica         192.168.1.28/32      trust

主库 postgresql.conf 文件添加主从同步参数

wal_level = hot_standby 
max_wal_senders = 8 
#wal_keep_segments = 64 
wal_sender_timeout = 60s
max_connections = 100

主库重启

/usr/local/pgsql/bin/pg_ctl -D /data/apps/postgresql/pgdata/ restart

从库验证可访问主库

$ psql -h 192.168.1.27 -U postgres
Password for user postgres:

停止从库
/usr/local/pgsql/bin/pg_ctl -D /data/apps/postgresql/pgdata/ stop

清空从库数据,拉取主库数据文件

从主库上备份数据

/data/apps/postgresql/pgdata/*

pg_basebackup -h 192.168.1.27 -D /data/apps/postgresql/pgdata/ -p 5432 -U replica -Fp -Xs -Pv -R --checkpoint=fast
Password:

从库 postgresql.conf 文件修改主从同步参数

primary_conninfo = 'host=192.168.1.27 port=5432 user=replica password=123456'
recovery_target_timeline = latest 
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
max_connections = 200  #大于主节点
max_worker_processes = 20

启动从库
/usr/local/pgsql/bin/pg_ctl -D /data/apps/postgresql/pgdata/ start

注意:备份的配置和主完全相同,启动前注意修改listen端口和配置信息。

主库验证同步

select client_addr,usename,backend_start,application_name,sync_state,sync_priority FROM pg_stat_replication;

测试创建删除数据库观察从库是否同步
create database test;

ps: yum安装可参考文末最后一篇链接,由于rhel8 系统上未成功启用 PowerTools 存储库。所以才尝试编译安装。

参考地址

https://www.postgresql.org/download/linux/redhat/
PostgreSQL 扩展之 PostGIS
https://juejin.cn/post/6875966571978850317
PostgreSql 主从同步搭建
https://juejin.cn/post/6999935606738403342
linux安装postgresql+postgis全流程
https://www.jianshu.com/p/25385623ca1d

如何在 CentOS/RHEL 8 上为 PostgreSQL安装 PostGIS
https://www.onitroad.com/jc/linux/centos/faq/how-to-install-postgis-for-postgresql-on-centos-rhel-8.html


本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!