def create_shell_scripts(self, *server):
'''此方法用于上传所需shell脚本，前提本地需存放shell脚本'''
# 主节点用postgres用户创建脚本
if server[0] == parameters['primary_node_ip']:
ftp_connectionServer(
r'%s\recovery_1st_stage' % parameters['shell_script_path'],
'%s/data/recovery_1st_stage' % self.pgpath, 2, *server)
ftp_connectionServer(
r'%s\pgpool_remote_start' % parameters['shell_script_path'],
'%s/data/pgpool_remote_start' % self.pgpath, 2, *server)
# 检查脚本是否创建成功
sf = ssh_connectionServer(*server)
sf.exec_command(
'chmod +x %s/data/{recovery_1st_stage,pgpool_remote_start}' %
self.pgpath)
res = sf.exec_command('cat %s/data/recovery_1st_stage' %
self.pgpath)
check_exec_command(res, 'exit 0', '%s创建脚本1成功' % server[0],
'%s创建脚本1失败' % server[0])
res = sf.exec_command('cat %s/data/pgpool_remote_start' %
self.pgpath)
check_exec_command(res, 'exit 0', '%s创建脚本2成功' % server[0],
'%s创建脚本2失败' % server[0])
# 因上传的shell脚本文件是dos格式，即每一行结尾以\r\n，在linux下执行需将其替换为\n
sf.exec_command(
sed_replace('\r', '',
'%s/data/recovery_1st_stage' % self.pgpath))
sf.exec_command(
sed_replace('\r', '',
'%s/data/pgpool_remote_start' % self.pgpath))
sf.close()
# 用root用户创建脚本
ftp_connectionServer(
r'%s\failover.sh' % parameters['shell_script_path'],
'%s/etc/failover.sh' % self.pgpoolpath, 2, server[0], 'root',
parameters['root_password'])
ftp_connectionServer(
r'%s\follow_master.sh' % parameters['shell_script_path'],
'%s/etc/follow_master.sh' % self.pgpoolpath, 2, server[0], 'root',
parameters['root_password'])
# 检查脚本是否创建成功
sf = ssh_connectionServer(server[0], 'root',
parameters['root_password'])
sf.exec_command('chmod +x %s/etc/{failover.sh,follow_master.sh}' %
self.pgpoolpath)
res = sf.exec_command('cat %s/etc/failover.sh' % self.pgpoolpath)
check_exec_command(res, 'exit 0', '%s创建脚本3成功' % server[0],
'%s创建脚本3失败' % server[0])
res = sf.exec_command('cat %s/etc/follow_master.sh' % self.pgpoolpath)
check_exec_command(res, 'exit 0', '%s创建脚本4成功' % server[0],
'%s创建脚本4失败' % server[0])
# 因上传的shell脚本文件是dos格式，即每一行结尾以\r\n，在linux下执行需将其替换为\n
sf.exec_command(
sed_replace('\r', '', '%s/etc/failover.sh' % self.pgpoolpath))
sf.exec_command(
sed_replace('\r', '', '%s/etc/follow_master.sh' % self.pgpoolpath))
sf.close()
def create_passwd_file(self, *server):
'''此方法创建密码文件'''
sf = ssh_connectionServer(*server)
sf.exec_command(
'echo "{0}:5432:replication:repl:{1}" >> ~/.pgpass'.format(
parameters['primary_node_ip'], parameters['dbpasswd']))
sf.exec_command(
'echo "{0}:5432:replication:repl:{1}" >> ~/.pgpass'.format(
parameters['standby01_node_ip'], parameters['dbpasswd']))
sf.exec_command(
'echo "{0}:5432:replication:repl:{1}" >> ~/.pgpass'.format(
parameters['standby02_node_ip'], parameters['dbpasswd']))
sf.exec_command('chmod 600 ~/.pgpass')
res = sf.exec_command('cat ~/.pgpass')
check_exec_command(res, '5432:replication:repl:',
'%s创建.pgpass成功' % server[0],
'%s创建.pgpass失败' % server[0])
sf.close()
# root用户创建密码
sf = ssh_connectionServer(server[0], 'root',
parameters['root_password'])
sf.exec_command("echo 'localhost:9898:pgpool:pgpool' > ~/.pcppass")
sf.exec_command("chmod 600 ~/.pcppass")
res = sf.exec_command('cat ~/.pcppass')
check_exec_command(res, 'localhost:9898:pgpool:pgpool',
'%s创建.pcppass成功' % server[0],
'%s创建.pcppass失败' % server[0])
sf.close()
def compile(local_file_path, remote_ip, username='******', passwd=''):
'''
此方法用于编译pg源码包
local_file_path:本地pg源码包文件路径(带文件名)
remote_ip:远端IP，需连接的IP
username:远端连接名
passwd:远端连接密码
'''
# 因上传的包解压后无文件格式后后缀，故先将文件格式后缀去掉，便于找到文件
res = re.search(r'(.*)\.tar', os.path.basename(local_file_path))
file = res.group(1)
sf = ssh_connectionServer(remote_ip, username, passwd)
print('%s开始configure配置......' % remote_ip)
stdin, stdout, stderr = sf.exec_command(
'cd {0};./configure --prefix={1} --enable-nls="zh_CN zh_TW"'.format(file, setup['pgpath']))
res = stdout.read().decode() + stderr.read().decode()
if re.search('configure\s*:\s*error', res):
raise Exception('%s配置出错!!!!!!\n' % remote_ip + res)
print("%s配置结束!!!!!!\n" % remote_ip + '%s开始编译......' % remote_ip)
res = sf.exec_command('cd {0};gmake world -j4'.format(file), timeout=360)
check_exec_command(res, 'successfully.*Ready to install', '%s编译成功' % remote_ip, '%s编译失败' % remote_ip)
print('%s编译结束!!!!!!\n' % remote_ip + '%s开始安装......' % remote_ip)
res = sf.exec_command('cd {0};gmake install-world'.format(file), timeout=360)
check_exec_command(res, 'installation complete', '%s安装成功' % remote_ip, '%s安装失败' % remote_ip)
print('%s安装结束!!!!!!' % remote_ip)
sf.close()
def send_package(local_file_path, remote_ip, username='******', passwd=''):
'''
此方法用于传包,将包传到/root下,并解压
local_file_path:本地文件路径(带文件名)
remote_ip:远端IP，需连接的IP
username:远端连接名
passwd:远端连接密码
'''
res = re.search(r'(.*)\.tar', os.path.basename(local_file_path))
file = res.group(1)
sf = ssh_connectionServer(remote_ip, username, passwd)
_, stdout, stderr = sf.exec_command('ls /root')
if re.search('%s' % file, stdout.read().decode()):
print('包已存在并解压')
else:
file = os.path.basename(local_file_path)
print('%s开始传包......' % remote_ip)
ftp_connectionServer(local_file_path, '/root/' + file, 2, remote_ip, username, passwd)
print('%s传包成功!!!!!!' % remote_ip)
print('%s开始解压......' % remote_ip)
res = sf.exec_command('tar -zxvf %s' % file, timeout=180)
check_exec_command(res, 'INSTALL', '%s解压完成!!!!!!' % remote_ip, '%s解压失败!!!!!!' % remote_ip)
print('%s删除压缩包' % remote_ip)
sf.exec_command('rm -rf %s' % file)
sf.close()
def check_data_status(self, *server):
'''此方检查数据库是否开启,
修改postgres密码，
创建流复制用户，
创建测试表
*server=(ip,dbusername,dbpasswd)'''
sf = ssh_connectionServer(*server)
# 初始化数据库,判断数据初始化是否成功
res = sf.exec_command(
'export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/initdb -D {0}/data'
.format(self.pgpath),
timeout=15)
check_exec_command(
res,
'exists but is not empty|Success. You can now start the database server using',
'%s数据库初始化成功' % server[0], '%s数据库初始化失败' % server[0])
# 启动数据库,判断数据库是否开启
sf.exec_command(
r'export PATH=$PATH:$PGHOME/bin:{0}/bin;export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/pg_ctl start -D {0}/data'
.format(self.pgpath))
time.sleep(3)
res = sf.exec_command(
'export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/pg_isready -d {0}/data'
.format(self.pgpath))
check_exec_command(res, 'accepting connections',
'%s数据库启动成功' % server[0], '%s数据库启动失败' % server[0])
# 修改postgres密码，创建流复制用户
res = sf.exec_command(
'''export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/psql -c "ALTER USER postgres WITH PASSWORD '123456'";'''
.format(self.pgpath))
check_exec_command(res, 'ALTER ROLE', '%s修改用户密码成功' % server[0],
'%s修改用户密码失败' % server[0])
res = sf.exec_command(
'''export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/psql -c "CREATE ROLE pgpool WITH PASSWORD '123456' LOGIN;";'''
.format(self.pgpath))
check_exec_command(res, ' role "pgpool" already exists|CREATE ROLE',
'%s创建pgpool角色成功' % server[0],
'%s创建pgpool角色失败' % server[0])
res = sf.exec_command(
'''export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/psql -c "CREATE ROLE repl WITH PASSWORD '123456' REPLICATION LOGIN";'''
.format(self.pgpath))
check_exec_command(res, 'role "repl" already exists|CREATE ROLE',
'%s创建流复制角色成功' % server[0], '%s创建角色失败' % server[0])
# 创建测试表
res = sf.exec_command(
'''export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/psql -c "CREATE TABLE tb_pgpool (id serial,age bigint,insertTime timestamp default now())";'''
.format(self.pgpath))
check_exec_command(res,
'CREATE TABLE|relation "tb_pgpool" already exists',
'%s创建表成功' % server[0], '%s创建表失败' % server[0])
# 向表中添加数据
res = sf.exec_command(
'''export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/psql -c "insert into tb_pgpool (age) values (1);";'''
.format(self.pgpath))
check_exec_command(res, 'INSERT 0 1', '%s插入数据成功' % server[0],
'%s插入数据失败' % server[0])
sf.close()
def create_extension(self, *server):
'''主节点创建扩展'''
sf = ssh_connectionServer(*server)
res = sf.exec_command(
'export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/psql -c "CREATE EXTENSION pgpool_recovery"'
.format(self.pgpath))
check_exec_command(
res, 'extension "pgpool_recovery" already exists|CREATE EXTENSION',
'主节点创建扩展成功', '主节点创建扩展失败')
sf.close()
def compile_pgpool(local_file_path, remote_ip, username='******', passwd=''):
'''
此方法用于编译pg源码包
local_file_path:本地pgpool包文件路径(带文件名)
remote_ip:远端IP，需连接的IP
username:远端连接名
passwd:远端连接密码
'''
# 因上传的包解压后无文件格式后后缀，故先将文件格式后缀去掉，便于找到文件
res = re.search(r'(.*)\.tar', os.path.basename(local_file_path))
file = res.group(1)
sf = ssh_connectionServer(remote_ip, username, passwd)
print('%s开始configure配置pgpool......' % remote_ip)
stdin, stdout, stderr = sf.exec_command(
'cd {0};./configure --prefix={1} --with-pgsql={2} '.format(
file, setup['pgpoolpath'], setup['pgpath']))
res = stdout.read().decode() + stderr.read().decode()
if re.search('configure\s*:\s*error', res):
raise Exception('%s配置pgpool出错!!!!!!' % remote_ip + '\n' + res)
print("%s配置pgpool结束!!!!!!\n" % remote_ip + '%s开始编译pgpool......' % remote_ip)
res = sf.exec_command('cd {0};make'.format(file), timeout=360)
check_exec_command(res, 'make all-am', '%s编译pgpool成功' % remote_ip, '%s编译pgpool失败' % remote_ip)
print('%s编译pgpool结束!!!!!!\n' % remote_ip + '%s开始安装pgpool......' % remote_ip)
res = sf.exec_command('cd {0};make install'.format(file), timeout=360)
check_exec_command(res, 'Making install in include', '%s安装pgpool成功' % remote_ip, '%s安装pgpool失败' % remote_ip)
print('%s编译pgpool扩展......' % remote_ip)
res = sf.exec_command('cd {0}/src/sql/pgpool-recovery;export PATH={1}/bin:$PATH;make'.format(file, setup['pgpath']),
timeout=360)
check_exec_command(res, 'pgpool-recovery.o|Nothing to be done', '%spgpool扩展编译成功' % remote_ip,
'%spgpool扩展编译失败' % remote_ip)
res = sf.exec_command(
'cd {0}/src/sql/pgpool-recovery;export PATH={1}/bin:$PATH;make install'.format(file, setup['pgpath']),
timeout=360)
check_exec_command(res, 'mkdir', '%s安装pgpool扩展成功' % remote_ip, '%s安装pgpool扩展失败' % remote_ip)
print('%s安装pgpool结束!!!!!!' % remote_ip)
sf.close()
def configure(remote_ip, username, passwd):
'''
此方法对安装后的包进行配置，包括创建用户，设置环境变量等
remote_ip:远端IP，需连接的IP
username:远端连接名
passwd:远端连接密码
'''
sf = Ssh(remote_ip)
# 创建用户和密码
print('创建postgres用户')
sf.connect(username, passwd)
sf.execute('groupadd postgres')
sf.execute('useradd postgres -g postgres')
print('修改密码......')
sf.interact([('passwd postgres', 'New password:'******'%s' % setup['postgrespasswd'], 'Retype new password:'******'%s' % setup['postgrespasswd'], '#')], timeout=5)
print('修改文件夹权限......')
sf.execute('chown postgres:postgres -R %s' % setup['pgpath'])
# 若有pgpool参数,添加pgpool环境变量
if setup['pgpoolpath']:
res = sf.execute('cat ~/.bash_profile')
if not re.search('%s' % setup['pgpoolpath'], res):
print('添加pgpool环境变量')
sf.execute('echo "export PATH={0}/bin:\$PATH" >> ~/.bash_profile'.format(setup['pgpoolpath']))
sf.execute('source ~/.bash_profile')
# 用postgres用户登录修改环境变量配置
sf = ssh_connectionServer(remote_ip, setup['pgusername'], setup['postgrespasswd'])
# 若环境变量中无所需变量，则加入环境变量中
_, stdout, stderr = sf.exec_command('cat ~/.bash_profile')
res = stdout.read().decode()
if not re.search('export PGHOME', res):
print('添加环境变量')
sf.exec_command('echo "export PGHOME=%s" >> ~/.bash_profile' % setup['pgpath'])
sf.exec_command('echo "export PGDATA=%s/data" >> ~/.bash_profile' % setup['pgpath'])
sf.exec_command('echo "export PATH=\$PGHOME/bin:\$PATH:\$HOME/bin" >> ~/.bash_profile')
sf.exec_command('echo "export LD_LIBRARY_PATH=\$PGHOME/lib:\$LD_LIBRARY_PATH" >> ~/.bash_profile')
sf.exec_command('source ~/.bash_profile')
sf.close()
def create_md5(self, *server):
'''此方法用于生成md5加密文本'''
sf = ssh_connectionServer(*server)
sf.exec_command('%s/bin/pg_md5 -u postgres -m 123456 ' %
self.pgpoolpath)
sf.exec_command('%s/bin/pg_md5 -u pgpool -m 123456 ' % self.pgpoolpath)
sf.exec_command('cp {0}/etc/pcp.conf.sample {0}/etc/pcp.conf'.format(
self.pgpoolpath))
_, stdout, _ = sf.exec_command('%s/bin/pg_md5 123456 ' %
self.pgpoolpath)
md5 = stdout.read().decode()
sf.exec_command('echo "postgres:{0}" >> {1}/etc/pcp.conf'.format(
md5, self.pgpoolpath))
time.sleep(0.1)
sf.exec_command('echo "pgpool:{0}" >> {1}/etc/pcp.conf'.format(
md5, self.pgpoolpath))
# 检查是否创建成功
res = sf.exec_command('cat %s/etc/pool_passwd' % self.pgpoolpath)
check_exec_command(res, r'postgres:.*\n*.*pgpool:',
'%s创建md5成功' % server[0], '%s创建md5失败' % server[0])
res = sf.exec_command('cat %s/etc/pcp.conf' % self.pgpoolpath)
check_exec_command(res, r'postgres:.*\n*.*pgpool:',
'%s创建md5成功' % server[0], '%s创建md5失败' % server[0])
def change_postgresql_conf_parameters(self, *server):
'''此方法用于修改postgresql.conf文件和pg_hba.conf文件'''
sf = ssh_connectionServer(*server)
postgresql_conf = self.pgpath + '/data/postgresql.conf'
sf.exec_command(
sed_replace("#logging_collector = off", "logging_collector = on",
postgresql_conf))
sf.exec_command(
sed_replace("#listen_addresses = 'localhost'",
"listen_addresses = '*'", postgresql_conf))
sf.exec_command(
sed_replace("#archive_mode = off", "archive_mode = on",
postgresql_conf))
# 创建归档日志需要的文件夹
sf.exec_command('mkdir {}/archivedir'.format(self.pgpath))
sf.exec_command(
sed_replace(
"#archive_command = ''",
"archive_command ='cp %p {}/archivedir/%f'".format(
self.pgpath), postgresql_conf))
sf.exec_command(
sed_replace("#max_wal_senders = 10", "max_wal_senders = 10",
postgresql_conf))
sf.exec_command(
sed_replace("#max_replication_slots = 10",
"max_replication_slots = 10", postgresql_conf))
sf.exec_command(
sed_replace("#wal_level = replica", "wal_level = replica",
postgresql_conf))
# pg_hba.conf需要修改一个参数，就不单独写方法，添加到此方法一起就行了
# 要配置子网掩码，将VIP拆分,IP的主机号变为0
ip_list = parameters['delegate_ip'].split('.')[0:3]
ip_list.append('0')
netaddress = '.'.join(ip_list) + '/24'
sf.exec_command(
'echo "host all all {0} trust" >> {1}/data/pg_hba.conf'
.format(netaddress, self.pgpath))
sf.exec_command(
'echo "host all all 0.0.0.0/0 trust" >> {1}/data/pg_hba.conf'
.format(netaddress, self.pgpath))
# 数据库参数修改后重启生效
stdin, stdout, stderr = sf.exec_command(
r'export PATH=$PATH:$PGHOME/bin:{0}/bin;export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/pg_ctl stop -D {0}/data'
.format(self.pgpath),
timeout=4)
# 此处需打印输出才能重启，而且不能用restart，不知为何！！！
print(stdout.read(), stderr.read())
time.sleep(1)
_, _, _ = sf.exec_command(
r'export PATH=$PATH:$PGHOME/bin:{0}/bin;export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/pg_ctl start -D {0}/data'
.format(self.pgpath),
timeout=4)
time.sleep(1)
res = sf.exec_command(
'export LD_LIBRARY_PATH={0}/lib:$LD_LIBRARY_PATH;{0}/bin/pg_isready -d {0}/data'
.format(self.pgpath))
check_exec_command(res, 'accepting connections',
'%s数据库重启成功' % server[0], '%s数据库重启失败' % server[0])
print('%s修改postgresql.conf和pg_hba.conf文件成功' % server[0])
sf.close()
def basic_setting(self, *server, **three_ip):
'''
此方法root用户登录用于改一些基础配置，如关闭防火墙，节点互信等
'''
sf = ssh_connectionServer(*server)
# 关闭防火墙
sf.exec_command('systemctl stop firewalld.service')
sf.exec_command('service firewalld stop')
sf.exec_command('systemctl disable firewalld.service')
# 创建所需文件，其中wal归档文件夹后面方法单独创建
sf.exec_command('chmod 777 %s' % self.pgpath)
sf.exec_command('mkdir {0}/log/ && touch {0}/log/pgpool.log'.format(
self.pgpoolpath))
sf.close()
# 创建root间节点互互信
sf = Ssh(server[0])
sf.connect(server[1], server[2])
# 判断节点间是否已互信,若未互信，建立互信
for ip in three_ip.values():
res = sf.execute(
'ssh root@%s -o PreferredAuthentications=publickey -o StrictHostKeyChecking=no "echo SSHOK"'
% ip)
if not re.search('SSHOK', res):
res = sf.execute('ls /root/.ssh/id_rsa')
if re.search('No such file or directory', res):
sf.interact([(
'ssh-keygen -t rsa',
'Enter file in which to save the key (/root/.ssh/id_rsa):'
), ('', 'Enter passphrase (empty for no passphrase):'),
('', 'Enter same passphrase again:'),
('', '#')])
try:
sf.interact([
('ssh-copy-id -i .ssh/id_rsa.pub root@%s' % ip,
'password:'******'%s' % parameters['root_password'], '#'),
])
except Exception:
sf.interact([
('ssh-copy-id -i .ssh/id_rsa.pub root@%s' % ip,
'(yes/no)'),
('yes', 'password:'******'%s' % parameters['root_password'], '#'),
])
else:
print('{0}与{1}root节点互信建立成功'.format(server[0], ip))
else:
print("{0}与{1}root节点原本已互信".format(server[0], ip))
# 创建postgres间节点互互信
sf = Ssh(server[0])
sf.connect(parameters['dbusername'], parameters['dbpasswd'])
for ip in three_ip.values():
res = sf.execute(
'ssh postgres@%s -o PreferredAuthentications=publickey -o StrictHostKeyChecking=no "echo SSHOK"'
% ip)
if not re.search('SSHOK', res):
res = sf.execute('ls /home/postgres/.ssh/id_rsa')
if re.search('No such file or directory', res):
r = sf.interact([(
'ssh-keygen -t rsa',
'Enter file in which to save the key (/home/postgres/.ssh/id_rsa):'
), ('', 'Enter passphrase (empty for no passphrase):'),
('', 'Enter same passphrase again:'),
('', '$')])
try:
sf.interact([
('ssh-copy-id -i .ssh/id_rsa.pub postgres@%s' % ip,
'password:'******'%s' % parameters['dbpasswd'], '$'),
])
except Exception:
sf.interact([
('ssh-copy-id -i .ssh/id_rsa.pub postgres@%s' % ip,
'(yes/no)'),
('yes', 'password:'******'%s' % parameters['dbpasswd'], '$'),
])
else:
print('{0}与{1}postgres节点互信建立成功'.format(server[0], ip))
else:
print("{0}与{1}postgres节点原本已互信".format(server[0], ip))
def change_pgpool_conf_parameters(self, *server, **three_ip):
'''此方法用于修改pgpool.conf文件参数和pool_hba.conf文件
*server接收参数为连接的服务器ip,连接的用户名，连接密码组成的元组
'''
sf = ssh_connectionServer(*server)
# 拷贝pgpool.conf文件
path_pgpool_conf = self.pgpoolpath + '/etc'
sf.exec_command('cd {0};cp pgpool.conf.sample pgpool.conf '.format(
path_pgpool_conf))
pgpool_conf = self.pgpoolpath + '/etc/pgpool.conf'
# 修改相应参数
sf.exec_command(
sed_replace("listen_addresses = 'localhost'",
"listen_addresses = '*'", pgpool_conf))
sf.exec_command(
sed_replace("sr_check_user = '******'", "sr_check_user = '******'",
pgpool_conf))
sf.exec_command(
sed_replace("health_check_period = 0", "health_check_period = 5",
pgpool_conf))
sf.exec_command(
sed_replace("health_check_timeout = 20",
"health_check_timeout = 30", pgpool_conf))
sf.exec_command(
sed_replace("health_check_user = '******'",
"health_check_user = '******'", pgpool_conf))
sf.exec_command(
sed_replace("health_check_max_retries = 0",
"health_check_max_retries = 3", pgpool_conf))
sf.exec_command(
sed_replace("backend_hostname0 = 'localhost'",
"backend_hostname0 = '%s'" % server[0], pgpool_conf))
# 端口号配置为固定的5432
sf.exec_command(
sed_replace("backend_port0 = 5432", "backend_port0 = 5432",
pgpool_conf))
sf.exec_command(
sed_replace("backend_weight0 = 1", "backend_weight0 = 1",
pgpool_conf))
# 此处数据库默认名称为data
sf.exec_command(
sed_replace("backend_data_directory0 = '/var/lib/pgsql/data'",
"backend_data_directory0 = '%s/data'" % self.pgpath,
pgpool_conf))
sf.exec_command(
sed_replace("backend_flag0 = 'ALLOW_TO_FAILOVER'",
"backend_flag0 = 'ALLOW_TO_FAILOVER'", pgpool_conf))
sf.exec_command(
sed_replace("#backend_hostname1 = 'host2'",
"backend_hostname1 = '%s'" % self.standby01_node,
pgpool_conf))
sf.exec_command(
sed_replace("#backend_port1 = 5433", "backend_port1 = 5432",
pgpool_conf))
sf.exec_command(
sed_replace("#backend_data_directory1 = '/data1'",
"backend_data_directory1 = '%s/data'" % self.pgpath,
pgpool_conf))
sf.exec_command(
sed_replace("#backend_flag1 = 'ALLOW_TO_FAILOVER'",
"backend_flag1 = 'ALLOW_TO_FAILOVER'", pgpool_conf))
# 添加backend_flag2的一些参数
sf.exec_command(
sed_add(
"backend_flag1 = 'ALLOW_TO_FAILOVER'",
"backend_hostname2 = '%s'\\nbackend_port2 = 5432\\nbackend_weight2 = 1"
% self.standby02_node, pgpool_conf))
sf.exec_command(
sed_add(
"backend_weight2",
"backend_data_directory2 = '%s/data'\\nbackend_flag2 = 'ALLOW_TO_FAILOVER'"
% self.pgpath, pgpool_conf))
sf.exec_command(
sed_replace(
"failover_command = ''",
"failover_command = '/opt/pgpool-406/etc/failover.sh %d %h %p %D %m %H %M %P %r %R'",
pgpool_conf))
sf.exec_command(
sed_replace(
"follow_master_command = ''",
"follow_master_command = '/opt/pgpool-406/etc/follow_master.sh %d %h %p %D %m %M %H %P %r %R'",
pgpool_conf))
sf.exec_command(
sed_replace("recovery_user = '******'",
"recovery_user = '******'", pgpool_conf))
sf.exec_command(
sed_replace("recovery_1st_stage_command = ''",
"recovery_1st_stage_command = 'recovery_1st_stage'",
pgpool_conf))
sf.exec_command(
sed_replace("enable_pool_hba = off", "enable_pool_hba = on",
pgpool_conf))
sf.exec_command(
sed_replace("use_watchdog = off", "use_watchdog = on",
pgpool_conf))
sf.exec_command(
sed_replace("delegate_IP = ''",
"delegate_IP = '%s'" % self.delegate_ip, pgpool_conf))
sf.exec_command(
sed_replace(
r"if_up_cmd = 'ip addr add \$_IP_\$/24 dev eth0 label eth0:0'",
r"if_up_cmd = 'ip addr add \$_IP_\$/24 dev ens33 label ens33:0'",
pgpool_conf))
sf.exec_command(
sed_replace(r"if_down_cmd = 'ip addr del \$_IP_\$/24 dev eth0'",
r"if_down_cmd = 'ip addr del \$_IP_\$/24 dev ens33'",
pgpool_conf))
sf.exec_command(
sed_replace(r"arping_cmd = 'arping -U \$_IP_\$ -w 1'",
r"arping_cmd = 'arping -U \$_IP_\$ -w 1 -I ens33'",
pgpool_conf))
sf.exec_command(
sed_replace("wd_hostname = ''", "wd_hostname = '%s'" % server[0],
pgpool_conf))
# 去除本机IP，剩下其余两个IP，便于配置other_pgpool和heartbeat_distination等参数
Other_2ip = [] # 用来装除了本机IP以外的两个IP
for other_two_ip in three_ip.values():
if server[0].strip() != other_two_ip:
Other_2ip.append(other_two_ip)
sf.exec_command(
sed_replace("#other_pgpool_hostname0 = 'host0'",
"other_pgpool_hostname0 = '%s'" % Other_2ip[0],
pgpool_conf))
sf.exec_command(
sed_replace("#other_pgpool_port0 = 5432",
"other_pgpool_port0 = 9999", pgpool_conf))
sf.exec_command(
sed_replace("#other_wd_port0 = 9000", "other_wd_port0 = 9000",
pgpool_conf))
sf.exec_command(
sed_replace("#other_pgpool_hostname1 = 'host1'",
"other_pgpool_hostname1 = '%s'" % Other_2ip[1],
pgpool_conf))
sf.exec_command(
sed_replace("#other_pgpool_port1 = 5432",
"other_pgpool_port1 = 9999", pgpool_conf))
sf.exec_command(
sed_replace("#other_wd_port1 = 9000", "other_wd_port1 = 9000",
pgpool_conf))
sf.exec_command(
sed_replace("heartbeat_destination0 = 'host0_ip1'",
"heartbeat_destination0 = '%s'" % Other_2ip[0],
pgpool_conf))
sf.exec_command(
sed_replace("#heartbeat_destination1 = 'host0_ip2'",
"heartbeat_destination1 = '%s'" % Other_2ip[1],
pgpool_conf))
sf.exec_command(
sed_replace("#heartbeat_destination_port1 = 9694",
"heartbeat_destination_port1 = 9694", pgpool_conf))
sf.exec_command(
sed_replace("#heartbeat_device1 = ''", "heartbeat_device1 = ''",
pgpool_conf))
sf.exec_command(
sed_replace("log_destination = 'stderr'",
"log_destination = 'stderr,syslog'", pgpool_conf))
sf.exec_command(
sed_replace("syslog_facility = 'LOCAL0'",
"syslog_facility = 'LOCAL1'", pgpool_conf))
sf.exec_command(
sed_replace("pid_file_name = '/var/run/pgpool/pgpool.pid'",
"pid_file_name = '%s/pgpool.pid'" % self.pgpoolpath,
pgpool_conf))
sf.exec_command(
sed_replace("logdir = '/var/log/pgpool'",
"logdir = '%s'" % self.pgpoolpath, pgpool_conf))
sf.exec_command(
sed_replace(
"memqcache_oiddir = '/var/log/pgpool/oiddir'",
"memqcache_oiddir = '%s/pgpool/oiddir'" % self.pgpoolpath,
pgpool_conf))
# pool_hba.conf文件只需向里追加两行参数，在此就不另外写方法了
sf.exec_command(
'cp {0}/etc/pool_hba.conf.sample {0}/etc/pool_hba.conf'.format(
self.pgpoolpath))
sf.exec_command(
'echo "host all pgpool 0.0.0.0/0 md5">>{0}/etc/pool_hba.conf'
.format(self.pgpoolpath))
sf.exec_command(
'echo "host all {0} 0.0.0.0/0 md5">>{1}/etc/pool_hba.conf'
.format(parameters['dbusername'], self.pgpoolpath))
print('%s修改配置文件pgpool.conf和pool_hba.conf成功' % server[0])
sf.close() # 关闭ssh连接对象
