greenplum主从复制

greenplum 主从部署

# host解析
vim /etc/hosts
192.168.12.235 node1.iotplatform.com
192.168.11.202 node2.iotplatform.com

# 创建用户
useradd gpadmin
yes gpadmin|passwd gpadmin

# 创建相关目录
mkdir -p /data/gpadmin/master
mkdir -p /data/gpadmin/mirror
mkdir -p /data/gpadmin/primary
mkdir -p /user/searchlight

# 解压文件
mkdir /opt/iotplatform
tar xf greenplum-db-6.16.0.tar.gz -C /opt/iotplatform/

# 安装
cd /opt/iotplatform/greenplum-db-6.16.0/
rpm -ivh apr-1.4.8-5.el7.x86_64.rpm
rpm -ivh  apr-util-1.5.2-6.el7.x86_64.rpm
rpm -ivh  bzip2-1.0.6-13.el7.x86_64.rpm
rpm -ivh  libevent-2.0.21-4.el7.x86_64.rpm
rpm -ivh  zip-3.0-11.el7.x86_64.rpm
yum -y install krb5-devel libyaml
rpm -ivh  greenplum-db-6.16.0-rhel7-x86_64.rpm

# 配置主机列表
vim /usr/local/greenplum-db-6.16.0/bin/allhosts
node1.iotplatform.com
node2.iotplatform.com

# 
echo 'kernel.shmall = 2041774
kernel.shmmax = 8363106304
kernel.shmmni = 4096
vm.overcommit_memory = 1
vm.overcommit_ratio = 95
net.ipv4.ip_local_port_range = 10000 65535
kernel.sem = 250 512000 100 2048
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
net.ipv4.tcp_syncookies = 1
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.all.arp_filter = 1
net.core.netdev_max_backlog = 10000
net.core.rmem_max = 2097152
net.core.wmem_max = 2097152
vm.swappiness = 10
vm.zone_reclaim_mode = 0
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
vm.dirty_background_ratio = 3
vm.dirty_ratio = 10
vm.max_map_count=655360
' >> /etc/sysctl.conf

# 生效
sysctl -p

