Python编写PostgreSQL数据库结构比对程序（超详细源代码）

---

前言

随着PostgreSQL数据库越来越被广泛的应用，它的优势也逐渐被广泛的认可。但针对该数据库的工具却较少。由于工作需要，经常有对PostgreSQL数据库进行比对生成升级脚本的需求，故本人使用python编写了一个PostgreSQL数据库比对程序和大家共同交流学习。（文章的最后将提供源程序的下载地址）

一、功能介绍

比对程序实现了对两个数据库的结构进行比较并生成差异化更新脚本。
比对的内容如下：
1、表结构比比较：包含表、字段、字段注释、序列、约束、索引、触发器的比较。
2、视图比较：包含普通视图和物化视图的比较，并生成创建或者修改脚本。
3、函数比较：函数（存储过程）的比较，并生成新建或者修改脚本。
4、外部表比较：外部表进行比较，并生成新建或者修改脚本。

二、代码目录结构

1、下图为代码的目录结构展开截图及说明

2、下面为程序运行界面

3、下图为生成差异化脚本updScript.sql文件部分片段

三、核心源代码解析

1、bin目录下main.py

import core.schemaSync as core
import PySimpleGUI as sg
import time

# 定义需要执行的任务
my_list = ['表', '视图', '函数', '外部表']
# 定义进度条
progressbar = [[sg.ProgressBar(len(my_list), orientation='h', size=(51, 10), bar_color=('#f47920', '#46485f'), key='progressbar')]]
# 定义输出窗口尺寸
output_win = [[sg.Output(size=(78, 20))]]
# 窗口布局
layout = [[sg.Frame('执行进度', layout=progressbar)],
          [sg.Frame('输出信息', layout=output_win)],
          [sg.Submit('开 始'), sg.Cancel('退 出')]]
window = sg.Window('PostgreSQL数据库比较程序', layout)
progress_bar = window['progressbar']
while True:
    event, values = window.read(timeout=10)
    # 点击按钮退出
    if event == '退 出' or event is None:
        break
    # 点击按钮开始
    elif event == '开 始':
        file_name = ''
        # 循环要执行的任务
        for i, item in enumerate(my_list):
            if i == 0:
                print(item + ' 比对开始...')
                file_name = core.sync_table()
                print(item+' 比对完成！（含字段及注释、序列、约束、索引、触发器的比对）\n')
            if i == 1:
                print(item + ' 比对开始...')
                core.sync_view()
                print(item+' 比对完成！（含普通视图、物化视图）\n')
            if i == 2:
                print(item + ' 比对开始...')
                core.sync_function()
                print(item+' 比对完成！\n')
            if i == 3:
                print(item + ' 比对开始...')
                core.sync_foreign_table()
                print(item+' 比对完成！\n')
            time.sleep(1)
            # 更新进度条
            progress_bar.UpdateBar(i + 1)
        # 输出生成脚本的路径
        print('差异脚本路径：' + file_name)

2、conf目录下config.ini

[database]
#postgre_src
pg_host_src=192.168.10.70
pg_port_src=5432
pg_user_src=postgres
pg_pwd_src=***
pg_db_src=data_use

#postgre_dst
pg_host_dst = 192.168.10.71
pg_port_dst=5432
pg_user_dst=postgres
pg_pwd_dst=***
pg_db_dst=data_use

[common]
output_file_name=updScript.sql
log_dir=

3、core目录下的schemaSync.py

import os
from util.connDB import getConn
from util.logger import log
import sqlConfig
from util.commonFunction import commonFunction
import util.readConfig as readConfig


