安装部署opengauss主从(轻简版,非OM方式1主2从)

环境:
OS:Centos 7
DB:3.0
Python:2.7.5 我这里不使用OM安装,对python没有要求,OM安装的必须是3.6.X
主库:192.168.1.134
备库:192.168.1.135
新增备库:192.168.1.85

 

--------------------------------主库部署(192.168.1.134)----------------------------------------
1.下载安装包
https://opengauss.org/zh/download.html
我这里下载的是openGauss_3.0.0 极简版:
openGauss-3.0.0-CentOS-64bit.tar.bz2

将下载安装包上传到服务器/soft/opangauss目录下
[root@host134 opengauss]# ls -1
openGauss-3.0.0-CentOS-64bit.tar.bz2

2.安装依赖包
yum install libaio-devel
yum install flex
yum install bison
yum install ncurses-devel
yum install glibc-devel
yum install patch
yum install redhat-lsb-core
yum install readline-devel

 

3.创建相关目录,用户,组
root账号下操作
[root@host134 ~]#groupadd dbgrp -g 2000
[root@host134 ~]#useradd omm -g 2000 -u 2000
[root@host134 ~]#echo "oracle" | passwd --stdin omm
[root@host134 ~]#mkdir -p /opt/opengauss/software ##软件安装目录
[root@host134 ~]#mkdir -p /opt/opengauss/data ##数据目录
[root@host134 ~]#chown -R omm:dbgrp /opt/opengauss

 

4.解压二进制文件
[root@host134 opengauss]# pwd
/soft/opengauss
[root@host134 opengauss]# ls -al openGauss-3.0.0-CentOS-64bit.tar.bz2
-rw-r--r-- 1 root root 96246093 Apr 1 18:26 openGauss-3.0.0-CentOS-64bit.tar.bz2

[root@host134 opengauss]# su - omm
[omm@host134 opengauss]$ cd /soft/opengauss
[omm@host134 opengauss]$ tar -jxf openGauss-3.0.0-CentOS-64bit.tar.bz2 -C /opt/opengauss/software/

 

 

5.设置用户omm环境变量
su - omm
export GAUSSHOME=/opt/opengauss/software
export PATH=$GAUSSHOME/bin:$PATH
export LD_LIBRARY_PATH=$GAUSSHOME/lib:$LD_LIBRARY_PATH

6.初始化数据库
su - omm
gs_initdb --pgdata=/opt/opengauss/data --nodename=slnngk --encoding=UTF-8 --locale=en_US.UTF-8

参数说明:
--nodename=NODENAME name of single node initialized

 

 

7.修改初始化参数
pg_hba.conf文件
vi /opt/opengauss/data/pg_hba.conf

host all all 192.168.1.135/32 trust
host all all 192.168.1.134/32 trust
host all all 192.168.1.85/32 trust

host all all 0.0.0.0/0 md5 ##该句放最后面

postgresql.conf文件
vi /opt/opengauss/data/postgresql.conf

port=26000
max_connections = 4000
listen_addresses = '*'
password_encryption_type = 0
##log_directory = '/opt/opengauss/pg_log'
most_available_sync=on ##该参数需要开启 默认是off,那样的话从库停掉了 主库无法写入数据

 

8.启动数据库
gs_ctl start -D /opt/opengauss/data

gs_ctl stop -D /opt/opengauss/data

 

9.登陆
gsql -d postgres -p 26000

修改密码
openGauss=# ALTER USER omm WITH PASSWORD 'Gauss@123456';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE

 

10.查看状态
[omm@host134 data]$ gs_ctl query -D /opt/opengauss/data
[2022-07-20 15:04:26.411][4454][][gs_ctl]: gs_ctl query ,datadir is /opt/opengauss/data
HA state:
local_role : Primary
static_connections : 1
db_state : Normal
detail_information : FATAL: Forbid remote connection with initial user.
FATAL: Forbid remote connection with initial user.

sync_mode : Async

Senders info:
No information
Receiver info:
No information

看到这里local_role角色是主库Primary

 

11.创建数据库和用户用户
gsql -d postgres -p 26000

openGauss=# create database db_test with encoding 'utf8' template = template0;
CREATE DATABASE

openGauss=# create user hxl password 'Gauss@123456';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

openGauss=# grant all privileges to hxl;

12.使用开发用户登陆
[omm@host134 data]$ gsql -U hxl -d db_test -h 127.0.0.1 -p 26000
Password for user hxl:
这里输入密码

