背景
PolarDB for PostgreSQL 三节点开源版本在3台主机上的部署例子.
https://github.com/alibaba/PolarDB-for-PostgreSQL
环境
3台ecs
8c 64g 2T ssd 内网ip: 172.17.164.62 172.17.164.63 172.17.164.64
系统环境部署
请参考这篇文档:
部署好后继续.
环境依赖
1、操作系统
cat /etc/redhat-release CentOS Linux release 7.9.2009 (Core)
2、内核
uname -a Linux iZbp18r4s9zxcmpkulkmkyZ 3.10.0-1160.31.1.el7.x86_64 #1 SMP Thu Jun 10 13:32:12 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux
3、GCC版本
gcc -v Using built-in specs. COLLECT_GCC=gcc COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-redhat-linux/4.8.5/lto-wrapper Target: x86_64-redhat-linux Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-bootstrap --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu-unique-object --enable-linker-build-id --with-linker-hash-style=gnu --enable-languages=c,c++,objc,obj-c++,java,fortran,ada,go,lto --enable-plugin --enable-initfini-array --disable-libgcj --with-isl=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/isl-install --with-cloog=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/cloog-install --enable-gnu-indirect-function --with-tune=generic --with-arch_32=x86-64 --build=x86_64-redhat-linux Thread model: posix gcc version 4.8.5 20150623 (Red Hat 4.8.5-44) (GCC)
部署步骤
1、安装依赖包
yum install -y bison flex libzstd-devel libzstd zstd cmake openssl-devel protobuf-devel readline-devel libxml2-devel libxslt-devel zlib-devel bzip2-devel lz4-devel snappy-devel python-devel unzip2、添加OS用户, 用于部署PolarDB数据库集群
useradd digoal3、设置用户密码
passwd digoal4、下载PolarDB for PostgreSQL源码
su - digoal wget https://github.com/alibaba/PolarDB-for-PostgreSQL/archive/refs/heads/master.zip unzip master.zip
5、配置PolarDB OS用户主机之间的ssh互认, 配置ssh互认后, 方便集群管理, 这也是Greenplum使用的方法.
all node:
产生ssh key
su - digoal ssh-keygen chmod 700 ~/.ssh chmod 400 ~/.ssh/id_rsa*
all node:
配置互相认证
su - digoal ssh-copy-id -f digoal@172.17.164.62 ssh-copy-id -f digoal@172.17.164.63 ssh-copy-id -f digoal@172.17.164.64 输入目标主机digoal用户登陆密码, 完成互认证
all node:
验证是否不需要密码, 返回日期表示已经完成互认
su - digoal ssh 'digoal@172.17.164.62' date ssh 'digoal@172.17.164.63' date ssh 'digoal@172.17.164.64' date
6、配置环境变量
all node:
su - digoal vi ~/.bashrc export POLARDBHOME="$HOME/polardb" export PATH="$POLARDBHOME/bin:$PATH" export LD_LIBRARY_PATH="$POLARDBHOME/lib:$LD_LIBRARY_PATH" export PGUSER=digoal export PGDATABASE=postgres export PGHOST=/tmp export PGPORT=10001
应用环境变量生效
su - digoal . ~/.bashrc
7、编译安装PolarDB for PostgreSQL二进制软件.
all node:
su - digoal cd ~/PolarDB-for-PostgreSQL-master
设置安装目录
export PG_INSTALL=$HOME/polardb其他部署详情, 可以看一下build.sh脚本的内容.
编译安装二进制软件:
sh build.sh debug ## 开发环境 或 sh build.sh deploy ## 生产环境
8、配置PolarDB 3主机集群.
创建配置文件存放目录
all node:
su - digoal mkdir $POLARDBHOME/etc
创建存放PolarDB集群数据文件的目录
su - root mkdir -p /data01/polardb/data chown -R digoal:digoal /data01/polardb chmod 700 /data01/polardb
生成集群部署配置文件模板(这个只需要在master主机执行即可, 172.17.164.62)
master node:
su - digoal touch $POLARDBHOME/etc/polardb_paxos.conf pgxc_ctl -v -c $POLARDBHOME/etc/polardb_paxos.conf prepare standalone
修改配置文件内容, 匹配我们的三主机环境
vi $POLARDBHOME/etc/polardb_paxos.conf #!/usr/bin/env bash # # polardb Configuration file for pgxc_ctl utility. # # Configuration file can be specified as -c option from pgxc_ctl command. Default is # $PGXC_CTL_HOME/pgxc_ctl.org. # # This is bash script so you can make any addition for your convenience to configure # your polardb. # #======================================================================================== # # # pgxcInstallDir variable is needed if you invoke "deploy" command from pgxc_ctl utility. # If don't you don't need this variable. # 修改 pgxcInstallDir=$HOME/polardb #---- OVERALL ----------------------------------------------------------------------------- # # 建议db superuser和os user使用同名 pgxcOwner=digoal # owner of the Postgres-XC databaseo cluster. Here, we use this # both as linus user and database user. This must be # the super user of each coordinator and datanode. pgxcUser=digoal # OS user of Postgres-XC owner tmpDir=/tmp # temporary dir used in XC servers localTmpDir=$tmpDir # temporary dir used here locally configBackup=n # If you want config file backup, specify y to this value. configBackupHost=pgxc-linker # host to backup config file configBackupDir=$HOME/pgxc # Backup directory configBackupFile=pgxc_ctl.bak # Backup file name --> Need to synchronize when original changed. # 修改 standAlone=n # 修改 dataDirRoot=/data01/polardb/data #---- Datanodes ------------------------------------------------------------------------------------------------------- #---- Shortcuts -------------- datanodeMasterDir=$dataDirRoot/dn_master datanodeSlaveDir=$dataDirRoot/dn_slave datanodeLearnerDir=$dataDirRoot/dn_learner datanodeArchLogDir=$dataDirRoot/datanode_archlog #---- Overall --------------- primaryDatanode=datanode_1 # Primary Node. datanodeNames=(datanode_1) datanodePorts=(10001) # Master and slave use the same port! #datanodePoolerPorts=(10011) # Master and slave use the same port! #datanodePgHbaEntries=(::1/128) # Assumes that all the coordinator (master/slave) accepts # the same connection # This list sets up pg_hba.conf for $pgxcOwner user. # If you'd like to setup other entries, supply them # through extra configuration files specified below. datanodePgHbaEntries=(172.17.164.62/32 172.17.164.63/32 172.17.164.64/32) # Same as above but for IPv4 connections #---- Master ---------------- datanodeMasterServers=(172.17.164.62) # none means this master is not available. # This means that there should be the master but is down. # The cluster is not operational until the master is # recovered and ready to run. datanodeMasterDirs=($datanodeMasterDir) datanodeMaxWalSender=16 # max_wal_senders: needed to configure slave. If zero value is # specified, it is expected this parameter is explicitly supplied # by external configuration files. # If you don't configure slaves, leave this value zero. datanodeMaxWALSenders=($datanodeMaxWalSender) # max_wal_senders configuration for each datanode #---- Slave ----------------- datanodeSlave=y # Specify y if you configure at least one coordiantor slave. Otherwise, the following # configuration parameters will be set to empty values. # If no effective server names are found (that is, every servers are specified as none), # then datanodeSlave value will be set to n and all the following values will be set to # empty values. datanodeSlaveServers=(172.17.164.63) # value none means this slave is not available datanodeSlavePorts=(10001) # Master and slave use the same port! #datanodeSlavePoolerPorts=(10011) # Master and slave use the same port! datanodeSlaveSync=y # If datanode slave is connected in synchronized mode datanodeSlaveDirs=($datanodeSlaveDir) datanodeArchLogDirs=($datanodeArchLogDir) datanodeRepNum=2 # no HA setting 0, streaming HA and active-active logcial replication setting 1 replication, paxos HA setting 2 replication. datanodeSlaveType=(3) # 1 is streaming HA, 2 is active-active logcial replication, 3 paxos HA. #---- Learner ----------------- datanodeLearnerServers=(172.17.164.64) # value none means this learner is not available datanodeLearnerPorts=(10001) # learner port! #datanodeSlavePoolerPorts=(10011) # learner pooler port! datanodeLearnerSync=y # If datanode learner is connected in synchronized mode datanodeLearnerDirs=($datanodeLearnerDir) # ---- Configuration files --- # You may supply your bash script to setup extra config lines and extra pg_hba.conf entries here. # These files will go to corresponding files for the master. # Or you may supply these files manually. datanodeExtraConfig=datanodeExtraConfig cat > $datanodeExtraConfig <<EOF #================================================ # Added to all the datanode postgresql.conf # Original: $datanodeExtraConfig log_destination = 'csvlog' unix_socket_directories = '., /tmp' logging_collector = on log_directory = 'log' listen_addresses = '0.0.0.0' max_connections = 1000 hot_standby = on synchronous_commit = on max_worker_processes = 30 cron.database_name = 'postgres' tcp_keepalives_idle = 30 tcp_keepalives_interval = 10 tcp_keepalives_count = 6 shared_buffers = 16GB maintenance_work_mem = 1GB bgwriter_delay = 10ms bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 5.0 effective_io_concurrency = 0 parallel_leader_participation = off max_wal_size = 48GB min_wal_size = 8GB wal_keep_segments = 4096 wal_sender_timeout = 5s random_page_cost = 1.1 effective_cache_size = 32GB log_truncate_on_rotation = on log_min_duration_statement = 3s log_checkpoints = on log_lock_waits = on log_statement = 'ddl' log_autovacuum_min_duration = 0 autovacuum_freeze_max_age = 800000000 autovacuum_multixact_freeze_max_age = 900000000 autovacuum_vacuum_cost_delay = 0ms vacuum_freeze_min_age = 700000000 vacuum_freeze_table_age = 850000000 vacuum_multixact_freeze_min_age = 700000000 vacuum_multixact_freeze_table_age = 850000000 statement_timeout = 0 # in milliseconds, 0 is disabled lock_timeout = 0 # in milliseconds, 0 is disabled idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled shared_preload_libraries = 'pg_cron' max_parallel_replay_workers = 0 EOF # Additional Configuration file for specific datanode master. # You can define each setting by similar means as above. datanodeSpecificExtraConfig=(none) datanodeSpecificExtraPgHba=(none)
9、初始化三节点集群
master node:
pgxc_ctl -c $POLARDBHOME/etc/polardb_paxos.conf clean all pgxc_ctl -c $POLARDBHOME/etc/polardb_paxos.conf init all
psql postgres=# select * from pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ pid | 18745 usesysid | 10 usename | digoal application_name | walreceiver client_addr | 172.17.164.63 client_hostname | client_port | 53338 backend_start | 2021-08-16 16:10:59.414899+08 backend_xmin | state | streaming sent_lsn | 0/4000120 write_lsn | 0/4000120 flush_lsn | 0/4000120 replay_lsn | 0/4000120 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async -[ RECORD 2 ]----+------------------------------ pid | 19166 usesysid | 10 usename | digoal application_name | walreceiver client_addr | 172.17.164.64 client_hostname | client_port | 50968 backend_start | 2021-08-16 16:11:09.975107+08 backend_xmin | state | streaming sent_lsn | 0/4000120 write_lsn | 0/4000120 flush_lsn | 0/4000120 replay_lsn | 0/4000120 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async
10、常用管理命令
检查三节点状态
pgxc_ctl -c $POLARDBHOME/etc/polardb_paxos.conf monitor all /bin/bash Installing pgxc_ctl_bash script as /home/digoal/pgxc_ctl/pgxc_ctl_bash. Installing pgxc_ctl_bash script as /home/digoal/pgxc_ctl/pgxc_ctl_bash. Reading configuration using /home/digoal/pgxc_ctl/pgxc_ctl_bash --home /home/digoal/pgxc_ctl --configuration /home/digoal/polardb/etc/polardb_paxos.conf Finished reading configuration. ******** PGXC_CTL START *************** Current directory: /home/digoal/pgxc_ctl Running: datanode master datanode_1 Running: datanode slave datanode_1 Running: datanode learner datanode_1
查看三节点配置
pgxc_ctl -c $POLARDBHOME/etc/polardb_paxos.conf show configuration allstart cluster or node
pgxc_ctl -c $POLARDBHOME/etc/polardb_paxos.conf start allstop cluster or node
pgxc_ctl -c $POLARDBHOME/etc/polardb_paxos.conf stop allfailover datanode (datanode_1 is node name configured in polardb_paxos.conf).
pgxc_ctl -c $POLARDBHOME/etc/polardb_paxos.conf failover datanode datanode_1cluster health check (check cluster status and start failed node).
pgxc_ctl -c $POLARDBHOME/etc/polardb_paxos.conf healthcheck allexamples of other commands
pgxc_ctl -c $POLARDBHOME/etc/polardb_paxos.conf kill all pgxc_ctl -c $POLARDBHOME/etc/polardb_paxos.conf log var datanodeNames pgxc_ctl -c $POLARDBHOME/etc/polardb_paxos.conf show configuration all