# 对比表、字段及注释、序列、约束、索引、触发器
def sync_table():
    try:
        # 获取源数据库连接
        global sourceConn,targetConn,sourceCursor,targetCursor
        sourceConn, sourceCursor = getConn('postgre_src')
        # 获取目标数据库连接
        targetConn, targetCursor = getConn('postgre_dst')
        # 求源和目标表的差集
        sourceCursor.execute(sqlConfig.selectTable)
        targetCursor.execute(sqlConfig.selectTable)
        sourceTable = sourceCursor.fetchall()
        targetTable = targetCursor.fetchall()
        tableReslut=set(sourceTable) - set(targetTable)
        tableName = ''
        for item in tableReslut:
            tableName=tableName+','+item[0]
        tableName=str(tableName[1:])
        # print(tableName)
        # 导出新增表
        cf=commonFunction()
        file_name=cf.dumpTable(tableName)

        # 对比序列
        # 求源和目标序列的差集
        sourceCursor.execute(sqlConfig.selectSequence % tableName.replace(",", "','"))
        targetCursor.execute(sqlConfig.selectSequence % tableName.replace(",", "','"))
        sourceSequence = sourceCursor.fetchall()
        targetSequence = targetCursor.fetchall()
        sequenceReslut = list(set(sourceSequence) - set(targetSequence))
        # print(sequenceReslut)
        for itemReslut in sequenceReslut:
            sequence_name = itemReslut[0]
            if (sequence_name != ''):
                str_sql = "create sequence " + sequence_name + ";"
                # print(str_sql)
                # 追加写入到指定文件
                with open(file_name, mode='a+', encoding="utf-8") as w:
                    w.write("-- Sequence compares" + "\n" + str_sql + "\n")

        # 对比字段
        # 求源和目标字段的差集
        sourceCursor.execute(sqlConfig.selectColumn % tableName.replace(",","','"))
        targetCursor.execute(sqlConfig.selectColumn % tableName.replace(",","','"))
        sourceColumn = sourceCursor.fetchall()
        targetColumn = targetCursor.fetchall()
        columnReslut = list(set(sourceColumn) - set(targetColumn))
        # 对列表根据表名称和字段名称排序
        columnReslut.sort()
        #print(columnReslut)
        if(len(columnReslut)>0):
            # 追加写入到指定文件
            with open(file_name, mode='a+', encoding="utf-8") as w:
                w.write("\n--\n-- Column compares\n--\n" + "\n")
        # 外循环，对比后的差集字段
        for itemReslut in columnReslut:
            table_name = itemReslut[0]
            column_name = itemReslut[1]
            column_type = itemReslut[2]
            column_default = itemReslut[3]
            description = itemReslut[4]
            # print(itemReslut[0],itemReslut[1],itemReslut[2],itemReslut[3],itemReslut[4],itemReslut[5])
            # 内循环，目标库的字段
            column_exists = 0
            for itemTarget in targetColumn:
                # 表名称和字段名称相等
                if(table_name == itemTarget[0] and column_name == itemTarget[1]):
                    column_exists = column_exists+1
                    # 字段类型不等
                    if(column_type != itemTarget[2]):
                        #print("alter table " + table_name + "  alter column " + column_name + " type " + column_type + ";")
                        str_sql = "alter table " + table_name + "  alter column " + column_name + " type " + column_type + ";\n"
                        # 追加写入
                        with open(file_name, mode='a+', encoding="utf-8") as w:
                            w.write(str_sql + "\n")
                    # 描述不等
                    if(description != itemTarget[4] and description != ''):
                        #print("comment on column " + table_name + "." + column_name + " is '" + description + "';")
                        str_sql = "comment on column " + table_name + "." + column_name + " is '" + description + "';\n"
                        # 追加写入
                        with open(file_name, mode='a+', encoding="utf-8") as w:
                            w.write(str_sql + "\n")
                    # 默认值不等
                    if (column_default != itemTarget[3] and column_default != ''):
                        # print("alter table " + table_name + " alter column " + column_name + " set DEFAULT " + column_default + ";")
                        str_sql = "alter table " + table_name + " alter column " + column_name + " set DEFAULT " + column_default + ";\n"
                        #追加写入
                        with open(file_name, mode='a+', encoding="utf-8") as w:
                            w.write(str_sql + "\n")

            # 若字段名称不存在，则新增字段
            if(column_exists == 0):
                # 判断新增字段的默认值是否为空
                if (column_default != ''):
                    #print("alter table " + table_name + "  add " + column_name + " " + column_type + " default " + column_default + ";")
                    str_sql="alter table " + table_name + "  add " + column_name + " " + column_type + " default " + column_default + ";\n"
                else:
                    #print("alter table " + table_name + "  add " + column_name + " " + column_type + ";")
                    str_sql="alter table " + table_name + "  add " + column_name + " " + column_type + ";\n"
                with open(file_name, mode='a+', encoding="utf-8") as w:
                    w.write(str_sql + "\n")
                # 判断新增字段的comment是否为空
                if (description != ''):
                    #print("comment on column " + table_name + "." + column_name + " is '" + description + "';")
                    str_sql="comment on column " + table_name + "." + column_name + " is '" + description + "';\n"
                    with open(file_name, mode='a+', encoding="utf-8") as w:
                        w.write(str_sql + "\n")
        # 对比约束
        # 求源和目标约束的差集
        sourceCursor.execute(sqlConfig.selectConstraint % tableName.replace(",", "','"))
        targetCursor.execute(sqlConfig.selectConstraint % tableName.replace(",", "','"))
        sourceConstraint = sourceCursor.fetchall()
        targetConstraint = targetCursor.fetchall()
        constraintReslut = list(set(sourceConstraint) - set(targetConstraint))
        # print(constraintReslut)
        if (len(constraintReslut) > 0):
            # 追加写入到指定文件
            with open(file_name, mode='a+', encoding="utf-8") as w:
                w.write("\n--\n-- Constraint compares\n--\n" + "\n")
        # 外循环，对比后的差集约束
        for itemReslut in constraintReslut:
            constraint_name = itemReslut[0]
            table_name = itemReslut[1]
            column_name = itemReslut[2]
            foreign_table_name = itemReslut[3]
            foreign_column_name = itemReslut[4]
            constraint_type = itemReslut[5]
            check_value = itemReslut[6]
            #print(targetConstraint)
            # 内循环，目标库的约束
            constraint_exists = 0
            for itemTarget in targetConstraint:
                # 约束名称相等，则不处理
                if(constraint_name == itemTarget[0]):
                    constraint_exists=constraint_exists+1
                # 约束名称不等、约束类型、表名称相等、字段名称相等，则不处理)
                if(constraint_name != itemTarget[0] and constraint_type == itemTarget[5] and table_name == itemTarget[1] and column_name == itemTarget[2]):
                    constraint_exists = constraint_exists + 1
            # 若约束名称不存在,#constraint_type有四种：UNIQUE、PRIMARY KEY、CHECK、FOREIGN KEY
            if(constraint_exists == 0):
                if(constraint_type == "PRIMARY KEY"):
                    str_sql = 'alter table ' + table_name + ' add constraint "' + constraint_name + '" primary key(' + column_name + ');'
                elif(constraint_type == "FOREIGN KEY"):
                    str_sql = 'alter table ' + table_name + ' add constraint "' + constraint_name + '" foreign key(' + column_name + ') references ' + foreign_table_name + '(' + foreign_column_name + ');'
                elif(constraint_type == "UNIQUE"):
                    str_sql = 'alter table ' + table_name + ' add constraint "' + constraint_name + '" unique(' + column_name + ');'
                elif(constraint_type == "CHECK"):
                    str_sql = 'alter table ' + table_name + ' add constraint "' + constraint_name + '" check' + check_value + ';'
                # 追加写入到指定文件
                with open(file_name, mode='a+', encoding="utf-8") as w:
                    w.write(str_sql + "\n")

        # 对比索引
        # 求源和目标索引的差集
        sourceCursor.execute(sqlConfig.selectIndex % tableName.replace(",", "','"))
        targetCursor.execute(sqlConfig.selectIndex % tableName.replace(",", "','"))
        sourceIndex = sourceCursor.fetchall()
        targetIndex = targetCursor.fetchall()
        indexReslut = list(set(sourceIndex) - set(targetIndex))
        #print(indexReslut)
        if (len(indexReslut) > 0):
            # 追加写入到指定文件
            with open(file_name, mode='a+', encoding="utf-8") as w:
                w.write("\n--\n-- Index compares\n--\n" + "\n")
        for itemReslut in indexReslut:
            indexname = itemReslut[1]
            indexdef = itemReslut[2]
            if (indexname != ''):
                str_sql = indexdef
                # print(str_sql)
                # 追加写入到指定文件
                with open(file_name, mode='a+', encoding="utf-8") as w:
                    w.write("-- Create index" + "\n" + str_sql + "\n")
        # 对比触发器
        # 求源和目标触发器的差集
        sourceCursor.execute(sqlConfig.selectTrigger % tableName.replace(",", "','"))
        targetCursor.execute(sqlConfig.selectTrigger % tableName.replace(",", "','"))
        sourceTrigger = sourceCursor.fetchall()
        targetTrigger = targetCursor.fetchall()
        triggerReslut = list(set(sourceTrigger) - set(targetTrigger))
        # print(triggerReslut)
        if (len(triggerReslut) > 0):
            # 追加写入到指定文件
            with open(file_name, mode='a+', encoding="utf-8") as w:
                w.write("\n--\n-- Trigger compares\n--\n" + "\n")
        #外循环，对比差集触发器
        for itemReslut in triggerReslut:
            trigger_name = itemReslut[0]
            event_object_table = itemReslut[1]
            definition = itemReslut[2]
            drop_definition = itemReslut[3]
            # 内循环，目标库的触发器
            trigger_exists = 0
            for itemTarget in targetTrigger:
                # 触发器名称、表名称都相等
                if (trigger_name == itemTarget[0] and event_object_table == itemTarget[1]):
                    trigger_exists = trigger_exists + 1
                    str_sql = drop_definition+'\n'+definition
                    # 追加写入到指定文件
                    with open(file_name, mode='a+', encoding="utf-8") as w:
                        w.write("\n--\n-- Alter trigger\n--\n" + str_sql+"\n")

            # 触发器名称不存在
            if (trigger_exists == 0):
                str_sql = definition
                # 追加写入到指定文件
                with open(file_name, mode='a+', encoding="utf-8") as w:
                    w.write("\n--\n-- Create trigger\n--\n" + str_sql + "\n")

        return file_name

    except Exception as e:
        # 异常回滚
        targetConn.rollback()
        log.error(str(e))
        #raise e
    finally:
        # 关闭数据库
        sourceCursor.close()
        sourceConn.close()
        targetCursor.close()
        targetConn.close()