db_test=> create table public.tb_test(id number,name varchar(32));
ERROR: permission denied for schema public
DETAIL: N/A

若是没有授予grant all privileges to hxl 权限就会报这个错误

 

13.主从配置添加项目
主库添加如下项:
vi /opt/opengauss/data/postgresql.conf
在最后添加如下项目
replconninfo1='localhost=192.168.1.134 localport=26001 localheartbeatport=26005 localservice=26004 remotehost=192.168.1.135 remoteport=26001 remoteheartbeatport=26005 remoteservice=26004'

localhost为主库IP,remotehost为备库IP

 

 

14.重启动主库

主库:
gs_ctl restart -D /opt/opengauss/data -M primary

 

-----------------------------备库(192.168.1.135)--------------------------------
1.下载安装包
https://opengauss.org/zh/download.html
我这里下载的是openGauss_3.0.0 极简版:
openGauss-3.0.0-CentOS-64bit.tar.bz2

将下载安装包上传到服务器/soft/opangauss目录下
[root@host134 opengauss]# ls -1
openGauss-3.0.0-CentOS-64bit.tar.bz2

2.安装依赖包
yum install libaio-devel
yum install flex
yum install bison
yum install ncurses-devel
yum install glibc-devel
yum install patch
yum install redhat-lsb-core
yum install readline-devel

3.创建相关目录,用户,组
root账号下操作
[root@host134 ~]#groupadd dbgrp -g 2000
[root@host134 ~]#useradd omm -g 2000 -u 2000
[root@host134 ~]#echo "oracle" | passwd --stdin omm
[root@host134 ~]#mkdir -p /opt/opengauss/software ##软件安装目录
[root@host134 ~]#mkdir -p /opt/opengauss/data ##数据目录
[root@host134 ~]#chown -R omm:dbgrp /opt/opengauss

4.解压二进制文件
[root@host134 opengauss]# pwd
/soft/opengauss
[root@host134 opengauss]# ls -al openGauss-3.0.0-CentOS-64bit.tar.bz2
-rw-r--r-- 1 root root 96246093 Apr 1 18:26 openGauss-3.0.0-CentOS-64bit.tar.bz2

[root@host134 opengauss]# su - omm
[omm@host134 opengauss]$ cd /soft/opengauss
[omm@host134 opengauss]$ tar -jxf openGauss-3.0.0-CentOS-64bit.tar.bz2 -C /opt/opengauss/software/

 

5.设置用户omm环境变量
su - omm
export GAUSSHOME=/opt/opengauss/software
export PATH=$GAUSSHOME/bin:$PATH
export LD_LIBRARY_PATH=$GAUSSHOME/lib:$LD_LIBRARY_PATH

 

 

6.初始化数据库
su - omm
gs_initdb --pgdata=/opt/opengauss/data --nodename=slnngkbak --encoding=UTF-8 --locale=en_US.UTF-8

参数说明:
这里从库的nodename我们定义为slnngkbak,与主库区分
--nodename=NODENAME name of single node initialized

 

7.修改初始化参数
pg_hba.conf文件
vi /opt/opengauss/data/pg_hba.conf

host all all 192.168.1.135/32 trust
host all all 192.168.1.134/32 trust
host all all 192.168.1.85/32 trust

host all all 0.0.0.0/0 md5

postgresql.conf文件
vi /opt/opengauss/data/postgresql.conf

port=26000
max_connections = 4000
listen_addresses = '*'
password_encryption_type = 0
##log_directory = '/opt/opengauss/pg_log'
most_available_sync=on ##该参数需要开启 默认是off,那样的话从库停掉了 主库无法写入数据

 

 

8.启动数据库
gs_ctl start -D /opt/opengauss/data

 

9.添加从库参数
vi /opt/opengauss/data/postgresql.conf
在最后添加如下项目
replconninfo1='localhost=192.168.1.135 localport=26001 localheartbeatport=26005 localservice=26004 remotehost=192.168.1.134 remoteport=26001 remoteheartbeatport=26005 remoteservice=26004'

这里的localhost与remotehost 跟主库的对调

 

10.构建从库
gs_ctl stop -D /opt/opengauss/data
gs_ctl build -D /opt/opengauss/data -b full -M standby

发现重新构建会把连接数参数修改了主库的一样了
max_connections = 4000

 

 

11.尝试重启从库
gs_ctl stop -D /opt/opengauss/data
gs_ctl start -D /opt/opengauss/data -M standby

