对比了列名、列的数量、列的类型,将不一致的输出
import psycopg2
# 连接到主库和从库
conn_master = psycopg2.connect(dbname="xxx", user="postgres", password="xxx", host="xxx.xxx.xxx.xxx", port="5432")
conn_slave= psycopg2.connect(dbname="xxx", user="postgres", password="xxx", host="xxx.xxx.xxx.xxx", port="5432")
# 获取主库和从库的表列表
with conn_master.cursor() as cursor_master:
cursor_master.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")
tables_master = [row[0] for row in cursor_master.fetchall()]
with conn_slave.cursor() as cursor_slave:
cursor_slave.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")
tables_slave = [row[0] for row in cursor_slave.fetchall()]
# 比较表名
for table_name in tables_master:
if table_name in tables_slave:
# 获取主库和从库的表结构
with conn_master.cursor() as cursor_master:
cursor_master.execute("""
SELECT c.column_name, c.data_type, c.character_maximum_length, c.numeric_precision
FROM information_schema.columns c
JOIN information_schema.tables t ON c.table_name = t.table_name AND c.table_schema = t.table_schema
WHERE t.table_name = %s AND t.table_schema = 'public'
ORDER BY c.ordinal_position
""", (table_name,))
columns_master = cursor_master.fetchall()
with conn_slave.cursor() as cursor_slave:
cursor_slave.execute("""
SELECT c.column_name, c.data_type, c.character_maximum_length, c.numeric_precision
FROM information_schema.columns c
JOIN information_schema.tables t ON c.table_name = t.table_name AND c.table_schema = t.table_schema
WHERE t.table_name = %s AND t.table_schema = 'public'
ORDER BY c.ordinal_position
""", (table_name,))
columns_slave = cursor_slave.fetchall()
# 比较列数和列属性
if len(columns_master) != len(columns_slave):
print(f"{table_name} 表在主从库中的列数量不一致")
else:
for col_master, col_slave in zip(columns_master, columns_slave):
if col_master != col_slave:
print(f"{table_name} 表的 {col_master[0]} 列在主从库中的属性不一致:\n主库:{col_master}\n从库:{col_slave}")
else:
print(f"主库的{table_name}表在从库中不存在")
# 关闭连接
conn_master.close()
conn_slave.close()