#对比普通视图、物化视图
def sync_view():
    try:
        # 获取生成文件路径及名称
        data = readConfig.readConfig()
        output_file_name = data.getFileName("output_file_name")
        root_dir = os.path.dirname(os.path.dirname(os.path.abspath('__file__')))
        output_path = os.path.join(root_dir, "output\\")
        output_file = output_file_name
        #print(output_path+output_file)
        # 获取源数据库连接
        global sourceConn,targetConn,sourceCursor,targetCursor
        sourceConn, sourceCursor = getConn('postgre_src')
        # 获取目标数据库连接
        targetConn, targetCursor = getConn('postgre_dst')
        # 对比源库和目标库视图
        sourceCursor.execute(sqlConfig.selectView)
        targetCursor.execute(sqlConfig.selectView)
        sourceView = sourceCursor.fetchall()
        targetView = targetCursor.fetchall()
        viewReslut = list(set(sourceView) - set(targetView))
        # print(viewReslut)
        # 外循环，循环视图差集
        for itemReslut in viewReslut:
            view_name = itemReslut[0]
            definition = itemReslut[1]
            view_type = itemReslut[2]
            #内循环，目标库视图
            view_exists = 0
            for itemTarget in targetView:
                # 视图名称相同
                if (view_name==itemTarget[0]):
                    view_exists=view_exists+1
                    # 判断视图定义是否相同
                    if(definition != itemTarget[1]):
                        # 修改普通视图
                        if(view_type=='view'):
                            str_sql='create or replace view ' + view_name + ' as \n' + definition
                            #print(str_sql)
                        # 修改物化视图
                        else:
                            str_sql = 'drop materialized view '+view_name+';\ncreate materialized view ' + view_name + ' as \n' + definition
                            #print(str_sql)
                        # 追加写入到指定文件
                        with open(output_path+output_file, mode='a+', encoding="utf-8") as w:
                            w.write("\n--\n-- Alter view or materialized view\n--\n" + "\n" + str_sql + "\n")
            # 视图名称不存在，创建视图
            if(view_exists==0):
                # 创建普通视图
                if (view_type == 'view'):
                    str_sql = 'create view or materialized view' + view_name + ' as \n' + definition
                    #print(str_sql)
                # 创建物化视图
                else:
                    str_sql = 'create materialized view ' + view_name + ' as \n' + definition
                    #print(str_sql)
                # 追加写入到指定文件
                with open(output_path + output_file, mode='a+', encoding="utf-8") as w:
                    w.write("\n--\n-- Create view or materialized view\n--\n" + "\n" + str_sql + "\n")

    except Exception as e:
        # 异常回滚
        targetConn.rollback()
        log.error(str(e))
        #raise e
    finally:
        # 关闭数据库
        sourceCursor.close()
        sourceConn.close()
        targetCursor.close()
        targetConn.close()

