The script checks changes of a Postgresql database schema and send notifications about it.

schema_check.py

#!/usr/bin/python
# schema_check.py -
#   Checks changes of a Postgresql database schema
# and send notifications about it.
#
# It may be critical for some cases
# (running logical replication, for example)
#
# Author: Andreyk Klychkov aaklychkov@mail.ru
# Licence: Copyleft free software
# Data: 28.03.2018
#
# Syntax: ./schema_check.py DBNAME PATH_TO_TMP_DIR
#
# PATH_TO_TMP_DIR will be content two temporary file
# The first of them is the previous database schema, the second is
# the current schema. After getting it the script
# creates two sets and compares them.
#
# Script may be executed by using cron with SEND_MAIL = 1
# IMPORTANT! You must set up desired mail parameters in the
# 'Mail params' section below.
#
# The mail notification content example:
# --------------------------------------
#   This changes have been ++ADDED++ to the otp_db schema:
#   CREATE INDEX test_idx ON public.test1011 USING btree (id);
#   -- Name: test_idx; Type: INDEX; Schema: public; Owner: postgres
#
#   This changes have been --DELETED-- from the otp_db schema:
#   CREATE TABLE public.test1 (
#   -- Name: test1; Type: TABLE; Schema: public; Owner: postgres
#   ALTER TABLE public.test1 OWNER TO postgres;
#

import datetime
import os
import shutil
import smtplib
import socket
import subprocess
import sys
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText


########################
#   PARAMETERS BLOCK   #
########################

# Check a number of command-line arguments:
if len(sys.argv) != 3:
    print('Syntax: ./schema_check.py DBNAME TMP_DIR')
    sys.exit(1)

# Main params:
DB = sys.argv[1]
TMP_DIR = sys.argv[2]

# Common params:
HOSTNAME = socket.gethostname()
NOW = datetime.datetime.now()
F_TIME = NOW.strftime('%Y%m%d-%H%M%S')

# Mail params:
SEND_MAIL = 1
SENDER = 'report.maydomain@gmail.com'
RECIPIENT = ['aaklychkov@mail.ru']
SMTP_SRV = 'smtp.gmail.com'
SMTP_PORT = 587
SMTP_PASS = 'password_here'


######################
#   FUNCTION BLOCK   #
######################

def send_mail(sbj, ms):
    if SEND_MAIL:
        msg = MIMEMultipart()
        msg['Subject'] = (sbj)
        msg['From'] = 'root@%s' % HOSTNAME
        msg['To'] = RECIPIENT[0]
        body = MIMEText(ms, 'plain')
        msg.attach(body)
        smtpconnect = smtplib.SMTP(SMTP_SRV, SMTP_PORT)
        smtpconnect.starttls()
        smtpconnect.login(SENDER, SMTP_PASS)
        smtpconnect.sendmail(SENDER, RECIPIENT, msg.as_string())
        smtpconnect.quit()
    else:
        pass


def now_time():
    now = datetime.datetime.now()
    f_time = now.strftime('%Y.%m.%d_%H:%M:%S')
    return f_time


def check_file(tmp_file_path):
    if not os.path.isfile(tmp_file_path):
        return False
    else:
        return True


def copy_tmp_file(cur_file_path, tmp_file_path):
    if not os.path.isfile(tmp_file_path):
        shutil.copyfile(cur_file_path, tmp_file_path)
        return False
    else:
        return True


def get_schema(db, schema_out_file):
    get_schema = "pg_dump --schema-only %s > %s" % (db, schema_out_file)
    ret = subprocess.Popen(get_schema, shell=True)
    ret.communicate()


def get_different_strings(first_file, second_file):
    '''Get two string arrays and return different strings'''
    diff_string_list = list(set(first_file) - set(second_file))
    return diff_string_list


def create_msg(msg, some_list):
    L = []
    L.append(msg)
    for s in some_list:
        L.append(str(s))

    return ''.join(L)


if __name__ == '__main__':

    # Check the TMP_DIR:
    if os.path.isdir(sys.argv[2]):
        TMP_DIR = sys.argv[2]
    else:
        print('Error: %s, no such directory')
        sys.exit(1)

    full_msg = ''

    prev_schema_file = TMP_DIR+'/'+DB+'_schema'

    if not check_file(prev_schema_file):
        msg = prev_schema_file+' not exists, create it and exit.'
        print(msg)

        get_schema(DB, prev_schema_file)

    else:
        cur_schema_file = TMP_DIR+'/'+DB+'_schema.tmp'
        get_schema(DB, cur_schema_file)

        msg_list = []

        # Open and read the current file
        with open(cur_schema_file) as cf:
            cur_file = cf.readlines()

        # ...and the conffile.tmp
        with open(prev_schema_file) as of:
            old_file = of.readlines()

        # If schemas are equal:
        if cur_file == old_file:
            sys.exit(0)

        # Get differences between the file and the file.tmp:
        cur_file_new_strings = get_different_strings(cur_file, old_file)
        old_file_changed_strings = get_different_strings(old_file, cur_file)

        if len(cur_file_new_strings) != 0:
            new_string_list = get_different_strings(
                cur_file_new_strings, old_file)
            msg = '\nThis lines have been ++ADDED++ to the %s schema:\n' % DB
            msg_list.append(create_msg(msg, new_string_list))

        if len(old_file_changed_strings) != 0:
            old_string_list = get_different_strings(
                old_file_changed_strings, cur_file)
            msg = '\nThis lines have been --DELETED-- '\
                  'from the %s schema:\n' % DB
            msg_list.append(create_msg(msg, old_string_list))

        # Replace files for the next check:
        shutil.copyfile(cur_schema_file, prev_schema_file)

        full_msg = ''.join(msg_list).rstrip('\n')

    if full_msg:
        sbj = '%s schema has been CHANGED on ==%s==' % (DB, HOSTNAME)

        print(sbj)
        print(full_msg)
        send_mail(sbj, full_msg)

sys.exit(0)