自定义博客皮肤VIP专享

*博客头图:

格式为PNG、JPG,宽度*高度大于1920*100像素,不超过2MB,主视觉建议放在右侧,请参照线上博客头图

请上传大于1920*100像素的图片!

博客底图:

图片格式为PNG、JPG,不超过1MB,可上下左右平铺至整个背景

栏目图:

图片格式为PNG、JPG,图片宽度*高度为300*38像素,不超过0.5MB

主标题颜色:

RGB颜色,例如:#AFAFAF

Hover:

RGB颜色,例如:#AFAFAF

副标题颜色:

RGB颜色,例如:#AFAFAF

自定义博客皮肤

-+
  • 博客(137)
  • 收藏
  • 关注

原创 【PostgreSQL里insert on conflict do操作时的冲突报错分析】

最近在巡检PostgreSQL的数据库的时候,发现部分数据库里存在大量的如下报错。

2024-04-16 15:25:14 358 1

翻译 【[译]简述PostgreSQL角色和权限】

PostgreSQL中的PUBLIC角色是一个特殊的角色,它为系统中的每个角色授予特权。它就像一个默认组,指示所有角色,甚至是后来创建的角色。它确保所有角色都有权访问某些特权。默认情况下,所有其他角色都会自动授予PUBLIC成员资格,并继承其特权。在PostgreSQL 15之前,每个用户都对公共模式拥有完全的权限(创建/使用),所有用户共享该权限。然而,从PostgreSQL 15开始,用户不再能够在公共模式中创建任何对象。此更改适用于代表所有用户的公共角色。

2024-04-11 16:14:26 16

原创 【PostgreSQL autovacuum清理死元组的相关优化】

如果有未提交的预定义语句,它们会阻止移除死元组。需要使用 COMMIT PREPARED或ROLLBACK PREPARED提交或回滚这些语句。//查询未提交的预定义语句的SQL如下。

2024-04-09 15:14:55 747

原创 【PostgreSQL的指标采集工具--pgmetrics】

pgmetrics是用go语言写的一款PostgreSQL的健康监控指标采集软件。可以连接到数据库,获取当前数据库的相关信息,如果连接的是本地数据库,则同时会采集服务器的状态信息。可以把采集的结果以json或者text或者csv的形式存储。

2024-03-20 10:06:01 273

原创 【PostgreSQL里pg_filenode.map文件的意义】

通常情况下,PostgreSQL中每张表在磁盘上都有与之相关的文件,而这些文件的名字便是relfilenode,我们可以通过pg_class的relfilenode字段去查询。对于一张普通表,其relfilenode和oid默认是一样的。系统表有的也是。但当我们对该表进行了例如vacuum full、truncate之类的操作,表重建后,那么relfilenode便会发生变化。

2024-03-19 09:25:43 470

原创 【PostgreSQL的变长字段数据超过多少会写入到TOSAST表】

通常PostgreSQL里如果一个元祖的变长字段的数据量,超过2KB,则PostgreSQL会尝试进行压缩,把元组控制在2KB之内,如果不能满足2KB之内的需求,就需要独立的toast表来存储了。

2024-03-11 08:55:52 479

原创 【PostgreSQL实现psql连接时候提示用户的密码有效时间】

我这里写了两种,一种是只显示查看自己登陆的用户的有效期剩余时间,一种是一并显示数据库里所有的设置了有效期的用户的剩余可使用天数,以及打印出剩余时间小于七天甚至已经过期的用户。首先是涉及到的判断是否需要修改密码和有效期的查询SQL,这里根据距离过期前7天为标准作为是否需要修改密码的提示。2.一并显示数据库里所有的设置了有效期的用户的剩余可使用天数,以及打印出剩余时间小于七天甚至已经过期的用户。函数需要在所有的数据库中创建,否则psql登陆会有如下的warning,但不影响使用。更改完之后重启数据库。

2024-02-21 09:09:26 703

原创 PostgreSQL限制密码的有效期(每次增加180天)

创建用户时,可以使用如下语句,自动在当前时间的基础上增加180天,作为这个用户密码的有效时间。等到密码到期后或者临近的时候,可以使用SQL修改用户密码,并且重新定义密码的有效期。(也可以直接指定时间,下面方法是为了不用自己算增加180天的天数)把生成的SQL粘贴执行。可以通过pg_roles这个视图查看用户的密码有效时间。

