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 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()
