Nickxyoung-CSDN博客

原创 PostgreSQL行级安全策略探究

最近和朋友讨论oracle行级安全策略(VPD)时，查看了下官方文档，看起来VPD的原理是针对应用了Oracle行级安全策略的表、视图或同义词发出的 SQL 语句动态添加where子句。通俗理解就是将行级安全策略动态添加为where 条件。那么PG中的行级安全策略是怎么处理的呢？行级安全策略(Row Level Security)是更细粒度的数据安全控制策略。行级策略可以根据每个用户限制哪些行可以通过常规查询返回，哪些行可以通过数据修改命令插入、更新或删除。

2024-07-11 12:55:31 929

原创优化器刺客之limit 1--Order by col limit n 代价预估优化探索

这里在where 条件不含排序字段走排序字段索引情况下，有可能通过索引匹配到第一条符合条件的数据会比较久，就是说要考虑索引扫描的整体代价作为limit的启动代价（当前默认逻辑是通过索引很快找到第一条数据，然后输出limit n行，因此整体cost在这个场景下是偏小的），最极端的场景很可能是先扫描了整个索引并且回表去匹配数据，这个cost要预估进去。在set_cheapest里比较表扫描的最优方式，循环比较pathlist的每个节点的startup_cost和total_cost，

2024-01-30 08:37:52 1118

原创 pg_terminate_backend()运维改造--打印调用记录及被终止query文本

同学，别再说不是你kill的会话了，这个锅DBA不背。

2023-02-26 15:43:08 920

原创 pg_cron优化案例--terminate pg_cron launcher可自动拉起

优化插件，当pg_cron launcher 被terminate后可自动拉起

2023-02-15 13:55:45 601

原创 pg_cron的一点优化

pg_cron 是一个简单的基于 cron 的 PostgreSQL（9.5 或更高版本）作业调度程序，作为扩展在数据库内部运行。它使用与常规 cron 相同的语法，但它允许您直接从数据库调度 PostgreSQL 命令。是一个Hook插件，在postmaster启动时，通过 process_shared_preload_libraries()函数根据配置的插件名找到对应的lib，然后运行里边的pg_init()函数，做一些初始化。......

2022-06-10 00:05:43 382

原创 pg_terminate_backend()运维改造--nonsuperuser支持kill autovacuum worker

一、背景介绍鉴于安全考虑，数据库的superuser一般来说是只掌握在DBA同学手中。最近多次碰到这样的场景，业务同学晚上发版修改表结构，吐槽表被系统进程锁住了，排查后发现是autovacuum worker阻塞的。经常需要爬起来支持，同时也阻塞了发版效率。那能不能将autovacuum worker的terminate权限给nonsuper user呢？可以方便业务账号直接处理被autovacuum worker阻塞的情况。业余时间研究了下，将pg_terminate_backend()做了一点小

2021-11-30 00:47:10 2214

原创 PostgreSQL的checkpoint简析

一、 Checkpoint简介官方文档对于checkpoint的描述：Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint.At checkpoint time, all dirty da

2021-10-20 20:39:18 801

原创 PostgreSQL从库只读的实现原理

我们接触到的数据库，基本都提供了只读实例的功能。在业务允许的情况下可以将一些读取数据的请求下发至只读实例，减缓primary的压力，以获得更佳的性能。物理从库只读已经是一个常识问题，在PostgreSQL中具体是怎么实现的呢，一起来看看一、报错分析从报错入手分析，在只读库里执行写操作，报错如下：postgres=# select * into tbl_t from tbl_test;ERROR: cannot execute SELECT INTO in a read-only transact

2021-09-12 23:45:32 545

原创 pg_basebackup工具运维改造

一、背景介绍在日常运维过程中，多次遇到HA切换后，全量重建，之前的pglog日志被覆盖的场景。这里HA管控侧使用的全量重建工具是pg_basebackup。因为pg_basebackup重建时指定的datadir必须为空，因此需要删除datadir下所有的文件再进行重建。很多时候是需要查看重建之前的老日志来分析一些问题。那这个时候就比较尴尬。当然我们可以在管控里加入拷贝老日志的逻辑，可以拷贝到本地专用磁盘路径，或者上传至对象存储，又或者将日志同步至ES等。但是无疑又要增加一部分存储成本。那是否可

2021-07-18 23:46:32 350 4

原创 PostgreSQL在线调整最大连接数方案设想

