单表功能测试

注意点

  1. 发布节点发布的表和订阅节点订阅的表必须都要有主键,否则无法同步update、delete操作。如果该表仅仅是做insert操作,那么可以不建主键。
  2. 逻辑复制订阅节点订阅的表不存在一致性检测,订阅节点订阅的表只会同步发布节点的操作,可以在订阅节点修改订阅表的内容,所以,如果要保证一致性可以设置用户权限来保证订阅节点不会修改订阅的表的内容。
  3. 表结构变更测试
  • 添加列
    1. #发布节点
    2. postgres=# alter table test add column tid int;
    3. ALTER TABLE
    4. postgres=# select * from test;
    5. id | name | cid | tid
    6. ----+------+-----+-----
    7. 1 | a | 1 |
    8. #订阅节点
    9. postgres=# select * from test;
    10. id | name | cid
    11. ----+------+-----
    12. 1 | a | 1
    13. #在订阅节点手动添加tid列
    14. postgres=# alter table test add column tid int;
    15. ALTER TABLE
    16. postgres=# select * from test;
    17. id | name | cid | tid
    18. ----+------+-----+-----
    19. 1 | a | 1 |
    20. #DML测试
    21. #1. insert
    22. #发布节点
    23. postgres=# insert into test values(2,'b',2,2);
    24. INSERT 0 1
    25. #订阅节点
    26. postgres=# select * from test;
    27. id | name | cid | tid
    28. ----+------+-----+-----
    29. 1 | a | 1 |
    30. 2 | b | 2 | 2
    31. #2. delete
    32. #发布节点
    33. postgres=# delete from test where id=2;
    34. DELETE 1
    35. #订阅节点
    36. postgres=# select * from test;
    37. id | name | cid | tid
    38. ----+------+-----+-----
    39. 1 | a | 1 |
    40. #3.update
    41. #发布节点
    42. postgres=# update test set tid=1 where id=1;
    43. UPDATE 1
    44. #订阅节点
    45. postgres=# select * from test;
    46. id | name | cid | tid
    47. ----+------+-----+-----
    48. 1 | a | 1 | 1
  • 删除列
  1. #发布节点
  2. postgres=# select * from test01;
  3. id | name | tid
  4. ----+------+-----
  5. 1 | a | 1
  6. postgres=# alter table test01 drop tid;
  7. ALTER TABLE
  8. postgres=# select * from test01;
  9. id | name
  10. ----+------
  11. 1 | a
  12. #订阅节点
  13. postgres=# select * from test01;
  14. id | name | tid
  15. ----+------+-----
  16. 1 | a | 1
  17. (1 row)
  18. postgres=# alter table test01 drop tid;
  19. ALTER TABLE
  20. postgres=# select * from test01;
  21. id | name
  22. ----+------
  23. 1 | a
  24. #1. insert
  25. #发布节点
  26. postgres=# insert into test01 values(2,'b');
  27. INSERT 0 1
  28. #订阅节点
  29. postgres=# select * from test01;
  30. id | name
  31. ----+------
  32. 1 | a
  33. 2 | b
  34. (2 rows)
  35. #2. delete
  36. #发布节点
  37. postgres=# delete from test01 where id=1;
  38. DELETE 1
  39. #订阅节点
  40. postgres=# select * from test01;
  41. id | name
  42. ----+------
  43. 2 | b
  44. (1 row)
  45. #3. update
  46. #发布节点
  47. postgres=# update test01 set name='c' where name='b';
  48. UPDATE 1
  49. postgres=# select * from test01;
  50. id | name
  51. ----+------
  52. 2 | c
  53. (1 row)
  54. #订阅节点
  55. postgres=# select * from test01;
  56. id | name
  57. ----+------
  58. 2 | c
  59. (1 row)
  • 改变列的数据类型
  1. #1. insert
  2. #发布节点
  3. postgres=# alter table test01 alter id type varchar;
  4. ALTER TABLE
  5. postgres=# select * from test01;
  6. id | name
  7. ----+------
  8. 2 | c
  9. (1 row)
  10. postgres=# \d test01
  11. Table "public.test01"
  12. Column | Type | Collation | Nullable | Default
  13. --------+-----------------------+-----------+----------+---------
  14. id | character varying | | not null |
  15. name | character varying(10) | | |
  16. Indexes:
  17. "test01_pkey" PRIMARY KEY, btree (id)
  18. Publications:
  19. "pub2"
  20. postgres=# insert into test01 values('3','a');
  21. INSERT 0 1
  22. #订阅节点
  23. postgres=# \d test01;
  24. Table "public.test01"
  25. Column | Type | Collation | Nullable | Default
  26. --------+-----------------------+-----------+----------+---------
  27. id | integer | | not null |
  28. name | character varying(10) | | |
  29. Indexes:
  30. "test01_pkey" PRIMARY KEY, btree (id)
  31. postgres=# select * from test01;
  32. id | name
  33. ----+------
  34. 2 | c
  35. 3 | a
  36. (2 rows)
  37. #2. update
  38. #发布节点
  39. postgres=# update test01 set id='4' where id='2';
  40. UPDATE 1
  41. #订阅节点
  42. postgres=# select * from test01;
  43. id | name
  44. ----+------
  45. 3 | a
  46. 4 | c
  47. (2 rows)
  48. #3.delete
  49. #发布节点
  50. postgres=# delete from test01 where id='3';
  51. DELETE 1
  52. #订阅节点
  53. postgres=# select * from test01;
  54. id | name
  55. ----+------
  56. 4 | c
  57. (1 rows)
  • 订阅某个库
  1. #发布节点
  2. postgres=# \c testdatabase
  3. You are now connected to database "testdatabase" as user "postgres".
  4. testdatabase=# \d
  5. List of relations
  6. Schema | Name | Type | Owner
  7. --------+------+-------+----------
  8. public | t1 | table | postgres
  9. public | t2 | table | postgres
  10. (2 rows)
  11. testdatabase=# create publication pub5 for all tables;
  12. CREATE PUBLICATION
  13. #订阅节点
  14. testdatabase=# create subscription sub5 connection 'host=192.168.0.109 port=5432 dbname=testdatabase user=postgres' publication pub5;
  15. ERROR: relation "public.t1" does not exist
  16. testdatabase=# create table t1(id int,name varchar(20));
  17. CREATE TABLE
  18. testdatabase=# create subscription sub5 connection 'host=192.168.0.109 port=5432 dbname=testdatabase user=postgres' publication pub5;
  19. ERROR: relation "public.t2" does not exist
  20. testdatabase=# create table t2(id int,name varchar(20));
  21. CREATE TABLE
  22. testdatabase=# create subscription sub5 connection 'host=192.168.0.109 port=5432 dbname=testdatabase user=postgres' publication pub5;
  23. NOTICE: created replication slot "sub5" on publisher
  24. CREATE SUBSCRIPTION
  25. testdatabase=# \d
  26. List of relations
  27. Schema | Name | Type | Owner
  28. --------+------+-------+----------
  29. public | t1 | table | postgres
  30. public | t2 | table | postgres
  31. (2 rows)
  32. #发布节点
  33. testdatabase=# create table t3(id int,name varchar(20));
  34. CREATE TABLE
  35. #订阅节点
  36. testdatabase=# \d
  37. List of relations
  38. Schema | Name | Type | Owner
  39. --------+------+-------+----------
  40. public | t1 | table | postgres
  41. public | t2 | table | postgres
  42. (2 rows)
  43. #发布节点
  44. testdatabase=# create table t4(id int,name varchar(10));
  45. CREATE TABLE
  46. testdatabase=# \d
  47. List of relations
  48. Schema | Name | Type | Owner
  49. --------+------+-------+----------
  50. public | t1 | table | postgres
  51. public | t2 | table | postgres
  52. public | t4 | table | postgres
  53. (3 rows)
  54. #订阅节点
  55. testdatabase=# \d
  56. List of relations
  57. Schema | Name | Type | Owner
  58. --------+------+-------+----------
  59. public | t1 | table | postgres
  60. public | t2 | table | postgres
  61. (3 rows)

总结

  1. 可以通过手动对订阅的表DDL来同步发布表的DDL操作。
  2. 对发布表的列的添加或删除一定要同步到订阅节点,否则,单纯在发布节点表的列增加删除操作会导致订阅节点数据无法同步(即使是增加后删除,也要同步这些动作)。
  3. 可以对订阅的表增加列,这样并不会影响数据的同步,可以删除订阅表的列,但前提是删除的列不能包括发布表中的列,否则数据无法同步。
  4. 当改变列的类型时(不管是发布表还是订阅表),不会影响数据的同步,但是发布表和订阅表列的类型最好还是保持一致。
  5. 目前逻辑复制只能到表级别,但是可以同步一个数据库中全部的表(在创建发布时指定all tables),同样需要提前在订阅节点建好这些表,如果后面想增加同步的表,那么需要把这个表添加到发布中才能实现同步该新表。
0 评论  
添加一条新评论