银河麒麟V10——Postgres-12.5主从编译安装

                            目录

一、机器信息

二、编译包下载

三、配置免密登录（两台都做）

四、编译安装postgresql-12.5(两台机器都做)

五、编译安装proj-6.2.1(两台机器都做)

六、编译安装geos-3.7.4(两台机器都做)

七、编译安装postgis-3.0.5(两台机器都做)

八、安装插件(两台机器都做)

九、主从搭建

 

 

---

 

一、机器信息

IP/主机名	备注
192.168.50.1/Rz01.local	主库
192.168.50.2/Rz02.local	从库

 

 

 

 

二、编译包下载

链接：https://pan.baidu.com/s/1fiOmYB8uplfA1LQDy_LhqQ 
提取码：5yxz

三、配置免密登录（两台都做）

#生成私钥
[root@Rz01 ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa
Your public key has been saved in /root/.ssh/id_rsa.pub
The key fingerprint is:
SHA256:aOff6seyptMmTDc8asILXjV7sj9qo5BERNn55TvuWWc root@Rz01.local
The key's randomart image is:
+---[RSA 3072]----+
|   .oo .         |
|   .. o   .      |
|    .  . o       |
|   .   .. .      |
|    . o S. .     |
|   . o +.oB      |
|    +..o+=.=. E  |
|   . +o O=B+oo   |
|    . o*+%O*.    |
+----[SHA256]-----+

#拷贝密钥到其他机器（例举一台）
[root@Rz01 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.50.1
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.50.1 (192.168.50.1)' can't be established.
ECDSA key fingerprint is SHA256:A5BrF9ngDlo96DXtjsA/wi2pHoq4zEQKpECILDmhCnM.
Are you sure you want to continue connecting (yes/no/[fingerprint])? ye
Please type 'yes', 'no' or the fingerprint: yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

Authorized users only. All activities may be monitored and reported.
root@192.168.50.1's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.50.1'"
and check to make sure that only the key(s) you wanted were added.

四、编译安装postgresql-12.5(两台机器都做)

#安装包
yum install -y openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel perl perl-devel install perl-ExtUtils-Embed readline readline-devel  zlib zlib-devel gettext gettext-devel bison flex  gcc gcc-c++ readline-devel

[root@Rz01 ~]# tar -xf postgresql-12.5.tar.gz
[root@Rz01 ~]# mkdir -p /export/servers/app/postgresql-12.5
[root@Rz01 ~]# cd postgresql-12.5
[root@Rz01 postgresql-12.5]# ./configure --prefix=/export/servers/app/postgresql-12.5 &&make &&make install

#添加环境变量
[root@Rz01 postgresql-12.5]# vim /etc/profile
export PGHOME=/export/servers/app/postgresql-12.5
export PGDATA=/export/servers/data/pgsql/
export PATH=$PGHOME/bin:$PATH
export LANG=en_US.utf8
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

#生效
[root@Rz01 postgresql-12.5]# source /etc/profile

#创建数据目录
useradd  postgres
mkdir -p /export/servers/data/pgsql/
chown postgres:postgres /export/servers/data/pgsql/

#初始化数据库,
su - postgres
export PGHOME=/export/servers/app/postgresql-12.5
export PGDATA=/export/servers/data/pgsql
export PATH=$PGHOME:/bin:$PATH

/export/servers/app/postgresql-12.5/bin/initdb -D /export/servers/data/pgsql/

#修改监听端口
vim /export/servers/data/pgsql/postgresql.conf
listen_addresses = '*'

#启动
/export/servers/app/postgresql-12.5/bin/pg_ctl -D /export/servers/data/pgsql/ -l logfile start

验证:

 登录并修改postgres密码

su - postgres
psql
alter user postgres with password 'postgres';
exit

#修改为md5表示用密码登录，如果为trust表示不需要密码， /export/servers/data/pgsql/pg_hba.conf 
# "local" is for Unix domain socket connections only

local   all             all                                     md5

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

#重启
/export/servers/app/postgresql-12.5/bin/pg_ctl -D /export/servers/data/pgsql/ -l logfile restart

验证：

五、编译安装proj-6.2.1(两台机器都做)

tar -xf proj-6.2.1.tar.gz
cd proj-6.2.1
./configure --prefix=/usr/local/proj-6.2.1
make &&make install

六、编译安装geos-3.7.4(两台机器都做)

tar -xf geos-3.7.4.tar.bz2
cd geos-3.7.4
./configure --prefix=/usr/local/geos-3.7.4
make && make install

七、编译安装postgis-3.0.5(两台机器都做)

tar -xf postgis-3.0.5.tar.gz
cd postgis-3.0.5

./configure --prefix=/export/servers/app/postgresql-12.5 --with-pgconfig=/export/servers/app/postgresql-12.5/bin/pg_config  --with-projdir=/usr/local/proj-6.2.1  --with-geosconfig=/usr/local/geos-3.7.4/bin/geos-config --with-gdalconfig=/usr/local/gdal-3.1.1/bin/gdal-config

八、安装插件(两台机器都做)

#登录数据库
su - postgres
[postgres@Rz01 ~]$ psql 
Password for user postgres: 
psql (12.5)


#安装插件
创建控件数据库：
create database gisdb;

切换到gisdb，提示gisdb=# 表示切换到gisdb
\c gisdb

需要给gisdb安装扩展gis插件：
CREATE EXTENSION postgis;

报错：ERROR:  could not load library "/export/servers/app/postgresql-12.5/lib/postgis-3.so": libgeos_c.so.1: cannot open shared object file: No such file or directory			

问题处理：原因是找不到链接库，新增动态链接库的配置文件
[root@Rz01 ~]# vim /etc/ld.so.conf.d/postgres.conf
/usr/local/geos-3.7.4/lib
/usr/local/gdal-3.1.1/lib
/usr/local/proj-6.2.1/lib

#生效
[root@Rz01 ]# ldconfig

执行ldconfig报错：
ldconfig: /usr/lib64/libLLVM-7.so is not a symbolic link
因为libxerces-c-3.0.so正常情况下应该是一个符号链接,而不是实体文集件,修改其为符号链接即可

问题解决：
[root@Rz01 ]# cd /usr/lib64/
[root@Rz01 lib64]# mv libLLVM-7.so libLLVM-7
[root@Rz01 lib64]# ln -s libLLVM-7 libLLVM-7.so
[root@Rz01 lib64]# ldconfig


#登录pg库安装插件
[postgres@Rz01 ~]$ psql
Password for user postgres: 
psql (12.5)
Type "help" for help.

postgres=# \c gisdb
You are now connected to database "gisdb" as user "postgres".
gisdb=# CREATE EXTENSION postgis;
CREATE EXTENSION

九、主从搭建

主库：

postgres=# CREATE ROLE replica login replication encrypted password 'replica';

修改配置文件增加备库访问控制

#vim /export//servers/data/pgsql/pg_hba.conf     //最后一行添加
host    replication     replica         0.0.0.0/0               md5

#因为匹配规则有先后顺序，于是需要将将host    all             all             127.0.0.1/32            md5
改为host    all             all             0.0.0.0/0            md5


vim /export/servers/data/pgsql/postgresql.conf
wal_level = hot_standby 
max_wal_senders = 10 
wal_keep_segments = 64 
wal_sender_timeout = 60s
max_connections = 100 

重启主库：

/export/servers/app/postgresql-12.5/bin/pg_ctl -D /export/servers/data/pgsql/ -l logfile restart

从库登录验证：

停止从库：

/export/servers/app/postgresql-12.5/bin/pg_ctl -D /export/servers/data/pgsql/ -l logfile stop

清理从库数据文件：

rm -rf /export/servers/data/pgsql/*

拉取主库配置(从节点机器，需要输入replica密码)：

[postgres@Rz02 ~]$ cd /export/servers/app/postgresql-12.5/bin

#拉取配置
[postgres@Rz02 bin]$  pg_basebackup -h 192.168.50.1 -D /export/servers/data/pgsql/ -p 5432 -U replica -Fp -Xs -Pv -R
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_112926"
49114/49114 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/3000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

验证：

 从库修改配置：

[postgres@Rz02 bin]$ vim /export/servers/data/pgsql/postgresql.conf
primary_conninfo = 'host=192.168.50.1 port=5432 user=replica password=replica'
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

启动从库：

/export/servers/app/postgresql-12.5/bin/pg_ctl -D /export/servers/data/pgsql/ -l logfile start 
waiting for server to start.... done
server started

验证主从同步：

主库查询：

 测试创建删除数据库观察从库是否同步：

主库创建数据库：

 从库验证：

 主库删除test:

 从库验证：