- 博客(433)
- 资源 (1)
- 收藏
- 关注
RSS订阅原创 PostgreSQL将在15版本支持MERGE INTO语法
MERGE语法可以参考如下：https://www.postgresql.org/docs/devel/sql-merge.html[ WITH with_query [, ...] ]MERGE INTO target_table_name [ [ AS ] target_alias ]USING data_source ON join_conditionwhen_clause [...]where data_source is{ source_table_name | ( source
2022-05-04 16:06:20
3231
原创 Postgresql逻辑复制报错could not start WAL streaming: ERROR: replication slot “x“is active for PID xxx
先看日志错误：#发布端报错如下：2022-04-01 10:18:23.812 CST,"postgres","hank",4666,"10.4.9.250:49138",624660ef.123a,1,"idle",2022-04-01 10:18:23 CST,10/0,0,ERROR,55006,"replication slot ""sub"" is active for PID 4575",,,,,,,,,"sub"2022-04-01 10:18:28.826 CST,"postgres"
2022-04-01 11:18:52
3672
原创 Postgresql审计插件pgaudit使用说明
最近由于审计，rds的审计成了问题，因为很多权限没开放出来，但是很多云厂商提供了pgaudit插件，这里简单介绍下吧，虽然没有达到想要的预期。pgaudit和postgresql版本兼容性匹配列表：pgAudit v1.6.X is intended to support PostgreSQL 14.pgAudit v1.5.X is intended to support PostgreSQL 13.pgAudit v1.4.X is intended to support PostgreSQL
2022-03-28 16:54:09
2884
转载 Postgresql查询优化相关插件
进行SQL优化之前，我们要找出相应的SQL，如何找，可以通过以下一些插件进行抓取。pg_stat_statements 可以根据执行SQL的时长找出相关查询语句，具体使用可以参考https://www.postgresql.org/docs/current/pgstatstatements.htmlpg_stat_kcache是一个用于检测系统和用户CPU使用比较高的模块。如果你的系统检测CPU使用率比较高，则可以使用该模块来查看是哪些查询导致了这种情况。需要单独下载使用：http://a
2022-03-07 15:30:40
985
原创 Postgresql动态共享内存类型
linux为多个进程通信提供了不同的IPC机制，如:System V , POSIX 和 MMAP，所以Postgresql共享内存管理也支持以上类型。在Postgresql中可以使用dynamic_shared_memory_type参数指定共享内存类型。默认使用posix,修改参数后需要重启数据库。dynamic_shared_memory_type = posix # the default is the first option
2022-03-02 18:06:47
2507
原创 一款分析linux上应用程序分析的工具----0x.tools
0x.tools是一个用于分析Linux上应用程序性能的开源实用程序。部署简单且依赖性小。无需升级操作系统、安装内核模块、繁重的监控框架、Java代理或数据库。这款工具还适用于十多年前的Linux内核，如14年前的2.6.18版本。包含的工具：安装和使用：$ git clone https://github.com/tanelpoder/0xtools$ make$ make install具体使用示例请参考：https://0x.tools/#installation–usage$ x
2022-03-01 11:10:05
1628
原创 Postgresql中如何执行动态DDL语句
什么是动态SQL在执行PL/pgSQL函数或SQL时，有时需要生成动态命令，因为命令涉及不同表或数据类型，仅在运行时才能确定具体对象或值。这时就很适合使用动态SQL。这里不过多解释，看下实例就很好理解了。生成动态SQL的几种方式使用 || 进行拼接#查看序列当前使用值并加1postgres=# select 'ALTER SEQUENCE '||sequencename||' RESTART '||last_value+1 from pg_sequences where last_value
2022-02-24 11:17:48
1841
原创 Postgresql中TCP keepalive相关设置使用
数据库连接描述提起TCP keepalive相关的设置，就避不开数据库连接这个话题，目前大部分使用环境，数据库连接都是长连接，也就是说连接可以复用。由于数据库建立连接和HTTP连接还不一样，HTTP是无状态的，新建连接的代价比较小，但是和数据库建立连接代价高多了，因为数据库连接不是无状态的;比如，在关闭连接的情况下，将会丢失打开的事务、临时表和prepare语句。而且连接进程都是由postgres主进程fork,fork也有相应的代价，所以数据库有一些空闲会话是正常的，但是保持太长时间大量的空闲会话也是
2022-02-22 17:48:39
5719
原创 Postgresql Extended Statistics(自定义统计信息)
正常情况下，数据库只会按单列收集统计信息，无法捕捉到任何关于跨列的相关性信息。扩展统计信息是从10版本引入，可以通过命令 CREATE STATISTICS 创建多列相关的统计信息。因为一个表一般有多个列，多列的任意组合很大，所以自动收集多列统计信息不现实，而我们可以根据自己的需求自己来创建多列的统计信息，这样可以让相关查询执行计划更加准确。下面举例介绍几种扩展统计的几种类型：Functional DependenciesMultivariate N-Distinct CountsMultiva
2022-01-25 20:20:13
1038
原创 Postgresql主备切换后逻辑复制槽处理的几种方式
到目前为止的版本，PG原生版本不支持逻辑复制槽的failover，这使得以下几种使用情况必须注意：在主备切换之后，需要手工去创建复制槽，而且订阅端极有可能丢失数据，因为创建复制槽的过程会有时间间隙。pg_upgrade进行大版本升级时,slot也会被清理这里我给出几种方式可以进行slot的failover，有喜欢的可以尝试一下可以使用拷贝文件的方式，正常情况standby节点pg_replslot目录下没有文件，需要从主节点拷贝该文件目录到备节点，然后重启备节点即可，在备节点提升为主节点之前，
2022-01-17 18:06:03
2936
原创 Postgresql之Covering Indexes介绍
Covering Indexes for B-trees (INCLUDE)从11开始引入Covering indexes for GiST (INCLUDE) 从12开始引入目前仅支持以上类型的索引(B-trees,GiST)。为什么使用覆盖索引呢？覆盖索引可以不需要回表，即减少了IO，使用index-only scan扫描即可拿到需要的数据。通过下图，我们可以比较和普通的B-tree的区别如图：左边是索引，后面是table,最底层的叶子节点，是一个双向链表。查找数据的时候，我们就会遍历这
2022-01-11 17:32:58
629
原创 Postgresql存储引擎zheap介绍以及测试
这款存储引擎也是Cybertec公司开发的，目前处于测试阶段，因为Postrgesql的表都是以heap形式存储的，MVCC机制中对删除或者更新的行使用的是根据事物号设置可见或者不可见，所以导致Postgresql中的表在频繁更新和删除的情况下，极易膨胀。而zheap则是类似引入了undo的技术，oracle和mysql就是使用此技术，所以不会有表膨胀的情况。有兴趣的同学可以安装试玩一下，生产千万不要用哦，bug很多。github地址：https://github.com/cybertecpostgr
2021-12-30 17:18:50
3126
1
原创 Postgresql在线分区插件之pg_rewrite使用
在使用数据库的过程中，有一些表开始无法估量大小，后面表变大后，需要做分区表，那么是一件比较烦人的事情，现在好了，有了cybertec公司这款插件，轻松实现。说到这个功能，Oracle从很早就有了，叫在线重定义，而且锁粒度也很小，DML不影响。这个功能还是比较实用的，现在PG终于也有了类似的功能。前置条件：支持13或者13以后的版本设置PG_CONFIG环境变量指向PostgreSQL安装的PG_CONFIG命令make && make install配置postgresql.c
2021-12-30 14:00:54
1304
3
原创 Postgresql之amcheck验证索引完整性
Postgresql提供了该插件验证索引或者是表的逻辑一致性。比如系统升级后，collate和原来不一致，数据库里索引的顺序和该collate不匹配。create extension if not exists amcheck;set statement_timeout to 0;do $$declare r record; sql text; ts_pre timestamptz; e_message text; e_detail text; e_context text;
2021-12-20 14:58:26
1114
翻译 Postgresql鲜为人知的一些功能(二)
接Postgresql鲜为人知的一些功能(一)，继续介绍下面的一些功能。引用$符号如果我们在数据库中存储一段文本，如果文本中有单引号，那么我们需要’来转义。db=# SELECT 'John''s Pizza'; ?column────────────── John's Pizza但是，文本中如果有很多需要的转义字符的时候，那将变的很麻烦，pg提供了$$符号的方法，例子如下：db=# SELECT $$a longstring with new linesand 'single
2021-12-08 16:51:31
1436
翻译 Postgresql鲜为人知的一些功能(一)
无意之中看到一篇文章，感觉很有意思，特翻译记录一下，题目写的是鲜为人知，但是对于一些玩PG的老鸟，其实并不鲜为人知。个人感觉文章太长不便阅读，故分为两篇文章描述。目录upsert的时候获取更新和插入的行数列级权限多个模糊匹配在不推进的情况下查找序列的当前值将\copy与多行SQL一起使用主键自动生成的方式生成数据透视表的两种方法$符号的引用数据库对象的注释为每个数据库保留一个单独的psql_history保留关键字大写sleep间隔函数说明获取没有子查询的组中的第一行或最后一行
2021-12-07 20:57:45
1234
原创 Postgresql中使用varchar(n)比varchar()和text节省空间吗？
我们看下PG中的字符类型：名称描述character varying(n), varchar(n)有长度限制的变长字符类型character(n), char(n)定长字符类型,插入字符长度不够，空格补充text没有长度限制的变长字符类型简要说明：varchar(n) 和 char(n) 分别是character varying(n) 和 character(n)的别名，如果不指定n值，那么character varying可以存储任意尺寸的字符串，有点类似于
2021-12-02 15:28:13
3728
原创 Postgresql13.1增量排序BUG
13版本新特性新增了增量排序，本来是为了排序提升性能，但是13.1版本存在BUG实例如下：以上取值明显是错误的我的测试环境13.4已经修复改BUG大概来说，由于索引列的值出现的次数超过DEFAULT_MAX_FULL_SORT_GROUP_SIZE(64)，则会调用switchToPresortedPrefixMode函数。在这个函数中，在读取最后一个元组并判断它不属于前一个组之后，就会从for循环中中断。然而，由于lastTuple被设置为true，后续进程将错误地认为该元组已被放入 pref
2021-11-26 18:04:59
757
原创 Postgresql插件之pg_gather
github地址： https://github.com/jobinau/pg_gather是一款使用SQL脚本收集postgresql数据库配置和性能相关指标的工具。使用很简单：操作用户建议有superuser, rds_superuser角色权限采集数据psql <connection_parameters_if_any> -X -f gather.sql > out.txt导入数据sed -e '/^Pager/d; /^Tuples/d; /^Output/d; /
2021-11-17 15:15:21
1004
转载 Mysql之binlog2sql介绍
一款可以解析binlog日志到sql的工具，如下：https://github.com/danfengcao/binlog2sql
2021-11-02 17:32:07
233
翻译 Postgresql几种定时调度任务工具的比较
让我们看一下以下的比较表。如果想了解更多相关的信息，可以查看相关以下工具的相关文档。pg_timetable: https://github.com/cybertec-postgresql/pg_timetablepg_cron: https://github.com/citusdata/pg_cronpgAgent: https://github.com/postgres/pgagentjpgAgent: https://github.com/GoSimpleLLC/jpgAgentpgbuc
2021-11-02 15:05:25
1293
翻译 Postgresql之产生序列间隙的几种情况
由回滚导致的序列间隙如下例子：插入2的时候失败，后续成功插入的值为3第二个语句回滚了，但是序列值2没有回滚，形成了一个间隙CREATE TABLE be_positive ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, value integer CHECK (value > 0)); -- the identity column is backed by a sequence:SELECT pg_get_ser.
2021-11-01 15:56:40
709
原创 Postgresql行安全策略(Row Security Policies)
PG行安全策略其实就是对不同用户，不同行数据的可见性，和可修改性。在我看来其实就是权限的维度不同，PG在9.5之前提供表级别，列级别的权限控制，9.5之后添加行级策略，这样用户就可以更加灵活的控制数据，对不同用户之间的数据隐私起到了保护作用。先举一个列权限的例子,也可以通过视图的方式进行权限限制：#一张表sex\c postgres postgres postgres=> \d sex Table "public.sex" Column |
2021-10-29 14:16:30
1318
原创 Postgresql之TOAST技术解析
那么什么是TOAST呢？先抛出一个概念解释：TOAST是The Oversized-Attribute Storage Technique的缩写，直译比较难理解，我们这里叫行外存储技术。因为在PostgreSQL中,一条记录不能跨PAGE存储，毕竟默认一个PAGE只有8K，不能存储很大的值，所以存储大字段的值，就使用TOAST存储。它和原表的数据是分开存储的。TOAST存储的表并不能单独创建，只有当普通表包含了main,extended或external存储格式的字段时，系统会自动创建一个和普通表关联的TO
2021-10-25 16:12:54
1679
原创 Postgresql中order by limit 索引选择错误导致查询很慢
开发今天发来一条SQL,说执行很慢，出不来结果，如下：select id, user_id, k_money_value, channel, status, add_time, note from hist_kmoney_record where user_id=133423946 order by id desc limit 10;表大小，记录数，索引信息如下:melotpay=# select count(id) from hist_kmoney_record ; count --
2021-10-12 18:21:30
3719
1
原创 Postgresql插件pg_bulkload
pg_bulkload为用户提供了高速的数据载入能力，绕过了wal和shared buffer,所以在效率上比copy更高。当然也有引入了一些新的问题，使用该插件有一些限制。使用pg_bulkload需要注意, 如果选择了direct模式导入, 会绕过wal, 以前的备份就无法使用wal，当然也无法基于过去的备份恢复到载入数据之后的时间点，PITR的归档恢复也就不可用， 所以必须在bulk load后做一次全量备份. 而且如果有逻辑订阅、物理standby, 都无法接受这些bulk load写入的数据，所
2021-09-27 10:42:20
773
原创 Postgresql修正序列插件之pg_sequence_fixer
如下例子，手动插入数据后，再使用默认的序列会报错，序列不能自动识别表中已使用的最大值。#创建测试表<16:28:43><db:hank><user:postgres><pid:30677>=# CREATE TABLE t_test_seq ((# id serial PRIMARY KEY, (# info text(# );CREATE TABLE#使用序列自动填充，插入两条数据<16
2021-09-07 16:58:37
432
原创 Postgresql并行计算配置
并行计算进程相关参数：#worker总进程数,默认8,修改后需要重启数据库max_worker_processes#所有session同时并行计算最大并行进程数，默认8max_parallel_workers#单条查询中，每个node最多允许开启的并行计算的进程数，设置为0，表示禁用并行计算max_parallel_workers_per_gather#也可以单独设置表级别并行度 alter table tb1 set (parallel_workers=4);ALTER TABLE
2021-08-19 15:32:17
1028
原创 Red Hat Enterprise Linux中free输出和/proc/meminfo中值的对应关系
free -k 输出 (RHEL 5 and RHEL 6): total used free shared buffers cachedMem: 7778104 2971960 4806144 0 211756 1071092-/+ buffers/cache: 1689112 6088992Swap: 4194296 0 41
2021-08-03 14:11:41
436
原创 Postgresql报告收集之开源工具pgmetrics介绍
pgmetrics是一个开源,零依赖的二进制工具，用于收集和报告运行中的PostgreSQL服务器上的各种统计数据和信息，以帮助故障排除、监控以及自动化。并可以以文本格式显示，或者以JSON和CSV格式导出。可以收集以下版本信息：PostgreSQL versions：9.3到13版本可以收集安装极其简单：可参考：https://pgmetrics.io/docs/install.htmlwget https://github.com/rapidloop/pgmetrics/releases/do
2021-08-02 17:13:58
433
原创 Postgresql使用ssh隧道连接数据库
如果数据从本地导入到远端的话，可通过建立ssh隧道，使用本地端口即可连接远端数据库，使用起来更加方便快捷，命令如下：#-C表示请求压缩所有数据，默认使用gzipssh -C -L local_port:localhost:remote_port remote_user@remote_ip实例：#建立ssh隧道ssh -C -L 666666:localhost:1921 root@8.1xx.2xx.9x#另外在本地shell使用该端口可连接远端的数据库psql -h 127.0.0.1
2021-07-23 15:03:47
1776
1
原创 Postgresql13之FETCH FIRST ROWS … WITH TIES展示打结的行
也算是13的新特性吧，是看了一篇文章觉的很有意思，所以拿出来做下实验。相信大家在使用翻页的时候，一般都会使用limit offset子句，但是其实这个是不符合SQL标准的，我们看看符合SQL标准的FETCH FIRST 子句如何使用。先看下limit offset子句LIMIT { count | ALL }OFFSET startcount表示返回的最大行数start指定跳过的行数如果count为null,那么就是显示所有行，即limit all#创建测试表，插入数据，select语句均
2021-07-22 20:24:14
947
原创 Postgresql LATERAL例子
lateral 子查询可以支持横向连接外面的表，在FROM 或者JOIN子句的子查询里面可以关联查询LATERAL前面的FROM子句或者JOIN子句，通过例子看如何使用：#创建测试表并初始化数据CREATE TABLE t_product AS SELECT id AS product_id, id * 10 * random() AS price, 'product ' || id AS product FROM generate
2021-07-21 15:40:24
1145
原创 Postgresql之pgcompacttable
PG表膨胀的解决另一个工具，不过看时间，已经很久没更新维护了。安装前需要安装pgstattuple插件，因为要基于该插件去查找膨胀的表，所以如果有大表的情况下，因为pgstattuple会扫全表，所以会比较耗时，这个是使用的时候一定要注意一下。原理：从表的头部填充新的行，在普通vacuum时候，截断表末尾的空page,达到收缩空间的效果。不需要占用额外的空间，使得表文件更加紧凑。而且不会加比较重的锁。对性能影响较小。具体安装使用如下https://github.com/dataesgret/pgc
2021-07-20 10:52:36
481
原创 Postgresql修改时区
首先修改时区的参数为timezone如：<10:28:18><db:postgres><user:postgres><pid:26229>=# show timezone; TimeZone ─────────────── Asia/Shanghai(1 row)如果是自建的数据库，那么修改配置文件postgresql.conf 或者通过数据库命令修改均可以，修改后，只需reload即可。<10:29:49><db
2021-07-16 10:44:55
2910
原创 Postgresql 原生pub/sub订阅端触发器如何控制
参数和表设置前面一篇文章已经介绍过，但是这里还是有一些和pglogical不一样的地方。如果表设置为default,无论session_replication_role设置为什么，都不会触发触发器如果表设置为replica,无论session_replication_role设置为什么，都会触发触发器如果表设置为always,无论session_replication_role设置为什么，都会触发触发器如果表设置为replica,无论session_replication_role设置为什么，都
2021-07-13 10:43:26
596
原创 Postgresql pglogical订阅端触发器如何控制
如果我们使用pglogical，表上有触发器，在复制的时候，肯定会考虑复制的时候触发器是否触发，根据官方文档，这个是可以自己控制的。如下：alter table xxxxx DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER该命令会加一个SHARE ROW EXCLUSIVE锁。表上的触发器由session_replication_role参数控制，值可以是 origin (默认), replica和local，分别有以下几种情况如果不写 [ REPLICA
2021-07-13 09:33:04
476
原创 Postgresql psqlrc文件使用
psqlrc文件作用：psql在连接数据库之前，首先会读取并执行改文件中的命令，然后连接到数据库，如果加上-X参数，则跳过该文件。所以通过该文件可以设置客户端或者是服务端的风格。系统范围内，改文件可以通过pg_config --sysconfdir命令查看，默认在etc相关的可执行文件目录，目录的名字也可以通过配置环境变量PGSYSCONFDIR来设置。用户个人使用的文件名是.psqlrc，可以用过环境变量PSQLRC配置。一般在用户home目录下，如~/.psqlrc。我们看几个常用的元命令，此篇
2021-07-07 18:09:36
1140
原创 Postgresql逻辑复制DDL插件pgl_ddl_deploy
pgl_ddl_deploy可以支持DDL复制，不过目前版本只支pg13持原生的逻辑复制，13之前的版本，需要安装pglogical,也就是需要部署两个插件。所以对于使用pg13版本的同学，可以单独使用该插件，以后就再也不用担心字段变更的时候，两边表字段不一致，导致复制槽false了。部署安装实例可参考如下：https://github.com/enova/pgl_ddl_deploy...
2021-07-05 16:12:47
726
原创 Postgresql从MD5密码验证改为SCRAM-SHA-256
随着密码学技术的发展，MD5哈希算法构造的密码越来越不安全，所以，PG顺应发展，从10版本开始支持了SCRAM-SHA-256加密算法，因为使用的新的哈希算法。使得在暴力破解的时候花费的代价更加昂贵。那么，接下来，在PG中看一下如何从MD5切换到SCRAM-SHA-256。首先，太老的的驱动不支持SCRAM-SHA-256，都会报错，如JDBC,会报The authentication type 10 is not supported。那么就需要升级驱动。接下来就是数据库服务端修改配置:#修改post
2021-06-28 17:57:54
4711
GREENPLUM管理向导
2018-04-28
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人