LightDB-A standby支持查询

追魂曲

已于 2023-11-14 09:41:13 修改

阅读量75

点赞数

分类专栏： LightDB postgresql 文章标签：数据库 postgresql mysql

于 2023-05-29 15:00:03 首次发布

本文链接：https://blog.csdn.net/qq_22066003/article/details/130923753

版权

LightDB 同时被 2 个专栏收录

34 篇文章 1 订阅

订阅专栏

postgresql

32 篇文章 0 订阅

订阅专栏

原生LightDB-A standby是不能查询segment节点数据的。

主下面简称cnp，standby下面简称cns。

现有环境如下(虚拟机环境)：

[gpadmin@localhost ~]$ gpstate -s
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-Starting gpstate with args: -s
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-local LightDB-A Version: 'LightDB-A 23.1-beta build dev'
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-coordinator LightDB-A Version: 'PostgreSQL 12.12 (LightDB-A Database 23.1-beta build dev) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180303 (Red Hat 7.3.1-5), 64-bit compiled on May 25 2023 23:25:43'
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-Obtaining Segment details from coordinator...
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-Gathering data from segments...
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-----------------------------------------------------
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:--Coordinator Configuration & Status
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-----------------------------------------------------
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Coordinator host                  = 192.168.237.145
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Coordinator postgres process ID   = 49893
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Coordinator data directory        = /home/gpadmin/data/cn/gpseg-1
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Coordinator port                  = 59000
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Coordinator current role          = dispatch
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   LightDB-A initsystem version      = 23.1-beta build dev
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   LightDB-A current version         = PostgreSQL 12.12 (LightDB-A Database 23.1-beta build dev) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180303 (Red Hat 7.3.1-5), 64-bit compiled on May 25 2023 23:25:43
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Postgres version                  = 12.12
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Coordinator standby               = 192.168.237.146
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Standby coordinator state         = Standby host passive
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-----------------------------------------------------
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-Segment Instance Status Report
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-----------------------------------------------------
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Segment Info
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Hostname                          = localhost.localdomain
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Address                           = 192.168.237.145
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Datadir                           = /home/gpadmin/data/dn/gpseg0
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Port                              = 59100
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Status
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      PID                               = 49849
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Configuration reports status as   = Up
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Database status                   = Up
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-----------------------------------------------------
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Segment Info
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Hostname                          = localhost.localdomain
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Address                           = 192.168.237.145
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Datadir                           = /home/gpadmin/data/dn/gpseg1
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Port                              = 59101
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Status
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      PID                               = 49851
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Configuration reports status as   = Up
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Database status                   = Up
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-----------------------------------------------------
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Segment Info
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Hostname                          = localhost.localdomain
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Address                           = 192.168.237.145
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Datadir                           = /home/gpadmin/data/dn/gpseg2
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Port                              = 59102
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Status
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      PID                               = 49852
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Configuration reports status as   = Up
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Database status                   = Up
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-----------------------------------------------------
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Segment Info
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Hostname                          = localhost.localdomain
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Address                           = 192.168.237.145
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Datadir                           = /home/gpadmin/data/dn/gpseg3
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Port                              = 59103
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-   Status
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      PID                               = 49853
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Configuration reports status as   = Up
20230528:19:55:16:050068 gpstate:localhost:gpadmin-[INFO]:-      Database status                   = Up

[gpadmin@localhost ~]$ gpstate -f
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:-Starting gpstate with args: -f
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:-local LightDB-A Version: 'LightDB-A 23.1-beta build dev'
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:-coordinator LightDB-A Version: 'PostgreSQL 12.12 (LightDB-A Database 23.1-beta build dev) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180303 (Red Hat 7.3.1-5), 64-bit compiled on May 25 2023 23:25:43'
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:-Obtaining Segment details from coordinator...
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:-Standby coordinator details
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:-----------------------
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:-   Standby address          = 192.168.237.146
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:-   Standby data directory   = /home/gpadmin/data/cn/gpseg-1
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:-   Standby port             = 59000
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:-   Standby PID              = 30100
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:-   Standby status           = Standby host passive
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:--------------------------------------------------------------
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:--pg_stat_replication
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:--------------------------------------------------------------
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:--WAL Sender State: streaming
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:--Sync state: sync
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:--Sent Location: 0/C135238
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:--Flush Location: 0/C135238
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:--Replay Location: 0/C135238
20230528:19:56:22:050260 gpstate:localhost:gpadmin-[INFO]:--------------------------------------------------------------

