当前位置：首页 > news >正文

postgresql 11.2+gis+pgpool 4.2.2 离线安装步骤

news 来源：原创 2024/4/26 22:22:26

PG：
192.168.1.1
192.168.1.2
192.168.1.3
VIP:192.168.1.10

服务器用户postgres 密码 123

数据库用户postgres 密码 123
数据库用户pgpool 密码 123
数据库用户repl 密码 123

[all servers]
vi /etc/hosts

192.168.1.1 server1
192.168.1.2 server2
192.168.1.3 server3

[server1]
hostnamectl set-hostname server1

[server2]
hostnamectl set-hostname server2

[server3]
hostnamectl set-hostname server3

### 安装包位置 /data/package

一、Postgresql安装

[all servers]
1.修改操作系统limits
编辑/etc/security/limits.conf添加如下参数：

vi /etc/security/limits.conf

```
postgres soft nofile 1048576
postgres hard nofile 1048576
postgres soft nproc 131072
postgres hard nproc 131072
postgres soft stack 10240
postgres hard stack 32768
postgres soft core 6291456
postgres hard core 6291456
```

2.添加用户postgres

groupadd postgres
useradd -d /data/postgres -m postgres -g postgres
passwd postgres

-- 123

###配置sudo权限
vi /etc/sudoers

```
postgres ALL=(ALL:ALL) ALL
```

3.解压资源包至data目录下

cd /data/package
tar -zxvf /data/package/postgresql-11.2.tar.gz -C /data/postgres/

4.创建文件夹（/data/postgres/pgdata），并给目录赋权

mkdir -p /data/postgres/pgdata
chown -R postgres:postgres /data/postgres/postgresql-11.2/
chown -R postgres:postgres /data/postgres/pgdata/

5.配置环境变量

vi /etc/profile
```
export PGHOME=/data/postgres/postgresql-11.2
export PGDATA=/data/postgres/pgdata
```

source /etc/profile
6.安装c编译器，切换用户postgres编译源码

cd /data/package

tar -zxvf gcc.tar.gz

cd gcc

rpm -Uvh *.rpm --nodeps --force

gcc -v

rpm -ivh ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm
rpm -ivh readline-devel-6.2-11.el7.x86_64.rpm

su postgres

cd /data/postgres/postgresql-11.2

./configure --prefix=/data/postgres/postgresql-11.2

make all
make install
7.进入编译生成的bin目录初始化数据库

cd bin/
./initdb -D /data/postgres/pgdata
8.启动数据库

/data/postgres/postgresql-11.2/bin/pg_ctl -D /data/postgres/pgdata -l logfile start

### 关闭

/data/postgres/postgresql-11.2/bin/pg_ctl -D /data/postgres/pgdata -l logfile stop

9.查看安装结果（端口号验证）

lsof -i:5432

netstat -tunlp |grep 5432

10、修改配置文件

cd /data/postgres/pgdata
（1）编辑pg_hba.conf，添加如下内容：

host all all 0.0.0.0/0 md5
1
（2）编辑postgresql.conf，修改如下参数：

listen_addresses = '*'

11、数据库环境变量
编辑/data/postgres/.bashrc，添加如下内容：

export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=postgres
export PGHOME=/data/postgres/postgresql-11.2
export PGDATA=/data/postgres/pgdata
export PATH=$PGHOME/bin:$PATH

12、配置数据库systemctl开机启动

root用户下

vi /etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/data/postgres/pgdata
OOMScoreAdjust=-1000
ExecStart=/data/postgres/postgresql-11.2/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/data/postgres/postgresql-11.2/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/data/postgres/postgresql-11.2/bin/pg_ctl reload -D ${PGDATA} -s
TimeoutSec=0
[Install]
WantedBy=multi-user.target

systemctl daemon-reload
systemctl start postgresql.service
systemctl enable postgresql.service

13.连接pg

su postgres
cd /data/postgres/postgresql-11.2/bin

./psql

select version();

14、设置默认密码

# psql -U postgres
postgres=# ALTER USER postgres with encrypted password '123';
postgres=# \q （退出）

二、GIS安装

[all servers]

1.### 安装依赖包

cd /data/package/