# 对比函数
def sync_function():
    try:
        # 获取生成文件路径及名称
        data = readConfig.readConfig()
        output_file_name = data.getFileName("output_file_name")
        root_dir = os.path.dirname(os.path.dirname(os.path.abspath('__file__')))
        output_path = os.path.join(root_dir, "output\\")
        output_file = output_file_name
        #print(output_path+output_file)
        # 获取源数据库连接
        global sourceConn,targetConn,sourceCursor,targetCursor
        sourceConn, sourceCursor = getConn('postgre_src')
        # 获取目标数据库连接
        targetConn, targetCursor = getConn('postgre_dst')
        # 对比源库和目标库函数
        sourceCursor.execute(sqlConfig.selectFunction)
        targetCursor.execute(sqlConfig.selectFunction)
        sourceFunction = sourceCursor.fetchall()
        targetFunction = targetCursor.fetchall()
        functionReslut = list(set(sourceFunction) - set(targetFunction))
        # print(viewReslut)
        # 外循环，循环函数差集
        for itemReslut in functionReslut:
            routine_name = itemReslut[0]
            routine_definition = itemReslut[1]
            full_definition = itemReslut[2]
            # 内循环，目标库函数
            function_exists = 0
            for itemTarget in targetFunction:
                # 函数名称相同
                if (routine_name==itemTarget[0]):
                    function_exists=function_exists+1
                    # 判断函数定义是否相同
                    if(routine_definition != itemTarget[1]):
                        # 修改函数
                        # 追加写入到指定文件
                        str_sql = full_definition
                        with open(output_path+output_file, mode='a+', encoding="utf-8") as w:
                            w.write("\n--\n-- Alter function\n--\n" + "\n" + str_sql + "\n")
            # 函数名称不存在，创建函数
            if(function_exists==0):
                # 创建函数
                # 追加写入到指定文件
                str_sql = full_definition
                with open(output_path + output_file, mode='a+', encoding="utf-8") as w:
                    w.write("\n--\n-- Create function\n--\n" + "\n" + str_sql + "\n")

    except Exception as e:
        # 异常回滚
        targetConn.rollback()
        log.error(str(e))
        #raise e
    finally:
        # 关闭数据库
        sourceCursor.close()
        sourceConn.close()
        targetCursor.close()
        targetConn.close()
