def target_schema_check():
con = DB(dbname=target_db)
schema_list = open(source_schemafile,'r')
schema_list.seek(0)
for schema in schema_list:
schema = schema.rstrip('\n')
date = now.strftime("%Y%m%d%H%M%S")
logging.info("Checking if %s schema exists in %s database" %(schema,target_db))
query = "SELECT nspname FROM pg_namespace where nspname = \'%s\'" %schema
print(query)
get_schema = con.query(query)
row = get_schema.getresult()
print(row)
if row:
logging.info("%s schema already exists in %s database" %(schema,target_db))
logging.info("Renaming %s schema to %s_hold_%s" %(schema,schema,date))
con.query("ALTER SCHEMA %s RENAME to %s_hold_%s" %(schema,schema,date))
nsp = con.query("SELECT nspname FROM pg_namespace where nspname = \'%s\'" %schema)
row = nsp.getresult()
if row:
logging.error("Failed to rename %s schema to %s_hold_%s" %(schema,schema,date))
logging.error("Please rename the schemas and run restore manually using timestamp: %s" %get_backupkey())
sendmail("Failed to rename %s schema to %s_hold_%s, Please Check logs" %(schema,schema,date))
sys.exit()
else:
logging.info("%s schema renamed successfully to %s_hold_%s" %(schema,schema,date))
else:
logging.info("%s schema doesn't exists in %s. Good to restore backup" %(schema,target_db))
schema_list.close()
con.close()
def task(db_object):
global counter
con = DB(dbname=database)
con.query("vacuum %s" % (db_object))
con.close()
with counter.get_lock():
counter.value += 1
logger.info(str(counter.value) + " objects completed")
def run_analyze(table):
global counter
con = DB(dbname = vDatabase)
con.query("analyze %s" %table)
con.close()
with counter.get_lock():
counter.value += 1
if counter.value % 50 == 0 or counter.value == len(get_tables()):
logger.info("analyze status: completed " + str(counter.value) + " out of " + str(len(get_tables())) + " tables or partitions ")
def get_objects():
database = configuration.DATABASE
con = DB(dbname=database)
tables = con.query("select schemaname||'.'||tablename as tablename from pg_tables where schemaname='public'")
tabledict = tables.dictresult()
tablelist = []
con.close()
for dict in tabledict:
tablelist.append(dict.get('tablename')) # You should replace the 'tablename' with column name from your SQL query in tables variable
return tablelist
LONG_RUNNING_QUERIES = os.popen(LONG_QUERY_PSQL_STRING).read()
message = message + LONG_RUNNING_QUERIES
try:
smtpObj = smtplib.SMTP('localhost')
smtpObj.sendmail(sender, receivers, message)
logging.info("Successfully sent email")
except SMTPException:
logging.error("Unable to send email")
logging.info("Checking if queries are running for long time")
try:
con = DB()
get_pid = con.query(LONG_QUERY_PID)
pid = str(get_pid.getresult()[0][0])
con.close()
except IndexError:
logging.info("No queries are running for longer time")
sys.exit()
logging.info("Long running pid: %s" % pid)
if pid and not os.path.isfile('/tmp/long_running_pid_%s' % pid):
os.popen(LONG_QUERY_PSQL_STRING)
sendemail()
os.mknod('/tmp/long_running_pid_%s' % pid)
logging.info("Lock file created for pid %s" % pid)
elif pid and os.path.isfile(
'/tmp/log_running_pid_%s' % pid) and (minute == 30 or minute == 00):
os.popen(LONG_QUERY_PSQL_STRING)
sendemail()
def get_tables():
con = DB(dbname = vDatabase)
tables = con.get_tables()
con.close()
return tables