hadoop集群部署2-离线版

发表于 2021-04-19 | 更新于: 2022-06-05 | 分类于 Linux

字数统计: 2.5k | 阅读时长 ≈ 11

1 环境

1.1 软件

Centos7.9 //最低7.2版本兼容中标麒麟（服务器操作系统V7.0U6）

Ambari 2.7.4 //支持HDP-3.1.5 和 HDF-3.2.0
Mysql 5.7 //只支持mysql5.7或者postgresql10.7/ 10.5/ 10. 2/ 9.6
JDK 1.80_77及以上版本
Chrome 57.0.2 / 56.0.2
https://supportmatrix.hortonworks.com/ (版本兼容查询)

1.2 硬件

推荐最低配置：
enter description here

1.3 参考文档

https://docs.cloudera.com/HDPDocuments/Ambari-2.7.5.0/bk_ambari-installation/content/ch_Getting_Ready.html （网上历史方式cloudera.com,已经收费）
备选：
https://ambari.apache.org (ambari开源版本)

2 前期准备（各节点均配置）

2.1 关闭防火墙/selinux

1	systemctl disable firewalld.service && systemctl stop firewalld.service

1 2	setenforce 0 /etc/selinux/config //永久关闭修改config文件

2.2 修改网络

设置主机IP地址为静态地址，同时开启网卡自启动。

2.3 系统优化

设置文件最大打开数

vim /etc/security/limits.conf
* soft nofile 65535      // 当前系统生效的最大文件打开数
* hard nofile 65535     // 当前系统所能设定的最大值
设置最大进程数
* soft nproc 11000
* hard nproc 11000

重启或重新进入shell窗口
enter description here

2.4 设置hosts

修改主机名，并将主机名加入各节点hosts文件

2.5 ssh免密登录

SSH免密码登录，因为Hadoop需要通过SSH登录到各个节点进行操作，我用的是root用户，每台服务器都生成公钥，再合并到authorized_keys
(1)CentOS默认没有启动ssh无密登录，去掉/etc/ssh/sshd_config其中2行的注释，每台服务器都要设置，

1
2
3

$ vi /etc/ssh/sshd_config
RSAAuthentication yes 
PubkeyAuthentication yes

(2)输入命令，ssh-keygen -t rsa，生成key，都不输入密码，一直回车，/root就会生成.ssh文件夹，每台服务器都要设置。

1	$ ssh-keygen -t rsa

(3)合并公钥到authorized_keys文件，在Master服务器，进入/root/.ssh目录，通过SSH命令合并

$ cat id_rsa.pub>> authorized_keys
$ Chmod 600 authorized_kdys
$ ssh root@192.168.0.165 cat ~/.ssh/id_rsa.pub>> authorized_keys
$ ssh root@192.168.0.166 cat ~/.ssh/id_rsa.pub>> authorized_keys
$ ssh root@192.168.0.167 cat ~/.ssh/id_rsa.pub>> authorized_keys

(4)把Master服务器的authorized_keys、known_hosts复制到Slave服务器的/root/.ssh目录

1
2
3

$ scp authorized_keys root@192.168.0.165:~/.ssh
$ scp authorized_keys root@192.168.0.166:~/.ssh
$ scp authorized_keys root@192.168.0.167:~/.ssh

(5)完成，ssh Slave1、ssh Slave2、ssh Slave3就不需要输入密码了

2.6 时间同步(提前下载)

1) master节点作为时间同步服务器，所有节点安装ntp ntpdate

1	yum install ntp ntpdate

2) master节点配置

1	vim /etc/ntp.config

启动时间同步服务器

1 2	systemctl start ntpd #启动时间同步程序 systemctl enable ntpd #允许时间同步程序开机启动

3) 在其它节点上运行如下命令同步时间

1	ntpdate -u 192.168.214.128

设置定时任务

1	3 * * * * root ntpdate -u 192.168.214.128 # 每三分钟执行一次同步

2.7 jdk安装

jdk-8u144-linux-x64.tar.gz 或 jdk-8u152-linux-x64.tar.gz
版本需要高于8u77
配置jdk环境

1 2	export JAVA_HOME=/usr/local/java_version export PATH=$JAVA_HOME/bin/:$PATH

2.8 mysql安装（ambari节点）

下载mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
mysql支持5.7版本
1) 下载安装包
http://dev.mysql.com/downloads/mysql/#downloads
推荐下载通用安装方法的tar.gz包
2) 安装过程

shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
shell> bin/mysqld --initialize --user=mysql
shell> bin/mysql_ssl_rsa_setup
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

默认密码如下
enter description here
3) 修改my.cnf的内容如下

[mysql] 
default-character-set=utf8

[mysqld]
skip-name-resolve 
port=3306
basedir=/usr/local/mysql  # 设置mysql的安装目录
datadir=/usr/local/mysql/data  # 设置mysql数据库的数据的存放目录
max_connections=200 # 允许最大连接数
character-set-server=utf8
default-storage-engine=INNODB # 创建新表时将使用的默认存储引擎
lower_case_table_names=1
max_allowed_packet=16M

4) 初始化密码

mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5) 添加远程访问权限

mysql> use mysql; 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> grant all privileges on *.* to root@'%' identified by '123456' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select host, user from user;
+-----------+-----------+
| host       | user      |
+-----------+-----------+
| %          | root      |
| localhost | mysql.sys |
+-----------+-----------+
mysql> quit
$ /etc/init.d/mysqld restart   //一定要重启才会生效。

6) 设置开机自启

1	chkconfig mysql on

3 设置本地库

3.1 apache安装（ambari节点）

1 2	yum install apache //搭建本地仓库 systemctl start httpd && systemctl enable httpd

3.2 下载并上传软件包

Ambari https://archive.cloudera.com/p/ambari/2.x/2.7.4.14/centos7/ambari-2.7.4.14-1-centos7.tar.gz
HDP https://archive.cloudera.com/p/HDP/3.x/3.1.4.0/centos7/HDP-3.1.4.0-centos7-rpm.tar.gz
HDP-UTILS https://archive.cloudera.com/p/HDP-UTILS/1.1.0.22/repos/centos7/HDP-UTILS-1.1.0.22-centos7.tar.gz

HDP-GPL https://archive.cloudera.com/p/HDP-GPL/3.x/3.1.4.0/centos7/HDP-GPL-3.1.4.0-centos7-gpl.tar.gz

解压缩文件，拷贝到/var/www/html下

3.3 配置源

4 Ambari安装

4.1 创建源文件

ambari.repo

[ambari]
name=ambari-2.7.4.0
baseurl=http://192.168.1.3/ambari/centos7/2.7.4.0-118/
gpgcheck=1

gpgkey=http://192.168.1.3/ambari/centos7/2.7.4.0-118/RPM-GPG-KEY/RPM-GPG-KEY-Jenkins
enabled=1

hdp.repo

[HDP]
name=HDP-3.1.4.0
baseurl=http://192.168.1.3/HDP/centos7/3.1.4.0-315/
gpgcheck=0

enabled=1

hdp-gpl.repo

[HDP-GPL]
name=HDP-GPL-3.1.4.0
baseurl=http://192.168.1.3/HDP-GPL/centos7/3.1.4.0-315/
gpgcheck=0

enabled=1

hdp-utils.repo

[HDP-UTILS]
name=HDP-UTILS-3.1.4.0
baseurl=http://192.168.1.3/HDP-UTILS/centos7/1.1.0.22/
gpgcheck=0

enabled=1

4.2 生成缓存

1 2	yum clean all yum makecache

4.3 安装Ambari Server

1	yum install ambari-server

离线版没有postgresql，需要提前使用–downloadonly下载好.

4.3.1 初始化Ambar

enter description here
备注：mysql连接器提前下载好放到/usr/share/java目录
https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-5.1.49.tar.gz
修改jar文件名为mysql-connector-java.jar 放到/usr/share/java目录。

4.3.2 初始化mysql

4.3.2.1 创建ambari数据库

1	CREATE DATABASE `ambari` CHARACTER SET utf8 COLLATE utf8_general_ci;

4.3.2.2 初始化数据

1	mysql -uroot -p123456 ambari < /var/lib/ambari-server/resources/Ambari-DDL-MySQL-CREATE.sql

4.3.3 启动Ambari server

ambari-server start启动服务。

此时，登录10.10.10.31:8080，即可看到ambari的页面，如下：
默认账号密码：admin admin
enter description here

5 安装HDP集群

集群名称 zhjx
enter description here
centos选择rathat7,输入本地私库的url，其他类型删除。

输入节点主机名，下载管理节点的ssh密码，id_rsa 文件，在页面上传密钥
备注，必须安装如下格式，不能使用hadoop2

没有按照上图设置主机名，会出现主机名不合规的提示。
enter description here

这里经常安装失败，可以查看日志排查。
enter description here
检查无误，NEXT→通过即可
如果这个步骤失败了错误，记得多看日志，多找问题，如果还不行的话，回档咯

[root@master ~]# # ambari-server stop    #停止命令
[root@master ~]# # ambari-server reset   #重置命令
[root@master ~]# # ambari-server setup   #重新设置 
[root@master ~]# # ambari-server start   #启动命令

问题1：
centos7.3/4安装ambari2.6以上版本报EOF occurred in violation of protocol (_ssl.c:579)
enter description here
解决办法：

开始可以少选些服务，减少错误，后面还可以添加。
HDFS、Hive、HBase、Sqoop、ZooKeeper、Flume
enter description here
节点分配，我这里默认了

设置密码，我这里直接都设置为admin1234

数据库连接都采用root用户，

配置数据库的时候执行下黄色部分的命令

enter description here

设置各组件的路径，我这里默认
enter description here
默认了

默认了

默认了

这一步也是经常报错，看日志解决吧。

5.1.1 设置组件自启动

admin>>Service Auto Start 所有组件全部开启
enter description here

6 问题

6.1 问题1：缺少软件（提前downloadonly）

unzip
nc
psmisc
redhat-lsb
libtirpc-devel
gcc
python-devel
python-kerberos

6.2 问题2：没有flume组件

高版本没有集成组件，需要手动集成，建议采用低版本hdp安装（ambari 2.6.2.2+hdp2.6.5）。

6.3 问题3：重启主机，ambari-server无法启动

日志vim /var/log/ambari-server/ambari-server.out
问题原因：数据库连接失败
enter description here
网上查找原因：是需要在mysql连接url后面添加 ?useSSL=false,其实不然。

enter description here
以上问题都是一个原因经尝试，都和ambari-server的配置文件有关系。
配置如下：

无论是使用IP地址，还是配置的主机名，都不可以链接到数据库。配置为localhost恢复正常。
如下：

6.4 问题4：hive组件配置数据库时，需要执行一下mysql连接驱动的路径的命令在master节点

否则数据库连接测试总是失败。

1	ambari-server setup --jdbc-db=mysql –jdbc-driver=/usr/share/java/mysql-connector-java.jar

enter description here

6.5 6.5 问题5：SmartSense Gateway is not active

原因：服务器离线，无法访问域名地址，忽略即可。

enter description here

7 扩展功能

7.1 配置namenode HA

参考资料：https://blog.csdn.net/qq_21153619/article/details/81974140

1) 在Ambari UI中，选择Services>HDFS>Summary

2) 点击Service Actions,点击Enable NameNode HA

3) 在Get Started页面中，输入一个Nameservice ID然后点Next

备注：设置HA前，需要先关闭HBase服务

调整服务节点

如上图操作：

在nameNode上创建检查点

1、登录Namenode Host Hadoop3.htsb。

2、将NameNode放在安全模式（只读模式）

1	sudo su hdfs -l -c 'hdfs dfsadmin -safemode enter'

3、一旦处于安全模式，创建一个检查点

1	sudo su hdfs -l -c 'hdfs dfsadmin -saveNamespace'

如上图所示：

初始化journalnode

1、登录Namenode Host Hadoop3.htsb。

2、运行初始化journalnode

1	sudo su hdfs -l -c 'hdfs namenode -initializeSharedEdits'

3、一旦Ambari检测到已成功初始化，您将能够继续。

如上图所示：

初始化预期的HA元数据

1、登录Namenode Host Hadoop3.htsb

2、通过运行初始化NameNode自动故障转移的元数据

1	sudo su hdfs -l -c 'hdfs zkfc -formatZK'

3、登录新Namenode节点namenode1.htsb

4、通过运行初始化其他nameNode的元数据

1	sudo su hdfs -l -c 'hdfs namenode -bootstrapStandby'

下一步

hadoop集群部署1

发表于 2021-04-19 | 更新于: 2022-06-05 | 分类于 Linux

字数统计: 4.5k | 阅读时长 ≈ 20

1. 环境

平台使用 Centos 7 64位作为系统环境。采用1个Master节点与3个Slave节点搭建成为分布式集群，详情见表 1。

enter description here

2. 基础安装

2.1. 安装CentOS 7

拷制U盘启动后，选择开机使用U盘启动，进入主引导装机页面如下图。

图 1
选择Test this medis & install CentOS Linux 7，进入下面界面：

图 2
稍等片刻后，进入CenOS7可视化安装界面，选择中文。

图 3
点击Continue后，进入安装信息摘要界面，选择日期和时间为亚洲/上海时区，主要保持每个服务器的时区一致，软件选择为最小安装，点击安装位置后如图 12所示，根据需要选择自动分区或者手动分区，这里为自动分区。点击完成后，回到图 11，点击开始安装。

图 4

图 5
安装进行过程中提示用户设置如下图，点击ROOT密码，为ROOT用户设置密码，如需要创建其他用户，可点击创建用户，新增其他用户。

图 6
之上内容设置完成后，点击重启，完成系统安装。

2.2. 配置网络

设置各节点静态IP
修改hostname：
$ vi /etc/hostname #编辑配置文件
Master

2.3. 关闭防火墙和SeLinux

CentOS 7.0默认使用的是firewall作为防火墙，默认为最小化安装时，系统没有安装防火墙，安装完可视化界面后，可能防火墙也会被安装上，因此需要关闭防火墙。

1 2	$ systemctl stop firewalld.service #停止firewall $ systemctl disable firewalld.service #禁止firewall开机启动

使用sestatus -v 命令，查看Selinux状态。如果不是disable状态，编辑/etc/sysconfig/selinux 文件。

1 2	$ vi /etc/sysconfig/selinux SELINUX=disabled

2.4. SSH免密码登录

SSH免密码登录，因为Hadoop需要通过SSH登录到各个节点进行操作，我用的是root用户，每台服务器都生成公钥，再合并到authorized_keys
(1) CentOS默认没有启动ssh无密登录，去掉/etc/ssh/sshd_config其中2行的注释，每台服务器都要设置，

1
2
3

$ vi /etc/ssh/sshd_config
RSAAuthentication yes 
PubkeyAuthentication yes

(2) 输入命令，ssh-keygen -t rsa，生成key，都不输入密码，一直回车，/root就会生成.ssh文件夹，每台服务器都要设置。

