Postgres数据库跨模式备份迁移数据表

简介：使用了python3编写的一个自动化、定时执行的脚本，可实现将一个模式下的单个或多个数据表备份至其他模式！

依赖：apscheduler、psycopg2模型库

使用方式：配置config配置文件，执行./run.sh start运行即可！

config.py配置文件

#!encoding=utf-8
"""
    information: This is configuration file
    update:2022/07/25
"""

# 数据库连接方式
postgres = {
    'database': 'gxdb',
    'user': 'postgres',
    'password': 'xxxx',
    'host': 'xxx.xxx.xxx.xxx',
    'port': 5432,
}

# 模式名；源模式：source_schema 目标模式：target_schema
source_schema = 'sljcpj_v2'
target_schema = 'bak'

#表名,一个表写一个，多个表按照格式递归增加即可
tablename = [
    'bhhc_cgxb_py',
    'bhhc_ccxb_py',
    'bhhc_cgxb_py_ceshi'
]

# 定时器间隔时间（秒）
interval = 21600

#==========邮箱设置==========#
"""是否启用发送邮件通知：true为是，false为否"""
#不启用发送邮件通知时，下面的邮箱配置略过
email_switch = 'false'

"""发送邮件"""
# username_send:发送人的用户名  password:发送人邮箱的授权码  username_to:收件人用户名  port:邮箱端口
email_information = {
    'mailserver': 'smtp.163.com',
    'username_send': 'xxxxxxxxxxx@163.com',
    'password': 'xxxxxxxxxxxxxx',
    'port': 25
}
username_to = ['xxxxxxxxx@qq.com',
               'xxxxxxxxx@qq.com',
               ]

"""邮件内容"""
# mail_head:邮件标题  mail_body:邮件内容
# email_text_start:开始备份时发送的邮件内容；email_text_end:备份完成后发送的邮件内容
email_text_start = {
    'mail_head': 'Waring！数据库开始备份！',
    'mail_body': ''
}
email_text_end = {
    'mail_head': '数据库备份完成的通知！',
    'mail_body': ''
}

run.sh运行python的脚本

#!/usr/bin/env bash
# auth:chenhao

#APP_NAME写被执行的程序名
APP_NAME=datamigrates.py

#使用说明，用来提示输入参数
usage() {
    echo "Usage: sh 执行脚本.sh 请在后面输入参数 [start|stop|restart|status]"
    echo "start:启动 stop:停止 restart:重启 status:查看运行状态"
    exit 1
}

#检查程序是否在运行
is_exist() {
    pid=$(ps -ef | grep ${APP_NAME} | grep -v grep | awk '{print $2}')
    #如果不存在返回1，存在返回0
    if [[ -z "${pid}" ]]; then
        return 1
    else
        return 0
    fi
}

#启动方法
start() {
    is_exist
    if [[ $? -eq "0" ]]; then
        echo "${APP_NAME} 已经处于运行状态了，pid号为 ${pid}"
    else
        nohup python3 ${APP_NAME} &
        echo "${APP_NAME} 正在启动……"
    fi
}

#停止方法
stop() {
    is_exist
    if [[ $? -eq "0" ]]; then
        kill -9 ${pid}
        echo "${APP_NAME} 已停止"
    else
        echo "${APP_NAME} 没有处于运行状态"
    fi
}

#输出运行状态
status() {
    is_exist
    if [[ $? -eq "0" ]]; then
        echo "${APP_NAME} 正在运行，pid号为 ${pid}"
    else
        echo "${APP_NAME} 没有处于运行状态"
    fi
}

#重启
restart() {
    stop
    start
}

#根据输入参数，选择执行对应方法，不输入则执行使用说明
case "$1" in
"start")
    start
    ;;
"stop")
    stop
    ;;
"status")
    status
    ;;
"restart")
    restart
    ;;
*)
    usage
    ;;
esac

datamigrates.py主程序代码

#!encoding=utf-8
"""
    auth:chenhao
    date:2022/07/25
    information: This is code_main to datamigrate

"""

import os.path
from config import postgres
import logging
from traceback import format_exc
import psycopg2 as pgsql
import warnings
from apscheduler.schedulers.blocking import BlockingScheduler
from config import tablename
from config import source_schema
from config import target_schema
from config import interval
import time
from config import email_switch, email_information, username_to, email_text_start, email_text_end
from email.mime.text import MIMEText
import smtplib

logging.basicConfig(level=logging.DEBUG, filename=os.path.abspath(os.path.dirname(__file__)) + '/migrate.log',
                    format='%(asctime)s - %(funcName)s{} -%(levelname)s : %(message)s'.format("方法"))
warnings.filterwarnings('ignore')


