def handle_execute_event(self, ev, dst_curs): """handle one EXECUTE event""" if self.copy_thread: return # parse event fname = ev.extra1 sql = ev.data # fixme: curs? pgver = dst_curs.connection.server_version if pgver >= 80300: dst_curs.execute("set local session_replication_role = 'local'") q = "select * from londiste.execute_start(%s, %s, %s, false)" res = self.exec_cmd(dst_curs, q, [self.queue_name, fname, sql], commit=False) ret = res[0]['ret_code'] if ret >= 300: self.log.warning("Skipping execution of '%s'", fname) return for stmt in skytools.parse_statements(sql): dst_curs.execute(stmt) q = "select * from londiste.execute_finish(%s, %s)" self.exec_cmd(dst_curs, q, [self.queue_name, fname], commit=False) if pgver >= 80300: dst_curs.execute("set local session_replication_role = 'replica'")
def handle_execute_event(self, ev, dst_curs): """handle one EXECUTE event""" if self.copy_thread: return # parse event fname = ev.extra1 sql = ev.data # fixme: curs? pgver = dst_curs.connection.server_version if pgver >= 80300: dst_curs.execute("set local session_replication_role = 'local'") q = "select * from londiste.execute_start(%s, %s, %s, false)" res = self.exec_cmd(dst_curs, q, [self.queue_name, fname, sql], commit = False) ret = res[0]['ret_code'] if ret >= 300: self.log.warning("Skipping execution of '%s'", fname) return for stmt in skytools.parse_statements(sql): dst_curs.execute(stmt) q = "select * from londiste.execute_finish(%s, %s)" self.exec_cmd(dst_curs, q, [self.queue_name, fname], commit = False) if pgver >= 80300: dst_curs.execute("set local session_replication_role = 'replica'")
def installer_apply_file(db, filename, log): """Find SQL file and apply it to db, statement-by-statement.""" fn = installer_find_file(filename) sql = open(fn, "r").read() if log: log.info("applying %s" % fn) curs = db.cursor() for stmt in skytools.parse_statements(sql): #log.debug(repr(stmt)) curs.execute(stmt)
def cmd_execute(self, *files): db = self.get_database('db') curs = db.cursor() for fn in files: fname = os.path.basename(fn) sql = open(fn, "r").read() q = "select * from londiste.execute_start(%s, %s, %s, true)" self.exec_cmd(db, q, [self.queue_name, fname, sql], commit = False) for stmt in skytools.parse_statements(sql): curs.execute(stmt) q = "select * from londiste.execute_finish(%s, %s)" self.exec_cmd(db, q, [self.queue_name, fname], commit = False) db.commit()
def handle_execute_event(self, ev, dst_curs): """handle one EXECUTE event""" if self.copy_thread: return # parse event fname = ev.extra1 s_attrs = ev.extra2 exec_attrs = ExecAttrs(urlenc=s_attrs) sql = ev.data # fixme: curs? pgver = dst_curs.connection.server_version if pgver >= 80300: dst_curs.execute("set local session_replication_role = 'local'") seq_map = {} q = "select seq_name, local from londiste.get_seq_list(%s) where local" dst_curs.execute(q, [self.queue_name]) for row in dst_curs.fetchall(): seq_map[row['seq_name']] = row['seq_name'] tbl_map = {} for tbl, t in self.table_map.items(): tbl_map[t.name] = t.dest_table q = "select * from londiste.execute_start(%s, %s, %s, false, %s)" res = self.exec_cmd(dst_curs, q, [self.queue_name, fname, sql, s_attrs], commit=False) ret = res[0]['ret_code'] if ret > 200: self.log.info("Skipping execution of '%s'", fname) if pgver >= 80300: dst_curs.execute( "set local session_replication_role = 'replica'") return if exec_attrs.need_execute(dst_curs, tbl_map, seq_map): self.log.info("%s: executing sql") xsql = exec_attrs.process_sql(sql, tbl_map, seq_map) for stmt in skytools.parse_statements(xsql): dst_curs.execute(stmt) else: self.log.info("%s: execution not needed on this node") q = "select * from londiste.execute_finish(%s, %s)" self.exec_cmd(dst_curs, q, [self.queue_name, fname], commit=False) if pgver >= 80300: dst_curs.execute("set local session_replication_role = 'replica'")
def restore_copy_ddl(self, ts, dst_db): self.log.info("%s: restoring DDL", ts.name) dst_curs = dst_db.cursor() for ddl in skytools.parse_statements(ts.dropped_ddl): self.log.info(ddl) dst_curs.execute(ddl) q = "select * from londiste.local_set_table_struct(%s, %s, NULL)" self.exec_cmd(dst_curs, q, [self.queue_name, ts.name]) ts.dropped_ddl = None dst_db.commit() # analyze self.log.info("%s: analyze", ts.name) dst_curs.execute("analyze " + skytools.quote_fqident(ts.name)) dst_db.commit()
def handle_execute_event(self, ev, dst_curs): """handle one EXECUTE event""" if self.copy_thread: return # parse event fname = ev.extra1 s_attrs = ev.extra2 exec_attrs = ExecAttrs(urlenc = s_attrs) sql = ev.data # fixme: curs? pgver = dst_curs.connection.server_version if pgver >= 80300: dst_curs.execute("set local session_replication_role = 'local'") seq_map = {} q = "select seq_name, local from londiste.get_seq_list(%s) where local" dst_curs.execute(q, [self.queue_name]) for row in dst_curs.fetchall(): seq_map[row['seq_name']] = row['seq_name'] tbl_map = {} for tbl, t in self.table_map.items(): tbl_map[t.name] = t.dest_table q = "select * from londiste.execute_start(%s, %s, %s, false, %s)" res = self.exec_cmd(dst_curs, q, [self.queue_name, fname, sql, s_attrs], commit = False) ret = res[0]['ret_code'] if ret > 200: self.log.info("Skipping execution of '%s'", fname) if pgver >= 80300: dst_curs.execute("set local session_replication_role = 'replica'") return if exec_attrs.need_execute(dst_curs, tbl_map, seq_map): self.log.info("%s: executing sql") xsql = exec_attrs.process_sql(sql, tbl_map, seq_map) for stmt in skytools.parse_statements(xsql): dst_curs.execute(stmt) else: self.log.info("%s: execution not needed on this node") q = "select * from londiste.execute_finish(%s, %s)" self.exec_cmd(dst_curs, q, [self.queue_name, fname], commit = False) if pgver >= 80300: dst_curs.execute("set local session_replication_role = 'replica'")
def create(self, curs, log=None): """Create a dbobject.""" if log: log.info('Installing %s' % self.name) if self.sql: sql = self.sql elif self.sql_file: fn = self.find_file() if log: log.info(" Reading from %s" % fn) sql = open(fn, "r").read() else: raise Exception('object not defined') for stmt in skytools.parse_statements(sql): #if log: log.debug(repr(stmt)) curs.execute(stmt)
def create(self, curs, log = None): """Create a dbobject.""" if log: log.info('Installing %s' % self.name) if self.sql: sql = self.sql elif self.sql_file: fn = self.find_file() if log: log.info(" Reading from %s" % fn) sql = open(fn, "r").read() else: raise Exception('object not defined') for stmt in skytools.parse_statements(sql): #if log: log.debug(repr(stmt)) curs.execute(stmt)
def cmd_execute(self, *files): db = self.get_database('db') curs = db.cursor() for fn in files: fname = os.path.basename(fn) sql = open(fn, "r").read() q = "select * from londiste.execute_start(%s, %s, %s, true)" res = self.exec_cmd(db, q, [self.queue_name, fname, sql], commit = False) ret = res[0]['ret_code'] if ret >= 300: self.log.warning("Skipping execution of '%s'" % fname) continue for stmt in skytools.parse_statements(sql): curs.execute(stmt) q = "select * from londiste.execute_finish(%s, %s)" self.exec_cmd(db, q, [self.queue_name, fname], commit = False) db.commit()
def cmd_execute(self, *files): db = self.get_database('db') curs = db.cursor() tables = self.fetch_set_tables(curs) seqs = self.fetch_seqs(curs) # generate local maps local_tables = {} local_seqs = {} for tbl in tables.values(): if tbl['local']: local_tables[tbl['table_name']] = tbl['dest_table'] for seq in seqs.values(): if seq['local']: local_seqs[seq['seq_name']] = seq['seq_name'] # set replica role for EXECUTE transaction if db.server_version >= 80300: curs.execute("set local session_replication_role = 'local'") for fn in files: fname = os.path.basename(fn) sql = open(fn, "r").read() attrs = ExecAttrs(sql=sql) q = "select * from londiste.execute_start(%s, %s, %s, true, %s)" res = self.exec_cmd( db, q, [self.queue_name, fname, sql, attrs.to_urlenc()], commit=False) ret = res[0]['ret_code'] if ret > 200: self.log.warning("Skipping execution of '%s'", fname) continue if attrs.need_execute(curs, local_tables, local_seqs): self.log.info("%s: executing sql", fname) xsql = attrs.process_sql(sql, local_tables, local_seqs) for stmt in skytools.parse_statements(xsql): curs.execute(stmt) else: self.log.info( "%s: This SQL does not need to run on this node.", fname) q = "select * from londiste.execute_finish(%s, %s)" self.exec_cmd(db, q, [self.queue_name, fname], commit=False) db.commit()
def cmd_execute(self, *files): db = self.get_database('db') curs = db.cursor() for fn in files: fname = os.path.basename(fn) sql = open(fn, "r").read() q = "select * from londiste.execute_start(%s, %s, %s, true)" res = self.exec_cmd(db, q, [self.queue_name, fname, sql], commit=False) ret = res[0]['ret_code'] if ret >= 300: self.log.warning("Skipping execution of '%s'" % fname) continue for stmt in skytools.parse_statements(sql): curs.execute(stmt) q = "select * from londiste.execute_finish(%s, %s)" self.exec_cmd(db, q, [self.queue_name, fname], commit=False) db.commit()
def handle_execute_event(self, ev, dst_curs): """handle one EXECUTE event""" if self.copy_thread: return # parse event fname = ev.extra1 sql = ev.data # fixme: curs? q = "select * from londiste.execute_start(%s, %s, %s, false)" res = self.exec_cmd(dst_curs, q, [self.queue_name, fname, sql], commit = False) ret = res[0]['ret_code'] if ret != 200: return for stmt in skytools.parse_statements(sql): dst_curs.execute(stmt) q = "select * from londiste.execute_finish(%s, %s)" self.exec_cmd(dst_curs, q, [self.queue_name, fname], commit = False)
def cmd_execute(self, *files): db = self.get_database('db') curs = db.cursor() tables = self.fetch_set_tables(curs) seqs = self.fetch_seqs(curs) # generate local maps local_tables = {} local_seqs = {} for tbl in tables.values(): if tbl['local']: local_tables[tbl['table_name']] = tbl['dest_table'] for seq in seqs.values(): if seq['local']: local_seqs[seq['seq_name']] = seq['seq_name'] # set replica role for EXECUTE transaction if db.server_version >= 80300: curs.execute("set local session_replication_role = 'local'") for fn in files: fname = os.path.basename(fn) sql = open(fn, "r").read() attrs = ExecAttrs(sql = sql) q = "select * from londiste.execute_start(%s, %s, %s, true, %s)" res = self.exec_cmd(db, q, [self.queue_name, fname, sql, attrs.to_urlenc()], commit = False) ret = res[0]['ret_code'] if ret >= 300: self.log.warning("Skipping execution of '%s'" % fname) continue if attrs.need_execute(curs, local_tables, local_seqs): self.log.info("%s: executing sql", fname) xsql = attrs.process_sql(sql, local_tables, local_seqs) for stmt in skytools.parse_statements(xsql): curs.execute(stmt) else: self.log.info("%s: This SQL does not need to run on this node.", fname) q = "select * from londiste.execute_finish(%s, %s)" self.exec_cmd(db, q, [self.queue_name, fname], commit = False) db.commit()
def cmd_execute(self, *files): db = self.get_database("db") curs = db.cursor() # set replica role for EXECUTE transaction if db.server_version >= 80300: curs.execute("set local session_replication_role = 'local'") for fn in files: fname = os.path.basename(fn) sql = open(fn, "r").read() q = "select * from londiste.execute_start(%s, %s, %s, true)" res = self.exec_cmd(db, q, [self.queue_name, fname, sql], commit=False) ret = res[0]["ret_code"] if ret >= 300: self.log.warning("Skipping execution of '%s'" % fname) continue for stmt in skytools.parse_statements(sql): curs.execute(stmt) q = "select * from londiste.execute_finish(%s, %s)" self.exec_cmd(db, q, [self.queue_name, fname], commit=False) db.commit()
def do_copy(self, tbl_stat, src_db, dst_db): """Entry point into copying logic.""" dst_db.commit() src_curs = src_db.cursor() dst_curs = dst_db.cursor() while 1: if tbl_stat.copy_role == 'wait-copy': self.log.info('waiting for first partition to initialize copy') elif tbl_stat.max_parallel_copies_reached(): self.log.info('number of max parallel copies (%s) reached' %\ tbl_stat.max_parallel_copy) else: break time.sleep(10) tbl_stat = self.reload_table_stat(dst_curs, tbl_stat.name) dst_db.commit() while 1: pmap = self.get_state_map(src_db.cursor()) src_db.commit() if tbl_stat.name not in pmap: raise Exception("table %s not available on provider" % tbl_stat.name) pt = pmap[tbl_stat.name] if pt.state == TABLE_OK: break self.log.warning("table %s not in sync yet on provider, waiting" % tbl_stat.name) time.sleep(10) src_real_table = pt.dest_table # 0 - dont touch # 1 - single tx # 2 - multi tx cmode = 1 if tbl_stat.copy_role == 'lead': cmode = 2 elif tbl_stat.copy_role: cmode = 0 # We need to see COPY snapshot from txid_current_snapshot() later. oldiso = src_db.isolation_level src_db.set_isolation_level(skytools.I_REPEATABLE_READ) src_db.commit() self.sync_database_encodings(src_db, dst_db) self.log.info("Starting full copy of %s" % tbl_stat.name) # just in case, drop all fkeys (in case "replay" was skipped) # !! this may commit, so must be done before anything else !! if cmode > 0: self.drop_fkeys(dst_db, tbl_stat.dest_table) # now start ddl-dropping tx if cmode > 0: q = "lock table " + skytools.quote_fqident(tbl_stat.dest_table) dst_curs.execute(q) # find dst struct src_struct = TableStruct(src_curs, src_real_table) dst_struct = TableStruct(dst_curs, tbl_stat.dest_table) # take common columns, warn on missing ones dlist = dst_struct.get_column_list() slist = src_struct.get_column_list() common_cols = [] for c in slist: if c not in dlist: self.log.warning("Table %s column %s does not exist on subscriber" % (tbl_stat.name, c)) else: common_cols.append(c) for c in dlist: if c not in slist: self.log.warning("Table %s column %s does not exist on provider" % (tbl_stat.name, c)) # drop unnecessary stuff if cmode > 0: objs = T_CONSTRAINT | T_INDEX | T_RULE | T_PARENT # | T_TRIGGER dst_struct.drop(dst_curs, objs, log = self.log) # drop data if tbl_stat.table_attrs.get('skip_truncate'): self.log.info("%s: skipping truncate" % tbl_stat.name) else: self.log.info("%s: truncating" % tbl_stat.name) q = "truncate " if dst_db.server_version >= 80400: q += "only " q += skytools.quote_fqident(tbl_stat.dest_table) dst_curs.execute(q) if cmode == 2 and tbl_stat.dropped_ddl is None: ddl = dst_struct.get_create_sql(objs) if ddl: q = "select * from londiste.local_set_table_struct(%s, %s, %s)" self.exec_cmd(dst_curs, q, [self.queue_name, tbl_stat.name, ddl]) else: ddl = None dst_db.commit() tbl_stat.dropped_ddl = ddl # do truncate & copy self.log.info("%s: start copy" % tbl_stat.name) p = tbl_stat.get_plugin() stats = p.real_copy(src_real_table, src_curs, dst_curs, common_cols) if stats: self.log.info("%s: copy finished: %d bytes, %d rows" % ( tbl_stat.name, stats[0], stats[1])) # get snapshot src_curs.execute("select txid_current_snapshot()") snapshot = src_curs.fetchone()[0] src_db.commit() # restore old behaviour src_db.set_isolation_level(oldiso) src_db.commit() tbl_stat.change_state(TABLE_CATCHING_UP) tbl_stat.change_snapshot(snapshot) self.save_table_state(dst_curs) # create previously dropped objects if cmode == 1: dst_struct.create(dst_curs, objs, log = self.log) elif cmode == 2: dst_db.commit() # start waiting for other copy processes to finish while tbl_stat.copy_role: self.log.info('waiting for other partitions to finish copy') time.sleep(10) tbl_stat = self.reload_table_stat(dst_curs, tbl_stat.name) dst_db.commit() if tbl_stat.dropped_ddl is not None: self.looping = 0 for ddl in skytools.parse_statements(tbl_stat.dropped_ddl): self.log.info(ddl) dst_curs.execute(ddl) q = "select * from londiste.local_set_table_struct(%s, %s, NULL)" self.exec_cmd(dst_curs, q, [self.queue_name, tbl_stat.name]) tbl_stat.dropped_ddl = None self.looping = 1 dst_db.commit() # hack for copy-in-playback if not self.copy_thread: tbl_stat.change_state(TABLE_OK) self.save_table_state(dst_curs) dst_db.commit() # copy finished if tbl_stat.copy_role == 'wait-replay': return # if copy done, request immidiate tick from pgqadm, # to make state juggling faster. on mostly idle db-s # each step may take tickers idle_timeout secs, which is pain. q = "select pgq.force_tick(%s)" src_curs.execute(q, [self.queue_name]) src_db.commit()
def do_copy(self, tbl_stat, src_db, dst_db): """Entry point into copying logic.""" dst_db.commit() src_curs = src_db.cursor() dst_curs = dst_db.cursor() while tbl_stat.copy_role == 'wait-copy': self.log.info('waiting for first partition to initialize copy') time.sleep(10) tbl_stat = self.reload_table_stat(dst_curs, tbl_stat.name) dst_db.commit() while 1: pmap = self.get_state_map(src_db.cursor()) src_db.commit() if tbl_stat.name not in pmap: raise Exception("table %s not available on provider" % tbl_stat.name) pt = pmap[tbl_stat.name] if pt.state == TABLE_OK: break self.log.warning("table %s not in sync yet on provider, waiting" % tbl_stat.name) time.sleep(10) # 0 - dont touch # 1 - single tx # 2 - multi tx cmode = 1 if tbl_stat.copy_role == 'lead': cmode = 2 elif tbl_stat.copy_role: cmode = 0 # change to SERIALIZABLE isolation level src_db.set_isolation_level(skytools.I_SERIALIZABLE) src_db.commit() self.sync_database_encodings(src_db, dst_db) self.log.info("Starting full copy of %s" % tbl_stat.name) # just in case, drop all fkeys (in case "replay" was skipped) # !! this may commit, so must be done before anything else !! self.drop_fkeys(dst_db, tbl_stat.name) # find dst struct src_struct = TableStruct(src_curs, tbl_stat.name) dst_struct = TableStruct(dst_curs, tbl_stat.name) # take common columns, warn on missing ones dlist = dst_struct.get_column_list() slist = src_struct.get_column_list() common_cols = [] for c in slist: if c not in dlist: self.log.warning("Table %s column %s does not exist on subscriber" % (tbl_stat.name, c)) else: common_cols.append(c) for c in dlist: if c not in slist: self.log.warning("Table %s column %s does not exist on provider" % (tbl_stat.name, c)) # drop unnecessary stuff if cmode > 0: # drop indexes objs = T_CONSTRAINT | T_INDEX | T_RULE # | T_TRIGGER dst_struct.drop(dst_curs, objs, log = self.log) # drop data if tbl_stat.table_attrs.get('skip_truncate'): self.log.info("%s: skipping truncate" % tbl_stat.name) else: self.log.info("%s: truncating" % tbl_stat.name) dst_curs.execute("truncate " + skytools.quote_fqident(tbl_stat.name)) if cmode == 2 and tbl_stat.dropped_ddl is None: ddl = dst_struct.get_create_sql(objs) q = "select * from londiste.local_set_table_struct(%s, %s, %s)" self.exec_cmd(dst_curs, q, [self.queue_name, tbl_stat.name, ddl]) dst_db.commit() tbl_stat.dropped_ddl = ddl # do truncate & copy self.real_copy(src_curs, dst_curs, tbl_stat, common_cols) # get snapshot src_curs.execute("select txid_current_snapshot()") snapshot = src_curs.fetchone()[0] src_db.commit() # restore READ COMMITTED behaviour src_db.set_isolation_level(1) src_db.commit() # create previously dropped objects if cmode == 1: dst_struct.create(dst_curs, objs, log = self.log) elif cmode == 2: dst_db.commit() while tbl_stat.copy_role == 'lead': self.log.info('waiting for other partitions to finish copy') time.sleep(10) tbl_stat = self.reload_table_stat(dst_curs, tbl_stat.name) dst_db.commit() if tbl_stat.dropped_ddl is not None: for ddl in skytools.parse_statements(tbl_stat.dropped_ddl): self.log.info(ddl) dst_curs.execute(ddl) q = "select * from londiste.local_set_table_struct(%s, %s, NULL)" self.exec_cmd(dst_curs, q, [self.queue_name, tbl_stat.name]) tbl_stat.dropped_ddl = None dst_db.commit() # set state if self.copy_thread: tbl_stat.change_state(TABLE_CATCHING_UP) else: tbl_stat.change_state(TABLE_OK) tbl_stat.change_snapshot(snapshot) self.save_table_state(dst_curs) dst_db.commit() # copy finished if tbl_stat.copy_role == 'wait-replay': return # analyze self.log.info("%s: analyze" % tbl_stat.name) dst_curs.execute("analyze " + skytools.quote_fqident(tbl_stat.name)) dst_db.commit() # if copy done, request immidiate tick from pgqadm, # to make state juggling faster. on mostly idle db-s # each step may take tickers idle_timeout secs, which is pain. q = "select pgq.force_tick(%s)" src_curs.execute(q, [self.queue_name]) src_db.commit()