postgresql使用RHCS套件搭建HA高可用集群

环境：

数据库服务器 2台

服务器一 操作系统：CentOS 6.8 x86_64

IP(eth0):192.168.11.61

主机名：node1

服务器二 操作系统：CentOS 6.8 x86_64

IP_1(eth0):192.168.11.62

主机名：node2

存储服务器 操作系统：CentOS 6.8 x86_64

IP_1(eth0):192.168.11.63

主机名：disk

共享盘:/dev/sdb（30G、LVM）

虚拟IP 192.168.11.69

1、共享磁盘的设置

[root@disk ~]# fdisk /dev/sdb

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel with disk identifier 0xa5927bb4.

Changes will remain in memory only, until you decide to write them.

After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to

switch off the mode (command 'c') and change display units to

sectors (command 'u').

Command (m for help): n

Command action

e extended

p primary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-2610, default 1):

Using default value 1

Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): +10G

Command (m for help): w

The partition table has been altered!

[root@node1 /]# fdisk -l

Disk /dev/sda: 53.7 GB, 53687091200 bytes

255 heads, 63 sectors/track, 6527 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0x0007f236

Device Boot Start End Blocks Id System

/dev/sda1 * 1 64 512000 83 Linux

Partition 1 does not end on cylinder boundary.

/dev/sda2 64 6528 51915776 8e Linux LVM

Disk /dev/sdb: 10.7 GB, 10737418240 bytes

255 heads, 63 sectors/track, 1305 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0x2eb7246b

Device Boot Start End Blocks Id System

/dev/sdb1 1 1305 10482381 83 Linux

Disk /dev/mapper/VolGroup-lv_root: 51.1 GB, 51078234112 bytes

255 heads, 63 sectors/track, 6209 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0x00000000

Disk /dev/mapper/VolGroup-lv_swap: 2080 MB, 2080374784 bytes

255 heads, 63 sectors/track, 252 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0x00000000

格式化磁盘

[root@node1 ~]# mkfs.ext3 /dev/sdb

mke2fs 1.41.12 (17-May-2010)

Filesystem label=

OS type: Linux

Block size=4096 (log=2)

Fragment size=4096 (log=2)

Stride=0 blocks, Stripe width=0 blocks

655360 inodes, 2620506 blocks

131025 blocks (5.00%) reserved for the super user

First data block=0

Maximum filesystem blocks=2684354560

80 block groups

32768 blocks per group, 32768 fragments per group

8192 inodes per group

Superblock backups stored on blocks:

32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632

Writing inode tables: done

Creating journal (32768 blocks): done

Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 39 mounts or

180 days, whichever comes first. Use tune2fs -c or -i to override.

2、用户及目录（两台数据库服务上都执行）：

创建 postgres用户

[root@node1 ~]# useradd postgres

创建数据库的data目录，并配置目录的权限

[root@node1 ~]# mkdir -p /home/postgres/pgsql9.6

[root@node1 ~]# mkdir -p /home/postgres/data

[root@node1 ~]# chown -R postgres:postgres /home/postgres

在postgres 用户下设置环境变量

export PATH=/home/postgres/pgsql9.6/bin:$PATH

export PGDATA=/home/postgres/data

export LD_LIBRARY_PATH=/home/postgres/pgsql9.6/lib

3、网络配置（三台机器都执行）：

root用户配置/etc/hosts文件，最终内容如下：

127.0.0.1 localhost.localdomain localhost.localdomain localhost4 localhost4.localdomain4 localhost

::1 localhost.localdomain localhost.localdomain localhost6 localhost6.localdomain6 localhost

192.168.11.61 node1

192.168.11.62 node2

192.168.11.63 disk

root用户关闭防火墙，命令如下：

[root@node1 ~]# chkconfig iptables off

[root@node1 ~]# service iptables stop

root用户编辑文件/etc/sysconfig/selinux，关闭selinux，命令如下：

[root@node1 ~]# vi /etc/sysconfig/selinux

# This file controls the state of SELinux on the system.

# SELINUX= can take one of these three values:

# enforcing - SELinux security policy is enforced.

# permissive - SELinux prints warnings instead of enforcing.

# disabled - No SELinux policy is loaded.

SELINUX= disabled

# SELINUXTYPE= can take one of these two values:

# targeted - Targeted processes are protected,

# mls - Multi Level Security protection.

SELINUXTYPE=targeted

4、SSH互信

root用户互信

[root@node1 ~]# ssh-keygen -t rsa -P ' '

