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_backupkey():
con = DB(dbname=source_db, host=source_host, user=source_user)
opts = backup_command[11:-13]
key = con.query("SELECT dump_key FROM gpcrondump_history where options = '%s' AND exit_text = 'COMPLETED' ORDER BY dump_key desc limit 1" %opts)
row = key.dictresult()
dump_key = row[0]["dump_key"]
return int(dump_key)
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
def gpdbrestore_restore():
target_schema_check()
restore_command = "gpdbrestore -t %s --noanalyze --redirect %s -a 2> /dev/null" % (
get_backupkey(), target_db)
os.popen(restore_command)
schemas = ''
schema_file = open(source_schemafile, 'r')
schema_file.seek(0)
for num, line in enumerate(schema_file, 1):
schema = line.rstrip('\n')
con = DB(dbname=target_db)
get_schema = con.query(
"SELECT nspname FROM pg_namespace where nspname = \'%s\'" % schema)
row = get_schema.getresult()
if row:
logging.info("Restore completed for %s schema" % schema)
else:
logging.info("Restore failed for %s schema" % schema)
def pg_dump_restore():
target_schema_check()
restore_command="psql -d %s -h %s -U %s < %s" %(target_db,target_host,target_user,backup_file)
os.popen(restore_command)
schemas = ''
schema_file = open(source_schemafile,'r')
schema_file.seek(0)
for num,line in enumerate(schema_file, 1):
schema = line.rstrip('\n')
con = DB(dbname=target_db)
get_schema = con.query("SELECT nspname FROM pg_namespace where nspname = \'%s\'" %schema)
row = get_schema.getresult()
if row:
logging.info("Restore completed for %s schema" %schema)
sendmail("pg_dump restore completed")
else:
logging.error("Restore failed for %s schema" %schema)
sendmail("Restore failed for %s schema" %schema)
sys.exit()
Content-type: text/html
Subject: Long running queries in """ + ENVIRONMENT + """\n"""
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)