1	$ ssh-keygen -t rsa

(3) 合并公钥到authorized_keys文件，在Master服务器，进入/root/.ssh目录，通过SSH命令合并

$ cat id_rsa.pub>> authorized_keys
$ Chmod 600 authorized_kdys
$ ssh root@192.168.0.165 cat ~/.ssh/id_rsa.pub>> authorized_keys
$ ssh root@192.168.0.166 cat ~/.ssh/id_rsa.pub>> authorized_keys
$ ssh root@192.168.0.167 cat ~/.ssh/id_rsa.pub>> authorized_keys

(4) 把Master服务器的authorized_keys、known_hosts复制到Slave服务器的/root/.ssh目录

1
2
3

$ scp authorized_keys root@192.168.0.165:~/.ssh
$ scp authorized_keys root@192.168.0.166:~/.ssh
$ scp authorized_keys root@192.168.0.167:~/.ssh

(5) 完成，ssh Slave1、ssh Slave2、ssh Slave3就不需要输入密码了

2.5. 安装JDK

安装JDK，Hadoop2.7需要JDK7，由于我的CentOS是最小化安装，所以没有OpenJDK，直接解压下载的JDK并配置变量即可
(1)下载“jdk-7u79-linux-x64.gz”，放到/home/java目录下
(2)解压，输入命令，tar -zxvf jdk-7u79-linux-x64.gz
(3)编辑/etc/profile

1
2
3

export JAVA_HOME=/home/java/jdk1.7.0_79
export CLASSPATH=.:$JAVA_HOME/jre/lib/rt.jar:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export PATH=$PATH:$JAVA_HOME/bin

(4)使配置生效，输入命令，source /etc/profile
(5)输入命令，java -version，完成

3. 数据库安装

1) 下载安装包
http://dev.mysql.com/downloads/mysql/#downloads
推荐下载通用安装方法的tar.gz包
2) 检查库文件是否存在，如有删除。
$ rpm -qa | grep mysql
mysql-libs-5.1.52-1.el6_0.1.x86_64
$ rpm -e mysql-libs-5.1.52.x86_64 –nodeps
3) 检查mysql组和用户是否存在，如无创建。

$ cat /etc/group | grep mysql
mysql:x:490:
$ cat /etc/passwd | grep mysql
mysql:x:496:490::/home/mysql:/bin/bash

以上为默认存在的情况，如无，执行添加命令：

1 2	$groupadd mysql $useradd -r -g mysql mysql

//useradd -r参数表示mysql用户是系统用户，不可用于登录系统。
4) 解压TAR包，更改所属的组和用户

$ cd /usr/local/
$ tar -xvfz mysql-5.6.37-linux-glibc2.5-x86_64.tar.gz
$ chown -R mysql mysql-5.6.37-linux-glibc2.5-x86_64/
$ chgrp -R mysql mysql-5.6.37-linux-glibc2.5-x86_64/

5) 安装和初始化数据库

1	$ bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

6) 改写配置文件

$ cp -a ./support-files/my-default.cnf /etc/my.cnf
$ cp -a ./support-files/mysql.server  /etc/init.d/mysqld
$ cd etc/
$ vi my.cnf

修改my.cnf的内容如下
[mysql]
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock
[mysqld]
skip-name-resolve
port=3306
socket=/var/lib/mysql/mysql.sock
basedir=/usr/local/mysql # 设置mysql的安装目录
datadir=/usr/local/mysql/data # 设置mysql数据库的数据的存放目录
max_connections=200 # 允许最大连接数
character-set-server=utf8
default-storage-engine=INNODB # 创建新表时将使用的默认存储引擎
lower_case_table_names=1
max_allowed_packet=16M

7) 启动数据库服务并设置服务开机自动启动

1
2
3

$ ./mysqld_safe --user=mysql &
$ /etc/init.d/mysqld restart 
$ chkconfig --level 35 mysqld on //设置开机启动

8) 初始化密码

1	$ cat /root/.mysql_secret

# Password set for user 'root@localhost' at 2016-06-01 15:23:25
,xxxxxR5H9
$ mysql -uroot –p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SET PASSWORD = PASSWORD(‘123456’);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
9) 添加远程访问权限
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host = ‘%’ where user = ‘root’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

4. Ambari安装

由于在线安装容易受到网络不稳定等方面影响，因此采用平台选用离线安装的方式。

4.1. 搭建Yum源服务器

1) 安装httpd服务
检查是否已经安装apache http服务。

1	$ which httpd

如果没有出现目录信息，则说明没有安装。通过下面的语句进行安装。

1	$ sudo yum install httpd

安装成功之后，apache工作目录默认在/var/www/html。检查如没有此目录，需要手动创建相同目录结构。检查端口是否占用，http服务使用80端口

1	$ netstat -nltp \| grep 80

如果有占用情况，安装完毕之后需要修改apache http服务的端口号：

1	$ sudo vi /etc/httpd/conf/httpd.conf

修改监听端口，Listen 80为其他端口。
为了加快安装速度和防止超时错误，建议为HDP配置本地源，请在事先在网上下载HDP、HDP-UTILS和Ambari，本例中几个组件版本为HDP-2.6.0，HDP-UTILS-1.1.0.21和Ambari-2.5.1.0。此处把slave3服务器做为源服务器。以下操作在slave3机器运行。
2) 下载压缩包
首先下载包含必要软件的压缩包（tarball）到本地，在https://docs.hortonworks.com/HDPDocuments/Ambari-2.5.1.0/bk_ambari-installation/content/ambari_repositories.html中，查看需要下载的tarball。下表为本平台所用的内容：
服务内容 Tarball源地址
Ambari http://public-repo-1.hortonworks.com/ambari/centos7/2.x/updates/2.5.1.0/ambari-2.5.1.0-centos7.tar.gz
HDP http://public-repo-1.hortonworks.com/HDP/centos7/2.x/updates/2.6.1.0/HDP-2.6.1.0-centos7-rpm.tar.gz
HDP-UTILS http://public-repo-1.hortonworks.com/HDP-UTILS-1.1.0.21/repos/centos7/HDP-UTILS-1.1.0.21-centos7.tar.gz
建立本地仓库，这里使用 httpd 来建立，安装启动httpd省略。解压三个包到 /var/www/html 目录下。

1
2
3

$ tar -zxvf ambari-2.5.1-centos7.tar.gz -C /var/www/html/
$ tar -zxvf HDP-2.6.1.0-centos7-rpm.tar.gz -C /var/www/html/
$ tar -zxvf HDP-UTILS-1.1.0.21-centos7.tar.gz -C /var/www/html/

3) 修改Repo
修改 ambari.repo，使之与 yum源机器匹配。

$ cd /etc/yum.repos.d
$vi ambary.repo
#VERSION_NUMBER=2.5.1.0-159
[Updates-ambari-2.5.1.0]
name=ambari-2.5.1.0- Updates
baseurl=http://192.168.1.3/data/ambari/centos7/
gpgcheck=1
gpgkey=http://192.168.1.3/data/ambari/centos7/RPM-GPG-KEY/RPM-GPG-KEY
-Jenkinsenabled=1
priority=1
修改 hdp.repo，使之与 yum源机器匹配。

$vi ambary.repo
[HDP-2.6]
name=HDP-2.6
baseurl=http://192.168.1.3/data/HDP/centos7/
path=/
enabled=1
gpgcheck=0

修改 HDP-UTILS.repo，使之与 yum源机器匹配。

$vi ambary.repo
[HDP-UTILS-1.1.0.21]
name=HDP-UTILS-1.1.0.21
baseurl=http://192.168.1.3/data/HDP-UTILS-1.1.0.21-centos7/
path=/
enabled=1
gpgcheck=0

4.2. 安装Ambari Server

在上述内容配置完成后，安装ambari-service。出现Complete! 则可

1	$ yum install ambari-server

启动Ambari-server服务，出现提示后，根据提示填写相应的回答。

$ ambari-server setup
Using python  /usr/bin/python2.6
Setup ambari-server
Checking SELinux...
SELinux status is 'disabled'
Customize user account for ambari-server daemon [y/n] (n)? y
Enter user account for ambari-server daemon (root):
Adjusting ambari-server permissions and ownership...
Checking firewall status...
Checking JDK...
[1] Oracle JDK 1.8 + Java Cryptography Extension (JCE) Policy Files 8
[2] Oracle JDK 1.7 + Java Cryptography Extension (JCE) Policy Files 7
[3] Custom JDK
==============================================================================
Enter choice (1): 3
WARNING: JDK must be installed on all hosts and JAVA_HOME must be valid on all hosts.
WARNING: JCE Policy files are required for configuring Kerberos security. If you plan to use Kerberos,please make sure JCE Unlimited Strength Jurisdiction Policy Files are valid on all hosts.
Path to JAVA_HOME: /usr/jdk64/jdk1.7.0_67
Validating JDK on Ambari Server...done.
Completing setup...
Configuring database...
Enter advanced database configuration [y/n] (n)? y
Configuring database...
==============================================================================
Choose one of the following options:
[1] - PostgreSQL (Embedded)
[2] - Oracle
[3] - MySQL
[4] - PostgreSQL
[5] - Microsoft SQL Server (Tech Preview)
==============================================================================
Enter choice (1): 3
Hostname (localhost): 
Port (3306): 
Database name (ambari): 
Username (ambari): 
Enter Database Password (bigdata): 
Configuring ambari database...
Copying JDBC drivers to server resources...
Configuring remote database connection properties...
WARNING: Before starting Ambari Server, you must run the following DDL against the database to create the schema: /var/lib/ambari-server/resources/Ambari-DDL-MySQL-CREATE.sql
Proceed with configuring remote database connection properties [y/n] (y)? 
Extracting system views...
...ambari-admin-2.1.0.1470.jar
...
Adjusting ambari-server permissions and ownership...
Ambari Server 'setup' completed successfully.

看到以上结果后，安装成功。
上面出现如下错误

下载mysql_jdbc驱动，放到/usr/share/java/目录，并重命名为mysql-connector-java.jar,否则后面安装会报错。
下载：https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.46.tar.gz

4.3. 初始化数据库

1 2	cd /var/lib/ambari-server/resources/ mysql -uroot -p123456 ambari < Ambari-DDL-MySQL-CREATE.sql

没初始化数据库，启动服务会报如下错误

4.4. 启动服务

ambari-server start启动服务。

此时，登录192.168.1.168:8080，即可看到ambari的页面，如下：
enter description here

4.5. 安装 HDP 集群

在ambari的登录页面中输入用户名：admin，密码：admin，即可开始准备创建集群。
1) 点击 launch install wizard ,开始创建一个集群，输入集群名称：BasicPlatform。
enter description here
2) 修改下面红框的内容，选择HDP的版本为2.6，修改HDP-2.6的数据源Base URL为http://192.168.1.3/HDP/centos7/，修改HDP-UTILS的数据源Base URL为http://192.168.1.3/HDP-UTILS-1.1.0.21-centos7/。

3) 设置集群机器：输入Target Hosts如下：
备注，必须安装如下格式，不能使用hadoop2
hadoop2.zhjx
hadoop3.zhjx
hadoop4.zhjx
从master中拷贝出来id_rsa文件，通过选择文件，上传到ambari中。
enter description here
点击确认后，开始向每个机器中安装ambari-client，稍等到下面页面，即可显示安装完成。

问题1：
centos7.3/4安装ambari2.6以上版本报EOF occurred in violation of protocol (_ssl.c:579)

解决办法：

4) 选择要安装的服务，与下图中的一致。
可先按照如下组件
HDFS、Hive、HBase、Sqoop、ZooKeeper、Flume
enter description here
这里选择默认了，也可以自己调整

配置标红的组件，进行修改配置

enter description here

5) 各个服务Master配置
详见附件2
6) 服务的Slaves 和 Clients节配置:
详见附件3
7) 服务详情配置
看到如下界面后，如有红色警告提示，根据警告修改服务详情内容，注意把Ooize等服务中使用的数据库改为MySQL数据库。
enter description here
待确定完修改后的配置时，开始安装：

全部安装成功界面如下：

点击下一步，确定安装内容后，即可看到集群情况。

、Hadooop开启高可用（现在安装好Hadoop是不支高可用的，下面准备开启Hadoop高可用）
1、开启高可用开关
enter description here
3、设置高可用名称（如果hbase是启动的话请关闭在开启HA高可用）

5. 安装ELK

5.1. 安装Elasticsearch

安装elasticsearch
下载地址： https://www.elastic.co/downloads/elasticsearch
下载对应的版本，下载后解压到想安装的文件夹中，因为es是绿色版本所以解压后就可以使用
./bin/elasticsearch其实是一个shell脚本，最终还是启动在java的虚拟机环境中，并加入了一定参数。

# rpm -ivh elasticsearch-5.6.4.rpm
# chkconfig --add elasticsearch
# chkconfig elasticsearch on 
# vim etc/elasticsearch/elasticsearch.yml
# /etc/init.d/elasticsearch.rpmnew start

可执行文件目录：/usr/share/elasticsearch/
配置文件目录： /etc/elasticsearch/
日志文件目录：/var/log/elasticsearch/
data文件目录：/var/lib/elasticsearch/
pid文件路径：/var/run/elasticsearch/
日志文件：/var/log/elasticsearch/

启动elasticsearch
守护进程 ./bin/elasticsearch -d
前台运行 ./bin/elasticsearch
配合elasticsearch-servicewrapper 插件将脚本服务化更易管理（2.x加不再支持，弃用）。
运行日志在../log/下,每一个索引一个文件，每日一个文件，包括运行的慢日志和服务日志。
测试elasticsearch
curl -XGET http://xxx:9200/?pretty
5.2. 安装Kibana
下载页面：https://www.elastic.co/cn/downloads/kibana
安装rpm包，我的电脑是x64的所以下载64位的安装包，rpm包安装完毕后会自动在/etc/init.d/下生成执行脚本，提供给service/chkconfig，更方便我们使用。

1
2
3

# wget https://artifacts.elastic.co/downloads/kibana/kibana-5.6.4-x86_64.rpm
# rpm -ivh kibana-5.6.4-x86_64.rpm
# vim /etc/kibana/kibana.yml

5.3. 安装x-pack插件

官方下载地址：https://www.elastic.co/downloads/x-pack
官方文档地址：https://www.elastic.co/guide/en/x-pack/current/xpack-introduction.html
https://www.elastic.co/gu ide/en/x-pack/6.0/setting-up-authentication.html#set-built-in-user-passwords

elasticsearch安装x-pack插件
 如果是集群架构，则每一台新机器都需要安装插件。
 kibana安装了x-pack，elasticsearch也必须要安装。

# /usr/share/elasticsea     rch/bin/elasticsearch-plugin install x-pack
# /usr/share/kibana/bin/kibana-plugin install x-pack
2.	编辑配置文件
# vim /etc/elasticsearch/x-pack/
3.	用户管理
# /usr/share/elasticsearch/bin/x-pack/users useradd test -p 123456 -r superuser

