openGauss
开源数据库
openGauss社区官网
开源社区
不使用om工具如何手工部署openGauss主从流复制环境
2020-11-27openGauss安装部署
不使用 om 工具如何手工部署 openGauss 主从流复制环境
背景
PostgresSQL 在 9.1 之后引入了主从的流复制机制，所谓流复制，就是从服务器通过 tcp 流从主服务器中同步相应的数据。这样当主服务器数据丢失时从服务器中仍有备份。与基于文件日志传送相比，流复制允许保持从服务器更新。从服务器连接主服务器，其产生的流 WAL 记录到从服务器， 而不需要等待主服务器写完 WAL 文件。
同步复制必须等主服务器和从服务器都写完 WAL 后才能提交事务。这样在一定程度上会增加事务的响应时间。配置同步复制仅需要一个额外的配置步骤：synchronous_standby_names 必须设置为一个非空值。
备注:主从服务器所在节点的系统、环境等最好一致。openGauss 版本也最好一致，否则可能会有问题。
二进制安装 openGauss
首先在三台机器上分别部署 openGauss 二进制安装包，直接解压配置环境变量就可以使用。
解压，配置环境变量。
tar zxvf openGauss-1.0.0-CentOS-64bit.tar.bz2
vi ~/.bashrc
export LD_LIBRARY_PATH=/gaussdata/opendb/lib:$LD_LIBRARY_PATH
export PATH=/gaussdata/opendb/bin:$PATH
流复制搭建
配置 pg_hba.conf 文件-主备，修改 pg_hba.conf，添加各对端白名单。
host all all ***.***.***.***/32 trust
host all all ***.***.***.***/32 trust
host all all ***.***.***.***/32 trust
配置 postgresql.conf 文件-主备。
表 1 postgresql.conf 文件-主备
重启数据库实例 。
gs_ctl stop -D gaussdata
gs_ctl start -D gaussdata -M primary
gs_ctl start -D gaussdata -M standby
gs_ctl start -D gaussdata -M standby
备机执行 build，首次配置主备，备机需要执行全量 build。
[gauss@db3 ~]$ gs_ctl build -D gaussdata/ -b full
2020-07-25 16:17:45 17913 gs_ctl:gs_ctl full build ,datadir is -D "/gaussdata"
waiting for server to shut down.... done
server stopped
[2020-07-25 16:17:46.732][17913][single_node_3][gs_ctl]:check connect to server success
[2020-07-25 16:17:47.040][17913][single_node_3][gs_ctl]:clear old target dir success
......
2020-07-25 16:17:57 17913 gs_ctl: done
2020-07-25 16:17:57 17913 gs_ctl:server started (/gaussdata)
查看流复制状态
gs_ctl query -D gaussdata/
2020-07-25 10:13:04 38232 gs_ctl:gs_ctl query ,datadir is -D "/gaussdata"
HA state:
local_role : Primary
static_connections : 2
db_state : Normal
detail_information : Normal
Senders info:
sender_pid : 55270
local_role : Primary
peer_role : Standby
peer_state : Normal
state : Streaming
sender_sent_location : 0/1002E430
sender_write_location : 0/1002E430
sender_flush_location : 0/1002E430
sender_replay_location : 0/1002E430
receiver_received_location : 0/1002E430
receiver_write_location : 0/1002E430
receiver_flush_location : 0/1002E430
receiver_replay_location : 0/1002E430
sync_percent : 100%
sync_state : Sync
sync_priority : 1
sync_most_available : Off
channel : ***.***.***.***:5433-->***.***.***.***:56956
sender_pid : 4589
local_role : Primary
peer_role : Standby
peer_state : Normal
state : Streaming
sender_sent_location : 0/1002E430
sender_write_location : 0/1002E430
sender_flush_location : 0/1002E430
sender_replay_location : 0/1002E430
receiver_received_location : 0/1002E430
receiver_write_location : 0/1002E430
receiver_flush_location : 0/1002E430
receiver_replay_location : 0/1002E430
sync_percent : 100%
sync_state : Potential
sync_priority : 1
sync_most_available : Off
channel : ***.***.***.***:5433-->***.***.***.***:35524
Receiver info:
2020-07-25 10:13:04 38232 gs_ctl:No information
pg_stat_get_wal_senders
在主节点上执行：
postgres=# \x
Expanded display is on.
postgres=# select pg_stat_get_wal_senders();
-[ RECORD 1 ]-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pg_stat_get_wal_senders | (281469756571152,40036,Primary,Standby,Normal,Streaming,"2020-07-25 15:43:33.380714+08","2020-07-25 15:43:33.485774+08",0/6844CCE0,0/6844CCE0,0/6844CCE0,0/6844CCE0,0/6844CCE0,0/6844CCE0,0/6844CCE0,0/6844CCE0,100%,Sync,1,Off,***.***.***.***:5433-->***.***.***.***:34610)
-[ RECORD 2 ]-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pg_stat_get_wal_senders | (281469722885648,15979,Primary,Standby,Normal,Streaming,"2020-07-25 15:43:33.380597+08","2020-07-25 15:43:33.493195+08",0/6844CCE0,0/6844CCE0,0/6844CCE0,0/6844CCE0,0/6844CCE0,0/6844CCE0,0/6844CCE0,0/6844CCE0,100%,Potential,1,Off,***.***.***.***:5433-->***.***.***.***:37190)
数据同步验证
主库创建测试表并插入测试数据。
postgres=# create database test1;
CREATE DATABASE
postgres=#
postgres=#
postgres=# \c test1;
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "test1" as user "gauss".
test1=# create table t1(id int);
CREATE TABLE
test1=# insert into t1 values(1);
INSERT 0 1
备库验证数据同步
test1=# select * from t1;
id
----
1
(1 row)
主备切换
关闭主库，在主库执行 gs_ctl stop 模拟主库宕机。
[gauss@db3 ~]$ gs_ctl stop -D gaussdata/
2020-07-25 15:56:30 23806 gs_ctl:gs_ctl stopped ,datadir is -D "/gaussdata"
waiting for server to shut down.... done
server stopped
备库状态
[gauss@db1 data]$ gs_ctl query -D gaussdata/
2020-07-25 15:58:52 15481 gs_ctl:gs_ctl query ,datadir is -D "/gaussdata"
HA state:
local_role : Standby
static_connections : 2
db_state : Need repair
detail_information : Connecting...
Senders info:
2020-07-25 15:58:52 15481 gs_ctl:No information
Receiver info:
2020-07-25 15:58:52 15481 gs_ctl:No information
执行 failover
[gauss@db1 data]$ gs_ctl failover -D gaussdata/
2020-07-25 16:00:37 16381 gs_ctl:gs_ctl failover ,datadir is -D "/gaussdata"
2020-07-25 16:00:37 16381 gs_ctl: waiting for server to failover...
.2020-07-25 16:00:38 16381 gs_ctl: done
2020-07-25 16:00:38 16381 gs_ctl: failover completed (/gaussdata)
查看新主库状态（备库直接连接新主库）
[gauss@db1 data]$ gs_ctl query -D gaussdata/
2020-07-25 16:00:57 24500 gs_ctl:gs_ctl query ,datadir is -D "/gaussdata"
HA state:
local_role : Primary
static_connections : 2
db_state : Normal
detail_information : Normal
Senders info:
sender_pid : 17014
local_role : Primary
peer_role : Standby
peer_state : Normal
state : Streaming
sender_sent_location : 0/68460020
sender_write_location : 0/68460020
sender_flush_location : 0/68460020
sender_replay_location : 0/68460020
receiver_received_location : 0/68460020
receiver_write_location : 0/68460020
receiver_flush_location : 0/68460020
receiver_replay_location : 0/68460020
sync_percent : 100%
sync_state : Sync
sync_priority : 1
sync_most_available : Off
channel : ***.***.***.***:5433-->***.***.***.***:36158
Receiver info:
2020-07-25 16:00:57 24500 gs_ctl:No information
老主库加入集群
[gauss@db3 ~]$ gs_ctl start -D /gaussdata -M standby
2020-07-25 16:21:39 55302 gs_ctl:gs_ctl started,datadir is -D "/gaussdata"
2020-07-25 16:21:39 55302 gs_ctl:waiting for server to start...
.0 LOG: 00000: [Alarm Module]Host Name: db3
......
2020-07-25 16:21:41 55302 gs_ctl: done
2020-07-25 16:21:41 55302 gs_ctl:server started (/gaussdata)
查看新集群状态
[gauss@db1 data]$ gs_ctl query -D /gaussdata/
2020-07-25 16:22:42 13717 gs_ctl:gs_ctl query ,datadir is -D "/gaussdata"
HA state:
local_role : Primary
static_connections : 2
db_state : Normal
detail_information : Normal
Senders info:
sender_pid : 17014
local_role : Primary
peer_role : Standby
peer_state : Normal
state : Streaming
sender_sent_location : 0/6A000128
sender_write_location : 0/6A000128
sender_flush_location : 0/6A000128
sender_replay_location : 0/6A000128
receiver_received_location : 0/6A000128
receiver_write_location : 0/6A000128
receiver_flush_location : 0/6A000128
receiver_replay_location : 0/6A000128
sync_percent : 100%
sync_state : Sync
sync_priority : 1
sync_most_available : Off
channel : ***.***.***.***:5433-->***.***.***.***:36158
sender_pid : 52804
local_role : Primary
peer_role : Standby
peer_state : Normal
state : Streaming
sender_sent_location : 0/6A000128
sender_write_location : 0/6A000128
sender_flush_location : 0/6A000128
sender_replay_location : 0/6A000128
receiver_received_location : 0/6A000128
receiver_write_location : 0/6A000128
receiver_flush_location : 0/6A000128
receiver_replay_location : 0/6A000128
sync_percent : 100%
sync_state : Potential
sync_priority : 1
sync_most_available : Off
channel : ***.***.***.***:5433-->***.***.***.***:43292
Receiver info:
2020-07-25 16:22:42 13717 gs_ctl:No information