Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/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-- '
- msg += '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)
Add Comment
Please, Sign In to add comment