其他安装
6.1. 安装VNC Service
由于165、166、167当做服务器的同时，也作为外网开发机使用，为了建立远程可编程环境，加入了可视化桌面也远程桌面，需要在每个机器上安装VNC Service。
1) 安装 X-Window
由于我们安装操作系统为最小化版本，因此首先需要安装 X-Window，在终端中运行下面的命令，安装会花费一点时间。

1
2
3

$ yum check-update
$ yum groupinstall "X Window System"
$ yum install gnome-classic-session gnome-terminal nautilus-open-terminal control-center liberation-mono-fonts

待安装完成后，修改系统启动界面为图形界面。

1 2	$ unlink /etc/systemd/system/default.target $ ln -sf /lib/systemd/system/graphical.target /etc/systemd/system/ default.target

重启电脑，重启完成后即可进入图形化界面。
2) 安装 VNC 服务器

1	$ yum install tigervnc-server -y

安装完成后后，我们需要在 /etc/systemd/system/ 目录里创建一个配置文件。我们可以将 /lib/systemd/sytem/vncserver@.service 拷贝一份配置文件范例过来。

1	$cp /lib/systemd/system/vncserver@.service /etc/systemd/system/vncserver @:1.service

打开 /etc/systemd/system/vncserver@:1.service ，找到下面这几行，用自己的用户名替换掉。

1 2	ExecStart=/sbin/runuser -l hadoop -c "/usr/bin/vncserver %i" PIDFile=/home/hadoop/.vnc/%H%i.pid

如果是 root 用户则替换内容如下：

1 2	ExecStart=/sbin/runuser -l root -c "/usr/bin/vncserver %i" PIDFile=/root/.vnc/%H%i.pid

修改完成后，按Esc退出编辑模式，!wq保存，下面重启 systemd。

1	$ systemctl daemon-reload

最后还要设置一下用户的 VNC 密码。要设置某个用户的密码，必须要有能通过 sudo 切换到用户的权限。这里我们用的root用户，直接输入一下代码即可。

1	$ sudo vncpasswd

3) 开启服务
用下面的命令（永久地）开启服务：

1	$ sudo systemctl enable vncserver@:1.service

启动服务。

1	$ sudo systemctl start vncserver@:1.service

4) 用 VNC 客户端连接服务器
要使用 VNC 连接服务器，我们还需要一个在本地计算机上安装的仅供连接远程计算机使用的 VNC 客户端。
可以用像 Tightvnc viewer 和 Realvnc viewer 的客户端来连接到服务器。此处用RealVNC Viewer连接，配置如下：

要用更多的用户连接，需要创建配置文件和端口，请回到第2步，添加一个新的用户和端口。你需要创建 vncserver@:2.service 并替换配置文件里的用户名和之后步骤里相应的文件名、端口号。请确保你登录 VNC 服务器用的是你之前配置 VNC 密码的时候使用的那个用户名。
VNC 服务本身使用的是5900端口。鉴于有不同的用户使用 VNC ，每个人的连接都会获得不同的端口。配置文件名里面的数字告诉 VNC 服务器把服务运行在5900的子端口上。在我们这个例子里，第一个 VNC 服务会运行在5901（5900 + 1）端口上，之后的依次增加，运行在5900 + x 号端口上。其中 x 是指之后用户的配置文件名 vncserver@:x.service 里面的 x 。
在建立连接之前，我们需要知道服务器的 IP 地址和端口。IP 地址是一台计算机在网络中的独特的识别号码。我的服务器的 IP 地址是96.126.120.92，VNC 用户端口是1。

附件1：账户密码表
表 2 系统用户表
Hostname 角色登录名/密码备注
Master root root/123456
Slave1 root root/123456 系统管理员
Slave2 root root/123456 系统管理员
Slave3 root root/123456 系统管理员
注*：大小写敏感

表 3 数据库用户表
数据库角色登录名/密码作用域备注
MySQL DBA root/ localhost
MySQL DBA zhjx/1234 %
注*：大小写敏感

表 4 服务用户表
服务名称角色登录名/密码作用域备注
Ambari superuser admin/admin
HUE DBA root/123456 %
注：大小写敏感

jira/confluence安装

发表于 2021-04-10 | 更新于: 2022-06-05 | 分类于 Linux

字数统计: 2.5k | 阅读时长 ≈ 10

1 运行环境

Centos 7.9
jira 8.13.4
confluence 7.4.5
postgres 10.16

2 说明

本文中的安装方案采用jira+confluence认证方式，先安装jira，再安装confluence。

3 数据库安装

Postgresql yum安装方式，具体安装过程，参考官网教程。
创建 jira confluence 2个数据库，设置数据库远程访问和本地访问密码验证，设置postgres 用户密码，这里密码为123456

4 jira安装

4.1 官网下周jira linux安装包

atlassian-jira-software-8.13.4-x64.bin

4.2 安装

4.2.1 赋权并执行安装

[root@jira soft]# chmod +x atlassian-jira-software-8.13.4-x64.bin 
[root@jira soft]# . /atlassian-jira-software-8.13.4-x64.bin

enter description here

• 安装完成后jira自动启动

4.2.2 开放8080端口

enter description here

4.2.3 执行破解

破解文件网上自行百度搜索下载，整理使用的破解文件版本：atlassian-extras-3.2.jar
• 将默认的atlassian-extras-3.2.jar移除到其他目录，将破解好的atlassian-extras-3.2.jar放入进去
放置目录/opt/atlassian/jira/atlassian-jira/WEB-INF/lib
重启jira服务
/etc/init.d/jira stop/start

4.2.4 访问页面

• JIRA默认端口8080
enter description here

• 这里需要等待程序进行数据初始化，需要一段时间
• 总共创建了264张表
enter description here

• 因为我们没有正式的license，所以需要我们在jira官网注册一个账号，然后利用这个账号申请一个可以试用30天的license，点击生成jira许可证。如下
enter description here

• 自动跳转到刚才填写KEY的界面，会自动将许可证填入进去，点击下一步
enter description here

4.2.5 检查破解是否生效

• 破解jira，其实我们已经破解了在前面复制atlassian-extras-3.1.2.jar到/opt/atlassian/jira/atlassian-jira/WEB-INF/lib/目录下时，再次启动jira时就已经破解了
• 我们现在登陆到jira中查看授权信息，如下
enter description here

• 通过上文章来源(Source)：https://www.dqzboy.com图，我们可以很明显的看到jira我们可以使用到2033年，到此有关jira的安装、破解就已经全部结束。

4.3 插件安装

旧系统插件在新系统进行安装破节后才能做数据迁移。

4.3.1 在线安装旧系统插件

直接在线市场安装

4.3.2 插件破解

jira插件授权更新：
将atlassian-universal-plugin-manager-plugin-2.22.4.jar替换掉/opt/atlassian/jira/atlassian-jira/WEB-INF/atlassian-bundled-plugins/下对应jar包，重启jira服务即可。

这里使用破解文件版本 atlassian-universal-plugin-manager-plugin-4.0.2.jar
文件直接百度搜索下载。
备份默认 atlassian-universal-plugin-manager-plugin-XXX.jar 文件，替换破解文件，重启jira服务。

点击使用插件——获取许可证——登录官网申请临时key——拷贝key激活，默认就破解了，可以查看插件破解信息。
enter description here

4.4 数据备份恢复-xml方式

4.4.1 备份数据

管理员登录jira, 管理——系统——导入导出——备份系统，导出jira数据。
enter description here
拷贝 /var/atlassian/application-data/jira/data/路径下的attachments和avatars目录，到新服务器相应目录，赋予目录及下级 jira用户权限

1	chown jira:jira -R attachments avatars

attachments 项目数据
avatars 头像等数据

4.4.2 恢复数据

管理员登录jira, 管理——系统——导入导出——恢复系统
enter description here
拷贝备份的zip文件到/var/atlassian/application-data/jira/import 下，输入文件名恢复系统。
拷贝attachments avatars 目录到/var/attassian/application-data/jira/data/下，并服务jira用户和组权限，重启jira服务。

4.4.3 重建索引

enter description here

恢复完成。

4.5 出现问题

4.5.1 问题1

头像不显示
原因：avatars目录数据没拷贝或放置位置有问题，权限有问题。
拷贝到 application-data/jira/data/ 下。

4.5.2 问题2

图片不显示
解决办法：拷贝 application-data/jira/logos 数据到新系统相应路径。

4.5.3 问题3

问题：用户登录后切换页面会要求重新登录认证
原因：未找到
解决办法：登录时候勾选记住登录，可解决。下次登录不会再出现问题。
enter description here

5 conflucese安装

参考https://www.dqzboy.com/atlassian-confluence%e5%ae%89%e8%a3%85%e5%92%8c%e7%a0%b4%e8%a7%a3

5.1 下载

官网下载安装包

5.2 安装

5.2.1 下载和安装程序

[root@wiki-test soft]# wget https://product-downloads.atlassian.com/software/confluence/downloads/atlassian-confluence-7.2.0-x64.bin
	[root@wiki-test soft]# chmod +x atlassian-confluence-7.2.0-x64.bin
	[root@wiki-test soft]# ./atlassian-confluence-7.2.0-x64.bin
	This will install Confluence 7.2.0 on your computer.
	OK [o, Enter], Cancel [c]
	o	   # 输入o或者直接回车
	Click Next to continue, or Cancel to exit Setup.
	 
	Choose the appropriate installation or upgrade option.
	Please choose one of the following:
	Express Install (uses default settings) [1], 
	Custom Install (recommended for advanced users) [2, Enter], 
	Upgrade an existing Confluence installation [3]
	1	   # 输入1
	 
	See where Confluence will be installed and the settings that will be used.
	Installation Directory: /opt/atlassian/confluence 
	Home Directory: /var/atlassian/application-data/confluence 
	HTTP Port: 8090 
	RMI Port: 8000 
	Install as service: Yes 
	Install [i, Enter], Exit [e]
	i    # 输入i或者直接回车
	 
	Extracting files ...
	                                                                           
	 
	Please wait a few moments while we configure Confluence.
	 
	Installation of Confluence 7.2.0 is complete
	Start Confluence now?
	Yes [y, Enter], No [n]
	y	    # 输入y或直接回车
	 
	Please wait a few moments while Confluence starts up.
	Launching Confluence ...
	 
	Installation of Confluence 7.2.0 is complete
	Your installation of Confluence 7.2.0 is now ready and can be accessed via
	your browser.
	Confluence 7.2.0 can be accessed at http://localhost:8090
	Finishing installation ...

# 安装完成会自动启动

5.2.2 查看端口和放行端口

[root@wiki-test soft]# ss -tnlp |grep 8090
LISTEN     0      10          :::8090                    :::*                   users:(("java",pid=18657,fd=45))
 
[root@wiki-test soft]# firewall-cmd --permanent --zone=public --add-port=8090/tcp
success
[root@wiki-test soft]# firewall-cmd --reload
success

5.2.3 访问网页

• http://ip:8090
enter description here

5.2.4 进行破解

1
2
3

#破解需要两部，一是破解文件，二是获取授权码
#注意，本地运行破解程序需要JAVA环境，直接在oraclejdk官网下载windows版本的exe程序安装即可
[root@wiki-test ~]# cd /opt/atlassian/confluence/confluence/WEB-INF/lib/

• 将该目录下的atlassian-extras-decoder-v2-3.4.1.jar拷贝到自己的电脑上并进行重命名为atlassian-extras-2.4.jar
enter description here
• 将该.jar文件跟破解工具放在一起，然后运行破解工具

• 选择.patch!找到刚才重命名的那个文件打开

• 打开后在当前目录下可以看到atlassian-extras-2.4.jar和atlassian-extras-2.4.bak两个文件，这里atlassian-extras-2.4.jar已经是破解好的了，将atlassian-extras-2.4.jar名字改回来atlassian-extras-decoder-v2-3.4.1.jar
enter description here
• 上传到服务器上的/opt/atlassian/confluence/confluence/WEB-INF/lib/目录，覆盖原来的atlassian-extras-decoder-v2-3.4.1.jar

1	[root@wiki-test lib]# cp atlassian-extras-decoder-v2-3.4.1.jar /

enter description here

1
2
3

# 注意：覆盖文件后，一定到重启服务
[root@wiki-test lib]# /etc/init.d/confluence stop
[root@wiki-test lib]# /etc/init.d/confluence start

5.2.5 获取授权码

• 查看网页中的服务器ID，运行破解工具confluence_keygen.jar，破解复制Key到Confluence里，然后点击下一步
enter description here

