PostgreSQL实战教程.pdf

23PostgreSQL实战教程国产化浪潮之上的PostgreSQL 04目录认识PostgreSQL中与众不同的索引 11PG Ganos时空场景快速开发实践23高维向量检索技术在PG中的设计与实践36PostgreSQL监控实战基于Pigsty解决实际监控问题 49PostgreSQL复制原理及高可用集群63性能优化和体系化运维 7545PostgreSQL实战教程国产化浪潮之上的PostgreSQL为什么数据库是过去式？因为过去正在变革。关系型数据库始于六七十年代，随着关系数据库理论的成立，诞生了很多伟大的公司，如：甲骨文、微软、IBM DB2、还有消失的Sybase。从七十年代至今，这些公司基本都处于垄断地位，即使在二十世纪初MySQL和PostgreSQL数据库逐渐盛行，但仍然无法影响这些历史悠久的公司的市场地位。这些历史悠久的数据库公司为什么如此坚不可摧，存在以下几个原因：1.拥有最优秀的产品提到关系型数据库时，自然而然联系到业界公认的龙头老大甲骨文。甲骨文的产品无论从性能或稳定性等方面，都是最顶尖的。2.客户需求坚不可摧由于这些公司历史悠久的品牌影响力与市场认可度，他们的产品成为了许多公司与企业的第一选择。例如当在使用甲骨文产品的过程中遇到问题时，由于对甲骨文的盲目推崇，公司与企业也不会对问题过于苛责，因为在他们心中这已经是世界上最好的产品，这也是市场不好的一个地方。3.强大的销售体系这些历史悠久的公司非常善于使用品牌营销进行产品推广与销售，每次的新品发布会总是能用出色的方式吸引众多业内人士的眼球，即使推出的新品是其他公司已经有类似产品，但这些龙头公司总能用深厚的品牌影响力与悠久的历史为自己背书，使得众多用户与企业趋之若鹜，痛快买单。（一）国际市场格局已经发生巨大变化国际市场格局的变化包括：云数据库、价格、客户需求。1.云数据库近年来云数据库对整个市场进行重新洗牌，由于云数据库能够节省成本，许多企业包括政府部门都把数据库迁到云上。2.价格从七八十年代至今，像甲骨文等公司的产品价格都十分高昂，用户在以前没有选择的余地，但如今越来越多开源和低廉的解决方案出现在市场，使得市场价格也在悄然改变。3.客户需求过去的客户购买产品的主要是奔着License，使得企业合法化或上市。如今客户虽然仍存在这样的需求，但技术服务的需求占比越来越重。结合2019年全球所有数据库的销售额来看，整个市场销售额呈下降趋势，甲骨文在全球市场销售额下降19%+，幅度十分大。国际数据库市场的巨大变化，为中国的数据库工程师和企业的发展带来很好的机遇与挑战，可以说是最好的时代。（二）国内市场迎来新机遇这里结合华泰证券一份公开的数据库研究报告来进行阐述。报告中显示，目前国内的数据库厂商处于IT产业链的中游，上承软硬件提供商与国外数据库厂商源代码授权，下接应用系统集成商与最终用户，包含电信、金融、能源和军工等。一、数据库过去式二、数据库最好的时代作者|赵振平3.客户需求过去的客户购买产品的主要是奔着License，使得企业合法化或上市。如今客户虽然仍存在这样的需求，但技术服务的需求占比越来越重。结合2019年全球所有数据库的销售额来看，整个市场销售额呈下降趋势，甲骨文在全球市场销售额下降19%+，幅度十分大。国际数据库市场的巨大变化，为中国的数据库工程师和企业的发展带来很好的机遇与挑战，可以说是最好的时代。（二）国内市场迎来新机遇这里结合华泰证券一份公开的数据库研究报告来进行阐述。报告中显示，目前国内的数据库厂商处于IT产业链的中游，上承软硬件提供商与国外数据库厂商源代码授权，下接应用系统集成商与最终用户，包含电信、金融、能源和军工等。图表：IT产业链资料来源：IDC，华泰证券研究所应用开发商集成商软硬件提供商电信金融能源军工公安财政.最终用户数据库厂商国外数据库厂商源代码授权应用系统集成商图表：数据库产业链上游开发商基础软件厂商数据库厂商操作系统厂商中间件厂商网络和硬件厂商CPU厂商服务器厂商网络设备厂商下游中游上游IT产业链下游67PostgreSQL实战教程目前国内数据库厂商主要分为三个方向，分别是传统数据库、云数据库和开源数据库，各个方向都有领头羊厂商在领跑数据库发展。根据2018年国内数据库市场份额可看出，国内的市场仍由国外市场垄断，国产数据库厂商所占市场份额相加仍与国外公司相差甚远。国产数据库武汉达梦南大通用人大金仓神舟通用蚂蚁OceanBase阿里云腾讯云百度云华为Gauss DB瀚高科技优炫软件巨杉数据库云数据库开源数据库传统数据库名次厂商市场份额（按销售额）1Oracle2IBM48.5%10.1%3Microsoft9.6%4SAP7.2%5Teradata6南大通用7达梦2018年国内数据库市场份额3.9%2.5%2.3%8神舟通用9人大金仓10其他1.5%1.1%13.3%报告指出，随着国内数据库市场的不断发展，2021年市场规模预计达到158.8亿元，并预测在未来的三年继续快速上升。综上所述，随着国产数据库厂商的不断突破与国内市场规模不断上涨，国内将迎来新的机遇与挑战。销售额（亿元）图表：中国数据库管理系统市场规模及预测02040608010012014016018092.92016105.32017117.62018130.32019144.02020158.82021（一）技术底座基于PostgreSQL的技术底座，用户可以开发很多东西。例如开发新的数据库，将PostgreSQL做成产品的一部分，如ERP的一部分或电信信息系统的一部分等。目前已经有许多成功案例，并且成为国内外很有影响力的公司，比如基于PostgreSQL的EDB，还有已经在美国上市的Greenplum，基于PostgreSQL也可以开发其他数据库。这里需要注意的是，PostgreSQL某些地方的功能无法满足用户的需求，用户可利用PostgreSQL特有的插件式机制，在PostgreSQL开发自己的插件。三、PostgreSQL是你的新底座PostgreSQLEDBGreenplum其他数据库报告指出，随着国内数据库市场的不断发展，2021年市场规模预计达到158.8亿元，并预测在未来的三年继续快速上升。综上所述，随着国产数据库厂商的不断突破与国内市场规模不断上涨，国内将迎来新的机遇与挑战。图表：中国数据库管理系统市场规模及预测020406080100120140160180（一）技术底座基于PostgreSQL的技术底座，用户可以开发很多东西。例如开发新的数据库，将PostgreSQL做成产品的一部分，如ERP的一部分或电信信息系统的一部分等。目前已经有许多成功案例，并且成为国内外很有影响力的公司，比如基于PostgreSQL的EDB，还有已经在美国上市的Greenplum，基于PostgreSQL也可以开发其他数据库。这里需要注意的是，PostgreSQL某些地方的功能无法满足用户的需求，用户可利用PostgreSQL特有的插件式机制，在PostgreSQL开发自己的插件。三、PostgreSQL是你的新底座89PostgreSQL实战教程（二）商业底座在往年，市场上20岁+的数据库工程师的简历有许多，35岁及以上的数据库工程师简历很少出现。而在近年，越来越多的35岁+的数据库工程师出现在求职市场，DBA中年危机开始显现。（一）数据库品牌虽然PostgreSQL在市场快速发展，但有不少Oracle工程师、DB2工程师、开发人员对PostgreSQL呈观望甚至抵触立场，原因是觉得学习PostgreSQL的门槛高，学习难度大。如果将数据库品牌比喻成汽车品牌，Oracle相当于宝马，DB2相当于奔驰，而新兴的PostgreSQL相当于长城汽车。如果你能够熟练驾驶宝马与奔驰，那么从技术角度来说，通过简单的熟悉与练习，你也能够快速上手长城汽车，因为大部分的原理都是一样的。因此，对于Oracle工程师、DB2工程师、开发人员来说，PostgreSQL并没有想象中的生涩难懂，相反，熟练掌握Oracle、DB2的工程师掌握PostgreSQL后，能在当下PostgreSQL盛行的数据库市场中取得极强的竞争力。（二）类似的体系结构如上图所示，Oracle与PostgreSQL的架构存在许多相似的地方：内存结构类似对一个数据库工程师来说，20岁是职业生涯的黄金发展时期，强大的学习能力使你能在短时间内成长为公司的骨干。当到达30岁时，大多数人基本到达数据库职业生涯的巅峰。当35岁的时候，许多工程师感受到人生的彷徨，在DBA生涯的分岔路上犹豫不决。此时有一部分人选择继续深耕技术，有一部分转向非技术工作。当到达45岁时，现实的残酷表明，对比20岁+的年轻人，大多数45+岁的工程师在技术市场竞争力非常小，中年危机显现。当一个数据库工程师到了30岁以后，技术的硬实力已经无法满足职业生涯发展的需求，更多的软实力才能让自己具备更多的市场竞争力。这个软实力包括许多部分，例如跟随行业变革前进、优秀团队、沟通能力、团队沟通能力与协同能力等。四、35岁DBA中年危机五、PostgreSQL学习方法论PostgreSQL强大的可塑性与广泛的使用市场使之成为商业底座。基于PostgreSQL创立的新公司遍布世界各地，这些公司基于PostgreSQL可以做许多新产品，例如EDB。基于PostgreSQL还可以满足许多需求，例如相关的技术服务、技术支撑、开发服务等。综上所述，PostgreSQL不仅是硬实力的技术底座，也是高速发展的商业底座。PostgreSQL新公司诞生数据库新产品数据库技术服务例如以前很多人做Oracle，也确实能有许多就业方向，但目前市场的Oracle工程师数量众多，市场竞争激烈。随着整个时代发生变革，市场往开源数据库和国产数据库方向前进，因此PostgreSQL是一个很好的基座。我们跟随行业与时代的变革而变革，离开舒适区，投身主流领域，才能更好扩展自身职业生涯。随着数据库市场的不断更新迭代，PostgreSQL产业在中国市场发生巨大变化，PostgreSQL管道铺设的各个行业，以各种直接或间接的形式渗透到各行各业。在PostgreSQL快速发展的时代，或许35岁的中年危机也是人生契机。（一）数据库品牌虽然PostgreSQL在市场快速发展，但有不少Oracle工程师、DB2工程师、开发人员对PostgreSQL呈观望甚至抵触立场，原因是觉得学习PostgreSQL的门槛高，学习难度大。如果将数据库品牌比喻成汽车品牌，Oracle相当于宝马，DB2相当于奔驰，而新兴的PostgreSQL相当于长城汽车。如果你能够熟练驾驶宝马与奔驰，那么从技术角度来说，通过简单的熟悉与练习，你也能够快速上手长城汽车，因为大部分的原理都是一样的。因此，对于Oracle工程师、DB2工程师、开发人员来说，PostgreSQL并没有想象中的生涩难懂，相反，熟练掌握Oracle、DB2的工程师掌握PostgreSQL后，能在当下PostgreSQL盛行的数据库市场中取得极强的竞争力。（二）类似的体系结构如上图所示，Oracle与PostgreSQL的架构存在许多相似的地方：内存结构类似五、PostgreSQL学习方法论例如以前很多人做Oracle，也确实能有许多就业方向，但目前市场的Oracle工程师数量众多，市场竞争激烈。随着整个时代发生变革，市场往开源数据库和国产数据库方向前进，因此PostgreSQL是一个很好的基座。我们跟随行业与时代的变革而变革，离开舒适区，投身主流领域，才能更好扩展自身职业生涯。随着数据库市场的不断更新迭代，PostgreSQL产业在中国市场发生巨大变化，PostgreSQL管道铺设的各个行业，以各种直接或间接的形式渗透到各行各业。在PostgreSQL快速发展的时代，或许35岁的中年危机也是人生契机。进程结构类似用户进程连接方式类似两者都有高速缓存区，Oracle称为SGA，PostgreSQL称为内存共享，区域也是对应的。都有日志输写进程、数据库输写进程、归档进程。当用户连接进来时，在Oracle里面默认分配服务器之间的进程，PostgreSQL也一样。需要排序时，Oracle在PGA里面进行，PostgreSQL在Work Men里面进行。22Y30Y35Y45Y硬实力软实力1011PostgreSQL实战教程故障排查的过程类似Oracle和PostgreSQL都有错误日志文件等。通过上述架构对比，可以发现Oracle与PostgreSQL十分相似，对于熟练掌握Oracle的工程师来说，可以快速上手PostgreSQL。随着国内数据库市场的巨大改革，PostgreSQL逐渐占据市场主导地位，然而Oracle工程师遍地都是，而从业12年的PostgreSQL工程师仍是凤毛麟角。不少工程师已经开始嗅到其中契机，上手PostgreSQL增强自身竞争力，在日渐扩大的数据库市场占得一席之地。认识PostgreSQL中与众不同的索引（一）索引的作用这些历史悠久的数据库公司为什么如此坚不可摧，存在以下几个原因：索引主要有三个作用：（1）加速TUPLE定位select*from test01 where k=10;select*from test01 where k100 and k、=、100 and k、=、图型没有重叠操作符号：、&BRIN索引块范围索引；存储放在一个表的连续物理块范围上的值摘要信息，如最大值、最小值；可以用于：、=、通常其他数据库没有BRIN索引，是PG的亮点功能。2.其他分类PG索引按照其他分类也可分为：唯一索引，部分索引，多列索引和表达式索引，这里不展开作详细介绍。（五）PostgreSQL中文社区技术认证目前PostgreSQL中文社区技术认证有三级认证，分别为PCA（认证专员）、PCP（认证专家）PCM（认证大师），可在社区网站“http:/”查看。（三）非阻塞式创建索引非阻塞式创建索引是PostgreSQL的一大优势。使用普通方式创建索引时，PostgreSQL会锁定表以防止写入，在此过程中其他用户仍然可以读取表，但是DML等操作被一直阻塞，直到索引创建完毕，这在大多数的在线数据库中都是不可接受的行为。鉴于此，PostgreSQL支持不长时间阻塞更新的情况下建立创建索引，这是通过“CREATE INDEX CONCURRENTLY idx_tab01_note on testtab01(note);”选项来实现的。当该选项被使用时，PostgreSQL会执行表的两次扫描，因此该方法需要更长一些的时间来建索引，尽管如此，这个选项也是很有用的一个功能。（四）非阻塞式重建索引在PostgreSQL的12版本之前，重建索引时不支持Concurrently的参数，可以在同样的列上用Concurrently建一个不同名的新索引，再把旧索引删除，这样也不阻塞DML等语句。（五）PostgreSQL中文社区技术认证目前PostgreSQL中文社区技术认证有三级认证，分别为PCA（认证专员）、PCP（认证专家）PCM（认证大师），可在社区网站“http:/”查看。唯一索引部分索引多列索引表达式索引（函数索引）软实力CREATE INDEX idx_test01_park_k ON test01(k)where k and k”表示这个数组中包含某个固定电话，这样就可以查出号码对应的号主。假设我们有一张表，记录了IP地址范围对应的地区，给一个公网IP就可以查询出这个IP地址所对应的地区。（一）普通解决方案如上图所示，该格式包含IP的ID，IP的起始地址与结束地址，IP所在地区，IP对应的运营商，Inet表示PostgreSQL里IP地址的范围，例表如下：在这里我们建了250000行数据，然后我们再给它建了一个GIN索引，用“”表示这个数组中包含某个固定电话，这样就可以查出号码对应的号主。通过执行计划可以看到，通过在PostgreSQL的数组上建立GIN索引来查找数值时，所需时间非常短，仅需0.108ms。三、数组上建GIN索引的例子四、快速查找某个IP是哪个地区-Bitmap Heap Scan on test01(cost=89.00.1508.48 rows=1 width=55)(actual time=2.293.2.354 row=1 loops=1)Recheck Cond:(id=100)Rows Removed by Index Recheck:387 Heap Block:lossy=4 -Bitmap Index Scan on idx_test01_k_brin_4(cost=0.00.89.00 rows=388 width=0)(actual time=2.226.2.227rows=40 loops=1)Index Cond:(id=100)Planning Time:0.225 msExecution Time:2.436 ms(8 rows)-Bitmap Heap Scan on contacts(cost=29.69.2298.29 rows=1250 width=95)(actual time=0.079.0.080 row=1 loops=1)Recheck Cond:(phone [phone]:character varying(32)Heap Blocks:exact=1 -Bitmap Index Scan on idx_contacts_phone (cost=0.00.29.37 rows=1250 width=0)(actual time=0.053.0.053rows=1 loops=1)Index Cond:(phone [phone]:character varying(32)Planning Time:0.113 msExecution Time:0.108 ms(7 rows)QUERY PLANQUERY PLAN联系人表：CREATE TABLE contacts(id int primary key,name varchar(40),phone varchar(32),address text);create table ipdb1(id int,ip_begin inet,ip_end inet,area text,sp text);普通做法基本格式osdba=#insert into contacts select seq,seq,arrayseq+13600000000,seq+13600000001 from generate_series(1,500000,2)as seq;INSERT 0 250000Time:2368.684 ms(00:02.369)osdba=#CREATE INDEX idx_contacts_phone on contacts using gin(phone);CREATE TNDEXTime:56196.839 ms(00:56.197)osdba=#SELECT*FROM contacts WHERE phone array13600006688:varchar(32);id|name|phone|address-+-+-+-6687|6687|13600006687,13600006688|(1 row)Time:5.345 msosdba=#explain SELECT*FROM contacts WHERE phone array13600006688:varchar(32);1617PostgreSQL实战教程可以看到，例如IP地址1.0.1.0到1.0.3.255是来自福建电信。有了这么一个地址库，我们就可以快速查询一个IP所对应的相关信息。例如我们想查询36.22.250.214来自哪里，可以输入：select*from ipdb1 where 36.22.250.214=ip_begin and 36.22.250.214 36.22.250.214:inet;查询 IP 36.22.250.214，可以快速查到对应信息浙江电信。从上方的执行计划可以看到耗时大幅减少，并且Cost值为8.3，对比之前的268大幅降低。通过这种方式，当有大量系统要来查询IP地址时，可以有效减少耗时，并降低CPU占用，以上就是GIST用RANGE使用的一个例子。如上方所示，首先我们建一张表，插入1000000条测试数据，接着收集统计信息。由于现在表中可能没有索引，走的并行做全盘扫描，此时执行时间为100300毫秒。如果关掉并行，执行时间还会更长。其他数据库中，like是要找两个%中间的数，通常是无能为力，但在PostgreSQL中可以解决这个问题。首先先装入插件create extension pg_trgm;，之后建一个GIN索引，让Like走%99999%。通过执行计划可以看到，这次执行时间为2ms，效率很高，解决了其他数据库遇到的难题。如上方所示，首先我们建一张表，插入1000000条测试数据，接着收集统计信息。由于现在表中可能没有索引，走的并行做全盘扫描，此时执行时间为100300毫秒。如果关掉并行，执行时间还会更长。其他数据库中，like是要找两个%中间的数，通常是无能为力，但在PostgreSQL中可以解决这个问题。首先先装入插件create extension pg_trgm;，之后建一个GIN索引，让Like走%99999%。通过执行计划可以看到，这次执行时间为2ms，效率很高，解决了其他数据库遇到的难题。PostgreSQL中还有一个黑科技让Like在%XXX%走索引，下面举例说明。五、让like%XXX%走索引2021PostgreSQL实战教程4.造数据（续）接着开始造入100000条记录的数据，由于标签是造的数据，所以是随机生成的。5.建GIN索引CREATE INDEX idx_user_tag_tag on user_tag using gin(tag);造数据完成后，在列上建GIN索引，建立完成后，可在表中快速查询到相应信息。例如查询性格为“外向”和“细心”的老师，可以通过语句：select*from user_tag where tag 性格:外向,细心 and tag 职业:老师;可以很快查到，如下方所示：如果要查询更为详细的信息，例如性格为“外向”和“细心”而又喜欢“滑雪”和“游泳”的医生，可以通过语句：select*from user_tag where tag 性格:外向,细心 and tag 职业:医生 and tag 爱好:滑雪,游泳;很快查到，如下方所示：4.造数据（续）接着开始造入100000条记录的数据，由于标签是造的数据，所以是随机生成的。5.建GIN索引CREATE INDEX idx_user_tag_tag on user_tag using gin(tag);造数据完成后，在列上建GIN索引，建立完成后，可在表中快速查询到相应信息。例如查询性格为“外向”和“细心”的老师，可以通过语句：select*from user_tag where tag 性格:外向,细心 and tag 职业:老师;可以很快查到，如下方所示：如果要查询更为详细的信息，例如性格为“外向”和“细心”而又喜欢“滑雪”和“游泳”的医生，可以通过语句：select*from user_tag where tag 性格:外向,细心 and tag 职业:医生 and tag 爱好:滑雪,游泳;很快查到，如下方所示：最后我们来看，如何用GIN索引在JSON上做用户画像系统。1.标签模型职业：农民、工人、IT工程师、理发师、医生、老师、美工、律师、公务员、官员爱好：游泳、乒乓球、羽毛球、网球、爬山、高尔夫球、滑雪、爬山、旅游学历：无学历、小学、初中、高中、中专、专科、本科、硕士、博士性格：外向、内向、谨慎、稳重、细心、粗心、浮躁、自信首先建立一个简单的标签模型如上，总共分为四类：职业、爱好、学历和性格。2.建表CREATE TABLE user_tag(uid serial primary key,tag jsonb);第二步通过我们建立一张表，第一个字段UID表示用户ID，第二个TAG是打标签，此处打一个JSONB的数据类型。3.造数据建完表后，为了查看效果需要造数据，我们写了一些辅助的函数来完成，函数如下：六、GIN+JSON用户画像2223PostgreSQL实战教程如果我们给用户打了这么一个标签，就可通过SQL很快查出对应的标签信息，以上就是用GIN索引做用户画像的一个简单示例。更多阿里云PostgreSQL图像识别、人脸识别、相似特征检索、相似人群圈选等精选案例可在https:/ Ganos时空场景快速开发实践（一）Ganos是什么Ganos是包含SQL+NoSQL云数据库与大数据的时空数据引擎。Ganos取名于大地女神盖亚（Gaea）和时间之神柯罗诺斯（Chronos），代表“时空”结合。Ganos的使命是将时空信息处理融入公有云/专有云PaaS服务，成为一种普惠计算。上图列举了Ganos的许多特性，这些特性有数据库本身的能力，更多的是Ganos赋能给数据库之后的能力。（二）Ganos支持哪些产品一、认识Ganos作者|图贲作者|图贲Ganos特性GeoSQL矢量模型栅格模型时空模型百万列万亿行10亿QPS事务支持高可展PB级稀疏表读写毫秒动态列R-Tree高压缩10:1分布式架构存储计算分离2425PostgreSQL实战教程Ganos不是一个独立的产品，而是以赋能的方式嵌入在云数据库产品中。如上图所示，Ganos赋能的产品包括RDS PG、PolarDB、ADB PG、Lindorm/HBase以及DLA。其中RDS PG和PolarDB是事务型关系数据库，既支持事务型应用，也支持轻量级分析，这两款数据库中的Ganos模型和函数功能是最全面也是最丰富的。可以将Ganos理解为PostGIS的升级版本PostGIS+，高度兼容PostGIS，其他几款数据库产品ADB Ganos、Lindorm/HBase Ganos、DLA Ganos更多是面向大数据分析型的场景。（三）Ganos中丰富的时空模型相比PostGIS，Ganos在时空模型上具备更多更强的特性和能力。除了支持传统的几何模型、栅格模型和拓扑网络模型，还扩展支持了网格模型、时空轨迹模型以及点云模型。其中空间网格模型是Ganos3.0版本推出的新特性，编码标准遵循自然资源部地球空间网格编码规则，是在这个规则基础之上设计和实现模型。（一）创建Ganos扩展几何模型Create extension ganos_geometry cascade;Create extension ganos_geometry_topology;栅格模型Create extension ganos_raster;轨迹模型Create extension ganos_trajectory;点云模型Create extension ganos_pointcloud;路径模型Create extension ganos_trajectory;网格码模型Create extension ganos_geomgrid;矢量金字塔Create extension ganos_geometry pyramid;在PostgreSQL数据库中使用Ganos需要先创建Ganos的扩展。上图列举了Ganos中的七大模型以及扩展语句，其中六个模型在上面已做过介绍，此处要额外补充的是矢量金字塔模型。它是在几何模型基础之上新增的一项黑科技，是为了能够快速显示大规模空间几何数据（千万级以上）而设计的一种索引结构。矢量金字塔对空间几何数据创建一种稀疏索引，可以动态输出标准的mvt-pbf格式数据，通过Ganos提供的矢量金字塔，亿条空间几何记录可以实现分钟级索引创建和秒级终端显示，无需进行传统繁琐的切瓦片处理。创建扩展之后，如上图所示，在数据库中可以通过dx命令就能查询到所有已经创建的扩展，目前Ganos3.3版本。（二）矢量、栅格、轨迹入库创建Ganos扩展之后，接下来要解决数据入库，不同的数据类型有不同的入库方法：矢量数据入库因为兼容postgis生态，可直接使用空间开源工具，包括ogr2ogr、shp2pgsql、QGIS、pg_dump/pg_restore等。栅格、遥感数据入库（1）Ganos提供入库接口ST_importFrom、ST_createRast；（2）Ganos支持OSS作为存储引擎，这意味着用户可以将栅格原始文件存放在OSS中，Ganos会建立内部高效连接，其中金字塔索引可选择数据库内建，也可选择适配OSS上已有索引信息；（3）pg_dump/pg_restore时，OSS外部原始文件不需要挪动，不影响PG数据库的使用；（4）入库时支持金字塔内建+外建自由组合；（5）支持批量文件并行入库，支持单幅超大影像切分后并行入库。轨迹数据入库Ganos提供入库接口ST_makeTrajectory；支持轨迹点动态追加；支持点表抽取为轨迹对象。此外，Ganos支持与商用GIS平台如SuperMap、ArcGIS对接，矢量数据与栅格/遥感数据可借助其平台直接入库。（三）PG Ganos如何管理PB级遥感影像1.PostgresSQL+Ganos+OSS组合二、如何使用Ganos2627PostgreSQL实战教程上文中提到，Ganos在云上可以借助OSS存储，它是在引擎层打通的。因此通过“PostgreSQL+Ganos+OSS”组合，可实现 PB级遥感影像的管理。元数据和部分金字塔数据可以存储在数据库内部，遥感数据原始文件存放在OSS中，由于OSS存储价格低廉，使得用户的使用成本也大大降低。2.遥感影像注册（入库）只需要按照insert SQL语句直接写入到数据库，将OSS地址传给ST_createRast接口即可。这里需要注意的是OSS与RDS购买域必须为同一个，比如都是北京区域。3.大范围影像拼接、镶嵌将遥感影像数据注册入库之后，在Ganos也可以通过ST_mosaicFrom、ST_mosaicTo对大范围的影像进行拼接镶嵌等操作再进行输出，达到管理PB级遥感影像管理的目的。如上图所示，Ganos管理轨迹数据主要通过轨迹构造、轨迹压缩和轨迹相似性判断。在Ganos中有原生的轨迹模型叫Trajectory，在创建轨迹表时可直接用这个数据类型。轨迹构造的单独接口ST_makeTrajectory有很多的重载版本，具体使用方式可在官网的用户手册里进行查看。轨迹还提供一些轨迹压缩与轨迹相似性判断这些比较重要的接口。轨迹压缩是通过ST_Compress压缩接口实现，压缩时可以保留重要的轨迹特征点，因此压缩质量会更好。轨迹相似性目前主要支持Lcss算法以及Jaccard的这种路径匹配算法。（五）Ganos与开源工具（四）PG Ganos如何管理轨迹数据Ganoscloud spatio-temporal DBMapServerSAGA GISTerraLibTerraVlewGeoNetworkGRASSGISgvSIGMapNikOpenJumpUDigGeoServerQGISOpen-StreetMap2829PostgreSQL实战教程如上图所示，Ganos无缝对接兼容PostGIS的各类GIS软件，显示和编辑包括GeoServer、QGIS、uDig、OpenJump等，这里重点介绍PGAdmin4。PGAdmin4通过与Ganos集成，能够支持显示Ganos中的矢量和栅格数据，后续也会支持轨迹数据的直接显示。同时，在PGAdmin4中可以使用Ganos矢量金字塔功能，也就是说在PGAdmin4中可以直接显示上亿级的矢量数据，可以达到不切片、无障碍浏览效果。（需要PGAdmin4的定制版本可与我们联系，将来PGAdmin4改造代码会开源出来，供大家下载）（二）空间统计分析1.数据准备数据准备包含两个类型的数据：矢量数据与栅格数据。（1）矢量数据：包含多边形的行政街区数据（表hk_tpu）与点类型的患者案例数据(表hk_cases)。如上图所示，通过矢量数据可以看到案例患者的性别与年龄、确诊医院、所属街区等。（一）实战介绍实战课题（1）如何通过Ganos快速分析城市结构、社会属性与新冠病毒传播的之间的关系；（2）如何在Ganos中通过轨迹数据追踪患者行程，并挖掘风险点。实战技能（1）利用Ganos进行空间统计分析；（2）实现矢量、栅格一体化查询；（3）实现轨迹追踪；（4）实现跨区域时空查询。实战目的（1）熟练使用Ganos；（2）学会多源数据融合处理；（3）实现时空场景快速呈现，减少开发成本。三、进阶实战3031PostgreSQL实战教程（2）栅格数据：是具有社会属性的数据，包括NDVI监测数据、建筑密度、建筑高度值等，都以Tif文件形式提供。2.数据入库矢量数据库入库 ogr2ogr-nln hk_tpu-nlt MULTIPOLYGON-geomfield geom-f PostgreSQL PG:dbname=ganos_train_dbhost=pgm-*port=1921 user=ganos_train password=ganos2021“”./data/hk_tpu_84.shp”ogr2ogr-nln hk_cases“./data/sick_cases.shp”矢量数据是用ogr2ogr进行入库，填写的是云上购买的RDS PG的访问参数。栅格数据入库 首先，Tif文件上传至OSS；其次，执行导入的SQL语句insert into hk_ndvi_rast values(1,st_importfrom(chunktbl,OSS:/accessKey:accessSecretoss-cn-*- m.tpu,m.sum as ndvi_sum,m.avg as ndvi_avg,n.cases_count from (select c.tpu as tpu,sum(c.value)as sum,avg(c.value)as avg from (SELECT b.tpu as tpu,(ST_Values(a.rast,b.geom,0).*from hk_ndvi_rast a,hk_tpu b)c group by c.tpu)m,(select b.tpu as tpu,count(a.id)as cases_count from hk_cases a,hk_tpu b where st_contains(b.geom,a.geom)group by b.tpu)n where m.tpu=n.tpu order by m.avg;3233PostgreSQL实战教程NDVI值表示植被指数，当这个值过大或者过小时，不一定代表人群数多，往往是在中间值时，人口聚集最多。如上图所示，我们截取了前面几十条以及后面几十条数据，通过结果可以发现NDVI值在为0.20.27之间，它的案例数是最聚集的。同样通过一条SQL语句，我们可以计算所有街区的建筑高度值与案例数之间的关系，如下所示：可以看到，在建筑密度比较低的这些街区中，案例分布通常是个位数。在建筑密度比较高的这些街区中，案例数明显增加,最大值也是分布在建筑密度比较高的这些区域,由此可以反映出来建筑密度值越大,案例数量呈现聚集性。（四）轨迹追踪1.用点表构造轨迹表我们可以将患者的案例形成轨迹，同时通过轨迹追踪患者的行程，挖掘一些潜在的风险点。如上图所示，我们用案例编号查询行程，可以看到编号为105的案例在不同的时间去过很多场所（案例数据中监测时间记录不够详细，只具体到某一天）。可以将监测行程点抽取形成一条轨迹数据，通过ST_makeTrajectory，把编号为105的案例聚合成一条轨迹写入到轨迹表里。如果想把所有的患者案例聚合成轨迹，将这个查询语句中的where cases=105直接改成Group Bycases就可以实现。2.轨迹追踪形成