def insertTableInfo():
truncOpsTable('table_ops_tables')
resetOpsSequence('table_ops_tables_id_seq')
tooldb = DataBase(host='dbtool01s', database='tripmaster_dbtool')
dbsinfo = getDBInfoFromToolDB()
for dbinfo in dbsinfo:
try:
v_hostname = dbinfo[0]
dbname = dbinfo[1]
dbid = dbinfo[2]
db = DataBase(host=v_hostname, database=dbname)
tables = getTableNameFromDB(db)
logger.info('Collect Table info in ' + v_hostname + ':' + dbname)
for table in tables:
tablename = table[1]
schemaname = table[0]
tablesize = getTableSizeFromDB(db, schemaname, tablename)
tablerows = getTableRowsFromDB(db, schemaname, tablename)
is_sync = getTableTriggerNumFromDB(db, schemaname, tablename)
table_anylyze_vacuum = getTableAnylyzeVacuum(
db, schemaname, tablename)
table_auto_anylyze = table_anylyze_vacuum[0]
table_auto_vacuum = table_anylyze_vacuum[1]
sql_insertinfo = '''INSERT INTO table_ops_tables(table_name , schema_name, table_size , rows_count, sync_trigger, last_analyze, last_vacuum, db_id_id)
VALUES (\'%s\', \'%s\', \'%s\', \'%s\', \'%s\', %s, %s, %s)
''' % (tablename, schemaname, tablesize, tablerows, is_sync,
table_auto_anylyze, table_auto_vacuum, dbid)
tooldb.update(sql_insertinfo)
except Exception as e:
logger.error('insertTableInfo error: hostname: ' + hostname +
'dbname: ' + dbname + 'tablename: ' + tablename +
' , exception: ' + str(e))
def updateInfoToOps(sql):
db = DataBase(host='dbtool01s.daodao.com',
database='tripmaster_dbtool',
user='******',
password='',
port=5432)
db.update(sql)
def insertColumnInfo():
truncOpsTable('table_ops_columns')
resetOpsSequence('table_ops_columns_id_seq')
tooldb = DataBase(host='dbtool01s', database='tripmaster_dbtool')
dbsinfo = getDBInfoFromToolDB()
for dbinfo in dbsinfo:
v_hostname = dbinfo[0]
dbname = dbinfo[1]
dbid = dbinfo[2]
tablesinfo = getTablesInfoFromToolDB(tooldb, dbid)
logger.info('Collect Column info in ' + v_hostname + ':' + dbname)
for table in tablesinfo:
try:
tableid = table[0]
schemaname = table[1]
tablename = table[2]
db = DataBase(host=v_hostname, database=dbname)
cols = getColumnsInfoFromDB(db, schemaname, tablename)
if cols:
for col in cols:
colname = col[0]
coltype = col[1]
is_notnull = col[2]
sql_insertinfo = ''' INSERT INTO table_ops_columns(column_name, column_type, is_notnull, table_id_id)
VALUES (\'%s\', \'%s\', \'%s\', \'%s\')
''' % (colname, coltype, is_notnull, tableid)
tooldb.update(sql_insertinfo)
else:
logger.warning(
str('Host: ' + v_hostname + ', DB: ' + dbname +
', Table: ' + tablename))
except Exception as e:
logger.warning('Host: ' + v_hostname + str(e))
continue
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 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 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 insertPgSyncHostsInfo():
truncOpsTable('db_ops_pgsync')
resetOpsSequence('db_ops_pgsync_id_seq')
dbsinfo = getAllPgSyncDBFromTool()
tooldb = DataBase(host='dbtool01s.daodao.com',
database='tripmaster_dbtool')
for db in dbsinfo:
dbid_p = db[0]
dbname = db[1]
hostname = db[2]
mirrorhosts = getPgSyncHosts(hostname, dbname)
for mir_host in mirrorhosts:
host_s = mir_host[0]
dbname_s = mir_host[1]
last_run = mir_host[2]
dbid_s = getSecondaryDBIdFromTool(host_s, dbname_s)
sql = ''' INSERT INTO db_ops_pgsync(dbname, master_db_id_id, slave_db_id_id, last_run)
VALUES (\'%s\', \'%s\', \'%s\', \'%s\')
''' % (dbname, dbid_p, dbid_s, last_run)
try:
tooldb.update(sql)
except Exception as e:
logger.error('Insert PGSync Host ERROR: ' + str(e))
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;')