2024-02-20 09:01:23 693

原创 PostgreSQL使用session_exec和file_fdw实现失败次数锁定用户策略

缺陷:实测发现锁用户后,进去解锁特定用户。只能允许一次登陆,应该再次登陆的时候,触发函数,把之前的日志里的错误登陆的信息也计算到登录次数里了。需要删除对应的pg_log,才能使foreign table信息清理掉,来重制该用户的密码错误记录。解锁同时需要删除pg_log下csv文件里的对应有改用户登陆失败的日志记录,重制密码登录错误的记录,否则可能会重复计算之前的错误登陆记录。用户是否被锁/是否允许登陆,可以查看pg_roles系统视图里的rolcanlogin字段。使用如下语句,可以查询登陆失败的记录。

2024-02-19 16:46:10 619

原创 PostgreSQL里实现计算多个数字的排列组合

在进行排列组合的时候,每一次需要知道是否有重复的值,并过滤出已经排列过的值。这个可以创建支持可变参数的函数来实现。下边的函数用到了聚合判断,并且可变参数使用variadic标记的数组。然后下边使用创建的判断是否有重复数据的函数以及使用SQL实现获取所有的排列组合。根据4的阶乘可以得到,总共应该有24种,阶乘可以使用factorial函数。然后是如何使用这个函数结合查询语句对一组数据进行排列组合。例如,一张表里有1234这四个值。想使用四个值做排列组合。先创建一个测试的表,里边存放要进行排列组合的数据。

2024-02-19 09:01:15 578

原创 【PostgreSQL灵活使用psql执行SQL的一些方式】

可以不进入数据库,在命令行,使用psql 的-c选项跟上需要执行的SQL。来获取SQL的执行结果可以使用psql的选项对查询结果进行一些处理。

2024-02-01 16:44:40 698

原创 【灵活设置PostgreSQL的PROMPT1客户端提示符】

psql。

2024-02-01 16:38:53 1260

原创 【openGauss/MogDB编写自定义extension】

例如openGauss的函数不支持PARALLEL语法等,有些opengauss不支持的不可以使用,这个需要注意。

2024-01-24 09:58:50 917

原创 【PostgreSQL采用MD5密码认证时密码和pg_authid里rolpassword的关系】

通过上述测试可以发现,pg_authid系统表里的rolpassword字段的MD5码 ="md5"字符串+ md5(pwd+username)),MD5在理论上是几乎无法破解的,虽然不能反向解析,但是如果获取到了这个MD5处理后的字符串,可以通过撞库方式获取MD5算法处理前的字符,用预先计算好的MD5散列值与已知的散列值进行比较,以查找匹配的明文,从而获取到用户名和密码。但是这种方法需要预先有一定的可能的明文和计算MD5散列值的能力。

2024-01-23 13:54:44 1156

原创 【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 412