[root@node1 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@node1

[root@node1 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@node2

[root@node1 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@disk

postgres用户互信

[root@node1 ~]# su - postgres

[postgres@node1 ~]$ ssh-keygen -t rsa -P ' '

[postgres@node1 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@node1

[postgres@node1 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@node2

[postgres@node1 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@disk

root用户与postgres用户互信

[root@node1 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@node1

[root@node1 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@node2

[root@node1 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@disk

[postgres@node1 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@node1

[postgres@node1 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@node2

[postgres@node1 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@disk

5、安装共享存储

共享存储采用icsci + multipath的形式。

ISCSI

存储服务器

服务器端安装scsi-target-utils包。

编辑/etc/tgt/targets.conf文件添加存储分区，添加内容如下：

<target iqn.2017-04-05.dev:server.target1>

backing-store /dev/sdb1

</target>

配置服务，命令如下：

[root@disk ~]# service tgtd start

[root@disk ~]# chkconfig tgtd on

节点服务器（两台都安装）

客户端安装iscsi-initiator-utils包。

编辑/etc/iscsi/initiatorname.iscsi文件添加存储目标，文件内容如下：

InitiatorName=iqn.2017-04-05.dev:server.target1

配置服务：

[root@node1 ~]# chkconfig iscsi on

[root@node1 ~]# chkconfig iscsid on

[root@node1 ~]# service iscsi restart

[root@node1 ~]# service iscsid restart

创建目录，联机iscsi共享盘，命令如下：

[root@node1 ~]# iscsiadm -m discovery -t sendtargets -p 192.168.11.63:3260

[root@node1 ~]# iscsiadm -m node -T iqn.2017-04-05.dev:server.target1 -l

[root@node1 ~]# mount /dev/sdb /home/postgres/data

[root@node1 ~]# chown -R postgres:postgres /home/postgres/data

6、安装postgresql数据库

安装过程：

节点一

[postgres@node1 ~]$ tar -xvf postgresql-9.6.1.tar.bz2

[postgres@node1 ~]$ cd postgresql-9.6.1

[postgres@node1 postgresql-9.6.1]$ ./configure --prefix=/home/postgres/pgsql9.6/

[postgres@node1 postgresql-9.6.1]$ make

[postgres@node1 postgresql-9.6.1]$ make install

[postgres@node1 postgresql-9.6.1]$ /home/postgres/pgsql9.6/initdb -D /home/postgres/data (注意要确保你的/dev/sdb /挂载到了/home/postgres/data 可以df -h查看一下)

节点一关闭数据库服务，umount共享盘

在节点二安装数据库，将共享盘至/home/postgres/data目录下，删除data目录下的所有文件，之后安装数据库（重复节点一安装数据库的过程）。

节点二关闭数据库服务，umount共享盘

将共享盘挂载至节点一，重启数据库服务，进行检查

7、HA软件安装配置

安装事项

存储服务器：yum安装luci

节点服务器：yum安装ricci、cman、rgmanager

这四个软件均设置为开机自启

[root@disk ~]# chkconfig luci on

[root@node1 ~]# chkconfig ricci on

[root@node1 ~]# chkconfig cman on

[root@node1 ~]# chkconfig rgmanager on

所有服务器关闭NetworkManager服务并设置为开机不启动（否则无法启动cman）

[root@node1 ~]# service NetworkManager stop

[root@node1 ~]# chkconfig NetworkManager off

节点服务器：passwd ricci设置密码

存储服务器以操作系统root用户及其密码登录web管理界面： https://localhost:8084

1.登陆

2.创建集群

• 点击面板左侧的“Manage Clusters”
• 点击“Create”

3.、添加失效域

添加完集群之后，会自动重启节点服务器。

 重新登录管理界面，点击刚创建的集群

 点击“Failover Domains”，添加失效域

4.添加资源

 点击“Resource”，添加资源

 在下拉菜单中选择“Fielsystem”，添加共享盘资源（data）

 在下拉菜单中选择“Ip Addres”，添加虚拟ip

 在下拉菜单中选择“Script”，添加数据库服务脚本，注意脚本的可执行权限

5.集群状态

6.数据库服务脚本（注意脚本权限）

[root@node1 postgres]# cat service.sh

#!/bin/bash

# environment.

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

export PGDATA=/home/postgres/data

export PGUSER=postgres

export PGDATABASE=postgres

export PGHOST=localhost

export PGPORT=5432

export PATH=$PATH:$HOME/bin:/home/postgres/pgsql9.6/bin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/postgres/pgsql9.6/lib

function start {

su - ${PGUSER} -c "pg_ctl start"

return 0

}

function stop {

su - ${PGUSER} -c "pg_ctl stop -m fast"

return 0

}

function reload {

su - ${PGUSER} -c "pg_ctl reload"

return 0

}

function status {

su - ${PGUSER} -c "pg_ctl status"

return $?

}

# See how we were called.

case "$1" in

"start")

start

exit $?

;;

"stop")

stop

exit $?

;;

"restart")

stop

start

exit $?

;;

"reload")

reload

exit $?

;;

"status")

status

exit $?

;;

*)

echo $"Usage: $prog {start|stop|restart|reload|status}"

exit 0

esac

7.服务器切换测试

将阶段一的服务器关闭，查看是否能切换到节点二上

[root@node2 postgres]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_node1-lv_root

37G 4.6G 31G 14% /

tmpfs 491M 26M 466M 6% /dev/shm

/dev/sda1 477M 35M 418M 8% /boot

/dev/sdb 20G 83M 19G 1% /home/postgres/data

[root@node2 postgres]# su - postgres

[postgres@node2 ~]$ psql

psql (9.6.1)

Type "help" for help.

postgres=#

服务器正常切换，数据库能正常使用

可以看出节点一掉了 ，服务运行中在节点二上