12.查看从库状态
gs_ctl query -D /opt/opengauss/data

 

------------------------新增一个备库(192.168.1.85)-------------------------------------
1.下载安装包
https://opengauss.org/zh/download.html
我这里下载的是openGauss_3.0.0 极简版:
openGauss-3.0.0-CentOS-64bit.tar.bz2

将下载安装包上传到服务器/soft/opangauss目录下
[root@host134 opengauss]# ls -1
openGauss-3.0.0-CentOS-64bit.tar.bz2

2.安装依赖包
yum install libaio-devel
yum install flex
yum install bison
yum install ncurses-devel
yum install glibc-devel
yum install patch
yum install redhat-lsb-core
yum install readline-devel

3.创建相关目录,用户,组
root账号下操作
[root@host134 ~]#groupadd dbgrp -g 2000
[root@host134 ~]#useradd omm -g 2000 -u 2000
[root@host134 ~]#echo "oracle" | passwd --stdin omm
[root@host134 ~]#mkdir -p /opt/opengauss/software ##软件安装目录
[root@host134 ~]#mkdir -p /opt/opengauss/data ##数据目录
[root@host134 ~]#chown -R omm:dbgrp /opt/opengauss

4.解压二进制文件
[root@host134 opengauss]# pwd
/soft/opengauss
[root@host134 opengauss]# ls -al openGauss-3.0.0-CentOS-64bit.tar.bz2
-rw-r--r-- 1 root root 96246093 Apr 1 18:26 openGauss-3.0.0-CentOS-64bit.tar.bz2

[root@host134 opengauss]# su - omm
[omm@host134 opengauss]$ cd /soft/opengauss
[omm@host134 opengauss]$ tar -jxf openGauss-3.0.0-CentOS-64bit.tar.bz2 -C /opt/opengauss/software/

 

5.设置用户omm环境变量
su - omm
export GAUSSHOME=/opt/opengauss/software
export PATH=$GAUSSHOME/bin:$PATH
export LD_LIBRARY_PATH=$GAUSSHOME/lib:$LD_LIBRARY_PATH

 

6.初始化数据库
su - omm
gs_initdb --pgdata=/opt/opengauss/data --nodename=slnngkbak02 --encoding=UTF-8 --locale=en_US.UTF-8

参数说明:
这里从库的nodename我们定义为slnngkbak02,与主库和之前部署的一个从库区分
--nodename=NODENAME name of single node initialized

 

7.修改初始化参数
pg_hba.conf文件
vi /opt/opengauss/data/pg_hba.conf

host all all 192.168.1.135/32 trust
host all all 192.168.1.134/32 trust
host all all 192.168.1.85/32 trust

host all all 0.0.0.0/0 md5

postgresql.conf文件
vi /opt/opengauss/data/postgresql.conf

port=26000
max_connections = 4000
listen_addresses = '*'
password_encryption_type = 0
##log_directory = '/opt/opengauss/pg_log'
most_available_sync=on ##该参数需要开启 默认是off,那样的话从库停掉了 主库无法写入数据

 

8.启动数据库
gs_ctl start -D /opt/opengauss/data

 

9.添加新从库参数
vi /opt/opengauss/data/postgresql.conf
在最后添加如下项目
replconninfo1='localhost=192.168.1.85 localport=26001 localheartbeatport=26005 localservice=26004 remotehost=192.168.1.134 remoteport=26001 remoteheartbeatport=26005 remoteservice=26004'

 

10.在主库上添加新的从库(注意:这里需要在主库上执行)
修改配置文件,添加如下项目:
vi /opt/opengauss/data/postgresql.conf

replconninfo2='localhost=192.168.1.134 localport=26001 localheartbeatport=26005 localservice=26004 remotehost=192.168.1.85 remoteport=26001 remoteheartbeatport=26005 remoteservice=26004'

重新启动主库
gs_ctl restart -D /opt/opengauss/data -M primary

11.构建新从库
gs_ctl stop -D /opt/opengauss/data
gs_ctl build -D /opt/opengauss/data -b full -M standby

 

12.尝试重启从库
gs_ctl stop -D /opt/opengauss/data
gs_ctl start -D /opt/opengauss/data -M standby

12.查看从库状态
gs_ctl query -D /opt/opengauss/data

 

 

-------------------------主备日常维护------------------------------
1.重启主库
su - omm
gs_ctl stop -D /opt/opengauss/data -M primary
gs_ctl start -D /opt/opengauss/data -M primary

 

