一、pg中的默认值优化     前篇学习了Oracleaddcolumndefault在各版本的优化，顺便也再研究了下pg对于addcolumndefault的优化及实现原理，记录一下。 Oracle的优化关注点在于新增default时是否有notnull约束，而pg则在于新增的default值是否是volatile的。具体而言： pg10及之前：新增带default值的列均需rewritetable pg11开始：新增volatile default值的列仍需rewritetable，例如random(),timeofday() &nbs...

一、replacevs重建     遇到开发提了个问题，createorreplaceview和重建视图（drop+create）有什么区别，查询资料整理了一下。 1. createorreplace 当存在同名视图时，尝试将其替换 新视图语句必须与现有视图查询具有相同的列（即相同的列名、列顺序和数据类型） pg8.1开始，之前向视图末尾添加新列 总体而言，改动限制较大，但replace后不影响权限和依赖于该视图的对象 2. drop+create 新视图定义不依赖原视图，灵活度高 重建后权限丢失，必须重新授权 -对象授权...

    原图来自：数据库IO链路访问图–OracleBlog 由于很复杂，为了加深理解自己重新画了一次，另外参考其他文档补充了各部分的插图和介绍。 一、存储服务器 1.物理磁盘 外层的壳子称为硬盘笼cage 2.chunklet     Chunklet是一个虚拟概念而不是实际硬件，由存储OS自动创建（不一定都会有，视存储而定），用于创建逻辑磁盘。一个存储块仅分配给一个逻辑磁盘。物理磁盘分为多个存储块，每个存储块的大小相同，占用物理磁盘上的连续空间，不能跨越其他磁盘。 3. 逻辑磁盘...

DUPLICATETARGETDATABASETO 一、 参数含义 db_create_file_dest：设置后开启OMF，创建数据文件、redo文件时不需手动指定路径 db_file_name_convert：主、从库数据文件目录转换对应，如果有多个，需要逐一指明。建议直接指定到数据文件上一层目录，否则其中子目录必须在从库中存在（参考后面案例）。 log_file_name_convert：主、从库redo文件目录转换对应，如果有多个，需要逐一指明。注意事项同上。 优先级： db_create_file_dest> db_file_name_convert与...

一、测试数据构造 1.数据样例 官方文档有给出一批数据样例。优点是比较真实，缺点是太大了，动辄上百G不适合简单小测试 AnonymizedYandex.MetricaDataset StarSchemaBenchmark WikiStat TerabyteofClickLogsfromCriteo AMPLabBigDataBenchmark NewYorkTaxiData OnTime 相对来说ssb-dbgen工具生成的表比较简单，数据量也可以自己控制，更加方便。 2.ssb-dbgen下载安装 下载 https://github.com/vadimtk/ssb-dbgen 安...

一、插件作用     众所周知，pg是没有像oracle那样的ash视图的，因此要回溯历史问题不太方便。pgsentinel插件会将pg_stat_activity与pg_stat_statements视图内容定期快照，并存入pg_active_session_history和pg_stat_statements_history视图中。 1. pg_active_session_history视图字段 Column Type 备注 ash_time timestampwithtimezone 采样时间 datid o...

一、背景 pgbackrest配置的归档目录/backup被写满 归档报错Nospaceleftondevice，wal日志堆积 解决方法直接查看第三部分 二、问题分析及处理 1.目录清理 首先想到的就是清理/backup目录，清理后剩余6T空间 但发现pgbackrest归档依旧在报错 Nospaceleftondevice，没有识别到空间已经释放，一直在尝试归档结尾0055那个wal日志文件。 归档目录写入依然停止在7:02 2.尝试重启pgbackrest 重启命令：pgBackRestUserGuideDebian&Ubuntu 执行 pgbac...

遇到的错误号，错误原因、处理方法及参考文档小结 一、Oracle报错记录 报错号 原因 参考 建议 errorwhileloadingsharedlibraries:libskgxp10.so:cannotopensharedobjectfile:Nosuchfileordirectory 环境变量LD_LIBRARY_PATH没有设置 Oracle的常见错误及解决办法沧海一滴 .bash_profile设置LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib ORA-609 TheORA-609erroristhrownwh...

