- 博客(145)
- 收藏
- 关注
RSS订阅原创 【PostgreSQL17新特性之-explain命令新增选项】
PostgreSQL17-beta1版本近日发布了，新版本里，explain命令新增了两个选项，分别是MEMORY 和SUMMARY。
2024-05-31 12:37:18
809
转载 【为什么我在 POSTGRESQL 中 COMMIT 很慢？】
原文标题：WHY DO I HAVE A SLOW COMMIT IN POSTGRESQL?原文作者：Laurenz Albe原文地址：https://www.cybertec-postgresql.com/en/why-do-i-have-a-slow-commit-in-postgresql/翻译：阎书利有时，我们的一位客户会查看数据库中最耗时的语句（使用pg_stat_statements或pgBadger），并发现COMMIT排名靠前。
2024-05-31 12:35:57
76
原创 【PostgreSQL17新特性之-COPY FROM的ON_ERROR容错选项】
经过测试，对于copy from的不同错误类型，就算选择了同样的ON_ERROR ignore选项，行为也可能不一样。
2024-05-30 15:23:34
854
原创 【PostgreSQL17新特性之-冗余IS [NOT] NULL限定符的处理优化】
David Rowley的相关邮件里也强调了：当我们优化Min/Max聚合时，规划器添加的IS NOT NULL qual会使重写的计划忽略NULL，这可能会导致索引选择不佳的问题。在PostgreSQL16版本及以前,就算原本的列上有非空索引，查询条件带有NULL和NOT NULL，也感知不到，依然会去扫描表去评估，增加额外的计划和执行的开销。在执行一个带有IS NOT NULL或者NOT NULL的SQL的时候，通常会对表的每一行，都会进行检查以确保列为空/不为空，这是符合常理的。
2024-05-30 15:21:59
418
原创 【PostgreSQL17新特性之-事务级别超时参数transaction_timeout】
PostgreSQL数据库里有多个和会话相关的参数，PostgreSQL17-beta1版本新增了一个transaction_timeout参数，来限制事务的持续时间。当前的一些和会话相关的超时参数如下。
2024-05-29 18:19:11
863
原创 【PostgreSQL17新特性之-新增系统视图】
PostgreSQL-17-beta1版本目前已经发布了，每个版本随着功能的增加和一些相关优化，会对部分视图进行调整，以及增加新的视图。目前的PostgreSQL-17-beta1新版本新增了两个视图，分别为pg_wait_events和pg_stat_checkpointer。pg_stat_checkpointer视图包含检查点信息，pg_wait_events 主要包含等待事件的描述。
2024-05-29 18:17:36
1197
原创 【PostgreSQL支持中文的全文检索插件(zhparser)】
PostgreSQL本身是支持全文检索的，提供两个数据类型（tsvector,tsquery），并且通过动态检索自然语言文档的集合，定位到最匹配的查询结果。而一个tsvector的值是唯一分词的分类列表，把一话一句词格式化为不同的词条，在进行分词处理的时候tsvector会自动去掉分词中重复的词条，按照一定的顺序装入。要支持中文的全文检索需要额外的中文分词插件，zhparser就是其中一种，是基于Simple Chinese Word Segmentation(SCWS)中文分词库实现的一个PG扩展。
2024-05-14 17:31:55
1801
原创 【PostgreSQL里的子查询解析】
子查询是一种嵌套在其他SQL查询中的查询方式，也可以称作内查询或嵌套查询。当一个查询是另一个查询的条件时，就称之为子查询。子查询的语法格式与普通查询相同，但其在查询过程中起着临时结果集的作用，为主查询提供所需数据或对检索数据进行进一步的限制。子查询最重要的方面是有所谓的相关子查询和不相关子查询。
2024-05-14 13:36:57
931
原创 【PostgreSQL里insert on conflict do操作时的冲突报错分析】
最近在巡检PostgreSQL的数据库的时候，发现部分数据库里存在大量的如下报错。
2024-04-16 15:25:14
1261
1
翻译 【[译]简述PostgreSQL角色和权限】
PostgreSQL中的PUBLIC角色是一个特殊的角色，它为系统中的每个角色授予特权。它就像一个默认组，指示所有角色，甚至是后来创建的角色。它确保所有角色都有权访问某些特权。默认情况下，所有其他角色都会自动授予PUBLIC成员资格，并继承其特权。在PostgreSQL 15之前，每个用户都对公共模式拥有完全的权限（创建/使用），所有用户共享该权限。然而，从PostgreSQL 15开始，用户不再能够在公共模式中创建任何对象。此更改适用于代表所有用户的公共角色。
2024-04-11 16:14:26
143
原创 【PostgreSQL autovacuum清理死元组的相关优化】
如果有未提交的预定义语句，它们会阻止移除死元组。需要使用 COMMIT PREPARED或ROLLBACK PREPARED提交或回滚这些语句。//查询未提交的预定义语句的SQL如下。
2024-04-09 15:14:55
958
原创 【PostgreSQL的指标采集工具--pgmetrics】
pgmetrics是用go语言写的一款PostgreSQL的健康监控指标采集软件。可以连接到数据库，获取当前数据库的相关信息，如果连接的是本地数据库，则同时会采集服务器的状态信息。可以把采集的结果以json或者text或者csv的形式存储。
2024-03-20 10:06:01
354
原创 【PostgreSQL里pg_filenode.map文件的意义】
通常情况下，PostgreSQL中每张表在磁盘上都有与之相关的文件，而这些文件的名字便是relfilenode，我们可以通过pg_class的relfilenode字段去查询。对于一张普通表，其relfilenode和oid默认是一样的。系统表有的也是。但当我们对该表进行了例如vacuum full、truncate之类的操作，表重建后，那么relfilenode便会发生变化。
2024-03-19 09:25:43
617
原创 【PostgreSQL的变长字段数据超过多少会写入到TOSAST表】
通常PostgreSQL里如果一个元祖的变长字段的数据量，超过2KB，则PostgreSQL会尝试进行压缩，把元组控制在2KB之内，如果不能满足2KB之内的需求，就需要独立的toast表来存储了。
2024-03-11 08:55:52
512
原创 【PostgreSQL实现psql连接时候提示用户的密码有效时间】
我这里写了两种，一种是只显示查看自己登陆的用户的有效期剩余时间，一种是一并显示数据库里所有的设置了有效期的用户的剩余可使用天数，以及打印出剩余时间小于七天甚至已经过期的用户。首先是涉及到的判断是否需要修改密码和有效期的查询SQL，这里根据距离过期前7天为标准作为是否需要修改密码的提示。2.一并显示数据库里所有的设置了有效期的用户的剩余可使用天数，以及打印出剩余时间小于七天甚至已经过期的用户。函数需要在所有的数据库中创建，否则psql登陆会有如下的warning，但不影响使用。更改完之后重启数据库。
2024-02-21 09:09:26
808
原创 PostgreSQL限制密码的有效期(每次增加180天)
创建用户时，可以使用如下语句，自动在当前时间的基础上增加180天，作为这个用户密码的有效时间。等到密码到期后或者临近的时候，可以使用SQL修改用户密码，并且重新定义密码的有效期。（也可以直接指定时间，下面方法是为了不用自己算增加180天的天数）把生成的SQL粘贴执行。可以通过pg_roles这个视图查看用户的密码有效时间。
2024-02-20 09:01:23
1181
原创 PostgreSQL使用session_exec和file_fdw实现失败次数锁定用户策略
缺陷：实测发现锁用户后，进去解锁特定用户。只能允许一次登陆，应该再次登陆的时候，触发函数，把之前的日志里的错误登陆的信息也计算到登录次数里了。需要删除对应的pg_log，才能使foreign table信息清理掉，来重制该用户的密码错误记录。解锁同时需要删除pg_log下csv文件里的对应有改用户登陆失败的日志记录，重制密码登录错误的记录，否则可能会重复计算之前的错误登陆记录。用户是否被锁/是否允许登陆，可以查看pg_roles系统视图里的rolcanlogin字段。使用如下语句，可以查询登陆失败的记录。
2024-02-19 16:46:10
966
1
原创 PostgreSQL里实现计算多个数字的排列组合
在进行排列组合的时候，每一次需要知道是否有重复的值，并过滤出已经排列过的值。这个可以创建支持可变参数的函数来实现。下边的函数用到了聚合判断，并且可变参数使用variadic标记的数组。然后下边使用创建的判断是否有重复数据的函数以及使用SQL实现获取所有的排列组合。根据4的阶乘可以得到，总共应该有24种，阶乘可以使用factorial函数。然后是如何使用这个函数结合查询语句对一组数据进行排列组合。例如，一张表里有1234这四个值。想使用四个值做排列组合。先创建一个测试的表，里边存放要进行排列组合的数据。
2024-02-19 09:01:15
643
原创 【PostgreSQL灵活使用psql执行SQL的一些方式】
可以不进入数据库，在命令行，使用psql 的-c选项跟上需要执行的SQL。来获取SQL的执行结果可以使用psql的选项对查询结果进行一些处理。
2024-02-01 16:44:40
1516
原创 【openGauss/MogDB编写自定义extension】
例如openGauss的函数不支持PARALLEL语法等，有些opengauss不支持的不可以使用，这个需要注意。
2024-01-24 09:58:50
977
原创 【PostgreSQL采用MD5密码认证时密码和pg_authid里rolpassword的关系】
通过上述测试可以发现，pg_authid系统表里的rolpassword字段的MD5码 ="md5"字符串+ md5(pwd+username)），MD5在理论上是几乎无法破解的，虽然不能反向解析，但是如果获取到了这个MD5处理后的字符串，可以通过撞库方式获取MD5算法处理前的字符，用预先计算好的MD5散列值与已知的散列值进行比较，以查找匹配的明文，从而获取到用户名和密码。但是这种方法需要预先有一定的可能的明文和计算MD5散列值的能力。
2024-01-23 13:54:44
1475
原创 【openGauss/MogDB使用mog_xlogdump解析 xlog文件内容】
REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING} 调整逻辑复制时写入WAL日志中的信息量，该选项仅在wal_level配置为logical时才有效。当原数据表发生更新时，默认的逻辑复制流只包含主键的历史记录，如果需要输出所需字段更新或删除的历史记录，可修改本参数。//或者不指定事物id，只指定表对应的filenode。//指定事物id，表应的filenode。
2024-01-22 12:09:29
530
原创 [mysqldump备份单库、部分库、全库、及排除部分库]
本文针对MySQL的mysqldump工具在使用时，对于某些场景需要备份多个数据库或者排除部分数据库的场景进行举例。主要针对于备份的库是否符合预期，其余一些备份参数可根据使用场景增加调整。排除掉information_schema、performance_schema、sys、mysql、database1几个库，只备份其余的库。4.mysqldump排除部分库备份。2.mysqldump备份部分库。1.mysqldump备份单库。3.mysqldump备份全库。我的环境的mysql的版本为。
2024-01-17 12:46:21
678
原创 【pgBackRest备份工具使用】
pgBackRest是PostgreSQL数据库的一个开源备份工具，支持全量、增量和差异备份。这一点优于PG原生的备份工具pg_basebackup，因为pg_basebackup不支持增量备份，并且支持备份完整性检查等等。pgBackRest v2.49是当前的稳定版本。项目主页：https://pgbackrest.org/Github链接：https://github.com/pgbackrest/pgbackrest优点：支持本地或远程操作， 但是远程操作需要配置SSH。
2024-01-16 14:30:08
1439
1
原创 【PostgreSQL在线创建索引(CIC)功能的锁分析以及使用注意】
前一篇文章提到了普通创建索引会阻塞DML操作而PostgreSQL里可以使用create index concurrently 在线创建索引(CIC)功能，降低创建索引在表上申请的锁的级别，ShareUpdateExclusiveLock级别的锁和RowExclusiveLock不冲突，不会阻塞表上的DML操作。
2024-01-08 09:53:20
1408
原创 【PostgreSQL创建索引的锁分析和使用注意】
PostgreSQL里有很多可以加锁的对象：表、单个页、单个元组、事务ID（包括虚拟和永久ID）和普通数据库对象等等，常规锁的locktype主要有以下几种。有时候通过pg_locks查询的时候，根据pid会查到许多的锁，但是这些锁并不一定都是加在表上的，根据locktype以及relation过滤出不同对象上的锁。/**/
2024-01-08 09:49:47
1254
原创 【PostgreSQL的WAL日志解析工具pg_waldump/pg_xlogdump】
PostgreSQL利用日志文件来防止断电之类的故障导致的数据丢失，任何试图修改数据库的操作都会写一份日志记录到磁盘，这个日志称为XLOG/WAL。在数据库定位问题时，就可以使用pg_xlogdump/pg_waldump来解析XLOG/WAL日志，包括日志类型、对应的事务号、修改的文件等等。pg_xlogdump/pg_waldump是查不出数据的，要查出数据，首先要有数据结构，然后要有数据。数据结构是需要查数据库的元数据才能查到，pg_xlogdump没办法做到。
2024-01-04 17:30:30
1973
1
原创 【PostgreSQL查看SQL执行过程主机的资源使用情况】
在分析SQL的时候，可以临时设置client_min_messages=log结合如下几个参数分别查看SQL在explain, parser, execute 过程系统资源使用分析。生产环境不建议开启，建议临时分析会话级别开启，用于定位问题。
2024-01-04 17:29:09
701
原创 【BCC动态跟踪PostgreSQL】
BPF Compiler Collection (BCC)是基于eBPF的Linux内核分析、跟踪、网络监控工具。其源码存放于想要监控PostgreSQL数据库的相关SQL需要在编译PostgreSQL的时候开启dtrace。下文主要介绍几个和PostgreSQL相关的工具,其他工具可根据需求自行了解。
2024-01-02 17:57:20
608
原创 【PostgreSQL如何查看page、index的详细信息】
为了更好地查看page页的内容，需要安装两个插件二、相关函数举例1.查看表占用多少个page2.查看一个表的tuple信息3.查看一个表上的索引信息4.查看表t的第0号页的页头信息5.查看表t的第0号页中行数据相关信息6.查看一个B树索引元页的信息7.查看B-树索引单一页面的摘要信息8.查看关于B-树索引页面上所有项的详细信息9.查看database 所有的含有需要toast子段的table
2023-12-27 16:35:54
785
原创 【流复制环境PostgreSQL-14.1到PostgreSQL-16.1大版本升级】
PostgreSQL大版本会定期添加新特性，这些新特性通常会改变系统表的布局，但内部数据存储格式很少改变。pg_upgrade通过创建新的系统表和重用旧的用户数据文件来执行快速升级。pg_upgrade升级主要有三种用法：1、使用pg_upgrade拷贝升级。2、使用pg_upgrade链接升级（带有- -link选项），- -link较快，但是启动新版本后修改了数据文件，再启动旧版本可能数据损坏，回滚较麻烦，所以尽量避免使用link参数进行升级。
2023-12-26 10:25:55
1966
原创 【PostgreSQL表增加/删除字段是否会重写表】
PostgreSQL 10 版本前表新增不带默认值的列不需要重写表，只需要更新数据字典，因此能瞬间执行。如果不带默认值，则会填充空值。如果新增的字段带默认值，则需要重写表。表越大，执行时间越长。重写表会对表加Access Exclusive锁，期间整张表是无法访问的。如果是生产环境下给大表添加带 Default 值的字段可能影响比较大，通常先添加不带 Default值的字段，然后写函数批量刷新新增字段的默认值。也可以在业务量较低的时候或者申请割接窗口停业务一次性完成带DEFAUL值字段的新增。
2023-12-22 22:18:36
2067
原创 openGauss/MogDB列存表vacuum DELTAMERGE过程申请的锁
vacuum DELTAMERGE过程，列存表原表，delta表均获取了8级锁，AccessExclusive Lock，除此之外，还有一个relfilenode对应的表，其实是delta表的原表，在vacuum DELTAMERGE过程中，delta表发生了重建，对应的表文件发生了变化。所以我想到了直接从锁入手，AccessExclusiveLock是8级锁，主库申请8级锁时，会将其记录到WAL日志里。
2023-12-19 14:29:35
444
原创 【openGauss/MogDB列存表的delta表测试】
列存储格式是OLAP类数据库系统最常用的数据格式，适合复杂查询、范围统计类查询的在线分析型处理系统。cstore列存储的主体数据文件以CU为I/O单元，只支持追加写操作，因此cstore只有读共享缓冲区。CU间和CU内的可见性由对应的CUDESE表（astore表）决定，因此其可见性和并发控制原理与行存储astore基本相同。
2023-12-19 14:25:34
1056
原创 【openGauss/MogDB的TPCH测试】
TPC-H是一个决策支持基准（Decision Support Benchmark），它由一套面向业务的特别查询和并发数据修改组成。查询和填充数据库的数据具有广泛的行业相关性。这个基准测试演示了检查大量数据、执行高度复杂的查询并回答关键业务问题的决策支持系统。TPC-H报告的性能指标称为TPC-H每小时复合查询性能指标(QphH@Size)，反映了系统处理查询能力的多个方面。这些方面包括执行查询时所选择的数据库大小，由单个流提交查询时的查询处理能力，以及由多个并发用户提交查询时的查询吞吐量。
2023-11-20 09:52:23
326
原创 PostgreSQL error错误码
错误代码情况名称01000warning0100C1008100310071006100401P012000no_data2001300080008003800680018004800708P0190000A0000B0000F0000F0010L0000LP010P0000Z0000Z0022000021000220002202E220212200822012220052200B22022220152201E。
2023-11-08 03:44:28
434
原创 【MogDB/openGauss误删未归档的xlog日志如何解决】
在使用MogDB/openGauss数据库的过程中，有时候大量业务，或者导数据会导致pg_xlog下的日志数量持续增长，此时如果xlog的产生频率太快，而来不及自动清理，极有可能造成pg_xlog目录的打满。如果对数据库的xlog不太了解的时候，可能造成误删未归档的xlog日志，或者更严重地，把对应操作还未写入数据文件的xlog也删除了。
2023-11-07 01:04:31
626
原创 【MogDB/openGauss的三种函数稳定性关键字】
在ORACLE里，function有着一个DETERMINISTIC参数，它表示一个函数在输入不变的情况下输出是否确定，只要输入的参数一样，返回的结果一定一样的，以保证函数对于任何输入总是完全相同的方式处理参数，oracle的内置函数 UPPER,TRUNC 等都是 deterministic 函数。因此，在事务中参数一致则结果一致，且只关注函数的最终结果可以标记为immutable，因为它调用函数的次数少，固化了函数的结果，执行计划的代价较小。任何具有副作用的函数的都不稳定的，即使其结果是可预测的。
2023-11-07 01:03:31
175
pgxmaster-sqlchecker
2023-04-12
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人