def drop_possibly_incompatible_objects(self):
from patroni.postgresql.connection import get_connection_cursor
logger.info(
'Dropping objects from the cluster which could be incompatible')
conn_kwargs = self.local_conn_kwargs
for d in self._get_all_databases():
conn_kwargs['database'] = d
with get_connection_cursor(**conn_kwargs) as cur:
logger.info(
'Executing "DROP FUNCTION metric_helpers.pg_stat_statements" in the database="%s"',
d)
cur.execute(
"DROP FUNCTION IF EXISTS metric_helpers.pg_stat_statements(boolean) CASCADE"
)
for ext in ('pg_stat_kcache', 'pg_stat_statements'
) + self._INCOMPATIBLE_EXTENSIONS:
logger.info(
'Executing "DROP EXTENSION IF EXISTS %s" in the database="%s"',
ext, d)
cur.execute("DROP EXTENSION IF EXISTS {0}".format(ext))
cur.execute(
"SELECT oid::regclass FROM pg_catalog.pg_class WHERE relpersistence = 'u'"
)
for unlogged in cur.fetchall():
logger.info('Truncating unlogged table %s', unlogged[0])
try:
cur.execute('TRUNCATE {0}'.format(unlogged[0]))
except Exception as e:
logger.error('Failed: %r', e)
def reset_custom_statistics_target(self):
from patroni.postgresql.connection import get_connection_cursor
logger.info('Resetting non-default statistics target before analyze')
self._statistics = defaultdict(lambda: defaultdict(dict))
conn_kwargs = self.postgresql.local_conn_kwargs
for d in self.postgresql.query(
'SELECT datname FROM pg_catalog.pg_database WHERE datallowconn'
):
conn_kwargs['dbname'] = d[0]
with get_connection_cursor(**conn_kwargs) as cur:
cur.execute(
'SELECT attrelid::regclass, quote_ident(attname), attstattarget '
'FROM pg_catalog.pg_attribute WHERE attnum > 0 AND NOT attisdropped AND attstattarget > 0'
)
for table, column, target in cur.fetchall():
query = 'ALTER TABLE {0} ALTER COLUMN {1} SET STATISTICS -1'.format(
table, column)
logger.info(
"Executing '%s' in the database=%s. Old value=%s",
query, d[0], target)
cur.execute(query)
self._statistics[d[0]][table][column] = target
def check_leader_is_not_in_recovery(self, **kwargs):
if not kwargs.get('database'):
kwargs['database'] = self._postgresql.database
try:
with get_connection_cursor(connect_timeout=3, options='-c statement_timeout=2000', **kwargs) as cur:
cur.execute('SELECT pg_catalog.pg_is_in_recovery()')
if not cur.fetchone()[0]:
return True
logger.info('Leader is still in_recovery and therefore can\'t be used for rewind')
except Exception:
return logger.exception('Exception when working with leader')
def drop_possibly_incompatible_extensions(self):
from patroni.postgresql.connection import get_connection_cursor
logger.info('Dropping extensions from the cluster which could be incompatible')
conn_kwargs = self.local_conn_kwargs
for d in self._get_all_databases():
conn_kwargs['database'] = d
with get_connection_cursor(**conn_kwargs) as cur:
for ext in self._INCOMPATIBLE_EXTENSIONS:
logger.info('Executing "DROP EXTENSION IF EXISTS %s" in the database="%s"', ext, d)
cur.execute("DROP EXTENSION IF EXISTS {0}".format(ext))
def get_replication_connection_cursor(self,
host='localhost',
port=5432,
**kwargs):
conn_kwargs = self.config.replication.copy()
conn_kwargs.update(host=host,
port=int(port) if port else None,
user=conn_kwargs.pop('username'),
connect_timeout=3,
replication=1,
options='-c statement_timeout=2000')
with get_connection_cursor(**conn_kwargs) as cur:
yield cur
def drop_possibly_incompatible_objects(self):
conn_kwargs = self.config.local_connect_kwargs
for p in ['connect_timeout', 'options']:
conn_kwargs.pop(p, None)
for d in self.query('SELECT datname FROM pg_catalog.pg_database WHERE datallowconn'):
conn_kwargs['database'] = d[0]
with get_connection_cursor(**conn_kwargs) as cur:
cur.execute("SET synchronous_commit = 'local'")
logger.info('Executing "DROP FUNCTION metric_helpers.pg_stat_statements" in the database="%s"', d[0])
cur.execute("DROP FUNCTION metric_helpers.pg_stat_statements(boolean) CASCADE")
logger.info('Executing "DROP EXTENSION IF EXISTS amcheck_next" in the database="%s"', d[0])
cur.execute("DROP EXTENSION IF EXISTS amcheck_next")
def checkpoint(self, connect_kwargs=None):
check_not_is_in_recovery = connect_kwargs is not None
connect_kwargs = connect_kwargs or self.config.local_connect_kwargs
for p in ['connect_timeout', 'options']:
connect_kwargs.pop(p, None)
try:
with get_connection_cursor(**connect_kwargs) as cur:
cur.execute("SET statement_timeout = 0")
if check_not_is_in_recovery:
cur.execute('SELECT pg_catalog.pg_is_in_recovery()')
if cur.fetchone()[0]:
return 'is_in_recovery=true'
return cur.execute('CHECKPOINT')
except psycopg2.Error:
logger.exception('Exception during CHECKPOINT')
return 'not accessible or not healty'
def update_extensions(self):
from patroni.postgresql.connection import get_connection_cursor
conn_kwargs = self.local_conn_kwargs
for d in self._get_all_databases():
conn_kwargs['database'] = d
with get_connection_cursor(**conn_kwargs) as cur:
cur.execute('SELECT quote_ident(extname) FROM pg_catalog.pg_extension')
for extname in cur.fetchall():
query = 'ALTER EXTENSION {0} UPDATE'.format(extname[0])
logger.info("Executing '%s' in the database=%s", query, d)
try:
cur.execute(query)
except Exception as e:
logger.error('Failed: %r', e)
def reanalyze(self):
from patroni.postgresql.connection import get_connection_cursor
if not self._statistics:
return
conn_kwargs = self.postgresql.local_conn_kwargs
for db, val in self._statistics.items():
conn_kwargs['dbname'] = db
with get_connection_cursor(**conn_kwargs) as cur:
for table in val.keys():
query = 'ANALYZE {0}'.format(table)
logger.info("Executing '%s' in the database=%s", query, db)
try:
cur.execute(query)
except Exception:
logger.error("Failed to execute '%s'", query)
def drop_possibly_incompatible_objects(self):
from patroni.postgresql.connection import get_connection_cursor
logger.info(
'Dropping objects from the cluster which could be incompatible')
conn_kwargs = self.local_conn_kwargs
for d in self.query(
'SELECT datname FROM pg_catalog.pg_database WHERE datallowconn'
):
conn_kwargs['database'] = d[0]
with get_connection_cursor(**conn_kwargs) as cur:
logger.info(
'Executing "DROP FUNCTION metric_helpers.pg_stat_statements" in the database="%s"',
d[0])
cur.execute(
"DROP FUNCTION IF EXISTS metric_helpers.pg_stat_statements(boolean) CASCADE"
)
logger.info('Executing "DROP EXTENSION pg_stat_kcache"')
cur.execute("DROP EXTENSION IF EXISTS pg_stat_kcache")
logger.info('Executing "DROP EXTENSION pg_stat_statements"')
cur.execute("DROP EXTENSION IF EXISTS pg_stat_statements")
logger.info(
'Executing "DROP EXTENSION IF EXISTS amcheck_next" in the database="%s"',
d[0])
cur.execute("DROP EXTENSION IF EXISTS amcheck_next")
if d[0] == 'postgres':
logger.info(
'Executing "DROP TABLE postgres_log CASCADE" in the database=postgres'
)
cur.execute(
'DROP TABLE IF EXISTS public.postgres_log CASCADE')
cur.execute(
"SELECT oid::regclass FROM pg_catalog.pg_class WHERE relpersistence = 'u'"
)
for unlogged in cur.fetchall():
logger.info('Truncating unlogged table %s', unlogged[0])
try:
cur.execute('TRUNCATE {0}'.format(unlogged[0]))
except Exception as e:
logger.error('Failed: %r', e)
def get_replication_connection_cursor(self,
host='localhost',
port=5432,
database=None,
**kwargs):
replication = self.config.replication
extra_kwargs = {
k: v
for k, v in replication.items()
if k not in ('username', 'password', 'connect_timeout', 'options')
}
with get_connection_cursor(host=host,
port=int(port),
database=database or self._database,
replication=1,
user=replication['username'],
password=replication.get('password'),
connect_timeout=3,
options='-c statement_timeout=2000',
**extra_kwargs) as cur:
yield cur
def restore_custom_statistics_target(self):
from patroni.postgresql.connection import get_connection_cursor
if not self._statistics:
return
conn_kwargs = self.postgresql.local_conn_kwargs
logger.info('Restoring default statistics targets after upgrade')
for db, val in self._statistics.items():
conn_kwargs['dbname'] = db
with get_connection_cursor(**conn_kwargs) as cur:
for table, val in val.items():
for column, target in val.items():
query = 'ALTER TABLE {0} ALTER COLUMN {1} SET STATISTICS {2}'.format(
table, column, target)
logger.info("Executing '%s' in the database=%s", query,
db)
try:
cur.execute(query)
except Exception:
logger.error("Failed to execute '%s'", query)
def sync_replication_slots(self, cluster):
if self._postgresql.major_version >= 90400:
try:
self.load_replication_slots()
slots = cluster.get_replication_slots(self._postgresql.name,
self._postgresql.role)
# drop old replication slots which are not presented in desired slots
for name in set(self._replication_slots) - set(slots):
if not self.drop_replication_slot(name):
logger.error("Failed to drop replication slot '%s'",
name)
self._schedule_load_slots = True
immediately_reserve = ', true' if self._postgresql.major_version >= 90600 else ''
logical_slots = defaultdict(dict)
for name, value in slots.items():
if name in self._replication_slots and not compare_slots(
value, self._replication_slots[name]):
logger.info(
"Trying to drop replication slot '%s' because value is changing from %s to %s",
name, self._replication_slots[name], value)
if not self.drop_replication_slot(name):
logger.error(
"Failed to drop replication slot '%s'", name)
self._schedule_load_slots = True
continue
self._replication_slots.pop(name)
if name not in self._replication_slots:
if value['type'] == 'physical':
try:
self._query((
"SELECT pg_catalog.pg_create_physical_replication_slot(%s{0})"
+
" WHERE NOT EXISTS (SELECT 1 FROM pg_catalog.pg_replication_slots"
+
" WHERE slot_type = 'physical' AND slot_name = %s)"
).format(immediately_reserve), name, name)
except Exception:
logger.exception(
"Failed to create physical replication slot '%s'",
name)
self._schedule_load_slots = True
elif value[
'type'] == 'logical' and name not in self._replication_slots:
logical_slots[value['database']][name] = value
# create new logical slots
for database, values in logical_slots.items():
conn_kwargs = self._postgresql.config.local_connect_kwargs
conn_kwargs['database'] = database
with get_connection_cursor(**conn_kwargs) as cur:
for name, value in values.items():
try:
cur.execute(
"SELECT pg_catalog.pg_create_logical_replication_slot(%s, %s)"
+
" WHERE NOT EXISTS (SELECT 1 FROM pg_catalog.pg_replication_slots"
+
" WHERE slot_type = 'logical' AND slot_name = %s)",
(name, value['plugin'], name))
except Exception:
logger.exception(
"Failed to create logical replication slot '%s' plugin='%s'",
name, value['plugin'])
self._schedule_load_slots = True
self._replication_slots = slots
except Exception:
logger.exception('Exception when changing replication slots')
self._schedule_load_slots = True
def get_replication_connection_cursor(self, host='localhost', port=5432, database=None, **kwargs):
replication = self.config.replication
with get_connection_cursor(host=host, port=int(port), database=database or self._database, replication=1,
user=replication['username'], password=replication.get('password'),
connect_timeout=3, options='-c statement_timeout=2000') as cur:
yield cur
