· 8 years ago · Apr 06, 2018, 12:10 AM
1#!/usr/bin/python
2# schema_check.py -
3# Checks changes of a Postgresql database schema
4# and send notifications about it.
5#
6# It may be critical for some cases
7# (running logical replication, for example)
8#
9# Author: Andreyk Klychkov aaklychkov@mail.ru
10# Licence: Copyleft free software
11# Data: 28.03.2018
12#
13# Syntax: ./schema_check.py DBNAME PATH_TO_TMP_DIR
14#
15# PATH_TO_TMP_DIR will be content two temporary file
16# The first of them is the previous database schema, the second is
17# the current schema. After getting it the script
18# creates two sets and compares them.
19#
20# Script may be executed by using cron with SEND_MAIL = 1
21# IMPORTANT! You must set up desired mail parameters in the
22# 'Mail params' section below.
23#
24# The mail notification content example:
25# --------------------------------------
26# This changes have been ++ADDED++ to the otp_db schema:
27# CREATE INDEX test_idx ON public.test1011 USING btree (id);
28# -- Name: test_idx; Type: INDEX; Schema: public; Owner: postgres
29#
30# This changes have been --DELETED-- from the otp_db schema:
31# CREATE TABLE public.test1 (
32# -- Name: test1; Type: TABLE; Schema: public; Owner: postgres
33# ALTER TABLE public.test1 OWNER TO postgres;
34#
35
36import datetime
37import os
38import shutil
39import smtplib
40import socket
41import subprocess
42import sys
43from email.mime.multipart import MIMEMultipart
44from email.mime.text import MIMEText
45
46
47########################
48# PARAMETERS BLOCK #
49########################
50
51# Check a number of command-line arguments:
52if len(sys.argv) != 3:
53 print('Syntax: ./schema_check.py DBNAME TMP_DIR')
54 sys.exit(1)
55
56# Main params:
57DB = sys.argv[1]
58TMP_DIR = sys.argv[2]
59
60# Common params:
61HOSTNAME = socket.gethostname()
62NOW = datetime.datetime.now()
63F_TIME = NOW.strftime('%Y%m%d-%H%M%S')
64
65# Mail params:
66SEND_MAIL = 1
67SENDER = 'report.maydomain@gmail.com'
68RECIPIENT = ['aaklychkov@mail.ru']
69SMTP_SRV = 'smtp.gmail.com'
70SMTP_PORT = 587
71SMTP_PASS = 'password_here'
72
73
74######################
75# FUNCTION BLOCK #
76######################
77
78def send_mail(sbj, ms):
79 if SEND_MAIL:
80 msg = MIMEMultipart()
81 msg['Subject'] = (sbj)
82 msg['From'] = 'root@%s' % HOSTNAME
83 msg['To'] = RECIPIENT[0]
84 body = MIMEText(ms, 'plain')
85 msg.attach(body)
86 smtpconnect = smtplib.SMTP(SMTP_SRV, SMTP_PORT)
87 smtpconnect.starttls()
88 smtpconnect.login(SENDER, SMTP_PASS)
89 smtpconnect.sendmail(SENDER, RECIPIENT, msg.as_string())
90 smtpconnect.quit()
91 else:
92 pass
93
94
95def now_time():
96 now = datetime.datetime.now()
97 f_time = now.strftime('%Y.%m.%d_%H:%M:%S')
98 return f_time
99
100
101def check_file(tmp_file_path):
102 if not os.path.isfile(tmp_file_path):
103 return False
104 else:
105 return True
106
107
108def copy_tmp_file(cur_file_path, tmp_file_path):
109 if not os.path.isfile(tmp_file_path):
110 shutil.copyfile(cur_file_path, tmp_file_path)
111 return False
112 else:
113 return True
114
115
116def get_schema(db, schema_out_file):
117 get_schema = "pg_dump --schema-only %s > %s" % (db, schema_out_file)
118 ret = subprocess.Popen(get_schema, shell=True)
119 ret.communicate()
120
121
122def get_different_strings(first_file, second_file):
123 '''Get two string arrays and return different strings'''
124 diff_string_list = list(set(first_file) - set(second_file))
125 return diff_string_list
126
127
128def create_msg(msg, some_list):
129 L = []
130 L.append(msg)
131 for s in some_list:
132 L.append(str(s))
133
134 return ''.join(L)
135
136
137if __name__ == '__main__':
138
139 # Check the TMP_DIR:
140 if os.path.isdir(sys.argv[2]):
141 TMP_DIR = sys.argv[2]
142 else:
143 print('Error: %s, no such directory')
144 sys.exit(1)
145
146 full_msg = ''
147
148 prev_schema_file = TMP_DIR+'/'+DB+'_schema'
149
150 if not check_file(prev_schema_file):
151 msg = prev_schema_file+' not exists, create it and exit.'
152 print(msg)
153
154 get_schema(DB, prev_schema_file)
155
156 else:
157 cur_schema_file = TMP_DIR+'/'+DB+'_schema.tmp'
158 get_schema(DB, cur_schema_file)
159
160 msg_list = []
161
162 # Open and read the current file
163 with open(cur_schema_file) as cf:
164 cur_file = cf.readlines()
165
166 # ...and the conffile.tmp
167 with open(prev_schema_file) as of:
168 old_file = of.readlines()
169
170 # If schemas are equal:
171 if cur_file == old_file:
172 sys.exit(0)
173
174 # Get differences between the file and the file.tmp:
175 cur_file_new_strings = get_different_strings(cur_file, old_file)
176 old_file_changed_strings = get_different_strings(old_file, cur_file)
177
178 if len(cur_file_new_strings) != 0:
179 new_string_list = get_different_strings(
180 cur_file_new_strings, old_file)
181 msg = '\nThis lines have been ++ADDED++ to the %s schema:\n' % DB
182 msg_list.append(create_msg(msg, new_string_list))
183
184 if len(old_file_changed_strings) != 0:
185 old_string_list = get_different_strings(
186 old_file_changed_strings, cur_file)
187 msg = '\nThis lines have been --DELETED-- '
188 msg += 'from the %s schema:\n' % DB
189 msg_list.append(create_msg(msg, old_string_list))
190
191 # Replace files for the next check:
192 shutil.copyfile(cur_schema_file, prev_schema_file)
193
194 full_msg = ''.join(msg_list).rstrip('\n')
195
196 if full_msg:
197 sbj = '%s schema has been CHANGED on ==%s==' % (DB, HOSTNAME)
198
199 print(sbj)
200 print(full_msg)
201 send_mail(sbj, full_msg)
202
203sys.exit(0)