Postgre SQL-decoderbufs配置方法

转载已于 2022-04-08 09:52:24 修改 · 2.1k 阅读

3 ·

CC 4.0 BY-SA版权

原文链接：https://www.kancloud.cn/datapipeline-group/v2_7_0/1475270

文章标签：

#postgresql

于 2022-04-07 15:41:12 首次发布

该博客详细介绍了在Linux环境中关闭SELinux和防火墙，升级GCC/G++，安装依赖环境，特别是针对PostgreSQL-decoderbufs的安装步骤，包括PostGIS、protobuf等组件的编译和配置。在安装过程中遇到了PostGIS编译时pg_config的位置问题，以及protobuf因网络问题导致的安装困难，并给出了相应的解决方案。最终成功安装并验证了PostgreSQL-decoderbufs，创建了逻辑复制槽。

部署运行你感兴趣的模型镜像

Postgre SQL-decoderbufs配置方法

关闭SELinux

[root@localhost ~]# setenforce 0    //临时关闭
[root@localhost ~]# getenforce
Enforcing
[root@localhost ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux      //永久关闭
[root@localhost ~]# cat /etc/sysconfig/selinux 

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

关闭防火墙

[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# systemctl disable firewalld.service 
[root@localhost ~]# firewall-cmd --state
not running

升级GCC/G++

强烈推荐使用devtoolset方式,传统编译方式安装极为耗时且成功率极低.

[root@localhost ~]# sudo yum install devtoolset-4-gcc devtoolset-4-gcc++ devtoolset-4-gcc-c++ -y
[root@localhost ~]# scl enable devtoolset-4 bash
[root@localhost ~]# echo "source /opt/rh/devtoolset-4/enable" >> /etc/profile
[root@localhost ~]# source /etc/profile

安装依赖环境

[root@localhost ~]# yum install autoconf automake libtool readline-devel zlib-devel libxslt-devel json-c-devel pcre-devel unzip -y

升级autoconf(版本>=2.6.4)

[root@localhost ~]# wget ftp://ftp.gnu.org/gnu/autoconf/autoconf-2.69.tar.gz
[root@localhost ~]# tar zxvf autoconf-2.69.tar.gz 
[root@localhost ~]# cd autoconf-2.69
[root@localhost ~]# ./configure --prefix=/usr/  
[root@localhost ~]# make && make install
[root@localhost ~]# /usr/bin/autoconf -V 
autoconf (GNU Autoconf) 2.69
Copyright (C) 2012 Free Software Foundation, Inc.
License GPLv3+/Autoconf: GNU GPL version 3 or later
<http://gnu.org/licenses/gpl.html>, <http://gnu.org/licenses/exceptions.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

下载并编译安装一下依赖环境

wget http://download.osgeo.org/geos/geos-3.6.2.tar.bz2
wget http://download.osgeo.org/proj/proj-4.9.3.tar.gz
wget http://download.osgeo.org/gdal/2.2.3/gdal-2.2.3.tar.gz
wget https://download.osgeo.org/postgis/source/postgis-2.3.7.tar.gz
*.以上四个安装包编译时不需要指定 --prefix=
tar -jxvf geos-3.6.2.tar.bz2
cd geos-3.6.2
./configure
make && make install

postgis编译时需要指定pg_config位置
find / -name pg_config
./configure --with-pgconfig=/usr/pgsql-10/bin/pg_config

可能需要安装部分依赖
yum install postgresql10-server-dev
yum install postgresql-devel
yum install postgresql-devel10
yum install postgresql10-devel
yum install postgresql10-contrib

可能会缺少部分文件导致无法编译，需要找到并复制到pg的lib
ldd /usr/pgsql-10/lib/postgis-2.3.so
find / -name libgeos_c.so.1
find / -name libproj.so.12
cp /usr/local/lib/libgeos_c.so.1 /usr/pgsql-10/lib/
cp /usr/local/lib/libproj.so.12 /usr/pgsql-10/lib/

在PG中扩展Postgis

postgres=# CREATE EXTENSION postgis;
postgres=# CREATE EXTENSION postgis_topology;
postgres=# CREATE EXTENSION fuzzystrmatch;
postgres=# CREATE EXTENSION postgis_tiger_geocoder;

验证Postgis安装

postgres=# \dx
                                            List of installed extensions
          Name          | Version |   Schema   |                             Description
------------------------+---------+------------+---------------------------------------------------------------------
 fuzzystrmatch          | 1.1     | public     | determine similarities and distance between strings
 plpgsql                | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis                | 2.3.7   | public     | PostGIS geometry, geography, and raster spatial types and functions
 postgis_tiger_geocoder | 2.3.7   | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 2.3.7   | topology   | PostGIS topology spatial types and functions
(5 rows)
postgres=# \q

安装ProtoBuf 3.3.0

[root@localhost ~]# wget https://github.com/google/protobuf/archive/v3.3.0.tar.gz
[root@localhost ~]# ./autogen.sh && ./configure --prefix=/usr/local/protobuf --libdir=/usr/lib64
[root@localhost ~]# make & make install
[root@localhost ~]# ldconfig
[root@localhost ~]# echo "export PATH=$PATH:/usr/local/protobuf/bin" >> /etc/profile
[root@localhost ~]# protoc --version

由于谷歌被墙，这个可能安装困难，vim autogen.sh看一下里面是需要下载两个压缩包googlemock-release-1.7.0.zip，googletest-release-1.7.0.zip，手动下载，手动跟着这个文件的步骤，准备好再编译

安装ProtoBuf-C 1.2.1

[root@localhost ~]# wget https://github.com/protobuf-c/protobuf-c/archive/v1.2.1.tar.gz
[root@localhost ~]# ./autogen.sh && ./configure --prefix=/usr/local/protobuf-c --libdir=/usr/lib64/
[root@localhost ~]# make & make install

安装postgresql-decoderbufs

[postgres@localhost ~]$ wget https://github.com/debezium/postgres-decoderbufs/archive/v0.7.5.tar.gz
[postgres@localhost ~]$ tar xzvf v0.7.5.tar.gz 
[postgres@localhost ~]$ make USE_PGXS=1 PG_CONFIG=/usr/pgsql-10/bin/pg_config
[postgres@localhost ~]$ make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-10/bin/pg_config
[postgres@localhost ~]$ cat /var/lib/pgsql/10/data/postgresql.conf
listen_addresses = '*'
shared_preload_libraries = 'decoderbufs'
wal_level = logical
max_wal_senders = 10
wal_keep_segments = 4
max_replication_slots = 4

重启PG数据库

[postgres@localhost ~]$ pg_ctl stop -D /usr/local/pgsql/data
[postgres@localhost ~]$ pg_ctl start -D /usr/local/pgsql/data
# systemctl restart postgresql-10.service

验证,创建有以下结果无报错则成功

postgres=# select * from pg_create_logical_replication_slot('decoderbufs_demo', 'decoderbufs');
LOG:  logical decoding found consistent point at 0/23205E0
DETAIL:  There are no running transactions.
STATEMENT:  select * from pg_create_logical_replication_slot('decoderbufs_demo', 'decoderbufs');
INFO:  Exiting startup callback
    slot_name     | xlog_position
------------------+---------------
 decoderbufs_demo | 0/2320618
(1 row)