openEuler离线安装pgpool+pg+gis实现高可用

您所在的位置：网站首页 › pgpool高可用 › openEuler离线安装pgpool+pg+gis实现高可用

openEuler离线安装pgpool+pg+gis实现高可用

2024-06-14 23:55| 来源: 网络整理| 查看: 265

openEuler安装pgpool-II+postgresql实现数据库高可用注意事项,编译出错可能是因为环境与lib依赖无法找到导致

注意不要一次把所有环境与lib依赖配置上，安装完某一项再把对应的配置进去 1.环境变量：/etc/profile 文件最后添加对应的配置刷新环境：source /etc/profile 在这里插入图片描述 2.lib库依赖：/etc/ld.so.conf 刷新依赖：ldconfig

安装依赖环境

cd rpm rpm -ivh --nodeps --force ./*.rpm 在这里插入图片描述

安装postgresql

此处选择版本为pg13，通过源码包编译安装下载地址：https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz

tar xvf postgresql-13.3.tar.gz cd postgresql-13.3 mkdir /usr/local/pgsql ./configure --prefix=/usr/local/pgsql --with-uuid=ossp --with-libxml make make install

在这里插入图片描述

创建postgres用户 useradd postgres 创建数据库初始化数据目录并更改权限 mkdir /usr/local/pgsql/data chown -R postgres:postgres /usr/local/pgsql 切换用户 su - postgres 初始化数据库 /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data 修改pg的配置文件 pg_hba.conf，postgresql.conf 启动数据库 /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start 配置数据库密码 postgres=# alter role postgres with password 'yourpassword' 重启数据库 /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile restart postgis前置安装上传相关文件

在这里插入图片描述

安装proj mkdir /usr/local/proj ./configure --prefix=/usr/local/proj make make install

在这里插入图片描述

安装geos mkdir /usr/local/geos ./configure --prefix=/usr/local/geos make make install

在这里插入图片描述

安装gdal mkdir /usr/local/gdal ./configure --prefix=/usr/local/gdal --with-proj=/usr/local/proj make make install

在这里插入图片描述

安装json-c mkdir /usr/local/json-c ./configure --prefix=/usr/local/json-c make make install

在这里插入图片描述

安装protobuf mkdir /usr/local/protobuf ./autogen.sh ./configure --prefix=/usr/local/protobuf make make install

在这里插入图片描述

安装protobuf-c mkdir /usr/local/protobuf-c ./configure --prefix=/usr/local/protobuf-c make make install

在这里插入图片描述

安装CGAL cd CGAL cmake3 . -DCMAKE_INSTALL_PREFIX=/usr/local/cgal make make install

在这里插入图片描述

安装SFCGAL cd SFCGAL-v1.3 cmake3 . -DCMAKE_INSTALL_PREFIX=/usr/local/sfcgal make make install

在这里插入图片描述

安装postgis cd postgis ./configure --prefix=/usr/local/postgis --with-pgsql=/usr/local/pgsql/bin/pg_config --with-pgconfig=/usr/local/pgsql/bin/pg_config --with-geosconfig=/usr/local/geos/bin/geos-config --with-projdir=/usr/local/proj --with-gdalconfig=/usr/local/gdal/bin/gdal-config --with-jsondir=/usr/local/json-c --with-protobufdir=/usr/local/protobuf-c --with-sfcgal=/usr/local/sfcgal/bin/sfcgal-config make make install

在这里插入图片描述

安装postgresql扩展

进入扩展目录 cd postgresql-13.3/contrib/ 进入需要安装的扩展目录 cd xxxx make && make install 然后链接数据库添加扩展在这里插入图片描述 ogr_fdw扩展（pg-13版本支持） https://codeload.github.com/pramsey/pgsql-ogr-fdw/tar.gz/refs/tags/v1.0.12

配置ssh免密通信 [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 postgres@server1 [all servers]# ssh-copy-id postgres@server2 [all servers]# ssh-copy-id 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 postgres@server1 [all servers]$ ssh-copy-id postgres@server2 [all servers]$ ssh-copy-id postgres@server3 [all servers]#vi /etc/ssh/ssh.config 最后添加 IdentityFile ~/.ssh/id_rsa_pgpool

记得修改 /etc/hosts文件

安装pgpool-II

此处选择版本为pgpool-II 4.3，通过源码包编译安装下载地址：https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.3.3.tar.gz

上传并解压

在这里插入图片描述

[all servers] #编译安装pgpool-II，依次执行下列命令 [all servers] #cd /home/soft/pgpool-II-4.3.3 [all servers] #mkdir /usr/local/pgpool [all servers] #./configure [all servers] #make [all servers] #make install 创建pid工作目录并修改权限 [all servers] #mkdir /usr/local/share/pgpool [all servers] #chown -R postgres:postgres /usr/local/share/pgpool [all servers] #chown -R postgres:postgres /usr/local/share/pgpool-II [all servers] #chown -R postgres:postgres /usr/local/etc 切换用户 [all servers] #su - postgres 修改pgpool配置 [all servers] $cd /usr/local/etc/ [all servers] $cp pgpool.conf.sample pgpool.conf [all servers] $cp pcp.conf.sample pcp.conf [all servers] $cp pool_hba.conf.sample pool_hba.conf 开启 WAL archiving [server1] # mkdir -p /var/lib/pgsql/archivedir [server1] # chown -R postgres:postgres /var/lib/pgsql [server1] # su - postgres 初始化数据库 [server1] $/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data [server1]$ vi /usr/local/pgsql/data/postgresql.conf 启用wal_log_hints 以使用pg_rewind。由于 Primary 可能稍后会成为 Standby，因此我们设置hot_standby = on。 listen_addresses = '*' archive_mode = on archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"' max_wal_senders = 10 max_replication_slots = 10 wal_level = replica hot_standby = on wal_log_hints = on [server1]# psql -U postgres -p 5432 postgres=# SET password_encryption = 'md5'; postgres=# CREATE ROLE pgpool WITH LOGIN; postgres=# CREATE ROLE repl WITH REPLICATION LOGIN; postgres=# alter role postgres with password 'yourpassword' postgres=# alter role pgpool with password 'yourpassword' postgres=# alter role repl with password 'yourpassword' postgres=# GRANT pg_monitor TO pgpool; 编辑pg_hba.conf以启用MD5身份验证方法 [server1]$ vi /usr/local/pgsql/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust host all all 0.0.0.0/0 trust host replication repl samenet trust [all servers]vi /usr/local/etc/pgpool_node_id 根据设备顺序，填入数字，0，1，2… 创建函数 [server1] $ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start [server1] $ cd /home/soft/pgpool-II-4.3.3/src/sql [server1] $make && make install [server1] $cd /home/soft/pgpool-II-4.3.3/src/sql/pgpool-regclass [server1] $make && make install [server1] $psql -f pgpool-regclass.sql template1 [server1] $cd /home/soft/pgpool-II-4.3.3/src/sql/pgpool-recovery [server1] $make && make install [server1] $psql -f pgpool-recovery.sql template1 [server1] $cd /home/soft/pgpool-II-4.3.3/src/sql [server1] $psql -f insert_lock.sql template1 [server1]# cp -p /usr/local/etc/recovery_1st_stage.sample /usr/local/pgsql/data/recovery_1st_stage [server1]# cp -p /usr/local/etc/pgpool_remote_start.sample /usr/local/pgsql/data/pgpool_remote_start [server1]# chown postgres:postgres /usr/local/pgsql/data/{recovery_1st_stage,pgpool_remote_start} [server1]#pg_md5 password [server1]# vi /usr/local/etc/pool_passwd 添加相应的md5密码 postgres:md52a29a4f7eb0a98abca0992ca3fb555b6 pgpool:md5bc458983af9a98798fefe59c1a81b8bd 修改脚本 [all servers]# cp -p /usr/local/etc/failover.sh{.sample,} [all servers]# cp -p /usr/local/etc/escalation.sh{.sample,} [all servers]# cp -p /usr/local/etc/follow_primary.sh{.sample,} [all servers]# chown -R postgres:postgres /usr/local/etc [all servers]# chmod +x /usr/local/etc/{failover.sh,escalation.sh,follow_primary.sh}

设置免密码执行命令

vi /etc/sudoers postgres ALL=NOPASSWD: /sbin/ip postgres ALL=NOPASSWD: /usr/sbin/arping vi /usr/local/etc/pcp.conf 添加 # USERID:MD5PASSWD postgres:e8a48653851e28c69d0506508fb27fc5 pgpool:ba777e4c2f15c11ea8ac3be7e0440aa0 repl:c3121723f31b44a9900cce919dd71ea3 vi /usr/local/etc/pool_hba.conf 类似 /usr/local/pgsql/data/pg_hba.conf vi /usr/local/etc/pgpool.conf ## [CONNECTIONS] listen_addresses = '*' port = 9999 socket_dir = '/tmp' ## [- Backend Connection Settings -] backend_hostname0 = 'server1' backend_data_directory0 = '/usr/local/pgsql/data' backend_application_name0 = 'server1' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_port0 = 5432 backend_weight0 = 1 backend_hostname1 = 'server2' backend_data_directory1 = '/usr/local/pgsql/data' backend_application_name1 = 'server2' backend_flag1 = 'ALLOW_TO_FAILOVER' backend_port1 = 5432 backend_weight1 = 1 ## [- Authentication -] enable_pool_hba = on pool_passwd = 'pool_passwd' ## [- POOLS -] # - Concurrent session and pool size - num_init_children = 400 # Number of concurrent sessions allowed # (change requires restart) max_pool = 2 # Number of connection pool caches per connection # (change requires restart) # - Life time - child_life_time = 5min # Pool exits after being idle for this many seconds ## [LOGS] logging_collector = on log_directory = '/var/log/pgpool_log' log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log' log_file_mode = 0600 log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 10MB ## [FILE LOCATIONS] pid_file_name = '/usr/local/share/pgpool/pgpool.pid' # 此目录用来存放 pgpool_status 文件，此文件保存集群状态（刷新有问题时会造成show pool_status不正确） logdir = '/var/log/pgpool_log' ## [CONNECTION POOLING] connection_cache = on # Activate connection pools # (change requires restart) reset_query_list = 'ABORT; DISCARD ALL' # The following one is for 8.2 and before ## [LOAD BALANCING MODE] load_balance_mode = on # Activate load balancing mode # (change requires restart) ignore_leading_white_space = on # Ignore leading white spaces of each query white_function_list = '' # Comma separated list of function names # that don't write to database # Regexp are accepted black_function_list = '' # Comma separated list of function names # that write to database # Regexp are accepted #black_query_pattern_list = '' # Semicolon separated list of query patterns # that should be sent to primary node # Regexp are accepted # valid for streaming replicaton mode only. database_redirect_preference_list = '' # comma separated list of pairs of database and node id. # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2' # valid for streaming replicaton mode only. ## [STREAMING REPLICATION MODE] sr_check_period = 0 # Streaming replication check period # Disabled (0) by default sr_check_user = 'postgres' # 为''时查找 pool_passwd sr_check_password = 'postgres' sr_check_database = 'postgres' #follow_primary_command = '/usr/local/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R' ## [HEALTH CHECK GLOBAL PARAMETERS] health_check_period = 20 health_check_timeout = 10 health_check_user = 'postgres' # 为''时查找 pool_passwd health_check_password = 'postgres' health_check_database = 'postgres' #health_check_max_retries = 2 ## [FAILOVER AND FAILBACK 故障切换和故障恢复] failover_command = '/usr/local/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S' ## [ONLINE RECOVERY 在线恢复，建议手动恢复，防止脑裂] #recovery_user = 'postgres' #recovery_password = 'postgres' #recovery_1st_stage_command = 'recovery_1st_stage' ## [WATCHDOG 看门狗] use_watchdog = on # Activates watchdog # (change requires restart) # -Connection to up stream servers - trusted_servers = 'server1,server2' # trusted server list which are used # to confirm network connection # (hostA,hostB,hostC,...) # (change requires restart) ping_path = '/bin' # ping command path # (change requires restart) ## [- Watchdog communication Settings -] hostname0 = 'server1' wd_port0 = 9000 pgpool_port0 = 9999 hostname1 = 'server2' wd_port1 = 9000 pgpool_port1 = 9999 wd_ipc_socket_dir = '/tmp' ## [- Virtual IP control Setting -] delegate_IP = '192.168.243.243' if_cmd_path = '/sbin' # path to the directory where if_up/down_cmd exists # If if_up/down_cmd starts with "/", if_cmd_path will be ignored. # (change requires restart) if_up_cmd = '/usr/bin/sudo /sbin/ip addr add 192.168.243.243/24 dev ens33 label ens33:0' # startup delegate IP command # (change requires restart) if_down_cmd = '/usr/bin/sudo /sbin/ip addr del 192.168.243.243/24 dev ens33' # shutdown delegate IP command # (change requires restart) arping_path = '/usr/sbin' # arping command path # If arping_cmd starts with "/", if_cmd_path will be ignored. # (change requires restart) arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U 192.168.243.243 -w 1 -I ens33' ## [- Behaivor on escalation Setting 升级与降级，建议手动恢复-] clear_memqcache_on_escalation = off # Clear all the query cache on shared memory # when standby pgpool escalate to active pgpool # (= virtual IP holder). # This should be off if client connects to pgpool # not using virtual IP. # (change requires restart) wd_escalation_command = '/usr/local/etc/escalation.sh' # Executes this command at escalation on new active pgpool. # (change requires restart) #wd_de_escalation_command = '' # Executes this command when master pgpool resigns from being master. # (change requires restart) ## [- Watchdog consensus settings for failover -] failover_when_quorum_exists = off # Only perform backend node failover # when the watchdog cluster holds the quorum # (change requires restart) failover_require_consensus = off # Perform failover when majority of Pgpool-II nodes # aggrees on the backend node status change # (change requires restart) #allow_multiple_failover_requests_from_node = on # A Pgpool-II node can cast multiple votes # for building the consensus on failover # (change requires restart) enable_consensus_with_half_votes = on # apply majority rule for consensus and quorum computation # at 50% of votes in a cluster with even number of nodes. # when enabled the existence of quorum and consensus # on failover is resolved after receiving half of the # total votes in the cluster, otherwise both these # decisions require at least one more vote than # half of the total votes. # (change requires restart) # [- Lifecheck Setting 看门狗生命周期检查-] wd_monitoring_interfaces_list = 'any' # Comma separated list of interfaces names to monitor. # if any interface from the list is active the watchdog will # consider the network is fine # 'any' to enable monitoring on all interfaces except loopback # '' to disable monitoring # (change requires restart) wd_lifecheck_method = 'heartbeat' # Method of watchdog lifecheck ('heartbeat' or 'query' or 'external') # (change requires restart) wd_interval = 10 # lifecheck interval (sec) > 0 # (change requires restart) # [-- heartbeat mode 看门狗心跳模式配置--] #wd_heartbeat_port = 9694 # Port number for receiving heartbeat signal # (change requires restart) #wd_heartbeat_keepalive = 2 # Interval time of sending heartbeat signal (sec) # (change requires restart) #wd_heartbeat_deadtime = 30 # Deadtime interval for heartbeat signal (sec) # (change requires restart) #heartbeat_destination0 = '192.168.243.201' # Host name or IP address of destination 0 # for sending heartbeat signal. # (change requires restart) #heartbeat_destination_port0 = 9694 # Port number of destination 0 for sending # heartbeat signal. Usually this is the # same as wd_heartbeat_port. # (change requires restart) #heartbeat_device0 = 'ens33' # Name of NIC device (such like 'eth0') # used for sending/receiving heartbeat # signal to/from destination 0. # This works only when this is not empty # and pgpool has root privilege. # (change requires restart) heartbeat_hostname0 = 'server1' heartbeat_port0 = 9694 heartbeat_device0 = 'ens33' heartbeat_hostname1 = 'server2' heartbeat_port1 = 9694 heartbeat_device1 = 'ens33' # -- query mode 看门狗检查pgsql数据库生命周期检查 -- wd_life_point = 3 # lifecheck retry times # (change requires restart) wd_lifecheck_query = 'SELECT 1' # lifecheck query to pgpool from watchdog # (change requires restart) wd_lifecheck_dbname = 'postgres' # Database name connected for lifecheck # (change requires restart) wd_lifecheck_user = 'postgres' # watchdog user monitoring pgpools in lifecheck # (change requires restart) wd_lifecheck_password = 'postgres' # Password for watchdog user in lifecheck # Leaving it empty will make Pgpool-II to first look for the # Password in pool_passwd file before using the empty password # (change requires restart) # [- Other pgpool Connection Settings -] #------------------------------------------------------------------------------ # OTHERS #------------------------------------------------------------------------------ enable_shared_relcache = off # If on, relation cache stored in memory cache, # the cache is shared among child process. # Default is on. # (change requires restart)

参考文章 https://blog.csdn.net/weixin_43848476/article/details/126749528

【本文地址】

openEuler离线安装pgpool+pg+gis实现高可用

openEuler离线安装pgpool+pg+gis实现高可用

今日新闻

推荐新闻