#对比外部表
def sync_foreign_table():
    try:
        # 获取生成文件路径及名称
        data = readConfig.readConfig()
        output_file_name = data.getFileName("output_file_name")
        root_dir = os.path.dirname(os.path.dirname(os.path.abspath('__file__')))
        output_path = os.path.join(root_dir, "output\\")
        output_file = output_file_name
        #print(output_path+output_file)
        # 获取源数据库连接
        global sourceConn,targetConn,sourceCursor,targetCursor
        sourceConn, sourceCursor = getConn('postgre_src')
        # 获取目标数据库连接
        targetConn, targetCursor = getConn('postgre_dst')
        # 对比源库和目标库外部表
        sourceCursor.execute(sqlConfig.selectForeignTable)
        targetCursor.execute(sqlConfig.selectForeignTable)
        sourceForeignTable = sourceCursor.fetchall()
        targetForeignTable = targetCursor.fetchall()
        foreignTableReslut = list(set(sourceForeignTable) - set(targetForeignTable))
        # print(foreignTableReslut)
        # 外循环，循环外部表差集
        for itemReslut in foreignTableReslut:
            table_name = itemReslut[0]
            column_name = itemReslut[1]
            definition = itemReslut[2]
            #内循环，目标库外部表
            foreign_exists = 0
            for itemTarget in targetForeignTable:
                # 外部表名称相同
                if (table_name==itemTarget[0]):
                    foreign_exists=foreign_exists+1
                    # 判断外部表字段是否相同
                    if(column_name != itemTarget[1]):
                        # 修改普通视图
                        str_sql = 'drop FOREIGN TABLE '+ table_name +';\n' + definition
                        # 追加写入到指定文件
                        with open(output_path+output_file, mode='a+', encoding="utf-8") as w:
                            w.write("\n--\n-- Alter foreign table\n--\n" + "\n" + str_sql + "\n")
            # 外部表名称不存在，创建外部表
            if(foreign_exists==0):
                # 创建sql
                str_sql = definition
                # 追加写入到指定文件
                with open(output_path + output_file, mode='a+', encoding="utf-8") as w:
                    w.write("\n--\n-- Create foreign table\n--\n" + "\n" + str_sql + "\n")

    except Exception as e:
        # 异常回滚
        targetConn.rollback()
        log.error(str(e))
        #raise e
    finally:
        # 关闭数据库
        sourceCursor.close()
        sourceConn.close()
        targetCursor.close()
        targetConn.close()

