MySQL 过渡 PostgreSQL 经验分享

UPUPMO
1,206 阅读21分钟
原文链接： mp.weixin.qq.com

声明

  • 本文思路从一开始就定位为基于云数据库来部署，不考虑生产环境自己搭建、维护、备份

  • 本文只为：sculptor-boot-generator 代码生成器体系服务 Github Gitee

  • 感谢群里老鐡童鞋审稿!

PostgreSQL 资料打包

  • 关注公众号：cd-k8s，聊天窗口输入： postgresql

  • 可以得到 1 套视频、9 本 PDF 资料

总结

  • MySQL 的用户群体性好于 PostgreSQL，特别是国内，更加容易上手。但是 PostgreSQL 从体验上最大优势就是插件以及带来的各种可能。

  • 两者的基准压力测试工具不同，很难说测试数据对比是公平的，如果是通过 Java 代码测试同配置的 CURD，非极限情况下，两者使用感受上差距不大。

  • PostgreSQL 在做统计分析上可以借助各种函数、语法进行支持，所以在数据分析上有优势

  • 借用知乎上的一句总结 PostgreSQL 我觉得很合适：PostgreSQL 是一专多长的全栈数据库：在可观的规模内，都能做到一招鲜吃遍天

    • 所以，作为中小企业我觉得完全可以依赖 PostgreSQL，特别是求活阶段的企业

本文大纲

  • PostgreSQL 官网资料介绍

  • PostgreSQL 和 MySQL 语法差异点

  • PostgreSQL 在阿里云的情况

  • PostgreSQL 的 Docker 本地部署

  • PostgreSQL 基准测试

  • PostgreSQL 特有索引介绍

  • PostgreSQL json 和 jsonb 类型

  • PostgreSQL 执行过程分析

  • PostgreSQL 中文分词、全文检索、自定义中文词库

  • PostgreSQL 日常维护

  • PostgreSQL 插件推荐

  • PostgreSQL 衍生生态介绍

PostgreSQL 官网资料介绍

GUI Tool

  • 我不推荐官方的 pgAdmin，只是因为有更好的产品替换：Navicat Premium

  • Navicat Premium 支持跨平台，支持各类关系型、非关系型数据库，支持各类云平台

  • 界面体验比 DBeaver 更加友好，支持模型与 SQL 转换

  • 支持各种场景的导入导出

  • 官网介绍：www.navicat.com.cn/products/na…

  • 但是，pgAdmin 也有一些优点，比如有 Dashboard、Statistics 可以直观查看服务器的一些统计信息。

PostgreSQL 和 MySQL 语法差异点

PostgreSQL 与 MySQL 常用共同数据类型

  • 如果考虑同时兼容多个数据库，最好在设计之初就考虑差异点

  • 数值类型

    • smallint

    • integer

    • bigint

    • double

    • decimal

  • 字符类型

    • char(100)

    • varchar(100)

    • text

  • 日期/时间类型（我个人喜欢存储时间戳，所以用 bigint）

    • timestamp

    • date

    • time

  • JSON 类型（PostgreSQL 推荐使用 jsonb，因为它支持 GIN 索引）

    • json

PostgreSQL 与 MySQL 语法共同点

  • 插入数据

INSERT INTO sys_demo(id, demo_name, demo_num, demo_type_enum, ranking, description, state_enum, delete_enum, create_date, create_user_id, update_date, update_user_id, delete_date, delete_user_id)VALUES (111111111111111101, '演示次数01', '101', 1, 100, '演示描述01', 1, 1, 1574663780828, 111111111111111111, 1574663780828, 111111111111111111, NULL, NULL);

  • 插入外键

alter table sys_login_log add constraint FK_ID1 foreign key (user_id) REFERENCES sys_user (id);

  • 插入索引

CREATE INDEX rel_permission_role_index_most ON rel_permission_role (role_id, permission_id);CREATE UNIQUE INDEX unique_username ON sys_user (username);

