GreenPlum数据库Master节点搭建Standby
Feb162016
GreenPlum数据库对于数据的冗余，也支持类似Oracle数据库物理DataGuard的数据冗余机制，Master的数据库镜像称为Standby，Segment节点的数据库镜像称之为Mirror，本文主要介绍如何为没有做Standby的Master节点添加Standby。
需要注意，在为Master节点搭建Standby的过程中，GreenPlum会自动关闭数据库，并以utility模式打开Master节点，然后修改gp_segment_configuration字典中增加Standby的信息，然后再关闭Master节点，将Master的数据拷贝到Standby节点，最后启动数据库，所以，在为Master节点添加Standby时，需要在空闲时段进行，否则会影响业务。
在搭建Standby的时候，Standby主机需要先安装GreenPlum数据库软件，这和正常安装GreenPlum数据库软件没有区别，从规范化考虑，尽量使用和Master主机同样的安装路径，下面简单结束安装过程。
1.创建gpadmin用户及安装目录
[root@mdw-std ~]# groupadd -g 520 gpadmin [root@mdw-std ~]# useradd -u 520 -g gpadmin gpadmin [root@mdw-std ~]# passwd gpadmin Changing password for user gpadmin. New password: BAD PASSWORD: it is based on a dictionary word BAD PASSWORD: is too simple Retype new password: passwd: all authentication tokens updated successfully. [root@mdw-std ~]# mkdir -p /gpdb/app [root@mdw-std ~]# mkdir -p /gpdb/gpdata [root@mdw-std ~]# chown -R gpadmin:gpadmin /gpdb/
2.配置hosts文件，添加所有主机的信息
[gpadmin@mdw config]$ vi /etc/hosts 10.9.15.20 mdw 10.9.15.22 mdw-std 10.9.15.24 sdw1 10.9.15.26 sdw2 10.9.15.28 sdw3
除Standby主机外，其他所有主机(Master节点和所有Segment节点)的hosts文件均需要添加Standby主机的信息，也就是所有GreenPlum数据库包含的主机的hosts文件都需要包含以上hosts文件中的所有主机信息。
3.上传安装文件及解压
[gpadmin@mdw-std gpdb]$ scp 10.9.15.20:/gpdb/*.zip . The authenticity of host '10.9.15.20 (10.9.15.20)' can't be established. RSA key fingerprint is 61:72:68:57:16:28:40:d4:bc:9e:68:f0:bc:ac:65:e9. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.9.15.20' (RSA) to the list of known hosts. gpadmin@10.9.15.20's password: greenplum-db-4.3.6.2-build-1-RHEL5-x86_64.zip 100% 134MB 133.7MB/s 00:01 [gpadmin@mdw-std gpdb]$ unzip greenplum-db-4.3.6.2-build-1-RHEL5-x86_64.zip Archive: greenplum-db-4.3.6.2-build-1-RHEL5-x86_64.zip inflating: README_INSTALL inflating: greenplum-db-4.3.6.2-build-1-RHEL5-x86_64.bin
4.配置内核参数，添加以下内容
[root@mdw-std ~]# vi /etc/sysctl.conf kernel.shmmax = 500000000 kernel.shmall = 4000000000 kernel.shmmni = 4096 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.ip_forward = 0 net.ipv4.conf.default.accept_source_route = 0 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_max_syn_backlog = 4096 net.ipv4.conf.all.arp_filter = 1 net.ipv4.conf.default.arp_filter = 1 net.ipv4.ip_local_port_range = 1025 65535 net.core.netdev_max_backlog = 10000 vm.overcommit_memory = 2
通过sysctl命令使之生效。
[root@mdw-std ~]# sysctl -p
5.配置资源限额(limit)
[root@mdw-std ~]# vi /etc/security/limits.conf gpadmin soft nofile 65536 gpadmin hard nofile 65536 gpadmin soft nproc 131072 gpadmin hard nproc 131072
6.修改磁盘模式，/boot/grub/menu.lst文件增加elevator=deadline。
[root@mdw-std ~]# echo deadline > /sys/block/sda/queue/scheduler
[root@mdw-std ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
[root@mdw-std ~]# vi /boot/grub/menu.lst
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE: You have a /boot partition. This means that
# all kernel and initrd paths are relative to /boot/, eg.
# root (hd0,0)
# kernel /vmlinuz-version ro root=/dev/sda3
# initrd /initrd-[generic-]version.img
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title Oracle Linux Server Unbreakable Enterprise Kernel (3.8.13-16.2.1.el6uek.x86_64)
root (hd0,0)
kernel /vmlinuz-3.8.13-16.2.1.el6uek.x86_64 ro root=UUID=1fb9c4cf-3cf4-47db-bf4a-0a87958d477d rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16 KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM rhgb quiet
initrd /initramfs-3.8.13-16.2.1.el6uek.x86_64.img
title Oracle Linux Server Red Hat Compatible Kernel (2.6.32-431.el6.x86_64)
root (hd0,0)
kernel /vmlinuz-2.6.32-431.el6.x86_64 ro root=UUID=1fb9c4cf-3cf4-47db-bf4a-0a87958d477d rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16 crashkernel=auto KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM elevator=deadline rhgb quiet
initrd /initramfs-2.6.32-431.el6.x86_64.img
7.配置磁盘预读块
[root@mdw-std ~]# /sbin/blockdev --setra 65535 /dev/sda3
8.安装GreenPlum数据库软件
[gpadmin@mdw-std gpdb]$ /bin/bash greenplum-db-4.3.6.2-build-1-RHEL5-x86_64.bin
跳过一堆的许可信息后，会提示license相关的信息。
******************************************************************************** Do you accept the Pivotal Database license agreement? [yes|no] ********************************************************************************
输入yes确认后，会提示安装路径相关信息。
yes ******************************************************************************** Provide the installation path for Greenplum Database or press ENTER to accept the default installation path: /usr/local/greenplum-db-4.3.6.2 ********************************************************************************
这里使用和Master相同的安装路径。
/gpdb/app ******************************************************************************** Install Greenplum Database into </gpdb/app>? [yes|no] ********************************************************************************
输入yes确认安装路径。
yes Extracting product to /gpdb/app ******************************************************************************** Installation complete. Greenplum Database is installed in /gpdb/app Pivotal Greenplum documentation is available for download at http://docs.gopivotal.com/gpdb ********************************************************************************
GreenPlum数据库软件安装完成，需要配置gpadmin用户的环境变量，增加以下内容。
[gpadmin@mdw-std app]$ vi /home/gpadmin/.bash_profile source /gpdb/app/greenplum_path.sh
使之生效。
[gpadmin@mdw-std app]$ . /home/gpadmin/.bash_profile
9.Standby主机创建数据库初始化数据存放目录及文件空间目录，需要和Master目录一样。
[gpadmin@mdw-std gpdata]$ mkdir /gpdb/gpdata/master [gpadmin@mdw-std gpdata]$ mkdir /gpdb/gpdata/fspc_master
以上均是在Standby节点的主机上操作，接下来是在Master节点的主机上操作。
10.在Master节点配置所有节点于Standby节点的ssh互信。
[gpadmin@mdw config]$ vi hostlist mdw mdw-std sdw1 sdw2 sdw3 [gpadmin@mdw config]$ gpssh-exkeys -f hostlist [STEP 1 of 5] create local ID and authorize on local host ... /home/gpadmin/.ssh/id_rsa file exists ... key generation skipped [STEP 2 of 5] keyscan all hosts and update known_hosts file [STEP 3 of 5] authorize current user on remote hosts ... send to mdw-std *** *** Enter password for mdw-std: ... send to sdw1 ... send to sdw2 ... send to sdw3 [STEP 4 of 5] determine common authentication file content [STEP 5 of 5] copy authentication files to all remote hosts ... finished key exchange with mdw-std ... finished key exchange with sdw1 ... finished key exchange with sdw2 ... finished key exchange with sdw3 [INFO] completed successfully
通过gpssh工具测试互信设置是否成功。
[gpadmin@mdw config]$ gpssh -f hostlist -e 'date' [mdw-std] date [mdw-std] Tue Feb 16 17:50:16 CST 2016 [ mdw] date [ mdw] Tue Feb 16 17:50:16 CST 2016 [ sdw2] date [ sdw2] Tue Feb 16 17:50:16 CST 2016 [ sdw1] date [ sdw1] Tue Feb 16 17:50:16 CST 2016 [ sdw3] date [ sdw3] Tue Feb 16 17:50:16 CST 2016
11.在Master节点通过gpinitstandby命令添加Standby。
[gpadmin@mdw config]$ gpinitstandby -s mdw-std The filespace locations on the master must be mapped to locations on the standby. These locations must be empty on the standby master host. The default provided is the location of the filespace on the master (except if the master and the standby are hosted on the same node or host). In most cases the defaults can be used. Enter standby filespace location for filespace fspc1 (default: /gpdb/gpdata/fspc_master/gpseg-1):
这里会提示Master节点中记录的文件空间的目录，默认和Master相同，这里可以使用和Master节点不同的路径，建议使用一样的路径。需要注意，在这个步骤之前，一定要创建和Master一样的数据库初始化目录，这里是/gpdb/gpdata/master目录，否则会报这个路径找不到的错误。如果文件空间直接使用默认路径，直接回车就可以。
> 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Validating environment and parameters for standby initialization... 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Checking for filespace directory /gpdb/gpdata/master/gpseg-1 on mdw-std 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Checking for filespace directory /gpdb/gpdata/fspc_master/gpseg-1 on mdw-std 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:------------------------------------------------------ 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Greenplum standby master initialization parameters 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:------------------------------------------------------ 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Greenplum master hostname = mdw 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Greenplum master data directory = /gpdb/gpdata/master/gpseg-1 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Greenplum master port = 5432 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Greenplum standby master hostname = mdw-std 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Greenplum standby master port = 5432 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Greenplum standby master data directory = /gpdb/gpdata/master/gpseg-1 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Greenplum update system catalog = On 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:------------------------------------------------------ 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:- Filespace locations 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:------------------------------------------------------ 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-pg_system -> /gpdb/gpdata/master/gpseg-1 20160216:17:54:26:011967 gpinitstandby:mdw:gpadmin-[INFO]:-fspc1 -> /gpdb/gpdata/fspc_master/gpseg-1 Do you want to continue with standby master initialization? Yy|Nn (default=N):
输入Y确认搭建Standby。
> y 20160216:17:54:56:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Syncing Greenplum Database extensions to standby 20160216:17:54:56:011967 gpinitstandby:mdw:gpadmin-[INFO]:-The packages on mdw-std are consistent. 20160216:17:54:56:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Adding standby master to catalog... 20160216:17:54:56:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Database catalog updated successfully. 20160216:17:54:56:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Updating pg_hba.conf file... 20160216:17:55:02:011967 gpinitstandby:mdw:gpadmin-[INFO]:-pg_hba.conf files updated successfully. 20160216:17:55:04:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Updating filespace flat files... 20160216:17:55:04:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Filespace flat file updated successfully. 20160216:17:55:04:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Starting standby master 20160216:17:55:04:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Checking if standby master is running on host: mdw-std in directory: /gpdb/gpdata/master/gpseg-1 20160216:17:55:05:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Cleaning up pg_hba.conf backup files... 20160216:17:55:11:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Backup files of pg_hba.conf cleaned up successfully. 20160216:17:55:11:011967 gpinitstandby:mdw:gpadmin-[INFO]:-Successfully created standby master on mdw-std
搭建完成，可以使用gpstate命令查看Standby的状态。
[gpadmin@mdw config]$ gpstate -s 20160216:17:55:37:012095 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -s 20160216:17:55:37:012095 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.6.2 build 1' 20160216:17:55:37:012095 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.6.2 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Nov 12 2015 23:50:28' 20160216:17:55:37:012095 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20160216:17:55:37:012095 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments... . 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:--Master Configuration & Status 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Master host = mdw 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Master postgres process ID = 2474 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Master data directory = /gpdb/gpdata/master/gpseg-1 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Master port = 5432 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Master current role = dispatch 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Greenplum initsystem version = 4.3.6.2 build 1 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Greenplum current version = PostgreSQL 8.2.15 (Greenplum Database 4.3.6.2 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Nov 12 2015 23:50:28 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Postgres version = 8.2.15 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Master standby = mdw-std 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Standby master state = Standby host passive 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:-Segment Instance Status Report 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Segment Info 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Hostname = sdw1 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Address = sdw1 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Datadir = /gpdb/gpdata/primary/gpseg0 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Port = 40000 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Status 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- PID = 2378 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Configuration reports status as = Up 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Database status = Up 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Segment Info 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Hostname = sdw2 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Address = sdw2 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Datadir = /gpdb/gpdata/primary/gpseg1 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Port = 40000 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Status 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- PID = 2362 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Configuration reports status as = Up 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Database status = Up 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Segment Info 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Hostname = sdw3 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Address = sdw3 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Datadir = /gpdb/gpdata/primary/gpseg2 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Port = 40000 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Status 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- PID = 2384 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Configuration reports status as = Up 20160216:17:55:38:012095 gpstate:mdw:gpadmin-[INFO]:- Database status = Up
也可以在Standby节点的主机上查看启动的进程来查看Standby的状态。
[gpadmin@mdw-std gpdata]$ ps -ef | grep gpadmin gpadmin 4362 1 0 17:55 ? 00:00:00 /gpdb/app/bin/postgres -D /gpdb/gpdata/master/gpseg-1 -p 5432 -b 5 -z 3 --silent-mode=true -i -M master -C -1 -x 0 -y -E gpadmin 4378 4362 0 17:55 ? 00:00:00 postgres: port 5432, master logger process gpadmin 4379 4362 0 17:55 ? 00:00:00 postgres: port 5432, startup process recovering 000000010000000000000002 gpadmin 4390 4362 0 17:55 ? 00:00:00 postgres: port 5432, wal receiver process
第一个进程是Standby的启动进程也就是守护进程，第二个进程是日志传输进程，第三个进程是数据同步进程也就是数据恢复进程，第四个进程是Standby与Master之间的通信检测进程，相当于心跳，以上进程的作用均是个人的猜测，有可能不准。
Standby搭建完成，即可在数据库中查询到Standby的信息。
[gpadmin@mdw config]$ psql -d dbdream
psql (8.2.15)
Type "help" for help.
dbdream=# select * from gp_segment_configuration;
dbid | content | role | preferred_role | mode | status | port | hostname | address | replication_port | san_mounts
------+---------+------+----------------+------+--------+-------+----------+---------+------------------+--------
1 | -1 | p | p | s | u | 5432 | mdw | mdw | |
2 | 0 | p | p | s | u | 40000 | sdw1 | sdw1 | |
3 | 1 | p | p | s | u | 40000 | sdw2 | sdw2 | |
4 | 2 | p | p | s | u | 40000 | sdw3 | sdw3 | |
5 | -1 | m | m | s | u | 5432 | mdw-std | mdw-std | |
(5 rows)
GreenPlum数据库Master节点搭建Standby：目前有2 条留言
发表评论
第6步-修改磁盘模式、第7步-.配置磁盘预读块.这两个配置的意义是什么？不同的linux系统内核版本配置的方法有什么区别？
2016-02-18 15:37第6步-修改磁盘模式 修改的是硬盘的驱动模式，greenplum建议使用deadline模式，不同的操作系统版本硬盘驱动是不同的，我用的是OEL 6.5版本的linux，默认就已经是deadline模式了。
2016-02-19 09:35第7步-.配置磁盘预读块 修改的是磁盘扇区的预读数。
都是为了提高磁盘的I/O能力