rpm -ivh xz-devel-5.2.2-1.el7.x86_64.rpm
rpm -ivh libxml2-devel-2.9.1-6.el7.5.x86_64.rpm
rpm -ivh sqlite-3.7.17-8.el7_7.1.x86_64.rpm
rpm -ivh sqlite-devel-3.7.17-8.el7_7.1.x86_64.rpm

2.### 安装proj

proj-6.1.0.tar.gz

tar -zxvf /data/package/proj-6.1.0.tar.gz -C /data/postgres/
chown -R postgres:postgres /data/postgres/proj-6.1.0/
cd /data/postgres/proj-6.1.0

su postgres

./configure --prefix=/data/postgres/proj

make all
make install

3.### 安装geos

sudo -i

yum -y install bzip2

tar xf /data/package/geos-3.8.2.tar.bz2 -C /data/postgres/

chown -R postgres:postgres /data/postgres/geos-3.8.2/

cd /data/postgres/geos-3.8.2

su postgres

./configure --prefix=/data/postgres/geos

make all
make install

4.### 安装gdal
sudo -i

tar -zxvf /data/package/gdal-3.1.2.tar.gz -C /data/postgres/
chown -R postgres:postgres /data/postgres/gdal-3.1.2
cd /data/postgres/gdal-3.1.2

su postgres
./configure --prefix=/data/postgres/gdal --with-proj=/data/postgres/proj

make all
make install

5.### 安装postgis

sudo -i

tar -zxvf /data/package/postgis-2.5.2.tar.gz -C /data/postgres/
chown -R postgres:postgres /data/postgres/postgis-2.5.2
cd /data/postgres/postgis-2.5.2
su postgres
./configure --prefix=/data/postgres/postgis --with-geosconfig=/data/postgres/geos/bin/geos-config --with-projdir=/data/postgres/proj --with-gdalconfig=/data/postgres/gdal/bin/gdal-config

make all
make install

6.###配置动态库
编辑文件/etc/ld.so.conf，在文件尾部追加如下内容：

/data/postgres/gdal/lib/
/data/postgres/proj/lib/
/data/postgres/geos/lib/

加载
ldconfig

7.### 数据库添加PostGIS扩展

数据库添加PostGIS扩展
进入数据后执行添加扩展命令

cd /data/postgres/postgresql-11.2/bin

su postgres

./psql

create database test;
\c test;
create extension postgis;

成功添加扩展表示安装成功。

查看版本

select postgis_full_version();

三、pgpool安装部署

1.安装依赖包

[all servers]

cd /data/package

rpm -ivh postgresql-libs-9.2.24-4.el7_8.x86_64.rpm
rpm -ivh libmemcached-1.0.16-5.el7.x86_64.rpm
rpm -ivh pgpool-II-pg11-4.2.2-1pgdg.rhel7.x86_64.rpm
rpm -ivh pgpool-II-pg11-extensions-4.2.2-1pgdg.rhel7.x86_64.rpm

cd /usr/pgsql-11/share/extension
cp pgpool_recovery--1.1--1.2.sql pgpool_recovery--1.1.sql pgpool_recovery--1.2--1.3.sql pgpool_recovery--1.2.sql pgpool_recovery--1.3.sql pgpool_recovery.control pgpool-recovery.sql /data/postgres/postgresql-11.2/share/extension/

chown -R postgres:postgres /data/postgres/postgresql-11.2/share/extension/

cd /usr/pgsql-11/lib
cp pgpool_adm.so pgpool-recovery.so /data/postgres/postgresql-11.2/lib
cp -r bitcode /data/postgres/postgresql-11.2/lib
chown -R postgres:postgres /data/postgres/postgresql-11.2/lib

2.创建本地存档WAL

[all servers]
su - postgres
mkdir /data/postgres/archivedir

3.修改pg配置文件

[server1]
vi /data/postgres/pgdata/postgresql.conf

max_connections = 2000
unix_socket_directories = '/tmp'
password_encryption = md5
shared_buffers = 10GB
maintenance_work_mem = 3GB
effective_cache_size = 24GB
archive_mode = on
archive_command = 'cp "%p" "/data/postgres/archivedir"'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
wal_log_hints = on
log_destination = 'stderr'
logging_collector = on
log_directory = '/data/postgres/pgdata/log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_messages = warning
log_min_error_statement = warning

