不使用华为om工具如何手工部署openGauss主从流复制环境

背景

PostgresSQL在9.1之后引入了主从的流复制机制，所谓流复制，就是从服务器通过tcp流从主服务器中同步相应的数据。这样当主服务器数据丢失时从服务器中仍有备份。与基于文件日志传送相比，流复制允许保持从服务器更新。从服务器连接主服务器，其产生的流WAL记录到从服务器，而不需要等待主服务器写完WAL文件。

同步复制必须等主服务器和从服务器都写完WAL后才能提交事务。这样在一定程度上会增加事务的响应时间。配置同步复制仅需要一个额外的配置步骤：synchronous_standby_names必须设置为一个非空值。

备注:主从服务器所在节点的系统、环境等最好一致。openGauss版本也最好一致，否则可能会有问题。

系统环境信息

二进制安装openGauss
首先在三台机器上分别部署openGauss二进制安装包，直接解压配置环境变量就可以使用。

解压，配置环境变量
tar zxvf openGauss-1.0.0-CentOS-64bit.tar.bz2vi ~/.bashrcexport LD_LIBRARY_PATH=/gaussdata/opendb/lib:$LD_LIBRARY_PATHexport PATH=/gaussdata/opendb/bin:$PATH

流复制搭建
配置pg_hba.conf文件-主备
修改pg_hba.conf，添加各对端白名单。
host    all         all           192.168.1.1/32         trusthost    all         all           192.168.1.2/32         trusthost    all         all           192.168.1.3/32         trust

配置postgresql.conf文件-主备

参数
参数（供参考，按实际配置）
参数描述
listen_addresses
*
监听所有IP
port
5432
监听端口
local_bind_address
监听IP地址
本机监听地址
pgxc_node_name
DN的nodename

enable_data_replicate
off

replication_type
1

application_name
DN的nodename

replconninfo1
localhost=192.168.1.1 localport=5433 localservice=5434 remotehost=192.168.1.3 remoteport=5433 remoteservice=5434
主备的流复制参数，最多支持6个备机，因此最多可配6个通道。无论准备，分别配置对端的所有DN的复制通道，无先后顺序区别。
replconninfo2
localhost=192.168.1.1 localport=5433 localservice=5434 remotehost=192.168.1.2 remoteport=5433 remoteservice=5434

max_connections
800
这个设置要注意下，从库的max_connections必须要大于主库的
remote_read_mode
non_authentication
因为不带om安装不会安装ssl控件，该参数必须设置为不认证，否则以-M primary/standby方式启动主备库会因为启动复制线程认证失败而造成数据库无法启动。

重启数据库实例
gs_ctl stop -D /gaussdatags_ctl start -D /gaussdata -M primarygs_ctl start -D /gaussdata -M standbygs_ctl start -D /gaussdata -M standby

备机执行build
首次配置主备，备机需要执行全量build
[gauss@db3 ~]$ gs_ctl build -D /gaussdata/ -b full2020-07-25 16:17:45  17913 gs_ctl:gs_ctl full build ,datadir is -D "/gaussdata"waiting for server to shut down.... doneserver 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: done2020-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 : Primarystatic_connections : 2db_state : Normaldetail_information : Normal Senders info: sender_pid : 55270local_role : Primarypeer_role : Standbypeer_state : Normalstate : Streamingsender_sent_location : 0/1002E430sender_write_location : 0/1002E430sender_flush_location : 0/1002E430sender_replay_location : 0/1002E430receiver_received_location : 0/1002E430receiver_write_location : 0/1002E430receiver_flush_location : 0/1002E430receiver_replay_location : 0/1002E430sync_percent : 100%sync_state : Syncsync_priority : 1sync_most_available : Offchannel : 192.168.1.2:5433-->192.168.1.3:56956sender_pid : 4589local_role : Primarypeer_role : Standbypeer_state : Normalstate : Streamingsender_sent_location : 0/1002E430sender_write_location : 0/1002E430sender_flush_location : 0/1002E430sender_replay_location : 0/1002E430receiver_received_location : 0/1002E430receiver_write_location : 0/1002E430receiver_flush_location : 0/1002E430receiver_replay_location : 0/1002E430sync_percent : 100%sync_state : Potentialsync_priority : 1sync_most_available : Offchannel : 192.168.1.2:5433-->192.168.1.1:35524 Receiver info: 2020-07-25 10:13:04 38232 gs_ctl:No information

pg_stat_get_wal_senders
在主节点上执行：
postgres=# \xExpanded 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,192.168.1.3:5433-->192.168.1.2: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,192.168.1.3:5433-->192.168.1.1:37190)

数据同步验证
主库创建测试表并插入测试数据。
postgres=# create database test1;CREATE DATABASEpostgres=#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 TABLEtest1=# 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.... doneserver 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 : Standbystatic_connections : 2db_state : Need repairdetail_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: done2020-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 : Primarystatic_connections : 2db_state : Normaldetail_information : Normal Senders info: sender_pid : 17014local_role : Primarypeer_role : Standbypeer_state : Normalstate : Streamingsender_sent_location : 0/68460020sender_write_location : 0/68460020sender_flush_location : 0/68460020sender_replay_location : 0/68460020receiver_received_location : 0/68460020receiver_write_location : 0/68460020receiver_flush_location : 0/68460020receiver_replay_location : 0/68460020sync_percent : 100%sync_state : Syncsync_priority : 1sync_most_available : Offchannel : 192.168.1.1:5433-->192.168.1.2:36158 Receiver info: 2020-07-25 16:00:57 24500 gs_ctl:No information

老主库加入集群
[gauss@db3 ~]$  gs_ctl start -D /gaussdata  -M standby2020-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: done2020-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 : Primarystatic_connections : 2db_state : Normaldetail_information : Normal Senders info: sender_pid : 17014local_role : Primarypeer_role : Standbypeer_state : Normalstate : Streamingsender_sent_location : 0/6A000128sender_write_location : 0/6A000128sender_flush_location : 0/6A000128sender_replay_location : 0/6A000128receiver_received_location : 0/6A000128receiver_write_location : 0/6A000128receiver_flush_location : 0/6A000128receiver_replay_location : 0/6A000128sync_percent : 100%sync_state : Syncsync_priority : 1sync_most_available : Offchannel : 192.168.1.1:5433-->192.168.1.2:36158sender_pid : 52804local_role : Primarypeer_role : Standbypeer_state : Normalstate : Streamingsender_sent_location : 0/6A000128sender_write_location : 0/6A000128sender_flush_location : 0/6A000128sender_replay_location : 0/6A000128receiver_received_location : 0/6A000128receiver_write_location : 0/6A000128receiver_flush_location : 0/6A000128receiver_replay_location : 0/6A000128sync_percent : 100%sync_state : Potentialsync_priority : 1sync_most_available : Offchannel : 192.168.1.1:5433-->192.168.1.3:43292 Receiver info: 2020-07-25 16:22:42 13717 gs_ctl:No information

推荐资讯

推荐站点