2.主从切换(switchover)
登陆从库进行切换,我这里登陆到从库192.168.1.135上操作,将该从库提升为主库
su - omm
gs_ctl switchover -D /opt/opengauss/data

这个时候查看主备库的状态
原主库(192.168.1.134)

[omm@host134 data]$ gs_ctl query -D /opt/opengauss/data
[2022-07-20 17:09:55.820][15238][][gs_ctl]: gs_ctl query ,datadir is /opt/opengauss/data 
 HA state:
        local_role                    : Standby
        static_connections            : 1
        db_state                      : Normal
        detail_information            : FATAL:  Forbid remote connection with initial user.
FATAL:  Forbid remote connection with initial user.

        sync_mode                     : Async

 Senders info:
        No information
 Receiver info:
        No information

 

原备库(192.168.1.135)

[omm@localhost data]$ gs_ctl query -D /opt/opengauss/data
[2022-07-20 17:10:52.856][30384][][gs_ctl]: gs_ctl query ,datadir is /opt/opengauss/data 
 HA state:
        local_role                    : Primary
        static_connections            : 1
        db_state                      : Normal
        detail_information            : FATAL:  Forbid remote connection with initial user.
FATAL:  Forbid remote connection with initial user.

        sync_mode                     : Async

 Senders info:
        No information
 Receiver info:
        No information

 

原备库(192.168.1.85)

[omm@localhost ~]$ gs_ctl query -D /opt/opengauss/data
[2022-07-22 08:51:57.123][32439][][gs_ctl]: gs_ctl query ,datadir is /opt/opengauss/data 
 HA state:
        local_role                    : Standby
        static_connections            : 1
        db_state                      : Need repair
        detail_information            : FATAL:  Forbid remote connection with initial user.
FATAL:  Forbid remote connection with initial user.

        sync_mode                     : Async

 Senders info:
        No information
 Receiver info:
        No information

 

这里发现数据库状态是Need repair,原因是我们之前没有配置两个从库的主从关系.
这样的话,若是需要切换的话,在后面的2个主从上都需要添加replconninfo1和replconninfo2参数

192.168.1.134
replconninfo1='localhost=192.168.1.134 localport=26001 localheartbeatport=26005 localservice=26004 remotehost=192.168.1.135 remoteport=26001 remoteheartbeatport=26005 remoteservice=26004'
replconninfo2='localhost=192.168.1.134 localport=26001 localheartbeatport=26005 localservice=26004 remotehost=192.168.1.85 remoteport=26001 remoteheartbeatport=26005 remoteservice=26004'

192.168.1.135
replconninfo1='localhost=192.168.1.135 localport=26001 localheartbeatport=26005 localservice=26004 remotehost=192.168.1.134 remoteport=26001 remoteheartbeatport=26005 remoteservice=26004'
replconninfo2='localhost=192.168.1.135 localport=26001 localheartbeatport=26005 localservice=26004 remotehost=192.168.1.85 remoteport=26001 remoteheartbeatport=26005 remoteservice=26004'

 

192.168.1.85
replconninfo1='localhost=192.168.1.85 localport=26001 localheartbeatport=26005 localservice=26004 remotehost=192.168.1.134 remoteport=26001 remoteheartbeatport=26005 remoteservice=26004'
replconninfo2='localhost=192.168.1.85 localport=26001 localheartbeatport=26005 localservice=26004 remotehost=192.168.1.135 remoteport=26001 remoteheartbeatport=26005 remoteservice=26004'

 

重启动主从库

主库:
gs_ctl restart -D /opt/opengauss/data -M primary

 

从库:

gs_ctl stop -D /opt/opengauss/data
gs_ctl start -D /opt/opengauss/data -M standby

或是
gs_ctl restart -D /opt/opengauss/data -M standby

2.主从切换(failover)
关闭主库(192.168.1.134),可以模拟主库down机
gs_ctl stop -D /opt/opengauss/data

 

我们将其中的从库192.168.1.135 failover为主库
gs_ctl failover -D /opt/opengauss/data

这个时候之前的从库192.168.1.135变成了主库(尝试写入数据),另外一个从库192.168.1.85变成了192.168.1.135的从库

尝试将原来的主库启动(比如故障排查后),这个时候只能以备库的角色启动
gs_ctl start -D /opt/opengauss/data -M standby

这个时候发现新的数据也同步过来了.

 

问题:
发现主备已经切换,发现2个原备库的配置文件参数max_connections = 4000都发生了修改,修改成与主库一致的了.