def _wait_startup_database(self, max_retry=300, sleep_time=2):
is_in_recovery_sql = 'SELECT pg_is_in_recovery()'
# use psycopg2
for i in range(max_retry + 1):
try:
with get_pg_connection(
dsn=get_pg_dsn(pghost=self.host,
pgport=self.port,
pguser=self.user,
pgpassword=self.password,
pgdatabase=self.database)) as conn:
with conn.cursor() as cur:
cur.execute(is_in_recovery_sql)
result = cur.fetchone()
if not result[0]:
logger.debug('PostgreSQL is already running.')
return
else:
logger.debug(
'PostgreSQL is in the process of recovery.')
time.sleep(sleep_time)
except psycopg2.OperationalError:
logger.debug('PostgreSQL is not running.')
time.sleep(sleep_time)
raise TimeoutError('PostgreSQL startup did not complete.')
def extract_workload(self):
extract_workload_sql = '''
SELECT
-- log_time,
-- query_stat_time = log_time - duration - start_unix_time
(log_time::timestamp(3) with time zone - substring(message from '(?<=duration: ).*ms')::interval
- to_timestamp(%s)) AS query_stat_time,
-- database_name,
session_id,
-- substring(message from '(?<=duration: ).*(?= ms)') AS duration,
substring(message from '(?<=statement: ).*') AS statement
FROM
csv_log
WHERE
log_time > to_timestamp(%s) AND
log_time <= to_timestamp(%s) AND
database_name = %s AND
message LIKE '%%duration%%'
ORDER BY session_id,
session_line_num;
-- log_time;
'''
with get_pg_connection(dsn=self.workload_sampling_config.dsn) as conn:
with conn.cursor(cursor_factory=DictCursor) as cur:
cur.execute(
extract_workload_sql,
(self.start_unix_time, self.start_unix_time,
self.end_unix_time, self.postgres_server_config.database))
workload_rows = cur.fetchall()
# logger.debug("workload_rows : {}".format(workload_rows))
self._create_transactions(workload_rows)
def execute_sql_file(self, sql_filepath):
logger.debug("start execute {}".format(sql_filepath))
with get_pg_connection(dsn=self.postgres_server_config.dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(open(sql_filepath, "r").read())
logger.debug("finish execute {}".format(sql_filepath))
def _reload_conf(self):
reload_sql = "SELECT pg_reload_conf()"
# use psycopg2
with get_pg_connection(dsn=self.postgres_server_config.dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(reload_sql)
def _create_csv_log_table(self, dsn):
create_table_sql = "CREATE TABLE IF NOT EXISTS {} (" \
"log_time timestamp(3) with time zone," \
"user_name text," \
"database_name text," \
"process_id integer," \
"connection_from text," \
"session_id text," \
"session_line_num bigint," \
"command_tag text," \
"session_start_time timestamp with time zone," \
"virtual_transaction_id text," \
"transaction_id bigint," \
"error_severity text," \
"sql_state_code text," \
"message text," \
"detail text," \
"hint text," \
"internal_query text," \
"internal_query_pos integer," \
"context text," \
"query text," \
"query_pos integer," \
"location text," \
"application_name text," \
"PRIMARY KEY (session_id, session_line_num));".format(self._csv_log_table_name)
with get_pg_connection(dsn=dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(create_table_sql)
def get_number_of_xact_commit(self):
get_number_of_xact_commit_sql = "SELECT xact_commit FROM pg_stat_database WHERE datname = %s"
with get_pg_connection(dsn=self.postgres_server_config.dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor(cursor_factory=DictCursor) as cur:
cur.execute(get_number_of_xact_commit_sql,
(self.postgres_server_config.database, ))
xact_commit = cur.fetchone()["xact_commit"]
return xact_commit
def vacuum_database(self):
"""
run vacuum analyze
"""
logger.debug("Run VACUUM ANALYZE.")
vacuum_analyze_sql = "VACUUM ANALYZE"
with get_pg_connection(dsn=self.postgres_server_config.dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(vacuum_analyze_sql)
def drop_database(self):
drop_database_sql = "DROP DATABASE {} ".format(
self.postgres_server_config.database)
backup_database_dsn = self._get_backup_database_dsn()
with get_pg_connection(dsn=backup_database_dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(drop_database_sql)
logger.debug("The database has been deleted. Database : {}".format(
self.postgres_server_config.database))
def get_recovery_wal_size(self):
latest_checkpoint_lsn = self._get_latest_checkpoint_lsn()
get_recovery_wal_size_sql = "SELECT pg_current_wal_insert_lsn() - '{}'::pg_lsn AS wa_size".format(
latest_checkpoint_lsn)
with get_pg_connection(dsn=self.postgres_server_config.dsn) as conn:
with conn.cursor(cursor_factory=DictCursor) as cur:
cur.execute(get_recovery_wal_size_sql)
row = cur.fetchone()
recovery_wal_size = row['wa_size']
return float(recovery_wal_size)
def get_parameter_value(self, param_name=None):
if param_name is None:
raise ValueError('Parameter name is not specified.')
get_parameter_value_sql = "SELECT setting FROM pg_catalog.pg_settings WHERE name = %s"
# use psycopg2
with get_pg_connection(dsn=self.postgres_server_config.dsn) as conn:
with conn.cursor(cursor_factory=DictCursor) as cur:
cur.execute(get_parameter_value_sql, (param_name, ))
row = cur.fetchone()
return row['setting']
def _get_csv_log_file_path(self):
get_parameter_value_sql = "SELECT pg_current_logfile('csvlog');"
# use psycopg2
with get_pg_connection(dsn=self._postgres_server_config.dsn) as conn:
with conn.cursor(cursor_factory=DictCursor) as cur:
cur.execute(get_parameter_value_sql)
row = cur.fetchone()
csv_file_path = row['pg_current_logfile']
if csv_file_path[0] != "/":
csv_file_path = os.path.join(self._postgres_server_config.pgdata,
csv_file_path)
return csv_file_path
def create_database_use_backup_database(self):
create_database_use_backup_sql = "CREATE DATABASE {} TEMPLATE {}".format(
self.postgres_server_config.database,
self._get_backup_database_name())
backup_database_dsn = self._get_backup_database_dsn()
with get_pg_connection(dsn=backup_database_dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(create_database_use_backup_sql)
logger.debug(
"Create a database using the backup database as a template. Database : {}, Backup Database : {}"
.format(self.postgres_server_config.database,
self._get_backup_database_name()))
def load_csv_to_database(self, copy_dir="/tmp", dsn=None):
self._copy_csv_logfile_to_local(
copy_dir) # copy logfile to directory(localhost)
self._create_csv_log_table(dsn)
self._truncate_csv_log_table(dsn) # truncate csv log table
with get_pg_connection(dsn=dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
with open(self.csv_log_local_file_path) as f:
# cur.copy_from(f, self.csv_log_table_name, sep=',')
cur.copy_expert(
"copy {} from stdin (format csv)".format(
self._csv_log_table_name), f)
def create_backup_database(self):
logger.debug("Start backing up the database. Database : {} ".format(
self.postgres_server_config.database))
create_database_backup_sql = "CREATE DATABASE {} TEMPLATE {}".format(
self._get_backup_database_name(),
self.postgres_server_config.database)
with get_pg_connection(dsn=self.postgres_server_config.dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(create_database_backup_sql)
logger.debug("The backup is complete. Database : {} ".format(
self._get_backup_database_name()))
def vacuum_database(self):
"""
run vacuum analyze
"""
vacuum_analyze_sql = "VACUUM ANALYZE"
with get_pg_connection(
dsn=get_pg_dsn(pghost=self.host,
pgport=self.port,
pguser=self.user,
pgpassword=self.password,
pgdatabase=self.database)) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(vacuum_analyze_sql)
def reset_param(self):
"""
reset postgresql.auto.conf
"""
# postgresql.auto.conf clear
alter_system_sql = "ALTER SYSTEM RESET ALL"
# use psycopg2
with get_pg_connection(dsn=self.postgres_server_config.dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(alter_system_sql)
self.reset_database(
is_free_cache=False) # restart PostgreSQL for reset parameter
def set_parameter(self,
param_name=None,
param_value=None,
pg_reload=False):
if param_name is None or param_value is None:
raise ValueError('Parameter or Value is not specified.')
alter_system_sql = "ALTER SYSTEM SET {} = '{}'".format(
param_name, param_value)
with get_pg_connection(dsn=self.postgres_server_config.dsn) as conn:
with conn.cursor(cursor_factory=DictCursor) as cur:
conn.set_session(autocommit=True)
cur.execute(alter_system_sql)
if pg_reload:
self._reload_conf()
def check_exist_backup_database(self):
check_exist_backup_database_sql = "SELECT count(datname) FROM pg_database WHERE datname = %s"
with get_pg_connection(dsn=self.postgres_server_config.dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor(cursor_factory=DictCursor) as cur:
cur.execute(check_exist_backup_database_sql,
(self._get_backup_database_name(), ))
result = cur.fetchone()["count"]
if result == 1:
logger.debug(
"Backup database already exists. Database : {}".format(
self._get_backup_database_name()))
return True
else:
logger.debug("There is no backup database.")
return False
def run(self, postgres_server_config: PostgresServerConfig):
start_time = time.time()
elapsed_times = 0
# sleep until first statement start
self._sleep_until_statement_start_time(start_time, self.query_start_time[0])
with get_pg_connection(dsn=postgres_server_config.dsn) as conn:
conn.autocommit = True
with conn.cursor() as cur:
for index in range(len(self.query_start_time)):
self._sleep_until_statement_start_time(start_time, self.query_start_time[index])
query_start_time = time.time()
cur.execute(self.statement[index])
# logger.info("Execute Statement : {}".format(self.statement[index]))
elapsed_times += (time.time() - query_start_time)
return elapsed_times
def change_param_to_trial_values(self, params_trial=None):
"""
change postgresql.auto.conf to trial values using ALTER SYSTEM
"""
# clear postgresql.auto.conf
self.reset_param()
# setting trial values
for param_trial in params_trial:
param_name, param_trial_value = self._convert_trial_value_unit(
param_trial)
alter_system_sql = "ALTER SYSTEM SET {} = '{}'".format(
param_name, param_trial_value)
# use psycopg2
with get_pg_connection(
dsn=self.postgres_server_config.dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(alter_system_sql)
def _wait_startup_database(self, max_retry=300, sleep_time=2):
is_in_recovery_sql = 'SELECT pg_is_in_recovery()'
logger.debug('Check the startup status of PostgreSQL.')
# use psycopg2
for i in range(max_retry + 1):
try:
with get_pg_connection(
dsn=self.postgres_server_config.dsn) as conn:
with conn.cursor() as cur:
cur.execute(is_in_recovery_sql)
result = cur.fetchone()
if not result[0]:
logger.debug('PostgreSQL is already running.')
return
else:
logger.debug(
'PostgreSQL is in the process of recovery.')
time.sleep(sleep_time)
except psycopg2.OperationalError:
logger.debug('PostgreSQL is not running.')
time.sleep(sleep_time)
raise TimeoutError('PostgreSQL startup did not complete.')
def reset_param(self):
"""
reset postgresql.auto.conf
"""
# postgresql.auto.conf clear
alter_system_sql = "ALTER SYSTEM RESET ALL"
# use psycopg2
with get_pg_connection(
dsn=get_pg_dsn(pghost=self.host,
pgport=self.port,
pguser=self.user,
pgpassword=self.password,
pgdatabase=self.database)) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(alter_system_sql)
# use psql(old version)
# alter_system_cmd = 'sudo -i -u {} {}/psql -h {} -U {} -d {} -Atqc "{}"' \
# .format(self.pg_os_user, self.bin, self.host, self.user, self.database, alter_system_sql)
# run_command(alter_system_cmd)
self.reset_database(
is_free_cache=False) # restart PostgreSQL for reset parameter
def _truncate_csv_log_table(self, dsn):
truncate_table_sql = "TRUNCATE {}".format(self._csv_log_table_name)
with get_pg_connection(dsn=dsn) as conn:
conn.set_session(autocommit=True)
with conn.cursor() as cur:
cur.execute(truncate_table_sql)