一、背景介绍PostgreSQL的最大连接数在某些时候会成为一个让业务很苦恼的问题。业务连接数暴增，连接数打满,但又不能重启服务这种场景下，显得非常尴尬。最大连接数就只能重启调整，要想不停库，貌似“无解”。为什么调整最大连接数需要重启，不能修改为reload方式？这个问题不详细展开，简单来说最大连接数参数和共享内存及信号量等相关的，设置最大连接数后，在启动时系统会相应分配这些资源。那原生的最大连接数参数看起来只能是重启方式修改生效了。原生pg除了max_connections，还可以ALTER US

2021-06-13 16:11:13 2290 1

原创 pg_stat_statements使用LWLock排他锁性能问题分析

一、问题描述值班同学发现一实例性能异常，用户反馈实例不可用。监控显示出现死锁，并且执行简单sql例如select 1也会“卡住”。持续一段时间后恢复抓取了某几个卡在parse环节的sql进程堆栈，大致如下：[postgres@postgres ~]$ ps -ef|grep pgbase|head -10|grep -v greppostgres 13622 89757 93 16:02 ? 00:00:28 postgres: pguser pgbase 192.138.23.

2021-06-05 14:09:15 972

原创 PostgreSQL中的tempfile探究

一、概念简介Tempfile，即临时文件。大家都熟悉的是当pgsql进程私有buffer不够用时，会将buffer内容dump到tempfile供postgres进程使用。常见的几个功能场景：ExecSorts、ExecHashJoin、ExecMergejoin、tempTable、CTE等，当work_mem或者是temp_buffers不足以容纳sql中间功能环节结果集时会产生tempfile。这篇博文简单探讨下tempfile的生命周期，何时产生，何时消除，以及对于可能存在的问题的一些思考。

2021-05-16 11:16:17 1144

原创 PG_VERSION文件被修改导致小版本升级后启动失败

一、问题背景小版本升级，如果版本间不涉及重大改动的话。通常可以通过替换可执行软件包并重启数据库进行快速升级。pg10.3升级至10.11后启动失败，报错如下：pg_ctl startwaiting for server to start....2021-02-28 18:51:15.066 CST [20514] FATAL: database files are incompatible with server2021-02-28 18:51:15.066 CST [20514] DETA

2021-02-28 22:38:33 2323

原创 PostgreSQL10.x版本relcache刷新异常问题分析

一、问题背景使用过pg10.x的小伙伴，一定了解relcache不更新的这个BUG。通常的表现是vacuum/autovacuum异常，会发生表膨胀；数据库不能进行事务号回收，甚至导致事务回卷。根据以往的经验，我们见到过以下这些场景：1.pglog中存在类似vacuum报错：ERROR: found xmin 10xxxxx from before relfrozenxid 21xxxxxxx2.不存在长事务的情况下，多个表发生膨胀3.$PGDATA/global目录下存在大量的pg_int

2021-01-31 12:22:35 1088

原创 PostgreSQL中的WAL保留策略

一、背景描述在PostgreSQL中，我们熟知对于wal日志的保留，有wal_keep_segments这个参数去配置。当主库日志刷新比较快时，我们一般会将这个参数调整为一个比较大的值，来保证从库稳定地进行流复制。但是有时，我们会发现主库保留的日志远远大于这个参数配置，甚至有些时候会出现日志暴增，存在写满磁盘的风险。这就涉及到了另外一个机制–复制槽，从官方文档描述来看复制槽提供了一种自动化的方法来确保主库在所有的从库收到 WAL 段之前不会移除它们。因此wal_keep_segments参数和复制槽

2020-10-09 13:10:22 4399

原创 PostgreSQL流复制小细节--从库如何确定起始位点receiveStart

一、问题引出PostgreSQL的流复制大家再熟悉不过了，今天在看文档时，脑子里突然冒出一个问题。从库起walreceiver进程时是如何确定该从哪个点开始接收日志？这个起始的点在数据库中是以什么形式记录的？怎么来的？是共享内存结构体？还是记录在ControlFile？带着这些乱七八糟的疑问，翻阅了下源代码，并gdb跟踪调试了下从库起walreceiver进程的过程，总算是把这个问题搞明白了。以下把这个起始点描述为receiveStart二、代码走读通过阅读源代码，了解到receiveStart

2020-10-06 12:17:07 590

原创 PostgreSQL运维案例--文件名超长导致从库重建失败

