cn:
[fang@vm101 pg13data (master)]$ psql -p 20010
psql (13.8)
Type "help" for help.
postgres=# select * from pg_dist_node;
nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
--------+---------+-----------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
3 | 2 | localhost | 20001 | default | f | t | primary | default | f | t
(1 row)
复制dn, 造一个standby: pg_basebackup -D /mnt/sdb/pg13data/citus-d-20011-standby -w -R --wal-method=stream --dbname="host=127.0.0.1 user=fang port=20001"
端口改为20011: vi citus-d-20011-standby/postgresql.conf
启动: pg_ctl -D citus-d-20011-standby start -l citus-d-20011-standby.log
cn加入新的dn节点作为secondary
$ psql -p 20010
psql (13.8)
Type "help" for help.
postgres=# select citus_add_node('localhost', 20011, 2, 'secondary');
citus_add_node
----------------
43
(1 row)
postgres=# select * from pg_dist_node;
nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
--------+---------+-----------+----------+----------+-------------+----------+-----------+-------------+----------------+------------------
3 | 2 | localhost | 20001 | default | f | t | primary | default | f | t
43 | 2 | localhost | 20011 | default | f | t | secondary | default | f | t
(2 rows)
postgres=# insert into t1 values(6),(5);
NOTICE: issuing INSERT INTO public.t1_102011 AS citus_table_alias (id) VALUES (6), (5)
DETAIL: on server fang@localhost:20001 connectionId: 1
INSERT 0 2
postgres=# select * from t1;
NOTICE: issuing SELECT id FROM public.t1_102011 t1
DETAIL: on server fang@localhost:20001 connectionId: 1
id
----
1
2
4
3
6
5
(6 rows)
postgres=#
停掉primary worker, 20001, pg_ctl -D citus-d-20001 stop
查不了
postgres=# select * from t1;
ERROR: connection to the remote node localhost:20001 failed with the following error: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 20001?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 20001?
设置citus.use_sencodary_nodes, 设不了
postgres=# set citus.use_secondary_nodes to always;
ERROR: parameter "citus.use_secondary_nodes" cannot be set after connection start
修改postgresql.conf, reload cn: 可以查
citus.use_secondary_nodes = always
$ psql -p 20010
psql (13.8)
Type "help" for help.
postgres=# show citus.use_secondary_nodes;
2023-01-12 14:12:17.575 CST [4213] LOG: starting maintenance daemon on database 13580 user 10
2023-01-12 14:12:17.575 CST [4213] CONTEXT: Citus maintenance daemon for database 13580 user 10
citus.use_secondary_nodes
---------------------------
always
(1 row)
postgres=# 2023-01-12 14:12:17.581 CST [4213] ERROR: writing to worker nodes is not currently allowed
2023-01-12 14:12:17.581 CST [4213] DETAIL: citus.use_secondary_nodes is set to 'always'
2023-01-12 14:12:17.581 CST [4213] CONTEXT: Citus maintenance daemon for database 13580 user 10
2023-01-12 14:12:17.583 CST [4197] LOG: background worker "Citus Maintenance Daemon: 13580/10" (PID 4213) exited with exit code 1
postgres=# 2023-01-12 14:12:22.590 CST [4215] LOG: starting maintenance daemon on database 13580 user 10
2023-01-12 14:12:22.590 CST [4215] CONTEXT: Citus maintenance daemon for database 13580 user 10
2023-01-12 14:12:22.593 CST [4215] ERROR: writing to worker nodes is not currently allowed
2023-01-12 14:12:22.593 CST [4215] DETAIL: citus.use_secondary_nodes is set to 'always'
2023-01-12 14:12:22.593 CST [4215] CONTEXT: Citus maintenance daemon for database 13580 user 10
2023-01-12 14:12:22.594 CST [4197] LOG: background worker "Citus Maintenance Daemon: 13580/10" (PID 4215) exited with exit code 1
postgres=# 2023-01-12 14:12:27.600 CST [4217] LOG: starting maintenance daemon on database 13580 user 10
2023-01-12 14:12:27.600 CST [4217] CONTEXT: Citus maintenance daemon for database 13580 user 10
2023-01-12 14:12:27.603 CST [4217] ERROR: writing to worker nodes is not currently allowed
2023-01-12 14:12:27.603 CST [4217] DETAIL: citus.use_secondary_nodes is set to 'always'
2023-01-12 14:12:27.603 CST [4217] CONTEXT: Citus maintenance daemon for database 13580 user 10
2023-01-12 14:12:27.604 CST [4197] LOG: background worker "Citus Maintenance Daemon: 13580/10" (PID 4217) exited with exit code 1
show citus.uelect * from t1;
NOTICE: issuing SELECT id FROM public.t1_102011 t1
DETAIL: on server fang@localhost:20011 connectionId: 1
id
----
1
2
4
3
6
5
6
5
(8 rows)
如果是always, 原来dn primary起来也不能写
postgres=# insert into t1 values(6),(5);
ERROR: writing to worker nodes is not currently allowed
DETAIL: citus.use_secondary_nodes is set to 'always'
citus.use_secondary_nodes (enum)
Sets the policy to use when choosing nodes for SELECT queries. If this is set to ‘always’, then the planner will query only nodes which are marked as ‘secondary’ noderole in pg_dist_node.
The supported values for this enum are:
never: (default) All reads happen on primary nodes.
always: Reads run against secondary nodes instead, and insert/update statements are disabled.
没有vip, 没有自动高可用切换时:
- 停掉CN primary, 手动promote CN standby, 可以手动连上CN standby继续读写;
- 有secondary DN, 停掉primary DN, 不能读也不能写;
- 此时把use_secondary_nodes设置为always(默认为never), reload, 可以读
- 再启动primary DN, 可以读, 不能写, 因为always
手动提升: pg_ctl -D citus-d-20011-standby promote
citus没有检测dn主备
$ psql -p 20010
psql (13.8)
Type "help" for help.
postgres=# select * from pg_dist_node;
nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
--------+---------+-----------+----------+----------+-------------+----------+-----------+-------------+----------------+------------------
3 | 2 | localhost | 20001 | default | f | t | primary | default | f | t
43 | 2 | localhost | 20011 | default | f | t | secondary | default | f | t
(2 rows)
postgres=# insert into t1 values(6),(5);
ERROR: connection to the remote node localhost:20001 failed with the following error: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 20001?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 20001?
可以强行设置secondary为primary,(要先改groupid,一个group不能有两个primary),再关联placement;
secondary不是备,主备没有登记在citus,主备切换后,应该用select citus_update_node();