新建表：

create table canopy_table_detail_classic(id int primary key,v text,branch_id varchar(100));
create table canopy_table_branch_classic(v text,branch_id varchar(100));
create table canopy_table_classic(id int primary key,v text);

insert into canopy_table_branch_classic select uuid_generate_v4()::text,id from generate_series(1,1000) id;
insert into canopy_table_detail_classic select id, uuid_generate_v4()::text,id % 1000 from generate_series(1,1000000) id;
insert into canopy_table_classic select id, uuid_generate_v4()::text from generate_series(1,10000000) id;

在cnp上查询数据：

postgres=# select b.branch_id,max(a.id),count(1),max(a.v) from canopy_table_classic a,canopy_table_detail_classic b,canopy_table_branch_classic c where a.id=b.id and b.branch_id = c.branch_id group by b.branch_id limit 10;
 branch_id |  max   | count |                 max                  
-----------+--------+-------+--------------------------------------
 979       | 999979 |  1000 | fff322a1-8e5f-4f30-9816-874e8727685f
 845       | 999845 |  1000 | ffd4be62-8d2f-4867-bf9d-7e36e44a953b
 975       | 999975 |  1000 | ffac120c-5e3f-45c8-bc3e-5877a5f2a731
 354       | 999354 |  1000 | ffee5817-e050-4a8f-9cfd-cc5642c9c6f8
 537       | 999537 |  1000 | ffeef7f7-51c7-4279-9f77-86bef73ce128
 877       | 999877 |  1000 | ff3b083a-a64e-45c5-80f5-a37521a542ed
 462       | 999462 |  1000 | ffdb0e49-d166-4cea-bf3f-e144b0897ea9
 497       | 999497 |  1000 | ffb0c3cc-3af3-492c-ac20-e1017860dca2
 551       | 999551 |  1000 | ff41696d-5abd-4ce8-8d6a-52cde470612f
 521       | 999521 |  1000 | ffc8f1bf-d0ab-41c7-8a68-6599ce9a2e4e
(10 rows)

在cns上查询数据：

postgres=# select b.branch_id,max(a.id),count(1),max(a.v) from canopy_table_classic a,canopy_table_detail_classic b,canopy_table_branch_classic c where a.id=b.id and b.branch_id = c.branch_id group by b.branch_id limit 10;
 branch_id |  max   | count |                 max                  
-----------+--------+-------+--------------------------------------
 979       | 999979 |  1000 | fff322a1-8e5f-4f30-9816-874e8727685f
 845       | 999845 |  1000 | ffd4be62-8d2f-4867-bf9d-7e36e44a953b
 975       | 999975 |  1000 | ffac120c-5e3f-45c8-bc3e-5877a5f2a731
 354       | 999354 |  1000 | ffee5817-e050-4a8f-9cfd-cc5642c9c6f8
 537       | 999537 |  1000 | ffeef7f7-51c7-4279-9f77-86bef73ce128
 877       | 999877 |  1000 | ff3b083a-a64e-45c5-80f5-a37521a542ed
 462       | 999462 |  1000 | ffdb0e49-d166-4cea-bf3f-e144b0897ea9
 497       | 999497 |  1000 | ffb0c3cc-3af3-492c-ac20-e1017860dca2
 551       | 999551 |  1000 | ff41696d-5abd-4ce8-8d6a-52cde470612f
 521       | 999521 |  1000 | ffc8f1bf-d0ab-41c7-8a68-6599ce9a2e4e
(10 rows)

发现在cns上是支持查询的。在cns上需要修改配置文件hot_standby = on