# 授权
chown -R gpadmin:gpadmin /usr/local/greenplum*
chown -R gpadmin:gpadmin /data/gpadmin
chown -R gpadmin:gpadmin /home/gpadmin
chown -R gpadmin:gpadmin /user/searchlight
setfacl -m d:u:gpadmin:rwx -R /user/searchlight
chmod +x /usr/local/greenplum-db-6.16.0/*.sh

# 生成秘钥
su - gpadmin -c "[ ! -f /home/gpadmin/.ssh/id_rsa.pub ] && ssh-keygen -t rsa -P \"\" -f /home/gpadmin/.ssh/id_rsa"
su - gpadmin -c "cat /home/gpadmin/.ssh/id_rsa.pub >> /home/gpadmin/.ssh/authorized_keys"

chmod 700 /home/gpadmin/.ssh
chmod 600 /home/gpadmin/.ssh/authorized_keys

# 推送秘钥,两台都要推送
ssh-copy-id -i ~/.ssh/id_rsa.pub root@node1.iotplatform.com
ssh-copy-id -i ~/.ssh/id_rsa.pub gpadmin@node1.iotplatform.com

ssh-copy-id -i ~/.ssh/id_rsa.pub root@node2.iotplatform.com
ssh-copy-id -i ~/.ssh/id_rsa.pub gpadmin@node2.iotplatform.com

echo  "source /usr/local/greenplum-db-6.16.0/greenplum_path.sh " >> /home/gpadmin/.bashrc

# 编辑配置文件
vim /usr/local/greenplum-db-6.16.0/bin/initconfig
---------------------------------------------------------------------------
ARRAY_NAME="greenplum cluster" 
CLUSTER_NAME="greenplum cluster" 
# 所有segment主机列表 
MACHINE_LIST_FILE=allhosts
SEG_PREFIX=gpseg 
PORT_BASE=28000 
# master 节点端口 
MASTER_PORT=5432 
# segment primary数据存储目录 配置2个目录表示每个主机安装2个segment 
declare -a DATA_DIRECTORY=(/data/gpadmin/primary /data/gpadmin/primary) 
# master节点主机名或IP 
MASTER_HOSTNAME=gp01
#master数据存储目录 
MASTER_DIRECTORY=/data/gpadmin/master 
TRUSTED_SHELL=ssh 
CHECK_POINT_SEGMENTS=8 
ENCODING=UNICODE 
# 默认连接数 
DEFAULT_QD_MAX_CONNECT=1000 
######### 以下是segment mirror节点配置（可选：看你是否需要配置高可用）###### 
MIRROR_PORT_BASE=29000 
REPLICATION_PORT_BASE=30000 
MIRROR_REPLICATION_PORT_BASE=31000 
# segment mirror数据存储目录，注意mirror个数要和primary保持一致 
declare -a MIRROR_DATA_DIRECTORY=(/data/gpadmin/mirror /data/gpadmin/mirror)
---------------------------------------------------------------------------

# 初始化数据库
su - gpadmin -c "yes y|gpinitsystem -c /usr/local/greenplum-db-6.16.0/bin/initconfig -h /usr/local/greenplum-db-6.16.0/bin/allhosts"

# 全局变量,两台都要执行
echo "source /usr/local/greenplum-db-6.16.0/greenplum_path.sh
export MASTER_DATA_DIRECTORY=/data/gpadmin/master/gpseg-1" >>/home/gpadmin/.bash_profile
source /home/gpadmin/.bash_profile

su - gpadmin -c "yes y | gpstop -u"

echo "
log_filename = 'greenplum-%u.log'
log_truncate_on_rotation = on
log_rotation_size = 100MB
log_rotation_age = 1d
gp_enable_global_deadlock_detector = on
optimizer = off
" >> /data/gpadmin/master/gpseg-1/postgresql.conf

su - gpadmin -c "gpconfig -c log_statement -v ddl"
su - gpadmin -c "yes y | gpstop -M fast"
su - gpadmin -c "yes y | gpstart"


nohup /usr/local/greenplum-db/bin/gpfdist -d /user/searchlight -p 30001 -l /home/gpadmin/gpfdisk &

echo "host     all         gpadmin         0.0.0.0/0      md5
host     all         all             ::1/128      md5
host     all         all             127.0.0.1/28      md5" >> /data/gpadmin/master/gpseg-1/pg_hba.conf
1. 在两台都要做以下配置
-----------------------------------------------------------------------------------------
su - gpadmin
vim .bashrc
export MASTER_DATA_DIRECTORY=/data/gpadmin/master/gpseg-1
export PGPORT=5432

注意: 别忘记全局生效 source .bashrc
-----------------------------------------------------------------------------------------


# 启用备用机
gpinitstandby -s node2.iotplatform.com

# 查看是否启动成功
gpstate -f
20210816:20:37:45:014335 gpstate:gp01:gpadmin-[INFO]:-   Standby address          =  node2.iotplatform.com
20210816:20:37:45:014335 gpstate:gp01:gpadmin-[INFO]:-   Standby data directory   = /data/gpadmin/master/gpseg-1

当主挂掉之后,执行脚本,启动备用节点

[root@node1 ~]# cat gp_start.sh 
#!/bin/sh
IP1=node1.iotplatform.com
IP2=node2.iotplatform.com

fun1(){
    ssh gpadmin@$IP1 'yes y|gpstart'
    # 切换stanbdy节点
    ssh gpadmin@$IP1 'yes y|gpactivatestandby -d $MASTER_DATA_DIRECTORY'
    # kill掉执行上条命令生成的进程
    netstat -lntup|grep -w '0.0.0.0:5432'|awk -F '[ /]+' '{print $7}'|xargs kill
    # 启动greenplum
    ssh gpadmin@$IP1 'yes y|gpstart'
    # 备份
    ssh root@$IP2 'mv /data/gpadmin/master/gpseg-1 /data/gpadmin/master/gpseg-1-`date +%F`'
    # 生成备用节点
    ssh gpadmin@$IP1 'yes y|gpinitstandby -s node2.iotplatform.com'
    # 查看是否生成备用节点
    ssh gpadmin@$IP1 'gpstate -f'
}
fun2(){
    ssh gpadmin@$IP2 'yes y|gpstart'
    # 切换stanbdy节点
    ssh gpadmin@$IP2 'yes y|gpactivatestandby -d $MASTER_DATA_DIRECTORY'
    # kill掉执行上条命令生成的进程
    netstat -lntup|grep -w '0.0.0.0:5432'|awk -F '[ /]+' '{print $7}'|xargs kill
    # 启动greenplum
    ssh gpadmin@$IP2 'yes y|gpstart'
    # 备份
    ssh root@$IP1 'mv /data/gpadmin/master/gpseg-1 /data/gpadmin/master/gpseg-1-`date +%F`'
    # 生成备用节点
    ssh gpadmin@$IP2 'yes y|gpinitstandby -s node1.iotplatform.com'
    # 查看是否生成备用节点
    ssh gpadmin@$IP2 'gpstate -f'
}
if [ `hostname` == $IP1 ];then
fun1
elif [ `hostname` == $IP2 ];then
fun2
fi