一、问题现象使用pg_basebackup重建从库失败，报错如下：[postgres@postgres_standby:pg11.5:6548 ~]$ pg_basebackup -D /opt/postgres/postgresql-11.5/pg11debug/data -U repuser -h 192.168.92.128 -P -vPassword:pg_basebackup: initiating base backup, waiting for checkpoint to c

2020-10-03 10:16:43 749

原创 PostgreSQL运维案例--create index失败

一、问题背景同事在一测试环境创建索引失败，报错如下：postgres=# create index idx_tab_pacloud_messages_id_regionid_region_userid_username_mes_0911 on tab_pacloud_messages_0911 using btree (id,user_id,region_id);NOTICE: identifier "idx_tab_pacloud_messages_id_regionid_region_useri

2020-09-19 14:28:00 1828

原创数据库相关知识学习

文章目录1. mysql与redis的区别2. 进程与线程3. 线程有哪些（拿innodb来说）4. innodb的内存结构5. 生产环境中如何批量更改mysql引擎6. innodb和myisam存储引擎7. innodb与myisam的选择1. mysql与redis的区别mysql与redis的区别有：（1）在类型上，mysql是关系型数据库，redis是缓存数据库；（2）在作用上，mysql用于持久化的存储数据到硬盘，其功能非常强大，而redis用于存储使用较为频繁的数据到缓存中，读

2020-08-05 22:26:16 137

原创 02. HTML基础知识和开发工具介绍

02. HTML基础知识和开发工具介绍1. 标签的书写注意规范2. 写出HTML骨架标签3. 写出超链接标签4. 写出图片标签并说出在图片标签中alt和title的区别5. 说出相对路径的三种形式标签的书写规范HTML基本结构标签以小猪佩奇的身体来举例：网页开发工具我们选用VS Code来进行网页开发。VS Code的下载安装教程参考以下链接：VS Code的下载安装教程在vscode里新建文件，首先将文件保存为html文件。在开发工具中输入一个英文的感叹号

2020-08-01 23:58:42 175

原创 01. Web前端基本概念

Web前端基本概念1. 网页的基本组成?2. 什么是HTML?3. 常用的浏览器4. Web标准的三大组成部分？ 1. 网页的基本组成?1.网站由许多页面组成，是多个网页的集合。2.网页是网站中的一页，通常是HTML格式的文件，它要通过浏览器来阅读。3.网页中有许多元素，网页是构成网站的基本元素，包括文字、图片、声音、链接、视频等元素组成。通常我们看到的网页，通常是以.html和.htm后缀结尾的文件，因此将其俗称为HTML文件。2. 什么是HTML?HTML是超文本标记语言(Hyp

2020-07-30 23:10:19 396

原创 PostgreSQL中的等待事件wait_event

一、等待事件分类事件类型：共有9个分类，每一类都由不同事件组成/* ---------- * Wait Classes * ---------- */#define PG_WAIT_LWLOCK 0x01000000U /* 等待LWLock */#define PG_WAIT_LOCK 0x03000000U /* 等待Lock */#define PG_WAIT_BUFFER_PIN 0x04000000U /* 等待访问数据缓冲区 */#define

2020-07-12 18:47:48 11746

原创 PostgreSQL中的锁--spinLock、LWLock、Lock

一、PostgreSQL中的锁PostgreSQL中根据不同对象，不同使用场景，使用到了三种锁，即spinLock，LWLock，Lock1.spinLockSpinLock也就是所谓的自旋锁，是并发场景下（多进程/线程），保护共享资源的一种机制。实现的成本最低，一般是使用基于硬件的TAS操作（test-and-set来实现的）。显著的特点是审请锁的进程一直在尝试能否加锁成功，只有等到持有锁的线程释放锁之后才可以获取锁。在等待锁的过程中进程并不是切入内核态进行sleep，而是忙等待，即忙循环–旋转–等

2020-06-02 22:38:28 3652

原创 PostgreSQL中的io多路复用--select和epoll实现

某日和同事闲聊，同事发现一个现象，PostgreSQL在空闲状态时（没有active连接），主进程的pstack显示一直在调用/lib64/libc.so.6的__select_nocancel ()接口，而子进程基本都在调用__epoll_wait_nocancel ()接口。同事咨询这是什么功能，有什么区别。之前对这块没有深入了解，只知道是多路复用的接口，因此翻阅了下源码，查询了一些资料，对这个问题做了下总结。主进程PostMaster的stack信息：[postgres@postgres_zabb

2020-05-17 23:07:07 1451

空空如也

空空如也