4、core目录下的sqlConfig.py

# 查询表信息
selectTable = """select tablename from pg_tables where schemaname='public';"""
# 查询序列信息
selectSequence = """select sequence_name
                    from information_schema.sequences
                    where 'nextval(''' || sequence_name || '''::regclass)' not in
                          (select column_default
                            from information_schema.columns
                            where column_default is not null
                            and table_name in ('%s'));"""
# 查询字段信息
selectColumn = """select a.table_name,
                       a.column_name,
                       (case
                            when substr(b.data_type, 1, 18) = 'character varying('
                                then replace(b.data_type, 'character varying(', 'varchar(')
                            when substr(b.data_type, 1, 10) = 'character('
                                then replace(b.data_type, 'character(', 'char(')
                            when b.data_type = 'timestamp without time zone'
                                then 'timestamp'
                            else b.data_type end)     as column_type,
                       coalesce(a.column_default, '') as column_default,
                       coalesce(b.description, '')    as description
                from information_schema.columns a
                         left join
                     (SELECT c.relname                            as table_name,
                             format_type(a.atttypid, a.atttypmod) as data_type,
                             a.attname                            as column_name,
                             d.description
                      FROM pg_class as c
                               join
                           pg_attribute as a on a.attrelid = c.oid
                               left join
                           pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
                      where a.attnum > 0
                     ) b
                     on a.table_name = b.table_name and a.column_name = b.column_name
                where a.table_schema = 'public'
                  and a.is_updatable = 'YES'
                  and a.table_name in (select tablename from pg_tables where schemaname = 'public' and tablename not in ('%s'));"""
# 查询约束信息
selectConstraint = """SELECT tc.constraint_name,
                       tc.table_name,
                       coalesce(kcu.column_name,'') as column_name,
                       ccu.table_name  AS foreign_table_name,
                       ccu.column_name AS foreign_column_name,
                       tc.constraint_type,
                       coalesce(co.consrc,'') as check_value
                FROM information_schema.table_constraints AS tc
                         left JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
                         JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
                         left join pg_constraint co on co.conname = tc.constraint_name
                WHERE tc.table_name not in ('%s');"""
# 查询索引信息
selectIndex = """select tablename, indexname, indexdef||';' as indexdef
                from pg_indexes
                WHERE schemaname = 'public'
                  and indexname not in (select conname from pg_constraint where contype in ('p', 'f', 'u'))
                  and tablename not in ('%s');"""
# 查询视图信息
selectView = """select viewname as view_name, pg_get_viewdef(viewname, true) as definition, 'view' as view_type
                from pg_views
                where schemaname = 'public'
                union all
                select matviewname as view_name, pg_get_viewdef(matviewname, true) as definition, 'matview' as view_type
                from pg_matviews
                where schemaname = 'public';"""
# 查询函数信息
selectFunction = """SELECT a.routine_name,
                   a.routine_definition,
                   'create or replace function ' || a.routine_name || '(' ||
                   string_agg(coalesce(b.parameter_name || ' ', '') || coalesce(b.data_type, ''), ',' order by ordinal_position) ||
                   E')\n\t' || ' returns ' || a.data_type || E'\nas\n$$' || a.routine_definition || '$$ LANGUAGE ' ||
                   external_language || ';' as full_definition
            FROM information_schema.routines a
                     left JOIN information_schema.parameters b ON a.specific_name = b.specific_name
            where a.specific_schema = 'public'
            group by a.specific_name, a.routine_name, a.data_type, a.external_language, a.routine_definition;"""
# 查询外部表
selectForeignTable = """select table_name,string_agg(column_name,',' order by attnum) as column_name,'create FOREIGN TABLE '||table_name||E'\n(\n\t'||string_agg(column_name||' '||data_type,E',\n\t')||E'\n)\n'||ftoptions as definition from
                        (SELECT b.relname                            as table_name,
                               format_type(c.atttypid, c.atttypmod) as data_type,
                               c.attname                            as column_name,
                                d.srvname,
                                'SERVER '||d.srvname||E'\nOPTIONS '||'('||replace(replace(array_to_string(a.ftoptions,','),'=',' '''),',',''',')||''');' as ftoptions,
                                c.attnum
                        FROM pg_foreign_table a
                                 join
                             pg_class as b on a.ftrelid = b.oid
                                 join
                             pg_attribute as c on c.attrelid = b.oid
                                join
                        pg_foreign_server d on a.ftserver=d.oid
                        where c.attnum > 0) t group by t.table_name,t.ftoptions;"""
# 查询触发器信息
selectTrigger = """select b.trigger_name,
                   b.event_object_table,
                   case
                       when a.tgenabled = 'D' then 'create trigger ' || b.trigger_name || ' ' || b.action_timing || ' ' ||
                                                   string_agg(b.event_manipulation, ' or ') || ' on ' || b.event_object_table ||
                                                   ' for each ' || b.action_orientation || ' ' || b.action_statement || E';\n' ||
                                                   'alter table ' || b.event_object_table || ' disable trigger ' ||
                                                   b.trigger_name || ';'
                       else 'create trigger ' || b.trigger_name || ' ' || b.action_timing || ' ' ||
                            string_agg(b.event_manipulation, ' or ') || ' on ' || b.event_object_table || ' for each ' ||
                            b.action_orientation || ' ' || b.action_statement || ';' end as definition,
                   'drop trigger if exists '||trigger_name||' on '||b.event_object_table||';' as drop_definition
            from (select tgname, relname, tgenabled
                  from pg_trigger tr
                           join pg_class cl on tr.tgrelid = cl.oid) a
                     join information_schema.triggers b on a.tgname = b.trigger_name and a.relname = b.event_object_table
            where b.trigger_schema='public' and b.event_object_table not in ('%s')
            group by a.tgenabled, b.trigger_name, b.event_object_table, b.action_statement, b.action_orientation, b.action_timing;"""

5、util目录为连接数据库、读取配置文件、生成日志的代码，代码较为简单暂不罗列。

四、完整源代码项目下载

Python编写PostgreSQL数据库结构比对程序源代码
下载地址：https://download.csdn.net/download/ma286388309/20687085

总结

以上为比对程序的核心代码，供大家交流学习。程序如遇到bug或不完善的地方请留言指正，我也好继续完善。