1.MVCC实现机制以及和Oracle的差异 MVCC： 多版本并发控制，核心作用：使得读写操作不相互阻塞，提升并发性能。 实现原理：通常有2种实现方法： 写新数据时，把旧数据存入其他位置（如oracle的回滚段、sqlserver的tempdb）。当读数据时，读的是快照的旧数据。 写新数据时，旧数据不删除，直接插入新数据。以pg为代表，在元组头中引入xmin,xmax,cid,ctid,t_infomask几个字段，并结合commitlog,snapshot来进行可见性判断。 以pg为例： 插入数据：xmin为执行插入的事务号，xmax为0 删除数据：xmin不变，xmax为执行删除...

核心思想就是减少对表的多次扫描，最好减成一次。 创建测试表 createtablet1asselectfromdba_objects; createindexind_object_idont1(object_id); createindexind_object_nameont1(object_name); createindexind_owneront1(owner); createindexind_object_typeont1(object_type); createtablet2asselectfromcdb_objects; createindexind_object_id_2...

本篇纪念小白第一次用开发工具调试源码成功     gdb使用命令行调试感觉还是有诸多不便，本来想简单点找找怎么直接调试Windows下的pg源码，找了半天只有用vs，太重而且步骤比较复杂。大多数实际环境还是运行在Linux的，所以最好还是调试Linux下的pg源码。 目标端：centos7+postgresql14 源端：Windows10+vscode 零、特别提醒 在调试的过程中，有些操作可能会导致整个pg挂掉，小白们千万别在生产环境瞎跑。 一、postgresql14源码编译与安装 特别要注意的就是编译的时候要加--enable-deb...

一、测试背景 业务希望对一些不由DBA运维的数据库配置备份设置加密，密码仅由业务同事保存，必须输入密码才能进行数据恢复。 二、RMAN加密方法 rman对备份提供3种常见的加密方法： 仅口令加密 基于wallet的加密 混合加密（口令加密+wallet加密） 考虑业务同事需要保存密码，而基于wallet的加密配置较复杂、DBA又难以接触到相关服务器，仅口令加密方式较为合适。 设置方法 SETENCRYPTIONIDENTIFIEDBY'密码'ONLY; 注意事项： 口令务必要记得，否则后续无法会恢复数据 由于set命令在单个rman会话中生效，不能作为全局设置，因此需要在rman备...

如何找到postgres中疯狂增长的wal日志的语句 一、定位每天DML量最大的表 pg_stat_all_tables视图的n_tup_ins,n_tup_upd,n_tup_del，这些数据都是累计值，所以要在不同时间点获取数据快照并取delta值。定时任务设置是一天一取，时间可以自行调整。 viget_table_dml_cnt.sh !/bin/bash TODAY=`date+"%Y%m%d"` YTD=`date-d'-1day''+%Y%m%d'` psql<<EOF createtabletable_dml_$TODAYasselectdate_trunc(...

函数是一个脚本代码块，可以为其命名并在代码中任何位置重用。要在脚本中使用该代码块时，只要使用所起的函数名就行了（这个过程称为调用函数）。 一、基本的脚本函数 1. 创建函数 有两种格式可以用来在bashshell脚本中创建函数。函数名必须是唯一的，否则可能会有问题。如果重定义了函数，新定义会覆盖原定义，且不会产生任何错误消息。 第一种格式采用关键字function，后跟分配给该代码块的函数名。 functionname{ commands } 第二种格式更接近于其他编程语言中定义函数的方式，函数名后的()表明正在定义一个函数。 name(){ commands } ...

一、问题现象 1.连接数飙升 8.218点起，主库活跃连接数显著飙升，至10:30左右逼近数据库最大连接数 平时活跃会话平均数约31 问题期间活跃会话平均数高达322，超出正常10倍以上 从连接用户看，其中一个用户明显高于其他 12-16点，将该用户连接切换至从库，从库连接数显著飙升 16点后，cs_rw用户连接切换回主库，业务量较低时，平均连接数也达到135，远高于之前的高峰期值 2.服务器负载飙升 由于活跃连接数暴增，查询量明显增加，服务器负载飙升 同时查询出现大量LWLock等待，执行速度降低，加剧连接堆积问题 二、问题分析与处理 1.问题分析 与业务共同分析，...

一、需求背景 业务中有一部分临时表，其特点如下： 仅用于数据中转或临时存储 其数据不需在从库访问（但主从切换后，新主库需要能访问到临时表表结构） 数据量通常较大（千万级、亿级）     计划将这部分临时表改为unloged表，减少WAL日志生成量。 二、 unloged表简介 1. unloged表特性 文档介绍主要如下： 不写WAL日志，因此性能优于普通表 从库查询unloged表会报错 非crash安全：数据库crash或以immediate方式关闭，unloged表数据会被自动清空 unloged表上的索引和序列也会自动...