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