PostgreSQL 与 MySQL 语法异同点

  • 符号上

    • PostgreSQL 不支持有 ` 号包裹表名、字段名等情况

  • 命名上

    • 创建索引的时候，MySQL 只要同表中索引名称唯一即可，PostgreSQL 必须整个库唯一

  • 创表：在 sculptor-boot-generator 项目上，有一个策略类：GeneratorPostgreSQL.java 在通过 MySQL 生成的时候可以直接生成 PostgreSQL 的创表语句，但是只针对我们自己的约定的字段类型

  • MySQL 创表

DROP TABLE IF EXISTS sys_user;CREATE TABLE sys_user(    id                   bigint      NOT NULL COMMENT '主键ID',    username             varchar(50) NOT NULL COMMENT '用户账号:oneParam',    real_name            varchar(50) NULL COMMENT '真实姓名',    user_password        varchar(50) NOT NULL COMMENT '登录密码',    password_salt        varchar(10) NOT NULL COMMENT '密码盐:放于密码后面',    user_email           varchar(50) NULL COMMENT '邮箱地址:oneParam',    telephone            varchar(20) NULL COMMENT '固话',    mobile_phone         varchar(20) NULL COMMENT '手机号:oneParam',    gender_enum          tinyint     NOT NULL DEFAULT '1' COMMENT '性别:[1=保密=PRIVACY, 2=男性=MALE, 3=女性=FEMALE, 4=中性=NEUTRAL]max=4',    register_type_enum   tinyint     NOT NULL DEFAULT '1' COMMENT '注册方式:[1=系统预置=SYSTEM_INIT, 2=后台管理系统新增=MANAGEMENT_ADD, 3=主动注册=REGISTER, 4=被邀请注册=INVITE]max=4',    register_origin_enum tinyint     NOT NULL DEFAULT '1' COMMENT '注册来源:[1=WEB方式=WEB, 2=安卓APP=ANDROID, 3=苹果APP=IOS, 4=H5=H5, 5=微信小程序=WECHAT_MINI_PROGRAM, 6=微信公众号=WECHAT_OFFICIAL_ACCOUNT]max=6',    state_enum           tinyint     NOT NULL DEFAULT '1' COMMENT '启用状态:[1=启用=ENABLE, 2=禁用=DISABLE]max=2',    delete_enum          tinyint     NOT NULL DEFAULT '1' COMMENT '删除状态:[1=未删除=NOT_DELETED, 2=已删除=DELETED]max=2',    create_date          bigint      NOT NULL COMMENT '创建时间',    create_user_id       bigint      NOT NULL COMMENT '创建人',    update_date          bigint      NOT NULL COMMENT '更新时间',    update_user_id       bigint      NOT NULL COMMENT '更新人',    delete_date          bigint      NULL COMMENT '删除时间',    delete_user_id       bigint      NULL COMMENT '删除人',    PRIMARY KEY (id)) COMMENT ='用户表';

  • PostgreSQL 创表

DROP TABLE IF EXISTS sys_user;CREATE TABLE sys_user(    id                   bigint      NOT NULL,    username             varchar(50) NOT NULL,    real_name            varchar(50) NULL,    user_password        varchar(50) NOT NULL,    password_salt        varchar(10) NOT NULL,    user_email           varchar(50) NULL,    telephone            varchar(20) NULL,    mobile_phone         varchar(20) NULL,    gender_enum          smallint    NOT NULL,    register_type_enum   smallint    NOT NULL,    register_origin_enum smallint    NOT NULL,    state_enum           smallint    NOT NULL,    delete_enum          smallint    NOT NULL,    create_date          bigint      NOT NULL,    create_user_id       bigint      NOT NULL,    update_date          bigint      NOT NULL,    update_user_id       bigint      NOT NULL,    delete_date          bigint      NULL,    delete_user_id       bigint      NULL,    PRIMARY KEY (id))WITH (    OIDS = FALSE);COMMENT ON TABLE sys_user IS '用户表';COMMENT ON COLUMN sys_user.id IS '主键ID';COMMENT ON COLUMN sys_user.username IS '用户账号';COMMENT ON COLUMN sys_user.real_name IS '真实姓名';省略其他

PostgreSQL 与 MySQL 事务差

  • PostgreSQL 除了常规的 DML 的 ROLLBACK 之外，还支持 DDL 的 ROLLBACK

  • 常规脚本操作，稳妥一点的流程基本是：BEGIN > COMMIT / ROLLBACK

  • PostgreSQL 额外支持 DDL 操作，比如 CREATE TABLE，TRUNCATE 等的 ROLLBACK

  • 测试：

select count(*) from sys_user;假设返回有 9 条数据BEGIN;TRUNCATE sys_user;select count(*) from sys_user;返回有 0 条数据ROLLBACK;select count(*) from sys_user;还是返回有 9 条数据

PostgreSQL 与 MySQL 在 Java 使用

  • 没有太大区别，ORM 框架都帮你解决了（假设使用的都是共有的类型）

  • PostgreSQL 配置信息

spring:  datasource:    driver-class-name: org.postgresql.Driver    url: jdbc:postgresql://postgresql.cdk8s.com:5432/cdk8s_sculptor_boot?useSSL=false    username: root    password: 123456

  • MySQL 配置信息

spring:  datasource:    driver-class-name: com.mysql.cj.jdbc.Driver    url: jdbc:mysql://mysql.cdk8s.com:3306/cdk8s_sculptor_boot?useSSL=false    username: root    password: 123456

PostgreSQL 在阿里云的情况

  • 阿里云 PostgreSQL 官网：www.aliyun.com/product/rds…

  • 阿里云 PostgreSQL 帮助文档：help.aliyun.com/document_de…

  • 阿里云 PostgreSQL 各版本对比：help.aliyun.com/document_de…

  • PostgreSQL进阶之路：RDS PG+HDB PG应用和案例集-云栖社区-阿里云：yq.aliyun.com/topic/118

  • 查看阿里云 PostgreSQL 的版本插件支持：www.alibabacloud.com/help/zh/doc…

    • 也可以连上数据库，然后输入：SELECT * FROM pg_available_extensions order by name

  • 2019-12 查询到的阿里云 PostgreSQL 12 支持的插件列表（目前 12 不支持高可用版本）

  • 补充：因为我的工单反馈后就阿里官方补充了文档，但是还是缺部分信息，我自己购买后做了测试，这里有完整的数据

（阿里云记录）

  • 2019-12 查询到的阿里云 PostgreSQL 11 支持的插件列表（目前 11 支持高可用版本）

（阿里云记录）
（阿里云记录）
（阿里云记录）

PostgreSQL 11 带 zhparser 插件的 Docker 部署（非官方）

  • hub.docker.com/r/davidlauh…

  • 字典文件在容器：/usr/share/postgresql/11/tsearch_data

  • 配置文件在容器：/var/lib/postgresql/data/postgresql.conf

mkdir -p /Users/youmeek/docker_data/pgsql11/data /Users/youmeek/docker_data/pgsql11/confsudo chmod -R 777 /Users/youmeek/docker_data/pgsql11先启动一个简单容器拿配置文件：docker run -d --name pgsql11 davidlauhn/postgres-11-with-zhparser复制配置文件出来，后面我就不说这个目录了，改为 /etc 目录下docker cp pgsql11:/var/lib/postgresql/data/postgresql.conf /Users/youmeek/docker_data/pgsql11/conf停止旧容器docker stop pgsql11 && docker rm pgsql11再次赋权sudo chmod -R 777 /Users/youmeek/docker_data/pgsql11启动新容器docker run \	-d \	--name pgsql11 \	-e POSTGRES_PASSWORD=123456 \	-v /Users/youmeek/docker_data/pgsql11/conf/postgresql.conf:/etc/postgresql/postgresql.conf \	-v /Users/youmeek/docker_data/pgsql11/data:/var/lib/postgresql/data \	-p 5432:5432 \	davidlauhn/postgres-11-with-zhparser \	-c 'config_file=/etc/postgresql/postgresql.conf'默认库名：postgres用户名：postgres

连接配置

  • 这个文件是专门配置登录认证信息：/var/lib/postgresql/data/pg_hba.conf

  • 默认 ip4 下只能是 127.0.0.1 才能登陆，我这里还补上了一个：192.168.0.101，大家可以自己扩展，一行一个配置

  • METHOD 列是配置密码连接加密方式，照抄即可

  • 修改完记得重启服务

  • 还有一个特殊的地方：

    • /etc/postgresql/postgresql.conf 里面有一个参数默认是：listen_addresses = '*'

    • 但是不排除有些版本是 localhost，那就需要改为星号，不然会连不上

    • 建议把连接改为大点：max_connections = 1000

# TYPE  DATABASE        USER            ADDRESS                 METHOD# "local" is for Unix domain socket connections onlylocal   all             all                                     trust# IPv4 local connections:host    all             all             127.0.0.1/32            trusthost    all             all             192.168.0.101/32        trust# IPv6 local connections:host    all             all             ::1/128                 trust# Allow replication connections from localhost, by a user with the# replication privilege.local   replication     all                                     trusthost    replication     all             127.0.0.1/32            trusthost    replication     all             ::1/128                 trusthost all all all md5

PostgreSQL 的数据库和模式说明（schema）

  • 这里把 schema 翻译成模式，我记得好像是阿里云还是哪里看到的，暂且就这样叫吧。

  • PostgreSQL 相对 MySQL 表特殊的地方在于其数据库下面还有一个东西叫做：schema 模式，默认每个库下都有一个 public 模式

  • PostgreSQL 支持多模式，但是不推荐使用其他。因为 public 是默认模式，所以在写查询 SQL 的时候可以省掉这个，这样方便以后系统做数据库迁移

常用管理 SQL

查看版本select version();查看软件配置select * from pg_config;查看设置参数select * from pg_settings;更多目录参数：https://www.postgresql.org/docs/11/catalogs.htmlhttp://postgres.cn/docs/10/catalogs.html创建用户CREATE USER root WITH PASSWORD '123456';创建库，并且赋权给 rootCREATE DATABASE cdk8s_sculptor_boot OWNER root;将 newDbName 数据库的所有权限都赋予 root：GRANT ALL PRIVILEGES ON DATABASE newDbName TO root;导出数据：docker exec -it 容器ID pg_dump -h localhost -U postgres 数据库名 > /data/backup.sql

pgbench 基准测试

  • PostgerSQL 自带基准测试工具：pgbench

  • 它可以自定义脚本文件进行测试，但是我们这里不复杂说，只讲内置的脚本进行测试

  • CentOS 7 独立安装：yum install -y postgresql-contrib

  • 查看版本：pgbench -V

初始化数据库

  • 初始化测试数据：pgbench -i -s 2 -F 100 -h 127.0.0.1 -p 5432 -U myUserName -d myDBName

    • -i 是初始化的意思

    • -s 2 表示初始化 2 个商户的数据。默认 1 个商户是 10W 个账号（pgbench_accounts），10 个出纳（pgbench_tellers），我们这里设置 2 就要对应的生成翻倍数据

    • -F 创建表时数据库的填充因子，取值 10 ~ 100，值越小 UPDATE 性能会有一定提升。

NOTICE:  table "pgbench_branches" does not exist, skippingNOTICE:  table "pgbench_tellers" does not exist, skippingNOTICE:  table "pgbench_accounts" does not exist, skippingNOTICE:  table "pgbench_history" does not exist, skippingcreating tables...10000 tuples done.20000 tuples done.30000 tuples done.40000 tuples done.50000 tuples done.60000 tuples done.70000 tuples done.80000 tuples done.90000 tuples done.100000 tuples done.set primary key...vacuum...done.

测试命令

  • 测试语句参数说明

-c 并发客户端数-j 工作线程数-M 提交查询到服务器使用的协议：simple|extended|prepared-n 运行测试时不执行清理-T 60 执行总时间，单位秒-r 输出每个SQL的平均每语句延迟

  • 只读测试，通过 -S 参数控制

nohup pgbench -c 5 -j 5 -M prepared -n -S -T 60 -h 127.0.0.1 -p 5432 -U myUserName -d myDBName > /opt/pgbenchtest-readonly.out 2>&1 &

  • 更新、查询、插入测试

nohup pgbench -c 5 -j 5 -M prepared -n -T 60 -h 127.0.0.1 -p 5432 -U myUserName -d myDBName > /opt/pgbenchtest-all.out 2>&1 &

  • 不执行更新测试，通过 -N 参数控制

nohup pgbench -c 5 -j 5 -M prepared -n -N -T 60 -h 127.0.0.1 -p 5432 -U myUserName -d myDBName > /opt/pgbenchtest-noupdate.out 2>&1 &

测试报告

pghost: 127.0.0.1 pgport: 5432 nclients: 5 duration: 10 dbName: myDBNametransaction type: TPC-B (sort of)scaling factor: 2query mode: preparednumber of clients: 5number of threads: 5duration: 60 snumber of transactions actually processed: 7237tps = 723.210820 (including connections establishing)tps = 723.959626 (excluding connections establishing)

  • 参数解释：

  • number of clients 是测试时指定的客户端数量

  • number of threads 是测试时指定的每个客户端的线程数

  • number of transactions actually processed 是测试结束时实际完成的事务数和计划完成的事务数，

  • tps = 723.210820 (including connections establishing)

    • 包含建立网络连接开销的 TPS 值

  • tps = 723.959626 (excluding connections establishing)

    • 不包含建立网络连接开销的 TPS 值

PostgreSQL 特有索引介绍

  • 索引类型：B-tree，Hash，GiST，SP-GiST，GIN

  • 一般普通表我们用 B-tree

  • 全文检索的用 GIN，又分为两种：

    • tsvector：表示一个被优化的，可以基于搜索的文档

    • tsquery：表示一个文本查询，支持布尔操作 & | !

    • 两者搭配使用：select * from tableName where to_tsvector('zhparser', fieldName) @@ to_tsquery('搜索内容1|其他内容2');

    • GIN 索引支持操作符：@>， ?，?&， ?|

PostgreSQL json 和 jsonb 类型

  • PostgreSQL 的 JSON 存储有两种字段类型：json、jsonb，两者的区别主要是对软件本身而言，一般是说：json 写入快，读取慢，jsonb 写入慢，读取快。因为 jsonb 是二进制存储，json 是文本存储。

  • jsonb 支持 GIN 索引类型，所以实际使用过程我们推荐 jsonb

  • 假设有一段 JSON：

{  "id": 11111111111111,  "username": "admin"}

  • 给字段创建 GIN 索引：CREATE INDEX idx_name ON tableName USING GIN(fieldName);

  • 查询操作：

select * from tableName where jsonFieldName @> '{"username":"admin"}'

PostgreSQL 执行过程分析

explain (analyze,verbose,costs,buffers,timing)select * from sys_user where to_tsvector('zhparser', real_name) @@ to_tsquery('李四|admin');

  • 输出内容格式如下：

Seq Scan on public.sys_user  (cost=0.00..66.38 rows=1 width=692) (actual time=0.837..1.622 rows=10 loops=1)  Output: id, username, real_name, user_password, password_salt, user_email, telephone, mobile_phone, ...省略... delete_date, delete_user_id  Filter: (to_tsvector('zhparser'::regconfig, (sys_user.real_name)::text) @@ to_tsquery('李四|admin'::text))  Rows Removed by Filter: 82  Buffers: shared hit=3Planning Time: 1.640 msExecution Time: 1.863 ms

  • 上面信息有一个内容叫做：扫描节点，常见的有：

    • Seq Scan，全表顺序扫描

    • Index Scan，基于索引扫描，但不只是返回索引列的值

    • IndexOnly Scan，基于索引扫描，并且只返回索引列的值，简称为覆盖索引

    • BitmapIndex Scan，利用Bitmap 结构扫描

    • BitmapHeap Scan，把BitmapIndex Scan 返回的Bitmap 结构转换为元组结构

    • Tid Scan，用于扫描一个元组TID 数组

    • Subquery Scan，扫描一个子查询

    • Function Scan，处理含有函数的扫描

    • TableFunc Scan，处理tablefunc 相关的扫描

    • Values Scan，用于扫描Values 链表的扫描

    • Cte Scan，用于扫描WITH 字句的结果集

    • NamedTuplestore Scan，用于某些命名的结果集的扫描

    • WorkTable Scan，用于扫描Recursive Union 的中间数据

    • Foreign Scan，用于外键扫描

    • Custom Scan，用于用户自定义的扫描

  • 代价估计信息：(cost=0.00..66.38 rows=1 width=692)

  • 真实执行信息：(actual time=0.837..1.622 rows=10 loops=1)

    • 点点符号之前是节点实际的启动时间，即找到符合该节点条件的第一个结果实际需要的时间

    • 后面是总执行时间

    • 单位都是 ms

    • actual time 执行时间

    • rows 是实际返回的行数值，不是 MySQL 的扫描行数

    • loops 指的是该节点实际的重启次数。如果一个计划节点在运行过程中，它的相关参数值（如绑定变量）发生了变化，就需要重新运行这个计划节点。

  • 除了以上 3 个核心，更多参数说明可以参看：mysql.taobao.org/monthly/201…

PostgreSQL 中文分词、全文检索、自定义中文词库

  • PostgreSQL 全文索引的实现要靠 gin 索引(通用倒排索引)。默认内置了英文、西班牙文等分词，但是没有中文。

  • 我们需要借助插件：zhparser

  • 阿里云帮助文档：help.aliyun.com/knowledge_d…

测试分词效果

数据库级别（不是软件用例级别）CREATE EXTENSION zhparser;我把配置叫做：zhparser，后面的都是采用这个，大家也可以自己改为自己喜欢的名字CREATE TEXT SEARCH CONFIGURATION zhparser (PARSER = zhparser);修改一个全文检索配置的定义映射了名词(n)，动词(v)，形容词(a)，成语(i),叹词(e)和习用语(l)6种。词典使用的是内置的simple词典，即仅做小写转换。这篇文章介绍写得很好：https://cloud.tencent.com/developer/article/1430039ALTER TEXT SEARCH CONFIGURATION zhparser ADD MAPPING FOR n,v,a,i,e,l WITH simple;select ts_debug('zhparser', '白垩纪是地球上海陆分布和生物界急剧变化、火山活动频繁的时代');SELECT * from ts_parse('zhparser', 'hello world! 2010年保障房建设在全国范围内获全面启动，从中央到地方纷纷加大 了保障房的建设和投入力度 。2011年，保障房进入了更大规模的建设阶段。住房城乡建设部党组书记、部长姜伟新去年底在全国住房城乡建设工作会议上表示，要继续推进保障性安居工程建设。');SELECT to_tsvector('zhparser','“今年保障房新开工数量虽然有所下调，但实际的年度在建规模以及竣工规模会超以往年份，相对应的对资金的需求也会创历>史纪录。”陈国强说。在他看来，与2011年相比，2012年的保障房建设在资金配套上的压力将更为严峻。');SELECT to_tsquery('zhparser', '保障房资金压力');SELECT plainto_tsquery('zhparser', '保障房资金压力');

扩展词库-方法1

  • 自定义词典的优先级高于自带的词典

  • 限制 zhparser 版本为 2.1 或以上

先查看没有扩展之前的效果：保障 房 资金 压力SELECT * FROM ts_parse('zhparser', '保障房资金压力'); 现在把 资金压力 插入到自定义表中insert into zhparser.zhprs_custom_word values('资金压力');同步数据select sync_zhprs_custom_word();断开当前连接，重新建立连接，再查询：资金压力 就为一个词了SELECT * FROM ts_parse('zhparser', '保障房资金压力');特别说明，阿里云的 zhparser 虽然显示是 1.0 版本，但是通过工单沟通后了解到他们改造了这个插件，所以版本没用。他们的自定义词库是这样用的：https://help.aliyun.com/knowledge_detail/44451.html表名和函数都进行了重命名：insert into pg_ts_custom_word values ('保障房资');select zhprs_sync_dict_xdb();SELECT to_tsquery('zhparser', '保障房资金压力');

扩展词库-方法2

自定义词典的文件必须放在 /usr/share/postgresql/11/tsearch_data 目录中zhparser根据文件扩展名确定词典的格式类型.txt扩展名表示词典是文本格式.xdb扩展名表示这个词典是xdb格式多个文件使用逗号分隔,词典的分词优先级由低到高,如：zhparser.extra_dicts = 'dict_extra.txt,mydict.xdb'该参数添加到：postgresql.conf 里面

  • dict_extra.txt 内容格式

; dict_extra.txt我是新增词     2.0再试一个       1.0       1.0    @; 以下词为删除项这是删除           1.0      1.0    !
测试：SELECT * FROM ts_parse('zhparser', '我是新增词，你可以再试一个');结果：114	我118	是118	新增110	词117	，114	你118	可以100	再118	试109	一个复制词典到容器：docker cp /Users/youmeek/dict_extra.txt pgsql11:/usr/share/postgresql/11/tsearch_data重启容器：docker restart pgsql11重新连接测试：SELECT * FROM ts_parse('zhparser', '我是新增词，你可以再试一个');结果：120	我是新增词117	，114	你118	可以120	再试一个

独立全文检索字段

  • 如果需要用到大量的全文检索，建议有额外的字段专门来存储对应字段的分词内容

create index idx_name on tableName using gin(to_tsvector('zhparser', fieldName));select * from tableName where to_tsvector('zhparser', fieldName) @@ '搜索内容';select * from tableName where to_tsvector('zhparser', fieldName) @@ '搜索内容11 & 搜索内容22';select * from tableName where to_tsvector('zhparser', fieldName) @@ '搜索内容11 | 搜索内容22';select * from tableName where to_tsvector('zhparser', fieldName) @@ to_tsquery('搜索内容1|其他内容2');添加一个分词字段，gin 索引必须是 tsvector 类型ALTER TABLE table ADD COLUMN tsv_columnName tsvector;将字段的分词向量更新到新字段中UPDATE table SET tsv_columnName = to_tsvector('zhparser', coalesce(fieldName,''));在新字段上创建索引CREATE INDEX idx_gin_zhcn ON table USING GIN(tsv_columnName);创建一个更新分词触发器create trigger trigger_name before insert or updateon table for each row execute procedure tsvector_update_trigger(tsv_columnName, 'zhparser', fieldname);

PostgreSQL 日常维护

开启 pg_stat_statements 模块

  • 该模块可用于收集数据库中的 SQL 运行信息，例如：SQL 的总执行时间、调用次数等，常用语监控 SQL 性能

修改配置文件：/etc/postgresql/postgresql.conf把原来默认值是：shared_preload_libraries = ''，改为shared_preload_libraries = 'pg_stat_statements'pg_stat_statements.max= 5000pg_stat_statements.track= allpg_stat_statements.track_utility = onpg_stat_statements.save = on重启软件用 postgres 超级用户登录：create extension pg_stat_statements;这时候会生成：1 个 pg_stat_statements 视图2 个函数：pg_stat_statements，pg_stat_statements_reset()统计调用次数最多select * from pg_stat_statements order by calls desc limit 10统计平均用时最多select * from pg_stat_statements order by mean_time desc limit 10清空统计数据select pg_stat_statements_reset();

  • 核心字段，查询的时候可以根据下面字段来做排序，筛选出对应的一些 SQL 记录

calls：调用次数query：执行 sql 内容total_time：执行的总时间，单位 msmin_time：执行的最小时间，单位 msmax_time：执行的最大时间，单位 msmean_time：执行的平均时间，单位 msrows：影响的数据行树shared_blks_hit：SQL 命中的共享内存数据块数量shared_blks_read：SQL 读取的共享内存数据块数量shared_blks_written：SQL 写入的共享内存数据块数量shared_blks_dirtied：SQL 产生的共享内存数据脏块数量

  • 如果是本地部署，需要通过 WEB UI 界面查看以上信息，可以借助它：pg_web_stats

  • pg_web_stats：github.com/kirs/pg_web…

日志检查

  • 设置日志输出格式为 csvlog

  • 每天存一份，一份最大 100M

  • 修改 postgresql.conf 文件，添加如下内容（默认配置参数是没有打开注释的）

  • 重启服务器，在 data 目录下可以看到一个新目录：pg_log

log_destination = 'csvlog'              # Valid values are combinations oflogging_collector = on                  # Enable capturing of stderr and csvloglog_directory = 'pg_log'                # directory where log files are written,log_filename = 'postgresql-%d_%H%M%S.log'       # log file name pattern,log_file_mode = 0600                    # creation mode for log files,log_truncate_on_rotation = on           # If on, an existing log file with thelog_rotation_age = 1d                   # Automatic rotation of logfiles willlog_rotation_size = 100MB               # Automatic rotation of logfiles will

  • csv 可以用 excel 直接打开

  • 如果数据很多，可以看下列头有哪些，然后创建一个表，把 csv 数据导入到表中，然后进行 sql 筛选

  • log 中如果有 error code 可以在这里找到对应说明：www.postgresql.org/docs/11/err…

连接数检查

  • 查看当前连接数的具体信息

SELECT * from  pg_stat_activity;

容量检查

  • 通过如下 SQL 可以看出当前库的存储大小

  • 如果定期查询，可以归纳出业务发展情况变化，是否有突发性增长，大表等等

  • pg_size_pretty 表示用人类可读的单位显示出来

select datname,pg_size_pretty(pg_database_size(oid)) from pg_database;

  • 查出所有表按大小排序并分离 data 与 index 存储大小

SELECT    table_name,    pg_size_pretty(table_size) AS table_size,    pg_size_pretty(indexes_size) AS indexes_size,    pg_size_pretty(total_size) AS total_sizeFROM (    SELECT        table_name,        pg_table_size(table_name) AS table_size,        pg_indexes_size(table_name) AS indexes_size,        pg_total_relation_size(table_name) AS total_size    FROM (        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name        FROM information_schema.tables    ) AS all_tables    ORDER BY total_size DESC) AS pretty_sizeswhere table_name like '"public"."%'

查看提交与回滚数量

  • 如果回滚次数 / 提交次数过大，就说明程序肯定哪里有问题，造成经常回滚

select datname,xact_commit,xact_rollback from pg_stat_database;

定期 vacuum

  • 新版本的 PostgreSQL 中有自动 vacuum 策略，但是如果是大批量的数据IO可能会导致自动执行很慢，需要配合手动执行以及自己的脚本来清理数据库

  • 打开配置文件，找到自动策略开启的配置：autovacuum = on

  • 手动执行，需要用最高权限的 postgres 用户，建议夜间业务低峰时处理

vacuumdb -d dbName -f -z -v

PostgreSQL 插件推荐

PostGIS 插件

  • 支持空间对象、空间索引、空间操作函数和空间操作符

  • 常见业务场景：

    • 圈地：以当前或者指定中心点，找出周边 1 公里范围内的商家或人

    • 围栏：检测指定点落在哪个地理围栏中，比如共享单车是否停在禁停区，无人机是否在禁飞区

  • 如果需要用到空间数据的话，这个插件就是最好的支持

  • 目前阿里云的 PostgreSQL 11 支持，最新的 12 还没有该插件

  • 推荐空间数据类型统一使用 Geometry 类型

  • PostgreSQL 也内置了几个几何类型，但是跟 PostGIS 的不是同机制，推荐使用以 ST 开头的对象（ST = Spatial Type）

pg_pathman

  • 官网：github.com/postgrespro…

  • 看德哥文章说比内置分区性能来的好，我没试过，大家可以自己试试。阿里云的 PostgreSQL 11 已经内置该插件

pgRouting 插件

  • 基于 PostgreSQL 和 PostGIS 提供了地理位置引路系统

  • 核心功能：

    • 所有最短路径组合（Johnson 算法）

    • 所有最短路径组合（Floyd-Warshall 算法）

    • 最短 A* 路径

    • 双向 Dijkstra 最短路径

    • 双向 A* 最短路径

    • Dijkstra 最短路径

    • 行车距离

    • K-最短路径，多候选路径

    • K-Dijkstra，一对多最短路径

    • 旅行销售人员（Traveling Sales Person）

    • TRSP（Turn Restriction Shortest Path）

TimescaleDB 插件

PASE 插件

其他插件

PostgreSQL 衍生生态介绍

官网之外参考资料

我们会努力的~

（长按识别扫描二维码）

专注互联网中小企业研发方案

免责申明：

本微信部分图片、音频资源来源于互联网及其他公众平台，版权归原作者所有。

如涉及侵权请予以告知，我们会在 24 小时内删除相关内容，感谢！

avatar
文章
阅读
粉丝