4.创建用户 repl pgpool

su postgres

[server1]# psql -U postgres -p 5432

postgres=# CREATE ROLE pgpool WITH LOGIN;
postgres=# CREATE ROLE repl WITH REPLICATION LOGIN;
postgres=# \password pgpool
-- 123
postgres=# \password repl
-- 123

GRANT pg_monitor TO pgpool;
\q

5.修改pg_hba.conf文件

vi /data/postgres/pgdata/pg_hba.conf

host replication all 0.0.0.0/0 md5

host all all 0.0.0.0/0 md5

6.设置免密登录

root/postgres 用户分别设置免密

[all servers]# mkdir ~/.ssh
[all servers]# chmod 700 ~/.ssh
[all servers]# cd ~/.ssh
[all servers]# ssh-keygen -t rsa -f id_rsa_pgpool
[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
-- 123
[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server2
[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server3

[all servers]# su - postgres
[all servers]$ mkdir ~/.ssh
[all servers]$ chmod 700 ~/.ssh
[all servers]$ cd ~/.ssh
[all servers]$ ssh-keygen -t rsa -f id_rsa_pgpool
[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server2
[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server3

验证测试
ssh postgres@server2 -i ~/.ssh/id_rsa_pgpool
ssh postgres@server3 -i ~/.ssh/id_rsa_pgpool

7.设置.pgpass

[all servers]#su - postgres
[all servers]vi /data/postgres/.pgpass

server1:5432:replication:repl:123
server2:5432:replication:repl:123
server3:5432:replication:repl:123
server1:5432:postgres:postgres:123
server2:5432:postgres:postgres:123
server3:5432:postgres:postgres:123

[all servers]
chmod 600 /data/postgres/.pgpass

8.创建pgpool_node_id

服务器1

[server1]# vi /etc/pgpool-II/pgpool_node_id
1

服务器2

[server2]# vi /etc/pgpool-II/pgpool_node_id
2

服务器3

[server3]# vi /etc/pgpool-II/pgpool_node_id
3



9.修改pgpool.conf 配置文件

vi /etc/pgpool-II/pgpool.conf

```````````````````````````````````````````````````
###

backend_clustering_mode = 'streaming_replication'

### pgpool Connection Settings
listen_addresses = '*'
port = 9999
socket_dir = '/tmp'

### pgpool Communication Manager Connection Settings

pcp_socket_dir = '/tmp'

# - Backend Connection Settings -

backend_hostname0 = 'server1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/data/postgres/pgdata'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'server1'

backend_hostname1 = 'server2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data/postgres/pgdata'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server2'

backend_hostname2 = 'server3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/data/postgres/pgdata'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'server3'

# - Authentication -

enable_pool_hba = on

# LOGS

log_destination = 'stderr'
logging_collector = on
log_connections = on
log_disconnections = on
log_hostname = on
log_statement = on
log_per_node_statement = on
log_client_messages = on
log_directory = '/data/postgres/log/pgpool_log'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 2d
log_rotation_size = 10MB

# NATIVE REPLICATION MODE

sr_check_user = 'pgpool'
sr_check_password = ''

# - Special commands -

follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'

# HEALTH CHECK GLOBAL PARAMETERS

health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3

# FAILOVER AND FAILBACK
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'

# ONLINE RECOVERY

recovery_user = 'postgres'
recovery_password = '123'
recovery_1st_stage_command = 'recovery_1st_stage'

# WATCHDOG

use_watchdog = on

hostname0 = 'server1'
wd_port0 = 9000
pgpool_port0 = 9999

hostname1 = 'server2'
wd_port1 = 9000
pgpool_port1 = 9999

hostname2 = 'server3'
wd_port2 = 9000
pgpool_port2 = 9999

wd_ipc_socket_dir = '/tmp'

# - Virtual IP control Setting -

delegate_IP = '192.168.1.10'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/26 dev eth0 label eth0:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/26 dev eth0'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'

# - Behaivor on escalation Setting -

wd_escalation_command = '/etc/pgpool-II/escalation.sh'

# -- heartbeat mode --

heartbeat_hostname0 = 'server1'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = 'server2'
heartbeat_port1 = 9694
heartbeat_device1 = ''
heartbeat_hostname2 = 'server3'
heartbeat_port2 = 9694
heartbeat_device2 = ''

````````````````````````````````````````````````
10.在服务器1上pgpool.conf的配置已完成。将pgpool.conf复制到其他Pgpool-II节点（server2 和 server3）。

[server1]# scp -p /etc/pgpool-II/pgpool.conf root@server2:/etc/pgpool-II/pgpool.conf
[server1]# scp -p /etc/pgpool-II/pgpool.conf root@server3:/etc/pgpool-II/pgpool.conf

11.修改pgpool-II脚本

[all servers]# cp -p /etc/pgpool-II/failover.sh{.sample,}
[all servers]# cp -p /etc/pgpool-II/follow_primary.sh{.sample,}
[all servers]# chown postgres:postgres /etc/pgpool-II/{failover.sh,follow_primary.sh}

[all servers]# vi /etc/pgpool-II/failover.sh
...
PGHOME=/data/postgres/postgresql-11.2
...

[all servers]# vi /etc/pgpool-II/follow_primary.sh
...
PGHOME=/data/postgres/postgresql-11.2
ARCHIVEDIR=/data/postgres/archivedir

···
primary_conninfo = 'host=${NEW_PRIMARY_NODE_HOST} port=${NEW_PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${NODE_HOST} passfile=''/data/postgres/.pgpass'''
-- 有两处位置
···
###

[all servers]# echo 'pgpool:'`pg_md5 123` >> /etc/pgpool-II/pcp.conf

[all servers]# su - postgres
[all servers]$ echo 'localhost:9898:pgpool:123' > ~/.pcppass
[all servers]$ chmod 600 ~/.pcppass

[server1]# cp -p /etc/pgpool-II/recovery_1st_stage.sample /data/postgres/pgdata/recovery_1st_stage
[server1]# cp -p /etc/pgpool-II/pgpool_remote_start.sample /data/postgres/pgdata/pgpool_remote_start
[server1]# chown postgres:postgres /data/postgres/pgdata/{recovery_1st_stage,pgpool_remote_start}

[server1]# vi /data/postgres/pgdata/recovery_1st_stage
...
PGHOME=/data/postgres/postgresql-11.2
ARCHIVEDIR=/data/postgres/archivedir
...

···
primary_conninfo = 'host=${PRIMARY_NODE_HOST} port=${PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${DEST_NODE_HOST} passfile=''/data/postgres/.pgpass'''

···

[server1]# vi /data/postgres/pgdata/pgpool_remote_start
...
PGHOME=/data/postgres/postgresql-11.2
...

···
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$DEST_NODE_HOST -i ~/.ssh/id_rsa_pgpool "
pkill postgres && sleep 5 && ps -aux | grep postgres"
## Start Standby node 前添加上述代码
···

[server1]# su - postgres
[server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"

12.修改pool_hba.conf、pool_passwd

[all servers]

vi /etc/pgpool-II/pool_hba.conf

host all pgpool 0.0.0.0/0 md5
host all postgres 0.0.0.0/0 md5

[all servers]# su - postgres
[all servers]$ sudo rm /etc/pgpool-II/pool_passwd
[all servers]$ sudo pg_md5 -m -f /etc/pgpool-II/pgpool.conf -u pgpool -p
-- 123
[all servers]$ sudo pg_md5 -m -f /etc/pgpool-II/pgpool.conf -u postgres -p
-- 123

# cat /etc/pgpool-II/pool_passwd
pgpool:md5832ef8fa27f8bfe44f24b69603b34570
postgres:md56d1599bc562b42583bf0cd64b4cfc825

13.修改escalation.sh

[all servers]# sudo cp -p /etc/pgpool-II/escalation.sh{.sample,}
[all servers]# chown postgres:postgres /etc/pgpool-II/escalation.sh

[all servers]# vi /etc/pgpool-II/escalation.sh
...
PGPOOLS=(server1 server2 server3)
VIP=192.168.1.10
DEVICE=eth0

/usr/bin/sudo /sbin/ip addr del $VIP/26 dev $DEVICE
...

14.在所有服务器上创建日志目录。

[all servers]# mkdir -p /data/postgres/log/pgpool_log/
[all servers]# chown postgres:postgres /data/postgres/log/pgpool_log/

15.修改pgpool Configuration

[all servers]# vi /etc/sysconfig/pgpool
...
OPTS=" -D -n"
···

16.依此启动server1、server2、server3的pgpool服务

systemctl start pgpool.service

pcp_recovery_node -h 192.168.1.10 -p 9898 -U pgpool -n 1

pcp_recovery_node -h 192.168.1.10 -p 9898 -U pgpool -n 2

pcp_watchdog_info -h 192.168.1.10 -p 9898 -U pgpool
-- 123

3 YES server1:9999 Linux 192.168.1.1 server1

server1:9999 Linux 192.168.1.1 server1 9999 9000 4 LEADER
server2:9999 Linux 192.168.1.2 server2 9999 9000 7 STANDBY
server3:9999 Linux 192.168.1.3 server3 9999 9000 7 STANDBY

psql -h 192.168.1.10 -p 9999 -U pgpool postgres -c "show pool_nodes"
-- 123

psql

查看主从信息

17.测试主从故障转移恢复

测试主从：

[server1]

postgres=# create table tb (id int4, create_time timestamp(0) without time zone);
CREATE TABLE
postgres=# insert into tb values (1, now());
INSERT 0 1
postgres=# select * from tb;
id | create_time
----+---------------------
1 | 2022-12-07 10:17:26
(1 row)

[server2]
postgres=# insert into tb values (1, now());
ERROR: cannot execute INSERT in a read-only transaction
postgres=# select * from tb;
id | create_time
----+---------------------
1 | 2022-12-07 10:17:26
(1 row)

[server3]
postgres=# insert into tb values (1, now());
ERROR: cannot execute INSERT in a read-only transaction
postgres=# select * from tb;
id | create_time
----+---------------------
1 | 2022-12-07 10:17:26
(1 row)

psql -h 192.168.1.10 -p 9999 -U postgres
-- 123

postgres=# insert into tb values (2, now());
INSERT 0 1
postgres=# select * from tb;
id | create_time
----+---------------------
1 | 2022-12-07 10:17:26
2 | 2022-12-07 10:22:32
(2 rows)

测试故障转移：

停止server1的pgpool、postgresql

[server1]
sudo systemctl stop pgpool
--123
sudo systemctl stop postgresql

[server2]

psql -h 192.168.1.10 -p 9999 -U pgpool postgres -c "show pool_nodes"

-- 123

server2自动升级为主库

pcp_watchdog_info -h 192.168.1.10 -p 9898 -U pgpool
-- 123

3 YES server3:9999 Linux 192.168.1.3 server3

server3:9999 Linux 192.168.1.3 server3 9999 9000 4 LEADER
server1:9999 Linux 192.168.1.1 server1 9999 9000 10 SHUTDOWN
server2:9999 Linux 192.168.1.2 server2 9999 9000 7 STANDBY

启动server1的 pgpool

[server1]
sudo systemctl start pgpool

pcp_watchdog_info -h 192.168.1.10 -p 9898 -U pgpool
Password:
3 YES server3:9999 Linux 192.168.1.3 server3

server3:9999 Linux 192.168.1.3 server3 9999 9000 4 LEADER
server1:9999 Linux server1 server1 9999 9000 7 STANDBY
server2:9999 Linux 192.168.1.2 server2 9999 9000 7 STANDBY

server1 pgpool加入机器成功

故障恢复

pcp_recovery_node -h 192.168.1.10 -p 9898 -U pgpool -n 0

-- 123

pcp_recovery_node -- Command Successful

psql -h 192.168.1.10 -p 9999 -U pgpool postgres -c "show pool_nodes"

-- 123

[server1]
psql

postgres=# select * from tb;
id | create_time
----+---------------------
1 | 2022-12-07 10:17:26
2 | 2022-12-07 10:22:32
(2 rows)

psql -h server2 -p 5432 -U pgpool postgres -c "select * from pg_stat_replication" -x
-- 123