原创 [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 507

原创 【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 1112 1

原创 【PostgreSQL在线创建索引(CIC)功能的锁分析以及使用注意】

前一篇文章提到了普通创建索引会阻塞DML操作而PostgreSQL里可以使用create index concurrently 在线创建索引(CIC)功能,降低创建索引在表上申请的锁的级别,ShareUpdateExclusiveLock级别的锁和RowExclusiveLock不冲突,不会阻塞表上的DML操作。

2024-01-08 09:53:20 1218

原创 【PostgreSQL创建索引的锁分析和使用注意】

PostgreSQL里有很多可以加锁的对象:表、单个页、单个元组、事务ID(包括虚拟和永久ID)和普通数据库对象等等,常规锁的locktype主要有以下几种。有时候通过pg_locks查询的时候,根据pid会查到许多的锁,但是这些锁并不一定都是加在表上的,根据locktype以及relation过滤出不同对象上的锁。/**/

2024-01-08 09:49:47 1141

原创 【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 1360 1

原创 【PostgreSQL查看SQL执行过程主机的资源使用情况】

在分析SQL的时候,可以临时设置client_min_messages=log结合如下几个参数分别查看SQL在explain, parser, execute 过程系统资源使用分析。生产环境不建议开启,建议临时分析会话级别开启,用于定位问题。

2024-01-04 17:29:09 619

原创 【BCC动态跟踪PostgreSQL】

BPF Compiler Collection (BCC)是基于eBPF的Linux内核分析、跟踪、网络监控工具。其源码存放于想要监控PostgreSQL数据库的相关SQL需要在编译PostgreSQL的时候开启dtrace。下文主要介绍几个和PostgreSQL相关的工具,其他工具可根据需求自行了解。

2024-01-02 17:57:20 564

原创 【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 642

原创 【流复制环境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 1634

原创 【PostgreSQL表增加/删除字段是否会重写表】

PostgreSQL 10 版本前表新增不带默认值的列不需要重写表,只需要更新数据字典,因此能瞬间执行。如果不带默认值,则会填充空值。如果新增的字段带默认值,则需要重写表。表越大,执行时间越长。重写表会对表加Access Exclusive锁,期间整张表是无法访问的。如果是生产环境下给大表添加带 Default 值的字段可能影响比较大,通常先添加不带 Default值的字段,然后写函数批量刷新新增字段的默认值。也可以在业务量较低的时候或者申请割接窗口停业务一次性完成带DEFAUL值字段的新增。

2023-12-22 22:18:36 1339

原创 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 426

原创 【openGauss/MogDB列存表的delta表测试】

列存储格式是OLAP类数据库系统最常用的数据格式,适合复杂查询、范围统计类查询的在线分析型处理系统。cstore列存储的主体数据文件以CU为I/O单元,只支持追加写操作,因此cstore只有读共享缓冲区。CU间和CU内的可见性由对应的CUDESE表(astore表)决定,因此其可见性和并发控制原理与行存储astore基本相同。

2023-12-19 14:25:34 1000

原创 【openGauss/MogDB的TPCH测试】

TPC-H是一个决策支持基准(Decision Support Benchmark),它由一套面向业务的特别查询和并发数据修改组成。查询和填充数据库的数据具有广泛的行业相关性。这个基准测试演示了检查大量数据、执行高度复杂的查询并回答关键业务问题的决策支持系统。TPC-H报告的性能指标称为TPC-H每小时复合查询性能指标(QphH@Size),反映了系统处理查询能力的多个方面。这些方面包括执行查询时所选择的数据库大小,由单个流提交查询时的查询处理能力,以及由多个并发用户提交查询时的查询吞吐量。

2023-11-20 09:52:23 201

原创 PostgreSQL error错误码

错误代码情况名称01000warning0100C1008100310071006100401P012000no_data2001300080008003800680018004800708P0190000A0000B0000F0000F0010L0000LP010P0000Z0000Z0022000021000220002202E220212200822012220052200B22022220152201E。

2023-11-08 03:44:28 271

原创 【MySQL错误码】

MySQL的 error 码

2023-11-08 02:46:03 190

原创 【MogDB/openGauss误删未归档的xlog日志如何解决】

在使用MogDB/openGauss数据库的过程中,有时候大量业务,或者导数据会导致pg_xlog下的日志数量持续增长,此时如果xlog的产生频率太快,而来不及自动清理,极有可能造成pg_xlog目录的打满。如果对数据库的xlog不太了解的时候,可能造成误删未归档的xlog日志,或者更严重地,把对应操作还未写入数据文件的xlog也删除了。

2023-11-07 01:04:31 404

原创 【MogDB/openGauss的三种函数稳定性关键字】

在ORACLE里,function有着一个DETERMINISTIC参数,它表示一个函数在输入不变的情况下输出是否确定,只要输入的参数一样,返回的结果一定一样的,以保证函数对于任何输入总是完全相同的方式处理参数,oracle的内置函数 UPPER,TRUNC 等都是 deterministic 函数。因此,在事务中参数一致则结果一致,且只关注函数的最终结果可以标记为immutable,因为它调用函数的次数少,固化了函数的结果,执行计划的代价较小。任何具有副作用的函数的都不稳定的,即使其结果是可预测的。

2023-11-07 01:03:31 126

原创 【MogDB/openGauss如何实现自增主键】

自增主键是我们在设计数据库表结构时经常使用的主键生成策略,主键的生成可以完全依赖数据库,无需人为干预,在新增数据的时候,我们只需要将主键的值设置为default,数据库就会为我们自动生成一个主键值。MySQL主键自增使用AUTO_INCREMENT关键字,PostgreSQL自增使用SERIAL关键字或者序列。而MogDB/openGauss里兼容两种语法。AUTO_INCREMENT在MogDB-3.1.0/openGauss-5.0.0以上适配。

2023-11-07 01:01:59 948

翻译 【向量(Vectors)是PostgreSQL里的新型JSON】

在“JSON的崛起”期间,我还是一个应用程序开发者。我正在构建的系统要么向前端发出JSON数据以完成某种操作(例如,呈现可更新的小部件),要么与一个“现代”API一起工作,该API以JSON格式返回其数据。JSON的好处在于它的简单性(非常容易阅读和操作),同时又相对表达力较强,可以用作数据交换格式。我希望JSON能够具备一些特性,尤其是在数据库方面,我很喜欢有模式的概念,但JSON确实简化了系统之间的高效通信,无论是从开发还是运维的角度来看。

2023-11-07 00:58:40 241

原创 【PostgreSQL数据库toast表数据损坏处理】

根据提示来看,主表字段还留存着Toast Pointer,但Toast表中已经没有对应的Chunk条目,怀疑toast表存在损坏或者缺失数据。toast表的表名是字符串"pg_toast"与表的oid拼接而成,根据这个pg_toast_2619的2619的oid,可以定位到是pg_statistic的表。

2023-11-07 00:54:23 353

原创 【迁移ORACLE数据到MogDB/openGauss时的字符集问题】

ORACLE数据库在存储数据的时候,有时候会存在这样一种现象,一张表里的数据,既存在UTF8字符的,也存在GBK字符的,同时还有可能存在乱码数据。NLS_CHARACTERSET是数据库字符集,NLS_NCHAR_CHARACTERSET是国家字符集,NLS_LANG 是 Oracle 数据库客户端的一个环境变量。这种问题在于ORACLE对于规定了一种字符集后,对于插入的数据并没有强校验(garbage-in–garbage-out)。

2023-11-07 00:53:18 308

原创 【使用perf和火焰图分析PostgreSQL数据库的性能瓶颈】

Perf工具可用来对软件进行优化,包括算法优化(空间复杂度、时间复杂度)和代码优化(提高执行速度、减少内存占用)等等,perf 最常用的参数有top、stat、record,另外还有list和report等。本文主要使用Perf的record,script以及FlameGraph工具生成火焰图,来进一步分析性能瓶颈和函数调用。

2023-08-24 22:19:11 467

原创 【PostgreSQL的CLOG解析】

clog,即 commit log,PostgreSQL10之前放在数据库目录的pg_clog下面。PostgreSQL10之后修更名为xact,数据目录变更为pg_xact下面,表现形式是一些物理文件。PostgreSQL为什么要使用clog呢,众所周知,PostgreSQL有着独特的MVCC机制,由于其多版本的特性,

2023-08-14 11:21:42 2165

原创 【ps -aux里stat的进程状态】

【代码】【ps -aux里stat的进程状态】

2023-07-18 11:21:45 459

原创 【PostgreSQL-16新特性之类型转换测试功能】

在PostgreSQL里,“::” 符号其实是一个强制类型转换符,作用等同于CAST。在很多情况下,我们需要将一种数据类型的值转换为另一种数据类型。那么我们就可以使用它来进行转换。

2023-07-04 12:28:05 1787

PostgreSQL中的区域和编码

PostgreSQL中的区域和编码

2023-06-29

pgxmaster-sqlchecker

一个PG的相关python小工具,可以通过读取excel里的标识和SQL,批量在数据库里执行,并最终得出成功执行个数,失败个数,并返回每个SQL的执行是否成功,和SQL的返回内容。可以把对应的巡检SQL放到excel里边作为一个巡检工具,或者作为工具检查执行过程哪些SQL执行失败。

2023-04-12

空空如也

TA创建的收藏夹 TA关注的收藏夹

TA关注的人

提示
确定要删除当前文章?
取消 删除