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 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 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 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 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_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 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 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 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()