5.2.6 配置数据库`

enter description here

#安装MySQL5.7驱动
	[root@wiki-test ~]# wget https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-java-5.1.48.tar.gz
	 
	#将 .jar 文件放入 /opt/atlassian/confluence/confluence/WEB-INF/lib，然后重启Confluence
	[root@wiki-test ~]# tar -xf mysql-connector-java-5.1.48.tar.gz 
	[root@wiki-test ~]# cd mysql-connector-java-5.1.48/
	[root@wiki-test mysql-connector-java-5.1.48]# cp *.jar /opt/atlassian/confluence/confluence/WEB-INF/lib
	 
	[root@wiki-test lib]# /etc/init.d/confluence stop
	[root@wiki-test lib]# /etc/init.d/confluence start

• 刷新网页，注意数据库字符集必须为UTF8
enter description here
• 注意：总共会创建117张表，会比较慢，需要等待一段时间

等待程序配置好数据库后即可完成安装了

5.3 数据备份恢复

5.3.1 备份

管理员登录conflucese——站点管理——一般配置——管理——备份与还原——导出网站
enter description here

5.3.2 恢复

拷贝备份zip包到新服务器/var/atlassian/application-data/confluence/restore 目录
管理员登录conflucese——站点管理——一般配置——管理——备份与还原——选中文件——导入
enter description here

导入完成，恢复成功。

5.4 问题

5.4.1 问题1

问题：恢复数据失败

原因：XML 备份包含导入尝试插入uniq_lwr_username user_mapping表的重复记录 = 用户 1（如上文所示）。

5.4.1.1 解决办法

参考 https://community.atlassian.com/t5/Confluence-questions/Unable-to-import-XML-space-to-Confluence-Server-exported-earlier/qaq-p/1307536
https://confluence.atlassian.com/confkb/confluence-site-xml-import-fails-with-duplicate-key-error-438993638.html
第 1 步：查找重复的用户键
运行下面的查询以获取重复用户的用户密钥：

SELECT * FROM user_mapping 
WHERE 
username IS NULL OR 
lower_username IS NULL OR 
lower_username NOT LIKE lower(username);

结果应如下：
enter description here
第 2 步：删除重复的用户
删除 user_mapping表中的重复用户

需要先删除content表中的用户记录
第 3 步：删除content表中的用户记录
根据第一步查询到的 “user_key”,查询content表中username字段进行匹配

删除记录，然后返回第二步删除user_mapping表中的相应用户记录（如果删除中提示其他表字段关联，在其他表进行user_key匹配，删除相应记录）
第 4 步：重新备份
清理历史系统中的缓存数据，管理登录后台——管理——缓存管理——缓存统计——清除缓存

重新执行数据备份恢复步骤，问题解决。

5.4.2 问题2

confluence使用jira用户认证。

5.4.2.1 jira配置

（历史系统恢复后已经配置好，我这里重新进行了配置）
管理员后台——用户管理——jira用户服务器——添加应用程序——设置认证信息——保存
enter description here

5.4.2.2 confluence配置

管理员后台——用户&安全——用户目录——下移Rmote jira Directory(可新建，这里修改原来的)——编辑——修改认证地址和认证信息——测试并保存——上移到第一位，同步——重新登录，认证成功。

6 内存优化

修改/bin/setenv.sh 文件默认配置。
参考
https://www.cnblogs.com/kevingrace/p/9413353.html

7 附件

docker-compose示例1

发表于 2021-04-10 | 更新于: 2022-06-05 | 分类于 Linux

字数统计: 1.4k | 阅读时长 ≈ 8

install.sh

#! /bin/bash
lj=$(pwd)

# usage
usage(){
   echo "##############################"
   echo -e "\033[31m 用法：$0 当前服务器IP地址 \033[0m"
   echo -e "\033[31m 示例：$0 192.168.1.10 \033[0m"
   echo "##############################"
}

# port
port(){
  if docker ps -a --no-trunc --format  "table {{.Names}}"| grep -i ${2}; then
      echo -e "\033[31m ERROR: ${2} container already exists !!! | ${2} 容器已经存在，请手动执行相关命令！！！\033[0m"
      exit
  else
      if [ "$(/usr/sbin/lsof -i :${1}|grep -v "PID" | awk '{print $2}')" != "" ]
      then
        echo -e "\033[31m  ERROR: ${1} port is already in use !!! | 错误: ${1} 端口已经被使用！！！\033[0m"
        exit
      else
        echo -e "\033[32m ${1} ready !!! | ${1} 空闲 !!! \033[0m"
      fi
  fi   
}


# 检测命令执行格式
if [ $# -ne 1 ]; then
  usage
  exit 1
else
  if [[ $1 =~ ^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$ ]]; then
    echo -e "\033[31m 服务器IP：$1 \033[0m"
  else
    usage
    exit 1
  fi
fi

# install lsof
if rpm -qa|grep lsof; then
  echo -e "\033[32m ## lsof 已经安装 \033[0m"
else
  echo -e "\033[32m ## install lsof \033[0m"
  yum localinstall -y ${lj}/docker/lsof/lsof-4.87-6.el7.x86_64.rpm
fi
 
# install docker
echo "################################################################################"
cd ${lj}
echo -e "\033[32m ### install docker \033[0m"
cd docker
sh install-docker.sh docker-18.09.5.tgz 
cp docker-compose /usr/local/bin/
cd ${lj}
 
# edit mapserv test file
echo "################################################################################"
echo -e "\033[32m ### edit mapserv main.js  file:::  ${lj}/mapdata/client/js/main.js \033[0m"
sed -i "21c url: 'http://${1}:10000/gdw/wms/demo?/'," ${lj}/data/client/js/main.js
echo "SERVER_IP=${1}" > .env
 
# Import image
echo "################################################################################"
echo -e "\033[32m ### import docker image \033[0m"
gunzip -c ${lj}/docker/postgresql.tar.gz | docker load
gunzip -c ${lj}/docker/redis.tar.gz | docker load
gunzip -c ${lj}/docker/gdwserv.tar.gz | docker load
gunzip -c ${lj}/docker/httpd.tar.gz | docker load

# 检测端口及容器占用
port 6379 crawler_redis
port 5432 crawler_postgis
port 10000 crawler_crawler
port 8199 crawler_crawler
port 2999 crawler_httpd
 
# create network zhjx
#if [ "$(docker network list | awk '{print $2}'|grep zhjx)" != "" ]; then
#  echo -e "\033[32m ## zhjx network already exists && zhjx 网络已经存在 \033[0m"
#else
#  echo -e "\033[32m ### create zhjx network \033[0m"
#  docker network create zhjx
#fi
 
# start postgresql
#echo "################################################################################"
#if docker ps -a --no-trunc --format  "table {{.Names}}"|grep crawler_postgis; then
#   echo -e "\033[31m ERROR: crawler_postgis container already exists | crawler_postgis 容器 已经存在，请手动执行相关命令！！！ \033[0m"
#   exit
#else
#   if [ "$(/usr/sbin/lsof -i :5432|grep -v "PID" | awk '{print $2}')" != "" ]
#   then
#      echo -e "\033[31m ERROR: 5432 port is already in use !!! | 错误：5432 端口已经被使用！！！\033[0m"
#      exit
#   else
#      echo "start run crawler"
#      docker run --network zhjx --name crawler_postgis --restart always -d -p 5432:5432 -e POSTGRES_PASSWORD=1qazxsw2 -v ${lj}/pgdata:/var/lib/postgresql/data mdillon/postgis:10
#   fi
#fi
# start redis
#echo "################################################################################"
#if docker ps -a --no-trunc --format  "table {{.Names}}"| grep -i crawler_redis; then
#   echo -e "\033[31m ERROR: crawler_redis container already exists !!! | crawler_redis 容器已经存在，请手动执行相关命令！！！\033[0m"
#   exit
#else
#   if [ "$(/usr/sbin/lsof -i :6379|grep -v "PID" | awk '{print $2}')" != "" ]
#   then
#      echo -e "\033[31m  ERROR: 6379 port is already in use !!! | 错误：6379 端口已经被使用！！！\033[0m"
#      exit
#   else
#      echo -e "\033[32m start run redis \033[0m"
#      docker run --network zhjx --name crawler_redis --restart always -d -p 6379:6379 -v ${lj}/redis:/data redis:5.0.9
#   fi
#fi
## start crawler
#echo "################################################################################"
#if docker ps -a --no-trunc --format  "table {{.Names}}"|grep crawler_mapser; then
#   echo -e "\033[31m ERROR: crawler_mapser container already exists | crawler_mapser 容器 已经存在，请手动执行相关命令！！！ \033[0m"
#   exit
#else
#   if [ "$(/usr/sbin/lsof -i :8080|grep -v "PID" | awk '{print $2}')" != "" ]
#   then
#      echo -e "\033[31m ERROR: 8080 port is already in use !!! | 错误：8080 端口已经被使用！！！\033[0m"
#      exit
#   else
#      if [ "$(/usr/sbin/lsof -i :10000|grep -v "PID" | awk '{print $2}')" != "" ]
#      then
#        echo -e "\033[31m ERROR: 10000 port is already in use !!! | 错误：10000 端口已经被使用！！！\033[0m"
#        exit
#      else
#        echo -e "\033[32m start run crawler \033[0m"
##        docker run -u root --name crawler_mapser --network zhjx --restart always -d -p 10000:80 -p 8080:8080 -e LANG="en_US.utf8" -e JAVA_OPTS="-server -Xms2048m -Xmx2048m -Xss512k -Dfile.encoding="UTF8" -Dfile.encoding="UTF8"" -e REDIS_IP=${1} -e REDIS_PORT=6379 -e REDIS_TOKENS=5 -e BASEURL=http://${1}:10000/gdw -dti -v ${lj}/services/:/tomcat/webapps -v ${lj}/mapdata/:/usr/local/gdw/data gdwserv:v0.1_tomcat8
#         docker run -u root --name crawler_mapser --network zhjx -d -p 10000:80 -p 8080:8080 -e LANG="en_US.utf8" -e JAVA_OPTS="-server -Xms2048m -Xmx4096m -Xss512k -Dfile.encoding="UTF8"" -e REDIS_IP=${1} -e REDIS_PORT=6379 -e REDIS_TOKENS=5 -e BASEURL=http://${1}:10000/gdw -dti -v ${lj}/service/:/tomcat/webapps -v ${lj}/data/:/usr/local/gdw/data/ -v ${lj}/service_data/root/crawler/:/root/crawler/ -v ${lj}/service_data/datafile/crawler:/datafile/crawler -v /service_data/data/ftp/:/data/ftp/ -v ${lj}/service_data/icons/crawler://icons/crawler -v ${lj}/service_data/vsicurl/:/vsicurl/ -v ${lj}/service_data/vsizip:/vsizip -v ${lj}/service_data/vsizip:/vsizip -v ${lj}/service_data/home/data/insight/:/home/data/insight/ gdwserv:v0.1_tomcat8
#      fi
#   fi
#fi

# start crawler
docker-compose up -d
sleep 2
docker ps
echo "----------------------------------------------------------------------------------------"
echo -e "\033[32m ALL Services install finish, Please view from the browser. http://${1}:8080/crawler \033[0m"
echo -e "\033[32m 所有服务安装完成，请通过浏览器访问.http://${1}:8080/crawler \033[0m"
echo "----------------------------------------------------------------------------------------"

docker-compose.yml

version: "3"
services:
   crawler:
      image: gdwserv:v0.1_tomcat8
      restart: always
      depends_on:
         - postgis
         - redis
      ports:
         - "8199:8080"
         - "10000:80"
      volumes:
         - "./service/:/tomcat/webapps"
         - "./data/:/usr/local/gdw/data"
         - "./service_data/root/crawler/:/root/crawler/"
         - "./service_data/datafile/crawler:/datafile/crawler"
         - "./service_data/data/ftp/:/data/ftp/"
         - "./service_data/vsicurl/:/vsicurl"
         - "./service_data/vsizip:/vsizip"
         - "./service_data/home/data/insight:/home/data/insight"
      environment:
         - LANG="en_US.utf8"
#         - JAVA_OPTS="-server -Xms2048m -Xmx4096m -Xss512k -Dfile.encoding="UTF8""
# 采用当前目录,默认 .env 文件内的变量
         - REDIS_IP=${SERVER_IP}
         - REDIS_PORT=6379
         - REDIS_TOKENS=5
         - BASEURL=http://${SERVER_IP}:10000/gdw
   postgis:
      image: mdillon/postgis:10
      restart: always
      ports:
         - "5432:5432"
# 采用指定文件内的变量
      env_file:
         - .post_pass
      volumes:
         - "./pgdata:/var/lib/postgresql/data"
   redis:
      image: redis:6
      restart: always
      ports:
         - "6379:6379"
      volumes:
         - "./redis:/data"
   httpd:
      image: httpd:2.4.46-alpine
      restart: always
      ports:
         - "2999:80"
      volumes:
         - "./service_data/home/data/insight:/usr/local/apache2/htdocs"

uninstall.sh

#! /bin/bash
lj=$(pwd)
echo "################################################################################"
# del services
echo -e "\033[32m ### del services \033[0m"
docker-compose down
 
# del images
echo -e "\033[32m ### del images \033[0m"
docker rmi gdwserv:v0.1_tomcat8
docker rmi mdillon/postgis:10
docker rmi redis:6
docker rmi httpd:2.4.46-alpine
 
# del network zhjx
#echo -e "\033[32m ### del docker-network \033[0m"
#docker network rm zhjx
 
# del docker
echo -e "\033[32m ### del docker service \033[0m"
systemctl stop docker.service
SYSTEMDDIR=/usr/lib/systemd/system
SERVICEFILE=docker.service
DOCKERDIR=/usr/bin/
COMPOSEDIR=/usr/local/bin/
rm -rf ${DOCKERDIR}/docker*
rm -rf ${SYSTEMDDIR}/${SERVICEFILE}
rm -rf ${COMPOSEDIR}/docker-compose
systemctl daemon-reload
 
echo -e "\033[32m ALL Services delete \033[0m"
echo "----------------------------------------------------------------------------------------"

1	1	1
1	1	1
1	1	1
1	1	1

postgresql+pgpool双机热备-高可用

发表于 2020-07-01 | 更新于: 2022-06-05 | 分类于 Linux

字数统计: 9.6k | 阅读时长 ≈ 53

一、主机

主机	IP	服务
master	192.168.0.56	postgresql、postgis、pgpool
slave	192.168.0.58	postgresql、postgis、pgpool

二、安装postgresql

过程忽略

三、配置白名单与流复制

1. master配置

PostgreSQL流复制默认是异步的。在主服务器上提交事务和从服务器上变化可见之间有一个小的延迟，这个延迟远小于基于文件日志传送，通常1秒能完成。如果主服务器突然崩溃，可能会有少量数据丢失。
同步复制必须等主服务器和从服务器都写完WAL后才能提交事务。这样在一定程度上会增加事务的响应时间。
配置同步复制仅需要一个额外的配置步骤： synchronous_standby_names 必须设置为一个非空值。synchronous_commit 也必须设置为 on。
这里部署的是异步的流复制

1.1 配置白名单

vim /pgdata/pg_hba.conf

# 在配置文件末尾添加
pg_hba.conf
host    all             all             0.0.0.0/0               md5
host    replication     replica         192.168.0.58/32         md5

1.2 修改配置文件

vim /pgdata/postgresql.conf

# postgresql.conf
data_directory = '/pgdata'                # 自定义data目录
archive_mode = on                         # 允许归档
archive_command = 'cp %p /pg_archive/%f'
wal_level = hot_standby                   # 选择热备
max_wal_senders = 16                      # 最多多少各流复制链接
wal_keep_segments = 256                   # 流复制保留最多的xlog数
wal_sender_timeout = 60s                  # 流复制主机发送数据超时时间
max_connections = 1000                    # 从库的max_connections必须大于主库的

full_page_writes = on                     # 使用pg_rewind命令同步数据库要用
wal_log_hints = on                        # 使用pg_rewind命令同步数据库要用
hot_standby = on                          # 使用pg_rewind命令同步数据库要用

listen_addresses = '*'                    # 修改监听

1.3 创建data目录，赋权并修改启动文件

1
2
3

mkdir -p /pgdata && chown postgres:postgres /pgdata && chmod 700 /pgdata
mkdir -p /pg_archive && chown postgres:postgres /pg_archive && chmod 700 pg_archive
初始化数据库：su - postgres -c "/usr/pgsql-10/bin/initdb -D /pgdata

1.4 修改启动文件

1	vim /usr/lib/systemd/system/postgresql-10.service

1.5 启动postgresql并创建replica用户，密码replica

systemctl start postgresql-10.service
su postgres
psql
CREATE ROLE replica login replication encrypted password 'replica';

1.6 在slave节点测试链接主节点

slave节点执行：psql -h 192.168.0.56 -U postgres (首先设置好postgres用户密码后测试)

2 slave配置

2.1 配置白名单

vim /pgdata/pg_hba.conf

# 在配置文件末尾添加
pg_hba.conf
host    all             all             0.0.0.0/0               md5
host    replication     replica         192.168.0.56/32         md5

2.2 创建data目录

1 2	mkdir -p /pgdata && chown postgres:postgres /pgdata && chmod 700 /pgdata mkdir -p /pg_archive && chown postgres:postgres /pg_archive && chmod 700 pg_archive

2.3 备份数据

# 切换用户
su postgres
# 备份主库的数据库到从库/pgdata
pg_basebackup -h 192.168.0.56 -U replica -D /pgdata -X stream –P

2.4 修改启动文件

1	vim /usr/lib/systemd/system/postgresql-10.service

2.5 配置recovery.conf

su postgres
cp /usr/pgsql-10/share/recovery.conf.sample /pgdata/recovery.conf
vim /pgdata/recovery.conf

# recovery.conf
standby_mode = on        # 该节点为从
primary_conninfo = 'host=192.168.123.180 port=5432 user=replica password=replica'    #主服务器的ip、user
recovery_target_timeline = 'latest'
trigger_file = '/tmp/trigger_file0'

2.6 配置postgresql.conf

vim /pgdata/postgresql.conf

# postgresql.conf
data_directory = '/pgdata'                # 自定义data目录
max_connections = 10000                    # 从库的max_connections必须大于主库的
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on

full_page_writes = on                     # 使用pg_rewind命令同步数据库要用
wal_log_hints = on                        # 使用pg_rewind命令同步数据库要用
hot_standby = on                          # 使用pg_rewind命令同步数据库要用

listen_addresses = '*'

2.7 启动postgresql

1	systemctl start postgresql-10.services

3 验证流复制

3.1 在master上登陆psql

查看状态:

1	select client_addr,sync_state from pg_stat_replication;

3.2 创建test库

1	Create database test;

3.3 slave上登陆psql

查看库

发现已同步。

4 主从切换及恢复

4.1 模拟主故障

关闭postgresql

4.2 升级从库为主库

[root@test3 log]# su postgres
bash-4.2$ /usr/pgsql-10/bin/pg_ctl promote -D /pgdata
waiting for server to promote.... 完成
server promoted
bash-4.2$

升级主库后，recovery.conf 会变成recovery.done，配置文件失效

4.3 通过pg_rewind命令同步数据，主库变成从库

[root@test2 log]# su postgres
# 同步主库（原备库）数据   ！！！ 同步前，本地postgresl要处于关闭状态
bash-4.2$ /usr/pgsql-10/bin/pg_rewind --target-pgdata=/pgdata --source-server='host=192.168.0.58  port=5432 user=postgres password=zhjx123 dbname=postgres' -P               
 
.....
received chunk for file "pg_wal/00000003000000000000000E", offset 10000000, size 1000000
received chunk for file "pg_wal/00000003000000000000000E", offset 11000000, size 1000000
received chunk for file "pg_wal/00000003000000000000000E", offset 12000000, size 1000000
received chunk for file "pg_wal/00000003000000000000000E", offset 13000000, size 1000000
received chunk for file "pg_wal/00000003000000000000000E", offset 14000000, size 1000000
received chunk for file "pg_wal/00000003000000000000000E", offset 15000000, size 1000000
received chunk for file "pg_wal/00000003000000000000000E", offset 16000000, size 777216
received chunk for file "pg_xact/0000", offset 0, size 8192
received chunk for file "postgresql.auto.conf", offset 0, size 88
received chunk for file "postgresql.conf", offset 0, size 23826
received chunk for file "recovery.done", offset 0, size 5986
已复制100084/100084 kB (100%)
正在创建备份标签并且更新控制文件
正在同步目标数据目录
同步数据到磁盘...成功
完成！
bash-4.2$ su root
[root@test2 pgdata]# ls
backup_label.old  log           pg_ident.conf  pg_replslot   pg_stat_tmp  PG_VERSION            postgresql.conf
base              pg_commit_ts  pg_logical     pg_serial     pg_subtrans  pg_wal                postmaster.opts
current_logfiles  pg_dynshmem   pg_multixact   pg_snapshots  pg_tblspc    pg_xact               postmaster.pid
global            pg_hba.conf   pg_notify      pg_stat       pg_twophase  postgresql.auto.conf  recovery.done

修改修改recovery.done

1	[root@test2 pgdata]# mv recovery.done recovery.conf

修改主库连接地址

启动本地数据库

1	[root@test2 pgdata]# systemctl start postgresql-10.serivce

4.4 检测集群状态

在主库（原从库）执行查询命令

查看状态:
select client_addr,sync_state from pg_stat_replication;

postgres=# select client_addr,sync_state from pg_stat_replication;
client_addr | sync_state
--------------+------------
192.168.0.56| async
(1 row)

postgres=#

四、配置pgpool (经测试不稳定)

参考：https://www.jianshu.com/p/ef183d0a9213
pgpool-II是PostgreSQL服务器之间一种有效的中间件和PostgreSQL数据库客户端。它提供了以下功能。
连接池
pgpool-II保存到PostgreSQL服务器的连接，当一个相同新连接(如用户名、数据库、协议版本)进来时，重用他们。它减少了连接开销，提高了系统的整体吞吐量。
复制
pgpool-II可以管理多个PostgreSQL服务器。使用复制功能可以使2个或更多的物理磁盘上创建一个实时备份，这样服务不会因服务器的磁盘故障而中断。
负载平衡
如果数据库是复制的，在任何服务器上执行一个SELECT查询会返回相同的结果。pgpool-II复制特性的优势在于减少每个PostgreSQL服务器上的负载，因为它可以使用分布在多个服务器之间进行SELECT查询，从而提高系统的整体吞吐量。最好是查询和PostgreSQL服务器数量成一定比例，多用户同时执行多查询达到负载均衡最好的效果。
限制连接数
PostgreSQL的最大并发连接数有一定限制的，当超过限制的连接数后，连接会被拒绝。然而，设置增加最大连接数又会增加资源消耗，影响系统性能。pgpool-II也有最大连接数限制，但超过的连接进来时是进行立即排队，而不是返回一个错误。
pgpool-II交互PostgreSQL的后端和前端协议时，起着继电器的作用。因此，数据库应用程序(前端)认为pgpool-II是真实的PostgreSQL服务器，服务器(后端)认为pgpool-II是它的客户端之一。因为pgpool-II在服务器和客户端是透明的，所以pgpool-II可以使用现有的数据库应用程序而做到几乎不修改它们。

1 各节点免密登录

关闭系统selinux防火墙，否则失败，同事修改SSH免密登录，使配置支持。
安装之前先配置密钥使master和slave这两台虚拟机的postgres用户能免密连接,

先修改postgres的密码，在root用户下

1 2	passwd postgres # 新密码 123456

# Master到slave1的免密码登陆：
# 在master上切换至postgres用户，生成密钥
su postgres ssh-keygen -t rsa
然后全输入回车
 
# 切换到postgres用户：
su postgres ssh-copy-id -i /var/lib/pgsql/.ssh/id_rsa 192.168.0.58
然后ssh 192.168.0.58 成功，实现master到slave的免密码登陆
-----------------------------------------------------------------------------
# Slave1到master的免密码登陆：
流程同上

2 安装pgpool（主从节点都安装）

1	yum install pgpool-II-10.x86_64

3 配置（主从节点均配置）

3.1 配置pool_hba.conf

pool_hba.conf是对登录用户进行验证的，要和pg的pg_hba.conf保持一致，要么都是trust，要么都是md5验证方式，这里采用了md5验证方式如下设置：

[postgres@master ~]$ cd /opt/pgpool/etc
[postgres@etc~]$ cp pool_hba.conf.sample pool_hba.conf
[postgres@etc~]$ vim pool_hba.conf
#编辑内容如下
# "local" is for Unix domain socket connections only
local   all         all                           md5
# IPv4 local connections:
host    all         all         0.0.0.0/0            md5
host    all         all         0/0                md5

3.2 配置pcp.conf

pcp.conf配置用于pgpool自己登陆管理使用的，一些操作pgpool的工具会要求提供密码等，配置如下：

[postgres@master ~]$ cd /etc/pgpool-II-10
[postgres@etc~]$ cp pcp.conf.sample pcp.conf
# 使用pg_md5生成配置的用户名密码，nariadmin 是pgpool的用户密码，随意自定义
[postgres@etc~]$ pg_md5 nariadmin   
6b07583ba8af8e03043a1163147faf6a
# pcp.conf是pgpool管理器自己的用户名和密码，用于管理集群。
[postgres@etc~]$ vim pcp.conf
# 编辑内容如下。postgres也是定义的用户，自定义
postgres:6b07583ba8af8e03043a1163147faf6a
# 保存退出！
# 在pgpool中添加pg数据库的用户名和密码
[postgres@etc~]$ pg_md5 -p -m -u postgres pool_passwd
# 数据库登录用户是postgres,这里输入登录密码，不能出错
# 输入密码后，在/etc/pgpool-II-10 目录下会生成一个pool_passwd文件

3.3 配置系统命令权限

配置ifconfig, arping 执行权限，执行failover_stream.sh需要用到，可以让其他普通用户执行。

1 2	[root@master ~]# chmod u+s /sbin/ifconfig [root@master ~]# chmod u+s /usr/sbin

3.4 配置master上的pgpool.conf

1
2
3

[postgres@master ~]$ cd /opt/pgpool/etc
[postgres@etc~]$ cp pgpool.conf.sample pgpool.conf
[postgres@etc~]$ vim pgpool.conf

编辑内容如下

# ----------------------------
# pgPool-II configuration file
# ----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# Whitespace may be used.  Comments are introduced with "#" anywhere on a line.
# The complete list of parameter names and allowed values can be found in the
# pgPool-II documentation.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pgpool reload".  Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#


#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------

# - pgpool Connection Settings -
# 修改监听
listen_addresses = '*'
                                   # Host name or IP address to listen on:
                                   # '*' for all, '' for no TCP/IP connections
                                   # (change requires restart)
port = 9999
                                   # Port number
                                   # (change requires restart)
socket_dir = '/tmp'
                                   # Unix domain socket path
                                   # The Debian package defaults to
                                   # /var/run/postgresql
                                   # (change requires restart)
listen_backlog_multiplier = 2
                                   # Set the backlog parameter of listen(2) to
                                   # num_init_children * listen_backlog_multiplier.
                                   # (change requires restart)
serialize_accept = off
                                   # whether to serialize accept() call to avoid thundering herd problem
                                   # (change requires restart)
reserved_connections = 0
                                   # Number of reserved connections.
                                   # Pgpool-II does not accept connections if over
                                   # num_init_chidlren - reserved_connections.

# - pgpool Communication Manager Connection Settings -
# 修改监听
pcp_listen_addresses = '*'
                                   # Host name or IP address for pcp process to listen on:
                                   # '*' for all, '' for no TCP/IP connections
                                   # (change requires restart)
pcp_port = 9898
                                   # Port number for pcp
                                   # (change requires restart)
pcp_socket_dir = '/tmp'
                                   # Unix domain socket path for pcp
                                   # The Debian package defaults to
                                   # /var/run/postgresql
                                   # (change requires restart)

# - Backend Connection Settings -
# pgpool集群后端主机信息
# 修改为本端主机名，要在hosts中加入解析
backend_hostname0 = 'test2'
                                   # Host name or IP address to connect to for backend 0
backend_port0 = 5432
                                   # Port number for backend 0
backend_weight0 = 1
                                   # Weight for backend 0 (only in load balancing mode)
# 修改postgresql的数据目录
backend_data_directory0 = '/pgdata'
                                   # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
                                   # Controls various backend behavior
                                   # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
                                   # or ALWAYS_MASTER
# 添加内容，
backend_hostname1 = 'test3'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/pgdata'
backend_flag1 = 'ALLOW_TO_FAILOVER'
# 添加结束

backend_application_name0 = 'server0'
                                   # walsender's application_name, used for "show pool_nodes" command
#backend_hostname1 = 'host2'
#backend_port1 = 5433
#backend_weight1 = 1
#backend_data_directory1 = '/data1'
#backend_flag1 = 'ALLOW_TO_FAILOVER'
#backend_application_name1 = 'server1'

# - Authentication -
# 激活pgpool认证方式
enable_pool_hba = on
                                   # Use pool_hba.conf for client authentication
pool_passwd = 'pool_passwd'
                                   # File name of pool_passwd for md5 authentication.
                                   # "" disables pool_passwd.
                                   # (change requires restart)
authentication_timeout = 60
                                   # Delay in seconds to complete client authentication
                                   # 0 means no timeout.

allow_clear_text_frontend_auth = off
                                   # Allow Pgpool-II to use clear text password authentication
                                   # with clients, when pool_passwd does not
                                   # contain the user password


# - SSL Connections -

ssl = off
                                   # Enable SSL support
                                   # (change requires restart)
#ssl_key = './server.key'
                                   # Path to the SSL private key file
                                   # (change requires restart)
#ssl_cert = './server.cert'
                                   # Path to the SSL public certificate file
                                   # (change requires restart)
#ssl_ca_cert = ''
                                   # Path to a single PEM format file
                                   # containing CA root certificate(s)
                                   # (change requires restart)
#ssl_ca_cert_dir = ''
                                   # Directory containing CA root certificate(s)
                                   # (change requires restart)

ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
                                   # Allowed SSL ciphers
                                   # (change requires restart)
ssl_prefer_server_ciphers = off
                                   # Use server's SSL cipher preferences,
                                   # rather than the client's
                                   # (change requires restart)
ssl_ecdh_curve = 'prime256v1'
                                   # Name of the curve to use in ECDH key exchange
ssl_dh_params_file = ''
                                   # Name of the file containing Diffie-Hellman parameters used
                                   # for so-called ephemeral DH family of SSL cipher.

#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------

# - Concurrent session and pool size -

num_init_children = 32
                                   # Number of concurrent sessions allowed
                                   # (change requires restart)
max_pool = 4
                                   # Number of connection pool caches per connection
                                   # (change requires restart)

# - Life time -

child_life_time = 300
                                   # Pool exits after being idle for this many seconds
child_max_connections = 0
                                   # Pool exits after receiving that many connections
                                   # 0 means no exit
connection_life_time = 0
                                   # Connection to backend closes after being idle for this many seconds
                                   # 0 means no close
client_idle_limit = 0
                                   # Client is disconnected after being idle for that many seconds
                                   # (even inside an explicit transactions!)
                                   # 0 means no disconnection


#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------

# - Where to log -

log_destination = 'stderr'
                                   # Where to log
                                   # Valid values are combinations of stderr,
                                   # and syslog. Default to stderr.

# - What to log -

log_line_prefix = '%t: pid %p: '   # printf-style string to output at beginning of each log line.

log_connections = off
                                   # Log connections
log_hostname = off
                                   # Hostname will be shown in ps status
                                   # and in logs if connections are logged
log_statement = off
                                   # Log all statements
log_per_node_statement = off
                                   # Log all statements
                                   # with node and backend informations
log_client_messages = off
                                   # Log any client messages
log_standby_delay = 'none'
                                   # Log standby delay
                                   # Valid values are combinations of always,
                                   # if_over_threshold, none

# - Syslog specific -

syslog_facility = 'LOCAL0'
                                   # Syslog local facility. Default to LOCAL0
syslog_ident = 'pgpool'
                                   # Syslog program identification string
                                   # Default to 'pgpool'

# - Debug -

#log_error_verbosity = default          # terse, default, or verbose messages

#client_min_messages = notice           # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   log
                                        #   notice
                                        #   warning
                                        #   error

#log_min_messages = warning             # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# pid文件路径  这里不做修改
pid_file_name = '/var/run/pgpool-II-10/pgpool.pid'
                                   # PID file name
                                   # Can be specified as relative to the"
                                   # location of pgpool.conf file or
                                   # as an absolute path
                                   # (change requires restart)
# pgpool集群状态信息文件，这里保持默认
logdir = '/var/log/pgpool-II-10'
                                   # Directory of pgPool status file
                                   # (change requires restart)


#------------------------------------------------------------------------------
# CONNECTION POOLING
#------------------------------------------------------------------------------

connection_cache = on
                                   # Activate connection pools
                                   # (change requires restart)

                                   # Semicolon separated list of queries
                                   # to be issued at the end of a session
                                   # The default is for 8.3 and later
reset_query_list = 'ABORT; DISCARD ALL'
                                   # The following one is for 8.2 and before
#reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'


#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
# 复制模式保持关闭
replication_mode = off
                                   # Activate replication mode
                                   # (change requires restart)
replicate_select = off
                                   # Replicate SELECT statements
                                   # when in replication mode
                                   # replicate_select is higher priority than
                                   # load_balance_mode.

insert_lock = on
                                   # Automatically locks a dummy row or a table
                                   # with INSERT statements to keep SERIAL data
                                   # consistency
                                   # Without SERIAL, no lock will be issued
lobj_lock_table = ''
                                   # When rewriting lo_creat command in
                                   # replication mode, specify table name to
                                   # lock

# - Degenerate handling -
# 
replication_stop_on_mismatch = off
                                   # On disagreement with the packet kind
                                   # sent from backend, degenerate the node
                                   # which is most likely "minority"
                                   # If off, just force to exit this session

failover_if_affected_tuples_mismatch = off
                                   # On disagreement with the number of affected
                                   # tuples in UPDATE/DELETE queries, then
                                   # degenerate the node which is most likely
                                   # "minority".
                                   # If off, just abort the transaction to
                                   # keep the consistency


#------------------------------------------------------------------------------
# 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 = 'currval,lastval,nextval,setval'
                                   # 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.
app_name_redirect_preference_list = ''
                                   # comma separated list of pairs of app name and node id.
                                   # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
                                   # valid for streaming replicaton mode only.
allow_sql_comments = off
                                   # if on, ignore SQL comments when judging if load balance or
                                   # query cache is possible.
                                   # If off, SQL comments effectively prevent the judgment
                                   # (pre 3.4 behavior).

disable_load_balance_on_write = 'transaction'
                                   # Load balance behavior when write query is issued
                                   # in an explicit transaction.
                                   # Note that any query not in an explicit transaction
                                   # is not affected by the parameter.
                                   # 'transaction' (the default): if a write query is issued,
                                   # subsequent read queries will not be load balanced
                                   # until the transaction ends.
                                   # 'trans_transaction': if a write query is issued,
                                   # subsequent read queries in an explicit transaction
                                   # will not be load balanced until the session ends.
                                   # 'always': if a write query is issued, read queries will
                                   # not be load balanced until the session ends.

statement_level_load_balance = off
                                   # Enables statement level load balancing

#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
# 激活主从模式
master_slave_mode = on
                                   # Activate master/slave mode
                                   # (change requires restart)
master_slave_sub_mode = 'stream'
                                   # Master/slave sub mode
                                   # Valid values are combinations stream, slony
                                   # or logical. Default is stream.
                                   # (change requires restart)

# - Streaming -
# 流复制检查周期
sr_check_period = 5
                                   # Streaming replication check period
                                   # Disabled (0) by default
# 流复制检查用户，replica是数据库的用户
sr_check_user = 'replica'
                                   # Streaming replication check user
                                   # This is necessary even if you disable
                                   # streaming replication delay check with
                                   # sr_check_period = 0
# 流复制检查用户密码
sr_check_password = 'replica'
                                   # Password for streaming replication check user.
                                   # Leaving it empty will make Pgpool-II to first look for the
                                   # Password in pool_passwd file before using the empty password
# 流复制检查数据库
sr_check_database = 'postgres'
                                   # Database name for streaming replication check
delay_threshold = 0
                                   # Threshold before not dispatching query to standby node
                                   # Unit is in bytes
                                   # Disabled (0) by default

# - Special commands -

follow_master_command = ''
                                   # Executes this command after master failover
                                   # Special values:
                                   #   %d = failed node id
                                   #   %h = failed node host name
                                   #   %p = failed node port number
                                   #   %D = failed node database cluster path
                                   #   %m = new master node id
                                   #   %H = new master node hostname
                                   #   %M = old master node id
                                   #   %P = old primary node id
                                   #   %r = new master port number
                                   #   %R = new master database cluster path
                                   #   %N = old primary node hostname
                                   #   %S = old primary node port number
                                   #   %% = '%' character

#------------------------------------------------------------------------------
# HEALTH CHECK GLOBAL PARAMETERS
#------------------------------------------------------------------------------
# 激活健康检查，周期10s
health_check_period = 10
                                   # Health check period
                                   # Disabled (0) by default
health_check_timeout = 20
                                   # Health check timeout
                                   # 0 means no timeout
# 健康检查用户，数据库管理员用户
health_check_user = 'postgres'
                                   # Health check user
# 健康检查用户密码
health_check_password = 'zhjx123'
                                   # Password for health check user
                                   # Leaving it empty will make Pgpool-II to first look for the
                                   # Password in pool_passwd file before using the empty password
# 健康检查数据库
# 必须设置，否则primary数据库down了，pgpool不知道，不能及时切换。从库流复制还在连接数据，报连接失败。
# 只有下次使用pgpool登录时，发现连接不上，然后报错，这时候，才知道挂了，pgpool进行切换。
health_check_database = 'postgres'
                                   # Database name for health check. If '', tries 'postgres' frist, then 'template1'

health_check_max_retries = 0
                                   # Maximum number of times to retry a failed health check before giving up.
health_check_retry_delay = 1
                                   # Amount of time to wait (in seconds) between retries.
connect_timeout = 10000
                                   # Timeout value in milliseconds before giving up to connect to backend.
                                   # Default is 10000 ms (10 second). Flaky network user may want to increase
                                   # the value. 0 means no timeout.
                                   # Note that this value is not only used for health check,
                                   # but also for ordinary conection to backend.

#------------------------------------------------------------------------------
# HEALTH CHECK PER NODE PARAMETERS (OPTIONAL)
#------------------------------------------------------------------------------
#health_check_period0 = 0
#health_check_timeout0 = 20
#health_check_user0 = 'nobody'
#health_check_password0 = ''
#health_check_database0 = ''
#health_check_max_retries0 = 0
#health_check_retry_delay0 = 1
#connect_timeout0 = 10000

#主备切换的命令行配置
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
# 启动故障转移

failover_command = '/etc/pgpool-II-10/failover_stream.sh %H'
                                   # Executes this command at failover
                                   # Special values:
                                   #   %d = failed node id
                                   #   %h = failed node host name
                                   #   %p = failed node port number
                                   #   %D = failed node database cluster path
                                   #   %m = new master node id
                                   #   %H = new master node hostname
                                   #   %M = old master node id
                                   #   %P = old primary node id
                                   #   %r = new master port number
                                   #   %R = new master database cluster path
                                   #   %N = old primary node hostname
                                   #   %S = old primary node port number
                                   #   %% = '%' character
failback_command = ''
                                   # Executes this command at failback.
                                   # Special values:
                                   #   %d = failed node id
                                   #   %h = failed node host name
                                   #   %p = failed node port number
                                   #   %D = failed node database cluster path
                                   #   %m = new master node id
                                   #   %H = new master node hostname
                                   #   %M = old master node id
                                   #   %P = old primary node id
                                   #   %r = new master port number
                                   #   %R = new master database cluster path
                                   #   %N = old primary node hostname
                                   #   %S = old primary node port number
                                   #   %% = '%' character

failover_on_backend_error = on
                                   # Initiates failover when reading/writing to the
                                   # backend communication socket fails
                                   # If set to off, pgpool will report an
                                   # error and disconnect the session.

detach_false_primary = off
                                   # Detach false primary if on. Only
                                   # valid in streaming replicaton
                                   # mode and with PostgreSQL 9.6 or
                                   # after.

search_primary_node_timeout = 300
                                   # Timeout in seconds to search for the
                                   # primary node when a failover occurs.
                                   # 0 means no timeout, keep searching
                                   # for a primary node forever.

auto_failback = off
                                   # Dettached backend node reattach automatically
                                   # if replication_state is 'streaming'.
auto_failback_interval = 60
                                   # Min interval of executing auto_failback in
                                   # seconds.

#------------------------------------------------------------------------------
# ONLINE RECOVERY
#------------------------------------------------------------------------------

recovery_user = 'nobody'
                                   # Online recovery user
recovery_password = ''
                                   # Online recovery password
                                   # Leaving it empty will make Pgpool-II to first look for the
                                   # Password in pool_passwd file before using the empty password

recovery_1st_stage_command = ''
                                   # Executes a command in first stage
recovery_2nd_stage_command = ''
                                   # Executes a command in second stage
recovery_timeout = 90
                                   # Timeout in seconds to wait for the
                                   # recovering node's postmaster to start up
                                   # 0 means no wait
client_idle_limit_in_recovery = 0
                                   # Client is disconnected after being idle
                                   # for that many seconds in the second stage
                                   # of online recovery
                                   # 0 means no disconnection
                                   # -1 means immediate disconnection


#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------

# - Enabling -
# 激活看门狗
use_watchdog = on
                                    # Activates watchdog
                                    # (change requires restart)

# -Connection to up stream servers -

trusted_servers = ''
                                    # 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 -
# 本端地址
wd_hostname = 'test2'
                                    # Host name or IP address of this watchdog
                                    # (change requires restart)
wd_port = 9000
                                    # port number for watchdog service
                                    # (change requires restart)
wd_priority = 1
                                    # priority of this watchdog in leader election
                                    # (change requires restart)

wd_authkey = ''
                                    # Authentication key for watchdog communication
                                    # (change requires restart)

wd_ipc_socket_dir = '/tmp'
                                    # Unix domain socket path for watchdog IPC socket
                                    # The Debian package defaults to
                                    # /var/run/postgresql
                                    # (change requires restart)


# - Virtual IP control Setting -
# 激活虚拟VIP，vip在hosts里面设置解析，否则直接填写ip
delegate_IP = 'vip'
                                    # delegate IP address
                                    # If this is empty, virtual IP never bring up.
                                    # (change requires restart)
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 $_IP_$/24 dev eth0 label eth0:0'
# 执行添加vip地址命令
if_up_cmd = '/sbin/ifconfig ens192:0 inet $_IP_$ netmask 255.255.254.0'
                                    # startup delegate IP command
                                    # (change requires restart)
#if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'
# 执行删除vip地址命令
if_down_cmd = '/sbin/ifconfig ens192:0 down'
                                    # 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 $_IP_$ -w 1 -I ens192'
                                    # arping command
                                    # (change requires restart)

# - Behaivor on escalation Setting -

clear_memqcache_on_escalation = on
                                    # 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 = ''
                                    # 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 = on
                                    # Only perform backend node failover
                                    # when the watchdog cluster holds the quorum
                                    # (change requires restart)

failover_require_consensus = on
                                    # Perform failover when majority of Pgpool-II nodes
                                    # aggrees on the backend node status change
                                    # (change requires restart)

allow_multiple_failover_requests_from_node = off
                                    # A Pgpool-II node can cast multiple votes
                                    # for building the consensus on failover
                                    # (change requires restart)

enable_consensus_with_half_votes = off
                                    # 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 -

# -- common --

wd_monitoring_interfaces_list = ''  # 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 = 'test3'
                                    # 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 = 'ens192'
                                    # 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_destination1 = 'host0_ip2'
#heartbeat_destination_port1 = 9694
#heartbeat_device1 = ''

# -- query mode --

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 = 'template1'
                                    # Database name connected for lifecheck
                                    # (change requires restart)
wd_lifecheck_user = 'nobody'
                                    # watchdog user monitoring pgpools in lifecheck
                                    # (change requires restart)
wd_lifecheck_password = ''
                                    # 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 -
# 激活其他pgpool服务器节点连接信息

#other_pgpool_hostname0 = 'host0'
other_pgpool_hostname0 = 'test3'
                                    # Host name or IP address to connect to for other pgpool 0
                                    # (change requires restart)
# pgpool连接端口改为9999
other_pgpool_port0 = 9999
                                    # Port number for other pgpool 0
                                    # (change requires restart)
other_wd_port0 = 9000
                                    # Port number for other watchdog 0
                                    # (change requires restart)
#other_pgpool_hostname1 = 'host1'
#other_pgpool_port1 = 5432
#other_wd_port1 = 9000


#------------------------------------------------------------------------------
# OTHERS
#------------------------------------------------------------------------------
relcache_expire = 0
                                   # Life time of relation cache in seconds.
                                   # 0 means no cache expiration(the default).
                                   # The relation cache is used for cache the
                                   # query result against PostgreSQL system
                                   # catalog to obtain various information
                                   # including table structures or if it's a
                                   # temporary table or not. The cache is
                                   # maintained in a pgpool child local memory
                                   # and being kept as long as it survives.
                                   # If someone modify the table by using
                                   # ALTER TABLE or some such, the relcache is
                                   # not consistent anymore.
                                   # For this purpose, cache_expiration
                                   # controls the life time of the cache.

relcache_size = 256
                                   # Number of relation cache
                                   # entry. If you see frequently:
                                   # "pool_search_relcache: cache replacement happend"
                                   # in the pgpool log, you might want to increate this number.

check_temp_table = catalog
                                   # Temporary table check method. catalog, trace or none.
                                   # Default is catalog.

check_unlogged_table = on
                                   # If on, enable unlogged table check in SELECT statements.
                                   # This initiates queries against system catalog of primary/master
                                   # thus increases load of master.
                                   # If you are absolutely sure that your system never uses unlogged tables
                                   # and you want to save access to primary/master, you could turn this off.
                                   # Default is on.
enable_shared_relcache = on
                                   # If on, relation cache stored in memory cache,
                                   # the cache is shared among child process.
                                   # Default is on.
                                   # (change requires restart)

relcache_query_target = master     # Target node to send relcache queries. Default is master (primary) node.
                                   # If load_balance_node is specified, queries will be sent to load balance node.
#------------------------------------------------------------------------------
# IN MEMORY QUERY MEMORY CACHE
#------------------------------------------------------------------------------
memory_cache_enabled = off
                                   # If on, use the memory cache functionality, off by default
                                   # (change requires restart)
memqcache_method = 'shmem'
                                   # Cache storage method. either 'shmem'(shared memory) or
                                   # 'memcached'. 'shmem' by default
                                   # (change requires restart)
memqcache_memcached_host = 'localhost'
                                   # Memcached host name or IP address. Mandatory if
                                   # memqcache_method = 'memcached'.
                                   # Defaults to localhost.
                                   # (change requires restart)
memqcache_memcached_port = 11211
                                   # Memcached port number. Mondatory if memqcache_method = 'memcached'.
                                   # Defaults to 11211.
                                   # (change requires restart)
memqcache_total_size = 67108864
                                   # Total memory size in bytes for storing memory cache.
                                   # Mandatory if memqcache_method = 'shmem'.
                                   # Defaults to 64MB.
                                   # (change requires restart)
memqcache_max_num_cache = 1000000
                                   # Total number of cache entries. Mandatory
                                   # if memqcache_method = 'shmem'.
                                   # Each cache entry consumes 48 bytes on shared memory.
                                   # Defaults to 1,000,000(45.8MB).
                                   # (change requires restart)
memqcache_expire = 0
                                   # Memory cache entry life time specified in seconds.
                                   # 0 means infinite life time. 0 by default.
                                   # (change requires restart)
memqcache_auto_cache_invalidation = on
                                   # If on, invalidation of query cache is triggered by corresponding
                                   # DDL/DML/DCL(and memqcache_expire).  If off, it is only triggered
                                   # by memqcache_expire.  on by default.
                                   # (change requires restart)
memqcache_maxcache = 409600
                                   # Maximum SELECT result size in bytes.
                                   # Must be smaller than memqcache_cache_block_size. Defaults to 400KB.
                                   # (change requires restart)
memqcache_cache_block_size = 1048576
                                   # Cache block size in bytes. Mandatory if memqcache_method = 'shmem'.
                                   # Defaults to 1MB.
                                   # (change requires restart)
memqcache_oiddir = '/var/log/pgpool/oiddir'
                                   # Temporary work directory to record table oids
                                   # (change requires restart)
white_memqcache_table_list = ''
                                   # Comma separated list of table names to memcache
                                   # that don't write to database
                                   # Regexp are accepted
black_memqcache_table_list = ''
                                   # Comma separated list of table names not to memcache
                                   # that don't write to database
                                   # Regexp are accepted

3.5 配置slave上的pgpool.conf

# ----------------------------
# pgPool-II configuration file
# ----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# Whitespace may be used.  Comments are introduced with "#" anywhere on a line.
# The complete list of parameter names and allowed values can be found in the
# pgPool-II documentation.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pgpool reload".  Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#


#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------

# - pgpool Connection Settings -
# 修改监听
listen_addresses = '*'
                                   # Host name or IP address to listen on:
                                   # '*' for all, '' for no TCP/IP connections
                                   # (change requires restart)
port = 9999
                                   # Port number
                                   # (change requires restart)
socket_dir = '/tmp'
                                   # Unix domain socket path
                                   # The Debian package defaults to
                                   # /var/run/postgresql
                                   # (change requires restart)
listen_backlog_multiplier = 2
                                   # Set the backlog parameter of listen(2) to
                                   # num_init_children * listen_backlog_multiplier.
                                   # (change requires restart)
serialize_accept = off
                                   # whether to serialize accept() call to avoid thundering herd problem
                                   # (change requires restart)
reserved_connections = 0
                                   # Number of reserved connections.
                                   # Pgpool-II does not accept connections if over
                                   # num_init_chidlren - reserved_connections.

# - pgpool Communication Manager Connection Settings -
# 修改监听
pcp_listen_addresses = '*'
                                   # Host name or IP address for pcp process to listen on:
                                   # '*' for all, '' for no TCP/IP connections
                                   # (change requires restart)
pcp_port = 9898
                                   # Port number for pcp
                                   # (change requires restart)
pcp_socket_dir = '/tmp'
                                   # Unix domain socket path for pcp
                                   # The Debian package defaults to
                                   # /var/run/postgresql
                                   # (change requires restart)

# - Backend Connection Settings -
# pgpool集群后端主机信息
# 修改为本端主机名，要在hosts中加入解析
backend_hostname0 = 'test2'
                                   # Host name or IP address to connect to for backend 0
backend_port0 = 5432
                                   # Port number for backend 0
backend_weight0 = 1
                                   # Weight for backend 0 (only in load balancing mode)
# 修改postgresql的数据目录
backend_data_directory0 = '/pgdata'
                                   # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
                                   # Controls various backend behavior
                                   # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
                                   # or ALWAYS_MASTER
# 添加内容，
backend_hostname1 = 'test3'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/pgdata'
backend_flag1 = 'ALLOW_TO_FAILOVER'
# 添加结束

backend_application_name0 = 'server0'
                                   # walsender's application_name, used for "show pool_nodes" command
#backend_hostname1 = 'host2'
#backend_port1 = 5433
#backend_weight1 = 1
#backend_data_directory1 = '/data1'
#backend_flag1 = 'ALLOW_TO_FAILOVER'
#backend_application_name1 = 'server1'

# - Authentication -
# 激活pgpool认证方式
enable_pool_hba = on
                                   # Use pool_hba.conf for client authentication
pool_passwd = 'pool_passwd'
                                   # File name of pool_passwd for md5 authentication.
                                   # "" disables pool_passwd.
                                   # (change requires restart)
authentication_timeout = 60
                                   # Delay in seconds to complete client authentication
                                   # 0 means no timeout.

allow_clear_text_frontend_auth = off
                                   # Allow Pgpool-II to use clear text password authentication
                                   # with clients, when pool_passwd does not
                                   # contain the user password


# - SSL Connections -

ssl = off
                                   # Enable SSL support
                                   # (change requires restart)
#ssl_key = './server.key'
                                   # Path to the SSL private key file
                                   # (change requires restart)
#ssl_cert = './server.cert'
                                   # Path to the SSL public certificate file
                                   # (change requires restart)
#ssl_ca_cert = ''
                                   # Path to a single PEM format file
                                   # containing CA root certificate(s)
                                   # (change requires restart)
#ssl_ca_cert_dir = ''
                                   # Directory containing CA root certificate(s)
                                   # (change requires restart)

ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
                                   # Allowed SSL ciphers
                                   # (change requires restart)
ssl_prefer_server_ciphers = off
                                   # Use server's SSL cipher preferences,
                                   # rather than the client's
                                   # (change requires restart)
ssl_ecdh_curve = 'prime256v1'
                                   # Name of the curve to use in ECDH key exchange
ssl_dh_params_file = ''
                                   # Name of the file containing Diffie-Hellman parameters used
                                   # for so-called ephemeral DH family of SSL cipher.

#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------

# - Concurrent session and pool size -

num_init_children = 32
                                   # Number of concurrent sessions allowed
                                   # (change requires restart)
max_pool = 4
                                   # Number of connection pool caches per connection
                                   # (change requires restart)

# - Life time -

child_life_time = 300
                                   # Pool exits after being idle for this many seconds
child_max_connections = 0
                                   # Pool exits after receiving that many connections
                                   # 0 means no exit
connection_life_time = 0
                                   # Connection to backend closes after being idle for this many seconds
                                   # 0 means no close
client_idle_limit = 0
                                   # Client is disconnected after being idle for that many seconds
                                   # (even inside an explicit transactions!)
                                   # 0 means no disconnection


#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------

# - Where to log -

log_destination = 'stderr'
                                   # Where to log
                                   # Valid values are combinations of stderr,
                                   # and syslog. Default to stderr.

# - What to log -

log_line_prefix = '%t: pid %p: '   # printf-style string to output at beginning of each log line.

log_connections = off
                                   # Log connections
log_hostname = off
                                   # Hostname will be shown in ps status
                                   # and in logs if connections are logged
log_statement = off
                                   # Log all statements
log_per_node_statement = off
                                   # Log all statements
                                   # with node and backend informations
log_client_messages = off
                                   # Log any client messages
log_standby_delay = 'none'
                                   # Log standby delay
                                   # Valid values are combinations of always,
                                   # if_over_threshold, none

# - Syslog specific -

syslog_facility = 'LOCAL0'
                                   # Syslog local facility. Default to LOCAL0
syslog_ident = 'pgpool'
                                   # Syslog program identification string
                                   # Default to 'pgpool'

# - Debug -

#log_error_verbosity = default          # terse, default, or verbose messages

#client_min_messages = notice           # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   log
                                        #   notice
                                        #   warning
                                        #   error

#log_min_messages = warning             # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# pid文件路径  这里不做修改
pid_file_name = '/var/run/pgpool-II-10/pgpool.pid'
                                   # PID file name
                                   # Can be specified as relative to the"
                                   # location of pgpool.conf file or
                                   # as an absolute path
                                   # (change requires restart)
# pgpool集群状态信息文件，这里保持默认
logdir = '/var/log/pgpool-II-10'
                                   # Directory of pgPool status file
                                   # (change requires restart)


#------------------------------------------------------------------------------
# CONNECTION POOLING
#------------------------------------------------------------------------------

connection_cache = on
                                   # Activate connection pools
                                   # (change requires restart)

                                   # Semicolon separated list of queries
                                   # to be issued at the end of a session
                                   # The default is for 8.3 and later
reset_query_list = 'ABORT; DISCARD ALL'
                                   # The following one is for 8.2 and before
#reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'


#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
# 复制模式保持关闭
replication_mode = off
                                   # Activate replication mode
                                   # (change requires restart)
replicate_select = off
                                   # Replicate SELECT statements
                                   # when in replication mode
                                   # replicate_select is higher priority than
                                   # load_balance_mode.

insert_lock = on
                                   # Automatically locks a dummy row or a table
                                   # with INSERT statements to keep SERIAL data
                                   # consistency
                                   # Without SERIAL, no lock will be issued
lobj_lock_table = ''
                                   # When rewriting lo_creat command in
                                   # replication mode, specify table name to
                                   # lock

# - Degenerate handling -
# 
replication_stop_on_mismatch = off
                                   # On disagreement with the packet kind
                                   # sent from backend, degenerate the node
                                   # which is most likely "minority"
                                   # If off, just force to exit this session

failover_if_affected_tuples_mismatch = off
                                   # On disagreement with the number of affected
                                   # tuples in UPDATE/DELETE queries, then
                                   # degenerate the node which is most likely
                                   # "minority".
                                   # If off, just abort the transaction to
                                   # keep the consistency


#------------------------------------------------------------------------------
# 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 = 'currval,lastval,nextval,setval'
                                   # 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.
app_name_redirect_preference_list = ''
                                   # comma separated list of pairs of app name and node id.
                                   # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
                                   # valid for streaming replicaton mode only.
allow_sql_comments = off
                                   # if on, ignore SQL comments when judging if load balance or
                                   # query cache is possible.
                                   # If off, SQL comments effectively prevent the judgment
                                   # (pre 3.4 behavior).

disable_load_balance_on_write = 'transaction'
                                   # Load balance behavior when write query is issued
                                   # in an explicit transaction.
                                   # Note that any query not in an explicit transaction
                                   # is not affected by the parameter.
                                   # 'transaction' (the default): if a write query is issued,
                                   # subsequent read queries will not be load balanced
                                   # until the transaction ends.
                                   # 'trans_transaction': if a write query is issued,
                                   # subsequent read queries in an explicit transaction
                                   # will not be load balanced until the session ends.
                                   # 'always': if a write query is issued, read queries will
                                   # not be load balanced until the session ends.

statement_level_load_balance = off
                                   # Enables statement level load balancing

#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
# 激活主从模式
master_slave_mode = on
                                   # Activate master/slave mode
                                   # (change requires restart)
master_slave_sub_mode = 'stream'
                                   # Master/slave sub mode
                                   # Valid values are combinations stream, slony
                                   # or logical. Default is stream.
                                   # (change requires restart)

# - Streaming -
# 流复制检查周期
sr_check_period = 5
                                   # Streaming replication check period
                                   # Disabled (0) by default
# 流复制检查用户，replica是数据库的用户
sr_check_user = 'replica'
                                   # Streaming replication check user
                                   # This is necessary even if you disable
                                   # streaming replication delay check with
                                   # sr_check_period = 0
# 流复制检查用户密码
sr_check_password = 'replica'
                                   # Password for streaming replication check user.
                                   # Leaving it empty will make Pgpool-II to first look for the
                                   # Password in pool_passwd file before using the empty password
# 流复制检查数据库
sr_check_database = 'postgres'
                                   # Database name for streaming replication check
delay_threshold = 0
                                   # Threshold before not dispatching query to standby node
                                   # Unit is in bytes
                                   # Disabled (0) by default

# - Special commands -

follow_master_command = ''
                                   # Executes this command after master failover
                                   # Special values:
                                   #   %d = failed node id
                                   #   %h = failed node host name
                                   #   %p = failed node port number
                                   #   %D = failed node database cluster path
                                   #   %m = new master node id
                                   #   %H = new master node hostname
                                   #   %M = old master node id
                                   #   %P = old primary node id
                                   #   %r = new master port number
                                   #   %R = new master database cluster path
                                   #   %N = old primary node hostname
                                   #   %S = old primary node port number
                                   #   %% = '%' character

#------------------------------------------------------------------------------
# HEALTH CHECK GLOBAL PARAMETERS
#------------------------------------------------------------------------------
# 激活健康检查，周期10s
health_check_period = 10
                                   # Health check period
                                   # Disabled (0) by default
health_check_timeout = 20
                                   # Health check timeout
                                   # 0 means no timeout
# 健康检查用户，数据库管理员用户
health_check_user = 'postgres'
                                   # Health check user
# 健康检查用户密码
health_check_password = 'zhjx123'
                                   # Password for health check user
                                   # Leaving it empty will make Pgpool-II to first look for the
                                   # Password in pool_passwd file before using the empty password
# 健康检查数据库
# 必须设置，否则primary数据库down了，pgpool不知道，不能及时切换。从库流复制还在连接数据，报连接失败。
# 只有下次使用pgpool登录时，发现连接不上，然后报错，这时候，才知道挂了，pgpool进行切换。
health_check_database = 'postgres'
                                   # Database name for health check. If '', tries 'postgres' frist, then 'template1'

health_check_max_retries = 0
                                   # Maximum number of times to retry a failed health check before giving up.
health_check_retry_delay = 1
                                   # Amount of time to wait (in seconds) between retries.
connect_timeout = 10000
                                   # Timeout value in milliseconds before giving up to connect to backend.
                                   # Default is 10000 ms (10 second). Flaky network user may want to increase
                                   # the value. 0 means no timeout.
                                   # Note that this value is not only used for health check,
                                   # but also for ordinary conection to backend.

#------------------------------------------------------------------------------
# HEALTH CHECK PER NODE PARAMETERS (OPTIONAL)
#------------------------------------------------------------------------------
#health_check_period0 = 0
#health_check_timeout0 = 20
#health_check_user0 = 'nobody'
#health_check_password0 = ''
#health_check_database0 = ''
#health_check_max_retries0 = 0
#health_check_retry_delay0 = 1
#connect_timeout0 = 10000

#主备切换的命令行配置
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
# 启动故障转移

failover_command = '/etc/pgpool-II-10/failover_stream.sh %H'
                                   # Executes this command at failover
                                   # Special values:
                                   #   %d = failed node id
                                   #   %h = failed node host name
                                   #   %p = failed node port number
                                   #   %D = failed node database cluster path
                                   #   %m = new master node id
                                   #   %H = new master node hostname
                                   #   %M = old master node id
                                   #   %P = old primary node id
                                   #   %r = new master port number
                                   #   %R = new master database cluster path
                                   #   %N = old primary node hostname
                                   #   %S = old primary node port number
                                   #   %% = '%' character
failback_command = ''
                                   # Executes this command at failback.
                                   # Special values:
                                   #   %d = failed node id
                                   #   %h = failed node host name
                                   #   %p = failed node port number
                                   #   %D = failed node database cluster path
                                   #   %m = new master node id
                                   #   %H = new master node hostname
                                   #   %M = old master node id
                                   #   %P = old primary node id
                                   #   %r = new master port number
                                   #   %R = new master database cluster path
                                   #   %N = old primary node hostname
                                   #   %S = old primary node port number
                                   #   %% = '%' character

failover_on_backend_error = on
                                   # Initiates failover when reading/writing to the
                                   # backend communication socket fails
                                   # If set to off, pgpool will report an
                                   # error and disconnect the session.

detach_false_primary = off
                                   # Detach false primary if on. Only
                                   # valid in streaming replicaton
                                   # mode and with PostgreSQL 9.6 or
                                   # after.

search_primary_node_timeout = 300
                                   # Timeout in seconds to search for the
                                   # primary node when a failover occurs.
                                   # 0 means no timeout, keep searching
                                   # for a primary node forever.

auto_failback = off
                                   # Dettached backend node reattach automatically
                                   # if replication_state is 'streaming'.
auto_failback_interval = 60
                                   # Min interval of executing auto_failback in
                                   # seconds.

#------------------------------------------------------------------------------
# ONLINE RECOVERY
#------------------------------------------------------------------------------

recovery_user = 'nobody'
                                   # Online recovery user
recovery_password = ''
                                   # Online recovery password
                                   # Leaving it empty will make Pgpool-II to first look for the
                                   # Password in pool_passwd file before using the empty password

recovery_1st_stage_command = ''
                                   # Executes a command in first stage
recovery_2nd_stage_command = ''
                                   # Executes a command in second stage
recovery_timeout = 90
                                   # Timeout in seconds to wait for the
                                   # recovering node's postmaster to start up
                                   # 0 means no wait
client_idle_limit_in_recovery = 0
                                   # Client is disconnected after being idle
                                   # for that many seconds in the second stage
                                   # of online recovery
                                   # 0 means no disconnection
                                   # -1 means immediate disconnection


#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------

# - Enabling -
# 激活看门狗
use_watchdog = on
                                    # Activates watchdog
                                    # (change requires restart)

# -Connection to up stream servers -

trusted_servers = ''
                                    # 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 -
# 本端地址，与master节点相反
wd_hostname = 'test3'
                                    # Host name or IP address of this watchdog
                                    # (change requires restart)
wd_port = 9000
                                    # port number for watchdog service
                                    # (change requires restart)
wd_priority = 1
                                    # priority of this watchdog in leader election
                                    # (change requires restart)

wd_authkey = ''
                                    # Authentication key for watchdog communication
                                    # (change requires restart)

wd_ipc_socket_dir = '/tmp'
                                    # Unix domain socket path for watchdog IPC socket
                                    # The Debian package defaults to
                                    # /var/run/postgresql
                                    # (change requires restart)


# - Virtual IP control Setting -
# 激活虚拟VIP，vip在hosts里面设置解析，否则直接填写ip
delegate_IP = 'vip'
                                    # delegate IP address
                                    # If this is empty, virtual IP never bring up.
                                    # (change requires restart)
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 $_IP_$/24 dev eth0 label eth0:0'
# 执行添加vip地址命令
if_up_cmd = '/sbin/ifconfig ens192:0 inet $_IP_$ netmask 255.255.254.0'
                                    # startup delegate IP command
                                    # (change requires restart)
#if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'
# 执行删除vip地址命令
if_down_cmd = '/sbin/ifconfig ens192:0 down'
                                    # 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 $_IP_$ -w 1 -I ens192'
                                    # arping command
                                    # (change requires restart)

# - Behaivor on escalation Setting -

clear_memqcache_on_escalation = on
                                    # 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 = ''
                                    # 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 = on
                                    # Only perform backend node failover
                                    # when the watchdog cluster holds the quorum
                                    # (change requires restart)

failover_require_consensus = on
                                    # Perform failover when majority of Pgpool-II nodes
                                    # aggrees on the backend node status change
                                    # (change requires restart)

allow_multiple_failover_requests_from_node = off
                                    # A Pgpool-II node can cast multiple votes
                                    # for building the consensus on failover
                                    # (change requires restart)

enable_consensus_with_half_votes = off
                                    # 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 -

# -- common --

wd_monitoring_interfaces_list = ''  # 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)
# 对端地址，与master节点相反
heartbeat_destination0 = 'test2'
                                    # 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 = 'ens192'
                                    # 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_destination1 = 'host0_ip2'
#heartbeat_destination_port1 = 9694
#heartbeat_device1 = ''

# -- query mode --

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 = 'template1'
                                    # Database name connected for lifecheck
                                    # (change requires restart)
wd_lifecheck_user = 'nobody'
                                    # watchdog user monitoring pgpools in lifecheck
                                    # (change requires restart)
wd_lifecheck_password = ''
                                    # 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 -
# 激活其他pgpool服务器节点连接信息

#other_pgpool_hostname0 = 'host0'
other_pgpool_hostname0 = 'test2'
                                    # Host name or IP address to connect to for other pgpool 0
                                    # (change requires restart)
# pgpool连接端口改为9999
other_pgpool_port0 = 9999
                                    # Port number for other pgpool 0
                                    # (change requires restart)
other_wd_port0 = 9000
                                    # Port number for other watchdog 0
                                    # (change requires restart)
#other_pgpool_hostname1 = 'host1'
#other_pgpool_port1 = 5432
#other_wd_port1 = 9000


#------------------------------------------------------------------------------
# OTHERS
#------------------------------------------------------------------------------
relcache_expire = 0
                                   # Life time of relation cache in seconds.
                                   # 0 means no cache expiration(the default).
                                   # The relation cache is used for cache the
                                   # query result against PostgreSQL system
                                   # catalog to obtain various information
                                   # including table structures or if it's a
                                   # temporary table or not. The cache is
                                   # maintained in a pgpool child local memory
                                   # and being kept as long as it survives.
                                   # If someone modify the table by using
                                   # ALTER TABLE or some such, the relcache is
                                   # not consistent anymore.
                                   # For this purpose, cache_expiration
                                   # controls the life time of the cache.

relcache_size = 256
                                   # Number of relation cache
                                   # entry. If you see frequently:
                                   # "pool_search_relcache: cache replacement happend"
                                   # in the pgpool log, you might want to increate this number.

check_temp_table = catalog
                                   # Temporary table check method. catalog, trace or none.
                                   # Default is catalog.

check_unlogged_table = on
                                   # If on, enable unlogged table check in SELECT statements.
                                   # This initiates queries against system catalog of primary/master
                                   # thus increases load of master.
                                   # If you are absolutely sure that your system never uses unlogged tables
                                   # and you want to save access to primary/master, you could turn this off.
                                   # Default is on.
enable_shared_relcache = on
                                   # If on, relation cache stored in memory cache,
                                   # the cache is shared among child process.
                                   # Default is on.
                                   # (change requires restart)

relcache_query_target = master     # Target node to send relcache queries. Default is master (primary) node.
                                   # If load_balance_node is specified, queries will be sent to load balance node.
#------------------------------------------------------------------------------
# IN MEMORY QUERY MEMORY CACHE
#------------------------------------------------------------------------------
memory_cache_enabled = off
                                   # If on, use the memory cache functionality, off by default
                                   # (change requires restart)
memqcache_method = 'shmem'
                                   # Cache storage method. either 'shmem'(shared memory) or
                                   # 'memcached'. 'shmem' by default
                                   # (change requires restart)
memqcache_memcached_host = 'localhost'
                                   # Memcached host name or IP address. Mandatory if
                                   # memqcache_method = 'memcached'.
                                   # Defaults to localhost.
                                   # (change requires restart)
memqcache_memcached_port = 11211
                                   # Memcached port number. Mondatory if memqcache_method = 'memcached'.
                                   # Defaults to 11211.
                                   # (change requires restart)
memqcache_total_size = 67108864
                                   # Total memory size in bytes for storing memory cache.
                                   # Mandatory if memqcache_method = 'shmem'.
                                   # Defaults to 64MB.
                                   # (change requires restart)
memqcache_max_num_cache = 1000000
                                   # Total number of cache entries. Mandatory
                                   # if memqcache_method = 'shmem'.
                                   # Each cache entry consumes 48 bytes on shared memory.
                                   # Defaults to 1,000,000(45.8MB).
                                   # (change requires restart)
memqcache_expire = 0
                                   # Memory cache entry life time specified in seconds.
                                   # 0 means infinite life time. 0 by default.
                                   # (change requires restart)
memqcache_auto_cache_invalidation = on
                                   # If on, invalidation of query cache is triggered by corresponding
                                   # DDL/DML/DCL(and memqcache_expire).  If off, it is only triggered
                                   # by memqcache_expire.  on by default.
                                   # (change requires restart)
memqcache_maxcache = 409600
                                   # Maximum SELECT result size in bytes.
                                   # Must be smaller than memqcache_cache_block_size. Defaults to 400KB.
                                   # (change requires restart)
memqcache_cache_block_size = 1048576
                                   # Cache block size in bytes. Mandatory if memqcache_method = 'shmem'.
                                   # Defaults to 1MB.
                                   # (change requires restart)
memqcache_oiddir = '/var/log/pgpool/oiddir'
                                   # Temporary work directory to record table oids
                                   # (change requires restart)
white_memqcache_table_list = ''
                                   # Comma separated list of table names to memcache
                                   # that don't write to database
                                   # Regexp are accepted
black_memqcache_table_list = ''
                                   # Comma separated list of table names not to memcache
                                   # that don't write to database
                                   # Regexp are accepted

配置文件里，故障处理配置的是failover_command = ‘/etc/pgpool-II-10/failover_stream.sh %H ‘，因此，需要在/opt/pgpool目录中写个failover_stream.sh脚本：

1
2
3

[postgres@master ~]$ cd /etc/pgpool-II-10
[postgres@pgpool~]$ touch failover_stream.sh
[postgres@pgpool~]$ vim failover_stream.sh

#! /bin/sh 
# Failover command for streaming replication. 
# Arguments: $1: new master hostname. 

new_master=$1
trigger_command="/usr/pgsql-10/bin/pg_ctl promote -D /pgdata"

# Prompte standby database. 
# 升级从数据库为主节点
/usr/bin/ssh -T $new_master $trigger_command

exit 0;

如果是其他用户创建的，需要赋予postgres可执行权限，例如

1 2	[root@opt ~]$ chown -R postgres.postgres /opt/pgpool [root@opt ~]]$ chmod 777 /opt/pgpool/failover_stream.sh

3.6 启动集群

分别启动postgrsql

1	systemctl start postgresql-10.service

分别启动pgpool

1	systemctl start pgpool-II-10.service

启动pgpool后，查看集群节点状态:

[postgres@master ~]$ psql -h vip -p 9999
psql (9.6.1)
#提示输入密码：
Type "help" for help.

postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | master   | 5432 | up     | 0.500000  | primary | 0             | false  | 0
 1       | slave     | 5432 | up     | 0.500000  | standby | 0             |  true  | 0
(2 rows)

#在slave上节点也是psql -h vip -p 9999，双pgpool使用虚拟ip，做到高可用。

1 环境

1.1 软件

1.2 硬件

1.3 参考文档

2 前期准备（各节点均配置）

2.1 关闭防火墙/selinux

2.2 修改网络

2.3 系统优化

2.4 设置hosts

2.5 ssh免密登录

2.6 时间同步(提前下载)

2.7 jdk安装

2.8 mysql安装（ambari节点）

3 设置本地库

3.1 apache安装（ambari节点）

3.2 下载并上传软件包

3.3 配置源

4 Ambari安装

4.1 创建源文件

4.2 生成缓存

4.3 安装Ambari Server

4.3.1 初始化Ambar

4.3.2 初始化mysql

4.3.2.1 创建ambari数据库

4.3.2.2 初始化数据

4.3.3 启动Ambari server

5 安装HDP集群

5.1.1 设置组件自启动

6 问题

6.1 问题1：缺少软件（提前downloadonly）

6.2 问题2：没有flume组件

6.3 问题3：重启主机，ambari-server无法启动

6.4 问题4：hive组件配置数据库时，需要执行一下mysql连接驱动的路径的命令在master节点

6.5 6.5 问题5：SmartSense Gateway is not active

7 扩展功能

7.1 配置namenode HA

1. 环境

2. 基础安装

2.1. 安装CentOS 7

2.2. 配置网络

2.3. 关闭防火墙和SeLinux

2.4. SSH免密码登录

2.5. 安装JDK

3. 数据库安装

4. Ambari安装

4.1. 搭建Yum源服务器

4.2. 安装Ambari Server

4.3. 初始化数据库

4.4. 启动服务

4.5. 安装 HDP 集群

5. 安装ELK

5.1. 安装Elasticsearch

5.2. 安装Kibana

5.3. 安装x-pack插件

6.1. 安装VNC Service

1 运行环境

2 说明

3 数据库安装

4 jira安装

4.1 官网下周jira linux安装包

4.2 安装

4.2.1 赋权并执行安装

4.2.2 开放8080端口

4.2.3 执行破解

4.2.4 访问页面

4.2.5 检查破解是否生效

4.3 插件安装

4.3.1 在线安装旧系统插件

4.3.2 插件破解

4.4 数据备份恢复-xml方式

4.4.1 备份数据

4.4.2 恢复数据

4.4.3 重建索引

4.5 出现问题

4.5.1 问题1

4.5.2 问题2

4.5.3 问题3

5 conflucese安装

5.1 下载

5.2 安装

一、主机