class Datamigrate(object):
    """数据定时迁移的类"""

    def conn_pgsq(self):
        """连接pgsql数据库的方法"""
        logging.info("进入连接pgsql库的方法")
        try:
            conn = pgsql.connect(
                host=postgres['host'],
                database=postgres['database'],
                user=postgres['user'],
                password=postgres['password'],
                port=postgres['port']
            )
            logging.warning("pgsql库连接信息为\n%s" % conn)
            return conn
        except Exception:
            logging.error("连接数据库报错！\n%s" % format_exc())

    def get_tables(self, schema_name):
        """获取模式下所有表的方法"""
        logging.info("进入获取模式下所有表的方法")
        con = self.conn_pgsq()
        cur = con.cursor()
        try:
            cur.execute("select tablename from pg_tables where schemaname = '{}'".format(schema_name))
            tables_list = cur.fetchall()
            return tables_list
        except Exception as e:
            logging.error("try.Exception抛出异常！\n%s" % format_exc())

    def move_table(self):
        """跨模式迁移表的方法"""
        self.emails_start()
        date = time.strftime('%Y%m%d%H')
        logging.info("进入跨模式迁移表的方法")
        con = self.conn_pgsq()
        cur = con.cursor()
        try:
            for table in tablename:
                for tables in list(self.get_tables(source_schema)):
                    if tables in self.get_tables(source_schema):
                        logging.info("执行创建 %s 表的sql语句……" % (table + date))
                        cur.execute("create table {0}.{1} (like {2}.{3} INCLUDING ALL)".format(
                            target_schema, table + date, source_schema, table
                        ))
                        logging.info("执行完毕！")
                        logging.info("执行将 %s 表数据插入至 %s 表的sql语句……" % (table, table + date))
                        cur.execute("insert into {0}.{1} select * from {2}.{3}".format(
                            target_schema, table + date, source_schema, table
                        ))
                        logging.info("执行完毕！")
                        break
                    else:
                        logging.error("for.if.table报错，表 %s 不存在 %s 模式中！" % (table, source_schema))
            cur.close()
            logging.info("关闭游标连接！")
            con.commit()
            logging.info("提交事务！")
        except Exception as E:
            logging.error("try.Exception抛出异常！\n%s" % format_exc())
            con.rollback()
            logging.warning("操作已回滚！")
        finally:
            logging.info("关闭数据库连接！")
            con.close()
            self.emails_end()

    def emails_start(self):
        """开始备份发送邮件的方法"""
        if email_switch == 'true':
            logging.info("进入执行发送邮件的方法")
            try:
                mailserver = email_information['mailserver']
                username_send = email_information['username_send']
                password = email_information['password']
                for user in username_to:
                    username_recv = user
                    mail = MIMEText(email_text_start['mail_body'])
                    mail['Subject'] = email_text_start['mail_head']
                    mail['From'] = username_send
                    mail['To'] = username_recv

                    smtp = smtplib.SMTP(mailserver, port=email_information['port'])
                    smtp.login(username_send, password)
                    smtp.sendmail(username_send, username_recv, mail.as_string())
                    smtp.quit()
            except Exception as e:
                logging.error("执行发送邮件的方法报错%s" % format_exc())
            else:
                logging.info("执行发送邮件的方法成功，请到邮箱查收！")
        elif email_switch == 'false':
            logging.info("未启用发送邮箱媒介")
        else:
            logging.info("未启用发送邮箱媒介")

    def emails_end(self):
        """备份完成后发送邮件的方法"""
        if email_switch == 'true':
            logging.info("进入执行发送邮件的方法")
            try:
                mailserver = email_information['mailserver']
                username_send = email_information['username_send']
                password = email_information['password']
                for user in username_to:
                    username_recv = user
                    mail = MIMEText(email_text_end['mail_body'])
                    mail['Subject'] = email_text_end['mail_head']
                    mail['From'] = username_send
                    mail['To'] = username_recv

                    smtp = smtplib.SMTP(mailserver, port=email_information['port'])
                    smtp.login(username_send, password)
                    smtp.sendmail(username_send, username_recv, mail.as_string())
                    smtp.quit()
            except Exception as e:
                logging.error("执行发送邮件的方法报错%s" % format_exc())
            else:
                logging.info("执行发送邮件的方法成功，请到邮箱查收！")
        elif email_switch == 'false':
            logging.info("未启用发送邮箱媒介")
        else:
            logging.info("未启用发送邮箱媒介")

    def schedulers_inval(self, func):
        """定时器方法"""
        logging.info("进入执行定时器方法")
        try:
            schedulers = BlockingScheduler()
            schedulers.add_job(func, 'interval', seconds=interval)
            schedulers.start()
        except Exception as e:
            logging.error("定时器方法执行报错！\n%s" % format_exc())


if __name__ == '__main__':
    """主程序"""
    logging.warning("进入main程序入口……")
    datamigrate = Datamigrate()
    datamigrate.schedulers_inval(datamigrate.move_table)

运行以后的目录结构：

 运行后的日志信息: