def getDBInfoFromToolDB():
toolsdb = DataBase(host='dbtool01s', database='tripmaster_dbtool')
sql_dbinfo = '''SELECT v_hostname, dbname, id FROM db_ops_database
WHERE dbname NOT IN (\'daodao_hedwig_hive_metastore\', \'daodao_swedwig_hive_metastore\',\'tripmaster_test\',\'kv1\',\'kv2\',\'kv3\',\'kv4\',\'kv5\',\'kv6\',\'kv7\',\'kv8\',\'kv9\',\'kv10\',\'kv11\',\'kv12\')
ORDER BY id; '''
dbsinfo = toolsdb.query(sql_dbinfo)
return dbsinfo
def queryInfoFromOps(sql):
db = DataBase(host='dbtool01s.daodao.com',
database='tripmaster_dbtool',
user='******',
password='',
port=5432)
result = db.query(sql)
return result
def getDBIsPgSync(host, dbname):
sql_pgsync_tables = 'SELECT count(*) FROM pg_tables WHERE tablename LIKE \'dbmirror%\'; '
sql_slave_count = 'SELECT count(*) FROM dbmirror_mirrorhost; '
db = DataBase(host=host,
database=dbname,
user='******',
password='',
port=5432)
pgsync_tables = db.query(sql_pgsync_tables)
if pgsync_tables[0][0]:
slave_count = db.query(sql_slave_count)
if slave_count[0][0]:
return True
else:
return False
else:
return False
def getAllPgSyncDBFromTool():
sql = ''' SELECT id, dbname, v_hostname FROM db_ops_database
WHERE is_pgsync = 't'
AND env = \'live\'
AND pgsync_mode = \'rw\'
ORDER BY v_hostname;
'''
db = DataBase(host='dbtool01s.daodao.com', database='tripmaster_dbtool')
result = db.query(sql)
return result
def getPgSyncHosts(host, dbname):
result = []
sql = ''' SELECT slave_host_name, slave_database, last_run FROM dbmirror_mirrorhost
WHERE slave_database IS NOT NULL
'''
try:
db = DataBase(host=host, database=dbname, user='******')
result = db.query(sql)
except Exception as e:
logger.error('Get PgSync Hosts From DB Error: ' + str(e))
finally:
return result
def getAllDBNameFromServer(host):
dbs = []
try:
sql = 'SELECT datname FROM pg_database WHERE datname NOT IN (\'template0\',\'template1\',\'postgres\')'
db = DataBase(host=host,
database='postgres',
user='******',
password='',
port=5432)
dbs = db.query(sql)
except Exception as e:
logger.error(e)
finally:
return dbs
def getPgSyncMode(host, dbname):
pgsyncmode = [
[''],
]
try:
sql = 'SELECT replication_mode FROM dbmirror_mode;'
db = DataBase(host=host,
database=dbname,
user='******',
password='',
port=5432)
pgsyncmode = db.query(sql)
except Exception as e:
logger.error(e)
return pgsyncmode[0][0]
def getDBSizeFromServer(host, dbname):
dbsize = [
[''],
]
try:
sql = 'SELECT pg_size_pretty(pg_database_size(\'%s\'))' % dbname
db = DataBase(host=host,
database=dbname,
user='******',
password='',
port=5432)
dbsize = db.query(sql)
except Exception as e:
logger.error(e)
return dbsize[0][0]
def getSecondaryDBIdFromTool(v_host, dbname):
if v_host == 'tripmonster':
v_host = 'tm01c'
if dbname == 'tripmonster' or dbname == 'ddmonster':
sql = '''SELECT id FROM db_ops_database
WHERE dbname = \'%s\'
AND v_hostname = \'%s\'
''' % (dbname, v_host)
else:
sql = ''' SELECT id FROM db_ops_database
WHERE dbname = \'%s\'
AND pgsync_mode = \'ro\'
AND env = \'live\'
''' % (dbname)
db = DataBase(host='dbtool01s.daodao.com', database='tripmaster_dbtool')
result = db.query(sql)
return result[0][0]
def updateServerIsPrimary():
for f_host in getAllDBHostName():
try:
hostname = f_host[0]
sql = 'SELECT pg_is_in_recovery();'
# primary
u_sql_p = 'UPDATE db_ops_host SET is_master = true WHERE full_hostname = \'%s\'' % hostname
# slave
u_sql_s = 'UPDATE db_ops_host SET is_master = false WHERE full_hostname = \'%s\'' % hostname
db = DataBase(host=hostname,
database='postgres',
user='******',
password='',
port=5432)
result = db.query(sql)
if result[0][0] == False:
updateInfoToOps(u_sql_p)
else:
updateInfoToOps(u_sql_s)
except Exception as e:
logger.error('updateServerIsPrimary error: hostname: ' + hostname +
' , exception: ' + str(e))
continue
updateInfoToOps('CLUSTER db_ops_host USING db_ops_host_pkey;')