def test_restart_server_while_replication_startup_not_finished(
started_cluster):
NUM_TABLES = 5
pg_manager.create_and_fill_postgres_tables(NUM_TABLES, 100000)
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port)
time.sleep(1)
instance.restart_clickhouse()
check_several_tables_are_synchronized(instance, NUM_TABLES)
def test_load_and_sync_all_database_tables(started_cluster):
NUM_TABLES = 5
pg_manager.create_and_fill_postgres_tables(NUM_TABLES)
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port)
check_several_tables_are_synchronized(instance, NUM_TABLES)
result = instance.query(
"SELECT count() FROM system.tables WHERE database = 'test_database';")
assert int(result) == NUM_TABLES
def test_abrupt_connection_loss_while_heavy_replication(started_cluster):
pytest.skip("Temporary disabled (FIXME)")
def transaction(thread_id):
if thread_id % 2:
conn = get_postgres_conn(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
database=True,
auto_commit=True,
)
else:
conn = get_postgres_conn(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
database=True,
auto_commit=False,
)
cursor = conn.cursor()
for query in queries:
cursor.execute(query.format(thread_id))
print("thread {}, query {}".format(thread_id, query))
if thread_id % 2 == 0:
conn.commit()
NUM_TABLES = 6
pg_manager.create_and_fill_postgres_tables(NUM_TABLES, numbers=0)
threads_num = 6
threads = []
for i in range(threads_num):
threads.append(threading.Thread(target=transaction, args=(i, )))
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port)
for thread in threads:
time.sleep(random.uniform(0, 0.5))
thread.start()
for thread in threads:
thread.join() # Join here because it takes time for data to reach wal
time.sleep(2)
started_cluster.pause_container("postgres1")
# for i in range(NUM_TABLES):
# result = instance.query(f"SELECT count() FROM test_database.postgresql_replica_{i}")
# print(result) # Just debug
started_cluster.unpause_container("postgres1")
check_several_tables_are_synchronized(instance, NUM_TABLES)
def test_clickhouse_restart(started_cluster):
NUM_TABLES = 5
pg_manager.create_and_fill_postgres_tables(NUM_TABLES)
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port)
check_several_tables_are_synchronized(instance, NUM_TABLES)
for i in range(NUM_TABLES):
instance.query(
"INSERT INTO postgres_database.postgresql_replica_{} SELECT 50 + number, {} from numbers(50000)"
.format(i, i))
instance.restart_clickhouse()
check_several_tables_are_synchronized(instance, NUM_TABLES)
def test_remove_table_from_replication(started_cluster):
NUM_TABLES = 5
pg_manager.create_and_fill_postgres_tables(NUM_TABLES, 10000)
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip, port=started_cluster.postgres_port)
check_several_tables_are_synchronized(instance, NUM_TABLES)
result = instance.query("SHOW TABLES FROM test_database")
assert(result == "postgresql_replica_0\npostgresql_replica_1\npostgresql_replica_2\npostgresql_replica_3\npostgresql_replica_4\n")
result = instance.query('SHOW CREATE DATABASE test_database')
assert(result[:63] == "CREATE DATABASE test_database\\nENGINE = MaterializedPostgreSQL(")
assert(result[-59:] == "\\'postgres_database\\', \\'postgres\\', \\'mysecretpassword\\')\n")
table_name = 'postgresql_replica_4'
instance.query(f'DETACH TABLE test_database.{table_name} PERMANENTLY');
result = instance.query_and_get_error(f'SELECT * FROM test_database.{table_name}')
assert("doesn't exist" in result)
result = instance.query("SHOW TABLES FROM test_database")
assert(result == "postgresql_replica_0\npostgresql_replica_1\npostgresql_replica_2\npostgresql_replica_3\n")
result = instance.query('SHOW CREATE DATABASE test_database')
assert(result[:63] == "CREATE DATABASE test_database\\nENGINE = MaterializedPostgreSQL(")
assert(result[-138:] == ")\\nSETTINGS materialized_postgresql_tables_list = \\'postgresql_replica_0,postgresql_replica_1,postgresql_replica_2,postgresql_replica_3\\'\n")
instance.query(f'ATTACH TABLE test_database.{table_name}');
check_tables_are_synchronized(instance, table_name);
check_several_tables_are_synchronized(instance, NUM_TABLES)
instance.query(f"INSERT INTO postgres_database.{table_name} SELECT number, number from numbers(10000, 10000)")
check_tables_are_synchronized(instance, table_name);
result = instance.query('SHOW CREATE DATABASE test_database')
assert(result[:63] == "CREATE DATABASE test_database\\nENGINE = MaterializedPostgreSQL(")
assert(result[-159:] == ")\\nSETTINGS materialized_postgresql_tables_list = \\'postgresql_replica_0,postgresql_replica_1,postgresql_replica_2,postgresql_replica_3,postgresql_replica_4\\'\n")
table_name = 'postgresql_replica_1'
instance.query(f'DETACH TABLE test_database.{table_name} PERMANENTLY');
result = instance.query('SHOW CREATE DATABASE test_database')
assert(result[:63] == "CREATE DATABASE test_database\\nENGINE = MaterializedPostgreSQL(")
assert(result[-138:] == ")\\nSETTINGS materialized_postgresql_tables_list = \\'postgresql_replica_0,postgresql_replica_2,postgresql_replica_3,postgresql_replica_4\\'\n")
cursor = pg_manager.get_db_cursor()
cursor.execute(f'drop table if exists postgresql_replica_0;')
# Removing from replication table which does not exist in PostgreSQL must be ok.
instance.query('DETACH TABLE test_database.postgresql_replica_0 PERMANENTLY');
assert instance.contains_in_log("from publication, because table does not exist in PostgreSQL")
def test_abrupt_server_restart_while_heavy_replication(started_cluster):
pytest.skip("Temporary disabled (FIXME)")
def transaction(thread_id):
if thread_id % 2:
conn = get_postgres_conn(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
database=True,
auto_commit=True,
)
else:
conn = get_postgres_conn(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
database=True,
auto_commit=False,
)
cursor = conn.cursor()
for query in queries:
cursor.execute(query.format(thread_id))
print("thread {}, query {}".format(thread_id, query))
if thread_id % 2 == 0:
conn.commit()
NUM_TABLES = 6
pg_manager.create_and_fill_postgres_tables(tables_num=NUM_TABLES,
numbers=0)
threads = []
threads_num = 6
for i in range(threads_num):
threads.append(threading.Thread(target=transaction, args=(i, )))
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port)
for thread in threads:
time.sleep(random.uniform(0, 0.5))
thread.start()
for thread in threads:
thread.join() # Join here because it takes time for data to reach wal
instance.restart_clickhouse()
check_several_tables_are_synchronized(instance, NUM_TABLES)
def test_replicating_dml(started_cluster):
conn = get_postgres_conn(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
database=True,
)
cursor = conn.cursor()
NUM_TABLES = 5
for i in range(NUM_TABLES):
create_postgres_table(cursor, "postgresql_replica_{}".format(i))
instance.query(
"INSERT INTO postgres_database.postgresql_replica_{} SELECT number, {} from numbers(50)"
.format(i, i))
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port)
for i in range(NUM_TABLES):
instance.query(
"INSERT INTO postgres_database.postgresql_replica_{} SELECT 50 + number, {} from numbers(1000)"
.format(i, i))
check_several_tables_are_synchronized(instance, NUM_TABLES)
for i in range(NUM_TABLES):
cursor.execute(
"UPDATE postgresql_replica_{} SET value = {} * {} WHERE key < 50;".
format(i, i, i))
cursor.execute(
"UPDATE postgresql_replica_{} SET value = {} * {} * {} WHERE key >= 50;"
.format(i, i, i, i))
check_several_tables_are_synchronized(instance, NUM_TABLES)
for i in range(NUM_TABLES):
cursor.execute(
"DELETE FROM postgresql_replica_{} WHERE (value*value + {}) % 2 = 0;"
.format(i, i))
cursor.execute(
"UPDATE postgresql_replica_{} SET value = value - (value % 7) WHERE key > 128 AND key < 512;"
.format(i))
cursor.execute(
"DELETE FROM postgresql_replica_{} WHERE key % 7 = 1;".format(
i, i))
check_several_tables_are_synchronized(instance, NUM_TABLES)
def test_table_schema_changes(started_cluster):
conn = get_postgres_conn(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
database=True,
)
cursor = conn.cursor()
NUM_TABLES = 5
for i in range(NUM_TABLES):
create_postgres_table(
cursor,
"postgresql_replica_{}".format(i),
template=postgres_table_template_2,
)
instance.query(
"INSERT INTO postgres_database.postgresql_replica_{} SELECT number, {}, {}, {} from numbers(25)"
.format(i, i, i, i))
pg_manager.create_materialized_db(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
settings=["materialized_postgresql_allow_automatic_update = 1"],
)
for i in range(NUM_TABLES):
instance.query(
"INSERT INTO postgres_database.postgresql_replica_{} SELECT 25 + number, {}, {}, {} from numbers(25)"
.format(i, i, i, i))
check_several_tables_are_synchronized(instance, NUM_TABLES)
expected = instance.query(
"SELECT key, value1, value3 FROM test_database.postgresql_replica_3 ORDER BY key"
)
altered_idx = random.randint(0, 4)
altered_table = f"postgresql_replica_{altered_idx}"
cursor.execute(f"ALTER TABLE {altered_table} DROP COLUMN value2")
for i in range(NUM_TABLES):
cursor.execute(
f"INSERT INTO postgresql_replica_{i} VALUES (50, {i}, {i})")
cursor.execute(f"UPDATE {altered_table} SET value3 = 12 WHERE key%2=0")
time.sleep(2)
assert_nested_table_is_created(instance, altered_table)
assert_number_of_columns(instance, 3, altered_table)
check_tables_are_synchronized(instance, altered_table)
print("check1 OK")
check_several_tables_are_synchronized(instance, NUM_TABLES)
for i in range(NUM_TABLES):
if i != altered_idx:
instance.query(
"INSERT INTO postgres_database.postgresql_replica_{} SELECT 51 + number, {}, {}, {} from numbers(49)"
.format(i, i, i, i))
else:
instance.query(
"INSERT INTO postgres_database.postgresql_replica_{} SELECT 51 + number, {}, {} from numbers(49)"
.format(i, i, i))
check_tables_are_synchronized(instance, altered_table)
print("check2 OK")
check_several_tables_are_synchronized(instance, NUM_TABLES)
def test_add_new_table_to_replication(started_cluster):
cursor = pg_manager.get_db_cursor()
cursor.execute("DROP TABLE IF EXISTS test_table")
NUM_TABLES = 5
pg_manager.create_and_fill_postgres_tables_from_cursor(
cursor, NUM_TABLES, 10000)
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port)
check_several_tables_are_synchronized(instance, NUM_TABLES)
result = instance.query("SHOW TABLES FROM test_database")
assert (
result ==
"postgresql_replica_0\npostgresql_replica_1\npostgresql_replica_2\npostgresql_replica_3\npostgresql_replica_4\n"
)
table_name = "postgresql_replica_5"
pg_manager.create_and_fill_postgres_table_from_cursor(cursor, table_name)
result = instance.query("SHOW CREATE DATABASE test_database")
assert (result[:63] ==
"CREATE DATABASE test_database\\nENGINE = MaterializedPostgreSQL("
) # Check without ip
assert (
result[-59:] ==
"\\'postgres_database\\', \\'postgres\\', \\'mysecretpassword\\')\n")
result = instance.query_and_get_error(
"ALTER DATABASE test_database MODIFY SETTING materialized_postgresql_tables_list='tabl1'"
)
assert (
"Changing setting `materialized_postgresql_tables_list` is not allowed"
in result)
result = instance.query_and_get_error(
"ALTER DATABASE test_database MODIFY SETTING materialized_postgresql_tables='tabl1'"
)
assert "Database engine MaterializedPostgreSQL does not support setting" in result
instance.query(f"ATTACH TABLE test_database.{table_name}")
result = instance.query("SHOW TABLES FROM test_database")
assert (
result ==
"postgresql_replica_0\npostgresql_replica_1\npostgresql_replica_2\npostgresql_replica_3\npostgresql_replica_4\npostgresql_replica_5\n"
)
check_tables_are_synchronized(instance, table_name)
instance.query(
f"INSERT INTO postgres_database.{table_name} SELECT number, number from numbers(10000, 10000)"
)
check_tables_are_synchronized(instance, table_name)
result = instance.query_and_get_error(
f"ATTACH TABLE test_database.{table_name}")
assert "Table test_database.postgresql_replica_5 already exists" in result
result = instance.query_and_get_error(
"ATTACH TABLE test_database.unknown_table")
assert "PostgreSQL table unknown_table does not exist" in result
result = instance.query("SHOW CREATE DATABASE test_database")
assert (result[:63] ==
"CREATE DATABASE test_database\\nENGINE = MaterializedPostgreSQL(")
assert (
result[-180:] ==
")\\nSETTINGS materialized_postgresql_tables_list = \\'postgresql_replica_0,postgresql_replica_1,postgresql_replica_2,postgresql_replica_3,postgresql_replica_4,postgresql_replica_5\\'\n"
)
table_name = "postgresql_replica_6"
create_postgres_table(cursor, table_name)
instance.query(
"INSERT INTO postgres_database.{} SELECT number, number from numbers(10000)"
.format(table_name))
instance.query(f"ATTACH TABLE test_database.{table_name}")
instance.restart_clickhouse()
table_name = "postgresql_replica_7"
create_postgres_table(cursor, table_name)
instance.query(
"INSERT INTO postgres_database.{} SELECT number, number from numbers(10000)"
.format(table_name))
instance.query(f"ATTACH TABLE test_database.{table_name}")
result = instance.query("SHOW CREATE DATABASE test_database")
assert (result[:63] ==
"CREATE DATABASE test_database\\nENGINE = MaterializedPostgreSQL(")
assert (
result[-222:] ==
")\\nSETTINGS materialized_postgresql_tables_list = \\'postgresql_replica_0,postgresql_replica_1,postgresql_replica_2,postgresql_replica_3,postgresql_replica_4,postgresql_replica_5,postgresql_replica_6,postgresql_replica_7\\'\n"
)
instance.query(
f"INSERT INTO postgres_database.{table_name} SELECT number, number from numbers(10000, 10000)"
)
result = instance.query("SHOW TABLES FROM test_database")
assert (
result ==
"postgresql_replica_0\npostgresql_replica_1\npostgresql_replica_2\npostgresql_replica_3\npostgresql_replica_4\npostgresql_replica_5\npostgresql_replica_6\npostgresql_replica_7\n"
)
check_several_tables_are_synchronized(instance, NUM_TABLES + 3)
