自定义博客皮肤VIP专享

*博客头图:

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

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

博客底图:

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

栏目图:

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

主标题颜色:

RGB颜色,例如:#AFAFAF

Hover:

RGB颜色,例如:#AFAFAF

副标题颜色:

RGB颜色,例如:#AFAFAF

自定义博客皮肤

-+

Focus on PostgreSQL

PostgreSQL技术爱好者

  • 博客(327)
  • 资源 (4)
  • 收藏
  • 关注

原创 PostgreSQL t_bits计算方法

在PostgreSQL的page头部使用t_bits来表示null值的列。定义如下:bits8 t_bits[FLEXIBLE_ARRAY_MEMBER]; /* bitmap of NULLs */之前看的时候以为其长度就是8个bit位,用1个字节来存储。今天和别人聊的时候才发现如果只是8个bit位那完全不够啊,pg中表最多允许1600列,那不是应该需要1600位?于是建个70列的表测试下看看:bill@bill=>create table t1(bill(# c1 int, c2

2021-07-28 14:48:28 575

原创 PostgreSQL 从cmin/cmax到combo cid

cmin和cmax介绍cmin和cmax是PostgreSQL中表的系统字段之一,用来判断同一个事务内的其他命令导致的行版本变更是否可见。即在事务中每个命令都应该能看到其之前执行的命令的变更。很多人都通过测试都会发现在同一张表中cmin和cmax总是相等的,所以认为这两个是同一个概念,其实准确来说这两者的含义并不相同:cmin:插入事务中的命令标识符(从0开始)。cmax:删除事务中的命令标识符(从0开始)。简单来说,cmin和cmax都是表示tuple的command id,即cmin是产生

2021-07-27 11:30:39 778

原创 PostgreSQL如何计算age

今天看到一个问题,我们在查看对象的age时,为什么索引这类对象的age总是2147483647:bill@bill=>select relname,age(relfrozenxid) from pg_class where relname in ('t1','idx_t1'); relname | age---------+------------ t1 | 673 t1 | 666 t1 | 665 t1

2021-07-26 19:56:05 851

原创 从数据库启动日志看PostgreSQL的崩溃恢复

背景今天碰到朋友问我个问题,数据库启动时日志中的这个“invalid record length at 3/EAA68B8: wanted 24, got 0”里面的wanted 24是啥意思。而数据库正常启动时的日志其实并不是上面那样,而是如下图所示:上图的日志是因为数据库异常关闭后重启导致的,例如进程直接被kill。为了搞清楚上面日志中的那条记录是啥意思,我们来一起研究下数据库异常关闭后恢复的过程。崩溃恢复概述首先我们要清楚数据库异常关闭和正常的停库有啥区别。当数据库异常关闭时,数据库的

2021-07-21 16:43:19 1134

原创 PostgreSQL OOM最佳实践

OOM是Linux中一个比较常见的情况,PostgreSQL数据库触发OOM现象就是数据库进程被KILL了。OOM发生的原因有很多,这里我们从OOM的产生以及如何在PostgreSQL中预防OOM发生来进行研究。OOM介绍什么是OOM?OOM(out-of-memory),顾名思义就是内存溢出了,之所以会出现这种情况和内存分配的overcommit有关。Linux为了保证在有限的内存中尽可能多的运行更多的进程,在内存分配策略中提出了overcommit的策略,即允许内存溢出。之所以可以这么做是因为我

2021-07-21 15:33:12 1895 1

原创 简单聊聊PostgreSQL buffer与OS cache

0、概述缓存可以说是数据库中相当重要的一部分,很多性能相关的问题都与之息息相关。那么我们今天就聊聊在PostgreSQL中的缓存。1、为什么需要缓存?在数据库中似乎我们最关心的是磁盘IO,经常会听到数据库IO存在瓶颈之类的问题。这也是为什么在数据库中我们需要缓存。打个比方,对于计算机而言,1个CPU周期是0.3ns,如果把这个时间当成是我们平时生活中的1s。那么固态硬盘1次IO的时间大约是50-150μs,这意味着什么呢?相当于我们的2到6天。这么一对比就可想而知,哪怕你的磁盘IO再快,相较于在内

2021-07-19 14:36:01 2468 1

原创 PostgreSQL——真的非要无脑关闭NUMA吗?

相较于Oracle,pg对操作系统的依赖要高很多,因此对于pg的调优很大一部分都在操作系统层面。最近刚好有看到别人聊关于numa这个话题,那么我们就一起来看看pg中numa该如何使用吧。很多有经验的DBA对于numa的建议都是直接关掉,Oracle中更是建议如此,似乎这已经成为大家一种约定俗成的习惯了。对于大部分情况来说的确如此,但是我们还是得弄清楚为啥这么做,当然你也可以说我不管什么系统我就无脑统统关掉numa,那我就只能什么是NUMA?在单CPU时代,CPU与内存的交互大致如下:随着多CP

2021-07-16 14:19:09 2595 1

原创 PostgreSQL copy协议与事务

今天看到有人问“PostgreSQL中的copy命令是在同一个事务中执行的吗”。其实我想之所以提出这个问题,主要是想知道在执行copy语句时如果出现错误导致中断,那么数据是会导出/入部分,还是会像事务一样回滚。为了解答这个问题,我们先来简单了解下copy协议。什么是copy协议?我们使用客户端和数据库进行交互时,都必须要遵守PostgreSQL数据库的通信协议才可以。让我们比较熟悉的协议有TCP/IP 协议和 HTTP 协议等。而PostgreSQL在TCP/IP 协议之上实现了一套基于消息的通信协议

2021-07-14 11:27:56 3258 1

原创 PostgreSQL 切勿kill -9去杀进程

熟悉Oracle的人可能会去经常使用kill -9杀进程,因为有的时候使用SQL命令去杀进程,一些进程的状态只是会被置为"killed"状态,其锁定的资源仍然没有释放,可能会收到提示:ORA-00031: session marked for kill因此甚至还建议大家去使用kill -9直接去操作系统上杀掉进程。又比如我们Oracle停库的时候常常都是先停监听,再手动kill掉LOCAL=NO的进程,然后再去停库。但是要注意了,切勿在PostgreSQL中使用kill -9去杀掉进程!首先我们

2021-07-13 11:35:50 1315

原创 PostgreSQL授权普通用户kill会话权限

作为DBA,可能经常回听到开发人员说“我有个SQL执行太久了,帮我kill下”“我有张表好像锁住了,帮我kill下”。时间久了确实挺烦的,此时我们肯定想的是让他们自己去kill,但是又不能直接给开发或者用户超级用户的权限,那么怎么只授权普通用户kill会话的权限呢?–方法1:PostgreSQL9.6开始,新增了默认角色pg_signal_backend,这个角色具有 cancel query、terminate 其它会话的权限。例子:bill@bill=>create user user01

2021-07-09 15:55:41 1506

原创 PostgreSQL如何对URL进行解析

尽管PostgreSQL中支持大量的数据类型,但是对于URL似乎并没有一个相应的类型能够去存储。那么对于URL的数据我们在数据库中要怎么去处理呢?首先还是需要看你要存储的URL数据是用来干嘛的,如果仅仅只是为了在以后查询的时候打印输出,那么用varchar或者text之类的类型不是就可以了吗?但是有的时候我们对于这些URL数据并不仅仅是简单的查询,我们可能希望通过这些URL能够查询一些额外的信息,例如URL的使用的协议、主机名等等。这里我们便可以使用ts_debug函数来进行解析了。函数ts_debu

2021-07-09 14:03:44 1212

原创 PostgreSQL获取用户下所有对象

有时我们需要对数据库用户下的对象进行审计时,可以使用下面脚本直接获取用户下所有对象。查看postgres用户下所有对象:select nsp.nspname as SchemaName ,cls.relname as ObjectName ,rol.rolname as ObjectOwner ,case cls.relkind when 'r' then 'TABLE' when 'm' then 'MATERIALIZED_VIEW

2021-07-08 11:22:09 1277

原创 PostgreSQL如何修改表的字段顺序

MySQL中我们可以使用类似下列的语句,在某个指定的字段后添加字段:alter table test add column c1 int after id; 那么在PostgreSQL是否可以实现类似的功能呢?或者说修改表当前的字段顺序呢?比较简单的方法就是将表删除后重建。还有一种比较常见的方法就是通过视图来代替,例如:bill@bill=>create view v_t1 as select c1,c3,c2 from t1;CREATE VIEWbill@bill=>sel

2021-07-05 10:09:31 1875

原创 PostgreSQL index monitor——监控表上索引使用频率

我们需要去监控哪些大表的查询并没有去使用索引:SELECT relname, CASE idx_scan WHEN 0 THEN 'Insufficient data' ELSE (100 * idx_scan / (seq_scan + idx_scan))::text END percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY

2021-07-05 09:21:52 1693

原创 源码探究PostgreSQL中的Fast Path Locking

1、概述pg_locks视图中有个fastpath字段,官方文档中对其解释是“True if lock was taken via fast path, false if taken via main lock table”,那么我们不禁要问,什么样的lock是通过fast path获得的?这个fast path lock又是什么呢?首先我们要知道,在数据库启动的时候会初始化共享内存区域,共享内存是一个统称,实际上有很多共享内存区,比如锁也是一块。这也就决定了锁其实是数据库中很宝贵的一部分资源,在pg中

2021-07-01 15:59:51 1620

原创 聊聊PostgreSQL中的VFD机制

1、什么是文件描述符在操作系统中,为了高效地管理文件,当进程打开或创建一个文件时,操作系统会为该文件分配一个文件描述符(文件句柄),通过该文件描述符来唯一标识该文件,其本质上就是用来管理文件的索引。理论上来说,我们的操作系统内存有多少就可以打开多少的文件描述符,但实际上一般最大打开文件数会是系统内存的10%(以KB来计算),我们可以通过sysctl -a | grep fs.file-max命令查看。同时为了防止单个进程过度使用文件描述符,也会对单个进程进行限制,可以通过ulimit -n查看,一般默

2021-06-30 15:06:55 1364

原创 PostgreSQL full_page_writes与checkpoint

我们都知道数据库中出现故障时都会去从最近一次的checkpoint开始应用wal日志进行恢复。那么checkpoin越频繁我们恢复就会越快,当然checkpoint也会对IO产生一些影响,不过一般在磁盘性能允许的情况下,还是建议checkpoint越频繁越好。但是在PostgreSQL中却并不如此,相较于其它的一些数据库,PG中会更希望checkpoint越晚越好,这是为什么呢?主要还是和pg中的full_page_writes机制有关了。full_page_writes是什么呢?简单点来说,因为pg中

2021-06-25 15:42:48 2819 1

原创 Greenplum query调用函数报错解决方案

我们可能经常会遇到需要在query调用自定义函数的情况,但是在Greenplum中,如果函数中有query,然后又在query中调用该函数则会报错。例子:创建函数iap=# create or replace function f1() returns text as $$declarec1 text;beginexecute 'select info from tt1 limit 1' into c1;return c1;end;$$ language plpgsql;query中

2021-06-18 14:14:48 732

原创 PostgreSQL如何限制用户查看函数内容

在某些情况下,我们可能只希望普通能够使用某个函数就好了,而不希望该用户能够看到函数体内部的内容,那么我们该如何控制用户的权限呢?例如我们可以通过编写C函数来实现,例如PG的内部函数一样:bill@bill=>\sf+ convert CREATE OR REPLACE FUNCTION pg_catalog.convert(bytea, name, name) RETURNS bytea LANGUAGE internal ST

2021-06-10 10:55:57 1336

原创 PostgreSQL jsonpath使用实践

jsonpath是用来解析json数据的工具,类似于xpath,jsonpath可以解析十分复杂的json数据。PostgreSQL json发展历史:PostgreSQL从9.2开始就支持json数据类型,但是由于解析json数据的性能很差,导致并不受大家青睐,而是选择使用nosql数据库代替。于是从pg9.4开始支持了jsonb数据类型,相较于json类型,jsonb由于并不需要每次使用时都去进行解析,因此性能提升很多,都是还支持索引查询等。而从pg12开始对于json的支持更加强大:sql 20

2021-06-07 20:02:14 1130

原创 PostgreSQL CTE语句与materialized

PostgreSQL with语句功能可谓十分强大,可以优化很多复杂的查询,实现递归等等。不过with虽好,可还是不要乱用。因为在PG12之前,with语句都是通过将子查询先进行物化,这就导致了一个问题:with子查询外的条件无法内推到里面。我们看看PG12之前:从执行计划可以看到,先对t1表进行全表扫描了一遍,然后再去进行过滤。postgres=# explain analyze with c1 as (select * from t1) select * from c1 where id = 1

2021-06-04 10:09:49 1153

原创 从数据加密到PostgreSQL SSL认证连接

为了保证数据库连接的安全性能,PostgreSQL允许使用SSL证书的方式去连接数据库,这无疑大大加强了数据访问的安全性能。在讲解PostgreSQL中SSL的使用前,我们先来了解下什么是SSL证书。1、数据加密首先我们先来聊聊什么是数据加密。1.1、对称加密数据加密这也是和我们生活中息息相关的,可能你不清楚什么是加密算法,不过你可能有过这样的经历:在读书的时候,每当考试的时候总会有人想尽办法去作弊,当然我想总不会有人蠢到考试的时候在监考老师眼皮地下像平时说话一样,第一题选A,第二题选B这样交流。

2021-05-31 19:26:10 2786 3

原创 PostgreSQL 大小写敏感的那些事

PostgreSQL和Oracle一样,默认都是大小写不敏感的,但两者仍然存在区别:**Oracle:**默认是大小写不敏感,表名、字段名等不区分大小写,小写字母会自动转换为大写字母;需要用小写字母时需要使用双引号,或借助函數upper()和lower();PostgreSQL:默认是大小写不敏感,表名、字段名等不区分大小写,大写字母会自动转换为小写字母;需要用大写字母时需要使用双引号,或借助函數upper()和lower();1、表、列名中的大小写敏感例如我们创建表test,表名写成tes

2021-05-28 14:09:30 1377

原创 PostgreSQL行级安全策略RLS和数据加密

我们在一般的数据库中可能对用户的查询权限只能到表级别,例如限制某个用户只能查询哪些表。PostgreSQL从9.5版本开始支持行级别的权限管控,允许不同的用户在表上查询到不同的数据。通过在表和角色上创建不同的策略来限制不同用户对于表的权限:Command: CREATE POLICYDescription: define a new row-level security policy for a tableSyntax:CREATE POLICY name ON table_name

2021-05-27 16:25:13 1131 1

原创 PostgreSQL 连接池 pgbouncer安装配置

0、概述PostgreSQL由于是多进程模式,每个连接,有一个独立的进程与之交互,所以如果有大量的连接去连数据库时,性能会产生明显下降(pg14中显著提升了海量连接下的性能),严重时甚至会发生OOM。为了解决该问题,建议使用连接池,比较常见的有pgbouncer,pgpool等。PgBouncer是为PostgreSQL提供的轻量级连接池工具,其作用主要有:能够缓存和PostgreSQL的连接,当有连接请求进来的时候,直接分配空闲进程,而不需要PostgreSQLfork出新进程来建立连接,以节

2021-05-26 13:35:54 1603 2

原创 PostgreSQL特殊恢复——直接修改数据文件恢复数据

刚写了篇PostgreSQL的page结构的文章,算是把page弄得比较透彻了。那么知道这些对我们有啥用呢,下面我们演示下类似Oracle bbed通过修改数据文件来恢复数据的方式。创建测试表并插入数据:bill=# create table t1(id int,info text);CREATE TABLEbill=# insert into t1 select generate_series(1,5),'bill';INSERT 0 5bill=# select * from t1; i

2021-05-20 19:31:24 1147 1

原创 这一次终于把PostgreSQL Page结构搞懂了

PostgreSQL中的page指的是数据文件内部被划分的一个个固定长度的页,和Oracle中的数据块类似。page默认大小是8k,可以在编译数据库时通过–with-blocksize参数指定。文件中的page从0开始一个个进行编号,当一个8k的page写满时就会在该page尾部追加一个新的page。这也是为什么pg中单表只能时32T,因为pg默认采用32位寻址,也就是说单张表的数据文件最多有2^32=4294967296个page。言归正传,接下来我们来一起揭开page的庐山真面目。1、page整体

2021-05-20 15:43:49 1465 1

原创 PostgreSQL LSN详解

PostgreSQL LSN即Log sequence number,日志序列号,这是WAL日志唯一的、全局的标识。那么pg中LSN究竟有什么作用呢?我们都知道wal日志中写入是有顺序的,比方说一条记录是先加100再乘200,如果顺序错乱变成先乘200再加100,那结果可是差之千里了,所以必须得记录wal日志的写入顺序。而LSN就是负责这个的,给每条产生的wal日志记录一个编号。熟悉Oracle的朋友可能清楚,这和Oracle中redo的LRBA和HRBA有点类似。LSN和WAL:我们先来看下LSN

2021-05-19 11:20:27 1539

原创 PostgreSQL如何对某行记录进行模糊查询

在某些场景下,我们可能需要对表的某行字段进行查询。例如BI报表的下拉框,用户可能会勾选多个条件进行查询,那么我们查询会很麻烦。例如:bill@bill=>create table test1(c1 int,c2 text,c3 text,c4 text);CREATE TABLEbill@bill=>insert into test1 values(1,'post','china','bill');INSERT 0 1如果我们要查询该表某行包含china的记录,我们可能需要这么去

2021-05-18 10:41:29 1079

原创 PostgreSQL如何判断事务新旧

PostgreSQL由于其多版本特性,经常需要去比较两个事务的新旧。那么该如何比较两个事务的新旧关系呢?你可能会说通过XID(事务ID)不就可以了,没错!例如一个事务ID是1000000,另一个事务ID是1000001,那么显然是1000000的事务更旧。但是通过事务ID去判断远不止这么简单,我们都知道pg中的事务ID是用32位无符号数来表示的,也就是说如果不引入特殊的处理,当PostgreSQL的XID 到达40亿,会造成溢出,从而新的XID 为0。而按照PostgreSQL的MVCC 机制实现,之

2021-05-17 20:00:10 1362 2

原创 PostgreSQL 14 新特性两阶段routines和两阶段提交

PostgreSQL14中支持不锁表的情况下去detach分区。语法为 ALTER TABLE … DETACH PARTITION … CONCURRENTLY, and FINALIZE。这个操作是基于pg14中的两阶段routines管理机制来实现的,alter table 支持两阶段 routine 管理,这使得将来某些需要rewrite整张表的操作将不再锁表(或者短暂加锁)。例如当前vacuum full是全程排它锁,而通过这个两阶段routines管理机制,将来可能不会锁表了。这一特性和

2021-05-17 19:19:56 939 2

原创 PostgreSQL 14 pg_prepared_statements新增统计软/硬解析次数

PostgreSQL中prepare statement可以用来cache plan,用来减少plan的次数。默认是前5次调用生成generic plan,然后生成custom plan。PG14中在pg_prepared_statements视图中新增了generic_plans和custom_plans两列,用来统计generic plan和custom plan的次数。bill@bill=>PREPARE pr1 AS SELECT * FROM pg_class WHEREbill-#

2021-05-13 10:27:44 1181 1

原创 PostgreSQL 14 窗口函数incremental sort

pg14支持在窗口函数中增量排序。–PG14之前:bill=# explain (COSTS OFF)bill-# select *bill-# from (select id,bill(# info,bill(# row_number() OVER(PARTITION BY id ORDER BY info) AS first_id,bill(# row_number() OVER(PARTITIO

2021-05-12 20:56:47 769

原创 PostgreSQL 14 idle_session_timeout空闲会话连接超时参数

PostgreSQL 14新增了idle_session_timeout参数,用来控制空闲会话连接超时的时间。当一个会话连接长时间没有执行SQL或者活动时,会将该会话释放,可以释放缓存避免出现例如OOM等问题。idle_session_timeout:默认值为0,表示禁用,其单位是毫秒。例子:我们将其值改为5000,查看:bill@bill=>show idle_session_timeout; idle_session_timeout----------------------

2021-05-12 20:17:00 806

原创 PostgreSQL 14 pageinspect新增gist索引支持

pageinspect插件可以用来查看表和索引的内部结构,但并不是所有的索引类型都支持,PG14中增加了三个函数用来支持对gist索引的支持。 function gist_page_items(bytea,regclass) function gist_page_items_bytea(bytea) function gist_page_opaque_info(bytea)例子:创建测试表和索引:bill@bill=>CREATE TABLE test_gist AS SELECT po

2021-05-12 19:35:08 758

原创 PostgreSQL 14 Tid Range Scan

PostgreSQL中堆表都是一个个page所组成,而每个page里面若干tuple。TID 即是tuple的寻址地址: (pageid, itemid), pageid即第几个数据块,,itemid即这个page内的第几条记录。例如tid=(10,1)表示第11个数据块的第一条记录 (page从0开始, item从1开始)。在pg14之前就已经支持了tid scan,我们可以直接指定要查询的行号, 只需要扫描一个page. 通过itemid offset直接拿到tuple, 所以速度非常快,类似于O

2021-05-12 18:42:03 1138

原创 PostgreSQL 14 group by distinct子句分组去重

我们通过group by子句进行分组时,经常会出现数据重复的情况。例如GROUP BY CUBE (a,b), CUBE (b,c)便可能出现数据重复的情况。PG14中支持group by distinct的语法,可以用来进行数据去重。例子:group by:bill@bill=>select a, b, cfrom (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c)group by rollup(a, b), rollu

2021-05-12 16:42:08 1187

原创 PostgreSQL 14默认密码加密改为scram-sha-256

PostgreSQL 14中将默认的密码加密由原先的MD5修改成了scram-sha-256,这个从安全性的角度有了一定的提升。之前我们也说过,MD5的方式如果别人获取了你密码的md5值是会存在安全问题的。因为其存储的方式为md5(‘用户名+密码’)。PG14之前:bill=# select rolname,rolpassword from pg_authid where rolname = 'bill'; rolname | rolpassword---------+-

2021-05-12 15:34:51 1250 2

原创 PostgreSQL 14 新增default role——pg_database_owner

PostgreSQL 14 中新增pg_database_owner role,表示是数据库的拥有者。这个role不能显示赋予,也不能被赋予。PG14中内部的role总共11种,如下:bill@bill=>select rolname from pg_roles where rolsuper = 'f'; rolname--------------------------- pg_database_owner pg_read_all_data pg_write_all

2021-05-12 15:09:21 1097

原创 PostgreSQL 14 新增只读/只写角色

在实际生产环境中,我们经常需要创建只读用户,将一些数据开放给用户查询使用。pg14中新增pg_read_all_data 角色,可以用来更方便的创建只读用户。–PG14之前:在pg14之前,我们创建一个只读的用户还是比较麻烦的,例如我们直接以下面的方式授权:bill=# grant select ON ALL tables in schema public to r2;GRANT这看起来没啥问题,但是如果在授权之后创建了新的对象,那么该只读用户是无法查看的。具体可以参考:PostgreSQ

2021-05-12 14:14:30 803

PostgreSQL11.2-CN-v1.0.pdf

PostgreSQL11.2中文文档

2020-04-29

PostgreSQL内核扩展入门.pdf

PostgreSQL内核扩展入门

2021-03-08

PostgreSQL awr

PostgreSQL中实现获取Oracle awr报告的类似功能

2020-10-23

达梦数据库常见问题FAQ_V1.0.docx

达梦数据库常见问题介绍,学习达梦常见的问题汇总及问题解答

2020-05-27

空空如也

空空如也

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

TA关注的人 TA的粉丝

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