PostgreSQL主从流复制状态监控和自动故障转移的轻量级实现
对于PostgreSQL的高可用，之前研究过repmgr以及pg_auto_failover，起作用都是起到主节点故障时，实现“自动故障转移”的目的。
但是repmgr以及pg_auto_failover得缺点是对数据库侵入过多，需要在被监控的数据库内部进行一系列的配置操作，甚至需要专用的服务器作为仲裁方，同时需要启动第三方服务实现节点的可用性监控，这又引入了额外的不确定性因素。
如果是单纯地为了故障转移，repmgr以及pg_auto_failover都显得过于沉重，于是快速尝试了一个轻量级的实现方案
实现思路
主要思路如下：
1，自动故障转移：该方式在正常情况下，作为一个服务持续监控主从复制状态
while 1：
轮训PostgreSQL流复制的状态：
if 如果主节点不可达：
if 如果从节点可达：
if 从节点状态是in_recovery：
promote 从节点，从节点接管读写服务
sleep(n)
2，手动故障转移：如果是主从节点都正常的情况下，为了演练灾备切换，人工故障转移
#首选关闭主节点，防止主节点非standby模式运行之后，pg_rewind无法修改wal日志时间线
1，关闭原始主库
if 从节点状态是是in_recovery：
promote 从节点，从节点接管读写服务
#把原始主节点作为从节点加入集群中运行
2，尝试修复原始主库时间线
3，尝试以standby的模式启动主库优点
快速尝试了一个轻量级的实现方案，优点如下：
1，不引入或者依赖于任何第三方中间件，可以同时实现多组流复制集群的监控和故障转移。
2，不对现有PostgreSQL的复制环境做任何修改和侵入，以“第三方”的视角来监控现有PostgreSQL流复制，可以自行实现PostgreSQL流复制状态监控和故障转移。
3，支持在主节点故障时实现自动故障转移，或者手动故障转移视线灾备切换演练，支持主从的单次或者多次反复切换。
4，可以自定义故障转移判断逻辑以及自定义日志，通知信息等
1，不引入或者依赖于任何第三方中间件，可以同时实现多组流复制集群的监控和故障转移。
2，不对现有PostgreSQL的复制环境做任何修改和侵入，以“第三方”的视角来监控现有PostgreSQL流复制，可以自行实现PostgreSQL流复制状态监控和故障转移。
3，支持在主节点故障时实现自动故障转移，或者手动故障转移视线灾备切换演练，支持主从的单次或者多次反复切换。
4，可以自定义故障转移判断逻辑以及自定义日志，通知信息等
详细实现
详细实现如下，目前在两台EC2上测试环境上，一台腾讯云，一台阿里云上搭建PostgreSQL流复制。经测试：可以一键快速实现主从切换，或者连续故障转移（A节点切换到B节点，B节点切换到A节点，需要修改主从连接信息，已测试切换超过30次）
import threading
import psycopg2
import time
import paramiko
# 连接测试
def conn_to_postgres(db_config):
try:
conn = psycopg2.connect(**db_config)
conn.close()
return True
except Exception as e:
print(f"Error connecting to master: {e}")
return False
# 判断节点身份
def is_postgresql_recovery(db_config):
try:
with psycopg2.connect(**db_config) as conn:
with conn.cursor() as cur:
cur.execute("SELECT pg_is_in_recovery();")
in_recovery = cur.fetchone()[0]
return in_recovery
except Exception as e:
print(f"Error connecting to master: {e}")
return False
# 探测节点是否可达
def is_postgresql_reachable(db_config,retry_times):
# 这里仅仅判断主节点是否可连通
# 判断节点是否可达的时候，可以增加其他判断，比如当前节点是否能ping的通DNS服务器等，标明当前节点的判断是没有问题的
while retry_times > 0:
if not conn_to_postgres(db_config):
print('the postgres cannot reachable,retrying......')
time.sleep(10)
retry_times = retry_times -1
else:
return True
else:
return False
def ssh_conn(host, username, password, port):
"""
在远程Linux服务器上执行命令并返回命令的输出和错误。
:param host: 远程服务器的主机名或IP地址
:param port: SSH端口，默认为22
:param username: 用于SSH登录的用户名
:param password: 用于SSH登录的密码
:param command: 要在远程服务器上执行的命令
:return: 一个元组，包含(标准输出, 错误输出)
"""
# 创建SSH客户端
ssh = paramiko.SSHClient()
# 自动添加策略，保存服务器的主机名和密钥信息
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
try:
# 连接到远程服务器
ssh.connect(hostname=host, port=port, username=username, password=password)
return ssh
except Exception as e:
# 如果发生异常，返回错误信息
return None, str(e)
def ssh_exec_commend(node,commend_shell):
ssh = ssh_conn(host=node['host'], username=node['user_name'], port=node['port'], password=node['password'])
stdin, stdout, stderr = ssh.exec_command(commend_shell)
output = stdout.read().decode('utf-8')
error = stderr.read().decode('utf-8')
if stderr.channel.recv_exit_status() != 0:
print(error)
raise Exception(f"{0} executed failed".format(commend_shell))
return output,error
def create_replication_slot_if_not_exists(db_config, slot_name):
try:
with psycopg2.connect(**db_config) as conn:
with conn.cursor() as cur:
cur.execute("SELECT slot_name FROM pg_replication_slots WHERE slot_name = '{0}';".format(slot_name))
var_slot_name = cur.fetchall()
if not var_slot_name:
cur.execute("SELECT * FROM pg_create_physical_replication_slot('{0}');".format(slot_name))
print(f"Replication slot '{slot_name}' created.")
else:
print(f"Replication slot '{slot_name}' already exists.")
except Exception as e:
print(f"Error connecting to master: {e}")
return False
def promote_standby_to_primary(db_config):
try:
# Connect to the standby server
with psycopg2.connect(**db_config) as conn:
conn.autocommit = True # Ensure changes are committed immediately
with conn.cursor() as cur:
cur.execute("SELECT pg_promote(true);")
print('SELECT pg_promote(true);')
print("{0} Standby promoted to primary successfully.".format(db_config['host']))
except Exception as e:
print("{0} Standby promoted to primary failed : {1}".format(db_config[0],e))
# 时间线修复
def run_pg_rewind(primary_node, standby_node):
# 新的主服务器的连接信息（pg_rewind需要这些信息）
primary_db_config = "host={0} port={1} user={2} password={3}".format(primary_node['db_config']['host'],
primary_node['db_config']['port'],
primary_node['db_config']['user'],
primary_node['db_config']['password'])
# 构建pg_rewind命令
pg_rewind_cmd = ("sudo -u postgres {0}/pg_rewind --target-pgdata={1} --source-server='host={2} port={3} user={4} dbname='postgres' password={5}'"
.format(standby_node['pg_base_dir'],
standby_node['pg_data_dir'],
primary_node['db_config']['host'],
primary_node['db_config']['port'],
primary_node['db_config']['user'],
primary_node['db_config']['password']
)
)
ssh = ssh_conn(host=standby_node['host'], username=standby_node['user_name'], port=standby_node['port'], password=standby_node['password'])
try:
# 停止standby数据库实例
ssh_exec_commend(node=standby_node, commend_shell="sudo systemctl stop postgresql9000")
print(pg_rewind_cmd)
stdin, stdout, stderr = ssh.exec_command(pg_rewind_cmd)
output = stdout.read().decode('utf-8')
error = stderr.read().decode('utf-8')
if stderr.channel.recv_exit_status() != 0:
print('******standby node pg_rewind failed')
print(output)
print(error)
raise Exception(f"{0} Failed to rewind commend".format(standby_node['db_config']['host']))
print(output)
print(error)
return True
except Exception as err:
print('pg_rewind failed ' + str(err))
return False
finally:
ssh.close()
def startup_as_replica(primary_node, standby_node):
standby_auto_conf_path = '{0}/postgresql.auto.conf'.format(standby_node['pg_data_dir'])
standby_signal_path = '{0}/standby.signal'.format(standby_node['pg_data_dir'])
ssh = ssh_conn(host=standby_node['host'], username=standby_node['user_name'], port=standby_node['port'], password=standby_node['password'])
# 要写入postgresql.auto.conf的内容（示例）
auto_conf_content = """
primary_conninfo = 'user={2} password=''{3}'' channel_binding=prefer host={0} port={1} sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = '{4}'
""".format(primary_node['db_config']['host'],
primary_node['db_config']['port'],
primary_node['repl_user'],
primary_node['repl_password'],
primary_node['slot_name']).lstrip()
# 要创建的standby.signal文件（空文件即可，表示该节点为备用节点）
try:
# 防止主库自动启动，再次执行停止从库，这个从库实际上是原来的主库
ssh_exec_commend(node=standby_node,commend_shell="sudo systemctl stop postgresql9000")
try:
# 创建standby.signal文件（如果尚不存在）
print('###### {0} touch {1}'.format(standby_node['host'],standby_signal_path))
output, error = ssh_exec_commend(node=standby_node,commend_shell = 'sudo -u postgres touch {0}'.format(standby_signal_path))
print('###### {0} touch {1}'.format(standby_node['host'], standby_auto_conf_path))
output, error = ssh_exec_commend(node=standby_node, commend_shell = 'sudo -u postgres touch {0}'.format(standby_auto_conf_path))
output, error = ssh_exec_commend(node=standby_node, commend_shell = '''sudo echo "{0}" > {1}'''.format(auto_conf_content,standby_auto_conf_path))
# 启动实例，此时以standby模式运行
ssh_exec_commend(node=standby_node, commend_shell="sudo systemctl start postgresql9000")
return True
except Exception as err:
print(err)
return False
finally:
ssh.close()
# 执行故障转移，因为主节点可达的情况，把主节点作为从节点运行，可以是灾备切换的预演，
def execute_failover(primary_node,standby_node):
# 故障转移开始的第一步，应该在第一时间停止主库，直接stop，不用判断，否则后续无法pg_rewind同步时间线
print('### 1 stop 主节点PostgreSQL服务')
try:
# stop_postgresql服务
ssh_exec_commend(node=primary_node,commend_shell="sudo systemctl stop postgresql9000")
except Exception as err:
print('stop primary postgresql error')
print(str(err))
print('### 2 promote 从节点为主节点')
# 判断standby节点是否处于recovery模式
if is_postgresql_recovery(standby_node['db_config']):
# 创建复制槽，为从节点复制做准备
create_replication_slot_if_not_exists(standby_node['db_config'], standby_node['slot_name'])
# promote standby节点
promote_standby_to_primary(standby_node['db_config'])
else:
print('当前节点非recovery模式，不可执行promote')
exit(1)
# 将旧的主节点作为从节点加入新的主节点复制
print('###### 3 从节点开始pg_rewind')
# 执行pg_rewind修复原始主节点的时间线，请注意：此时从节点提升为主节点，主节点已经变为从节点，所以需要注意节点身份的变化和参数的变化
pg_rewind_result = run_pg_rewind(primary_node=standby_node, standby_node=primary_node)
if pg_rewind_result:
print('######从节点开始pg_rewind 成功')
if pg_rewind_result:
print('### 4 将原始主节点，重新以从节点身份加入复制集群')
# 注意此时主从节点身份已经变换，所以这里参数也变化,原来的从节点已经成为主节点，所以注意参数的交换
startup_as_standby = startup_as_replica(primary_node=standby_node, standby_node=primary_node)
if startup_as_standby:
print('### 原始主节点以从节点身份加入复制集群成功')
# 启动自动检测和故障转移
def auto_failover(primary_node,standby_node):
while True:
if not is_postgresql_reachable(primary_node['db_config'], retry_times=5):
# 告警通知，说明当前配置的主节点故障，即将开始主从切换
execute_failover(primary_node,standby_node)
# 执行完切换之后，进行邮件/短信告警通知等
# 因为主从身份发生了变化，不再对当前的主从配置进行监控，、
# 应该第一时间人工介入：1，确认原始主节点是否正常，2，原始主节点是否以从节点的身份加入集群
# 定期检查间隔
time.sleep(15)
if __name__ == "__main__":
# 数据库连接配置
primary_node = {
'host':'***.***.***.***', #主机名
'user_name':"root", #系统用户名
'password': "******", #系统密码
'port':22, #ssh端口号
'pg_base_dir':'/usr/local/pgsql16/server/bin', #PostgreSQL Server路径
'pg_data_dir':'/usr/local/pgsql16/pg9000/data', #PostgreSQL 数据路径
'repl_user':'replica_user', #流复制用户名
'repl_password':'******', #流复制用户密码
'slot_name': 'pgstandby_slave01', #流复制slot名字
'db_config': { #数据库配置
'host': '***.***.***.***',
'user': 'postgres',
'password': '******',
'dbname': 'postgres',
'port': '******'
},
}
standby_node1 = {
'host':'***.***.***.***',
'user_name':"root",
'password': "******",
'port':22,
'pg_base_dir':'/usr/local/pgsql16/server/bin',
'pg_data_dir':'/usr/local/pgsql16/pg9000/data',
'repl_user':'replica_user',
'repl_password':'******',
'slot_name': 'pgstandby_slave01',
'db_config': {
'host': '***.***.***.***',
'user': 'postgres',
'password': '******',
'dbname': 'postgres',
'port': '******'
},
}
postgres_cluster_list = [{'primary_node':primary_node,'standby_node1':standby_node1},] #这里可以配置多个集群，每个集群有一个PrimaryNode，一个或者多个standbyNode组成
# 调试模式：注意，手动故障转移，表明参数里的主从节点交换身份
for cluster_info in postgres_cluster_list:
print(cluster_info['primary_node'])
execute_failover(primary_node=cluster_info['primary_node'], standby_node=cluster_info['standby_node1'])
# 单独的拉起一个从节点，分两步：
# 1 单独的时间线修复操作
# pg_rewind_result = run_pg_rewind(primary_node=standby_node1, standby_node=primary_node)
# 2，standby模式运行
# 注意此时主从节点身份已经变换，所以这里参数也变化,原来的从节点已经成为主节点，所以注意参数的交换
# startup_as_replica(primary_node=standby_node1, standby_node=primary_node)
# 如果有多组集群，每个集群一个独立线程进行监控
'''
thread_list = []
for cluster_info in postgres_cluster_list:
t = threading.Thread(target=auto_failover(primary_node=cluster_info['primary_node'], standby_node=cluster_info['standby_node1']))
thread_list.append(t)
for t in thread_list:
t.start()
for r in thread_list:
t.join()
'''
待改进
1，用户名密码均已明文方式写在主机信息中
2，pg_rewind的时候需要重启服务器，这里把PostgreSQL的服务写死了，笔者的测试环境PostgreSQL服务名是postgresql9000
3，如果是自动切换模式，自动故障转移后，尚未修改主从配置信息，此时节点的身份信息还是切换前的
4，判断节点是否正常的逻辑中，仅仅通过是否可以连接来实现，如果监控服务本身无法与被监控节点通信，可能会出现误判的情况
