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 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 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=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)
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 dsn(self):
return get_pg_dsn(pghost=self.get_parameter_value('pghost'),
pgport=self.get_parameter_value('pgport'),
pgdatabase=self.get_parameter_value('pgdatabase'),
pguser=self.get_parameter_value('pguser'),
pgpassword=self.get_parameter_value('pgpassword'))
def dsn(self):
return get_pg_dsn(pghost=self.host,
pgport=self.port,
pgdatabase=self.database,
pguser=self.user,
pgpassword=self.password)