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)