postgresql
文章平均质量分 78
PostgreSQL运维技术
这个作者很懒,什么都没留下…
展开
-
pg常用工具之pg_waldump
mysql中,读取binlog,可以利用mysqlbinlog工具。那么pg中是否可以对wal日志进行解析呢?答案是可以的,工具就是pg_waldump。原创 2022-10-11 18:57:06 · 3144 阅读 · 1 评论 -
postgresql的死锁检测机制
死锁检测,是pg的一种自动检测机制,可以发现两个或者多个session之间对互斥资源的申请造成的死锁,并且可以随机将其中一个事务回滚掉,以解除死锁。特殊情况下,如果我们想定位死锁问题,我们可以先调大这个值,让因死锁而“挂起“的会话,维持“挂起”状态的时间更长点,以便我们定位具体SQL。死锁只有在锁存在的情况下才会发生,所以缓解死锁的一个策略就是减少锁的使用,比如说避免大事务,尽量减少显式锁的使用,减小锁的粒度等。一般我们要确保使用数据的所有应用程序,以一致的顺序获取多个对象上的锁,防止出现互相等待的情况。.原创 2022-07-29 14:07:13 · 3123 阅读 · 0 评论 -
数据库术语中的基数和选择性
在数据库中,某一列的唯一键(distinct Keys)的数量叫作基数。比如性别列,该列只有男女之分,所以这一列基数是2。主键列的基数等于表的总行数。查看某个列的基数。选择性列的基数与表中总行数的比值再乘以100%就是某个列的选择性。列的选择性可以衡量数据库索引能够帮助缩小对表中特定值的搜索范围的程度。查看某个列的选择性。我们知道索引最重要的目的之一是尽可能地缩小匹配行的初始候选值,从而减少io,提升查询性能。所以通常来说索引的选择性越高则查询效率越高,因为选择性高的索引可以让数据库在查找时原创 2022-06-15 18:31:36 · 1772 阅读 · 1 评论 -
postgresql之提升distinct查询性能
在pg中,distinct关键字与select语句一起使用,用于去除重复记录。然而postgresql目前缺乏从有序索引中有效提取唯一值列表的能力, 它需要扫描整个索引来找到唯一的值。随着表的增长,这个操作会很慢。对一个大表(几百万行甚至更大)的某个字段进行distinct操作,其执行计划可能如下图所示正如我们所知,HashAggregate通过在内存构建数据的哈希表来对数据进行分组,而这通常会打来较大的性能损耗。针对distinct或group by慢的问题,mysql中提供了Loo.原创 2022-04-02 22:43:14 · 4727 阅读 · 2 评论 -
PostgreSQL之双冒号(::)的含义
对于一些从mysql转过来或不熟悉pg的开发者来说,可能在看到SQL中使用"::"符号时会表示疑问,这个符号表示什么含义呢?近期有被问到这个问题,所以还是记录一下。"::" 符号其实是一个强制类型转换符,作用等同于CAST。在很多情况下,我们需要将一种数据类型的值转换为另一种数据类型。那么我们就可以使用它来进行转换。"::"的语法格式为:expression::type用法示例:postgres=#SELECT'100'::INTEGER,'01-O...原创 2021-11-07 11:41:06 · 13498 阅读 · 2 评论 -
PostgreSQL之外键约束
外键又称外键约束,Foreign key constraint。外键是一个表中,用于标识另一张表中行的一个字段或多个字段。包含外键的表称为引用表,外键引用表称为被引用表。所谓外键约束是指引用字段必须在被引用字段中出现。被引用字段需要是唯一约束或主键。外键约束维护引用表和被引用表之间的参照完整性(referential integrity)。外键约束可以在创建表时定义,也可以在表创建后通过alter table语句定义。定义外键约束的完整语法<span style="col..原创 2021-09-28 17:51:19 · 8516 阅读 · 0 评论 -
postgresql之ssl
1、PostgreSql之SSL ?PostgreSQL支持使用SSL连接加密客户端/服务器通信,以提高链路安全性。这要求在客户端和服务器系统上都安装OpenSSL,并且在构建时启用PostgreSQL中的ssl支持(使用源码安装时的--with-openssl参数)。2、什么是OpenSSL?OpenSSL是一个工具包,用于Transport Layer Security(TLS)和Secure Sockets Layer(SSL)协议。它也是一个通用的密码学库。Open...原创 2021-05-29 16:41:54 · 2536 阅读 · 1 评论 -
Error: rpmdb open failed 解决方法
解决:rpm --rebuilddbyum clean all原创 2021-05-20 19:41:13 · 316 阅读 · 0 评论 -
PostgreSQL之TOAST
1、什么是TOAST?TOAST,全称是The Oversized-Attribute Storage Technique, 超大属性存储技术,顾名思义,就是说Pg中超长字段在数据库中的存储方式。主要用来应对物理数据行超过数据块(页)大小的场景。在PG中,页(或者叫block)是数据在文件存储中的基本单位,它的大小是固定的,并且只能在编译期指定,之后无法修改,默认的大小为8kb。同时PG不允许一行数据跨页存储。因此页大小就是行大小的硬上限,因此,数据库就无法直接存储很大的字段值,为了克服这一限制原创 2021-05-08 16:43:57 · 1537 阅读 · 2 评论 -
PostgreSQL之BgWriter
最近搞了一个公众号PostgreSQL运维技术,欢迎来踩~悄悄放一张:PostgreSQL运维技术1、bgwriter是什么?Bgwriter是一个Background Writer进程的简称。Bgwriter会将缓存中一些已经更改的数据(脏缓冲区)写入到磁盘。之前我在checkpoint的文章里介绍过,checkpoint也是会将缓存中的脏数据写入到磁盘。2、那么checkpoint与Bgwriter的区别是什么?checkpoint是以特定的时间...原创 2021-04-29 14:46:25 · 1791 阅读 · 0 评论 -
PostgreSQL之pg_stat_replication最新版
参考:ahttps://www.cybertec-postgresql.com/en/monitoring-replication-pg_stat_replication/1、什么是pg_stat_replication?pg_stat_replicatio是一个视图,主要用于监控PG流复制情况。2、它包含哪些信息?test=# \d pg_stat_replicationView "pg_catalog.pg_stat_replication"Column ...原创 2021-04-25 15:17:10 · 1260 阅读 · 0 评论 -
PostgreSQL之FULL_PAGE_WRITES
1、full_page_write是什么?官网中,对它的解释是:当这个参数打开时,PG服务器在一个检查点之后的页面的第一次修改期间将每个页面的全部内容写到wal中,这么做是因为在操作系统崩溃恢复期间正在处理的一次页写入可能只有部分完成,从而导致在一个磁盘页面中混合有新旧数据。在崩溃后的恢复期间,通常存储在wal中的行级改变数据不足以恢复这样一个页面。存储完整的页面映像可以保证页面被正确的存储,但是代价是增加了必须被写入wal的数据量。可参考:https://postgresqlco.nf/...原创 2021-04-24 11:50:48 · 1717 阅读 · 1 评论 -
第七章 PostgreSQL之HOT和Index-Only Scans
最近搞了一个公众号PostgreSQL运维技术,欢迎来踩~悄悄放一张:PostgreSQL运维技术本章描述了与索引扫描相关的两个特性,即HOT 和 Index-Only Scans。注:是The Internals of PostgreSQL第七章7.1. Heap Only Tuple (HOT)PG在8.3版本中实现了HOT, 主要是为了当要更新的行存储在与旧行相同的表页中时,有效地使用索引和表中的页面。Hot也减少了VACUUM处理的必要性。7....原创 2021-04-22 16:55:12 · 360 阅读 · 0 评论 -
PostgreSQL中的VACUUM
最近搞了一个公众号PostgreSQL运维技术,欢迎来踩~悄悄放一张:PostgreSQL运维技术我在之前的一篇介绍MVCC的文章中提到,MVCC有事务ID回卷的问题,它的解决方案是VACUUM。但是没有展开讲。这章的话,会详细地介绍下VACUUM。VACUUM的两个主要任务是删除死亡元组和冻结事务ID。为了删除死亡元组,VACUUM提供了两种模式,即Concurrent Vacuum和Full Vacuum。Concurrent Vacuum通常简称为VACUUM。...原创 2021-04-15 09:09:51 · 9376 阅读 · 0 评论 -
PostgreSQL之Shared_buffer、wal_buffers等参数
Shared_buffer1、什么是shared_buffer?shared_buffer是数据页缓冲区。2、为什么要引入shared_buffer?在数据库系统中,我们主要关注磁盘IO, 大部分oltp工作负载都是随机IO,因此从磁盘获取非常慢。为了解决这个问题,postgre将数据缓存在RAM中,来提高性能。postgresql在查询前,会先查找shared_buffer的页,如果命中,就直接返回,避免从磁盘中查询。3、为什么要修改shared_buffer参数的值?由于P.原创 2021-04-12 19:42:07 · 3902 阅读 · 0 评论 -
linux之内存管理
1、内存管理主要做什么?主要负责内存的分配和回收。2、操作系统的内存管理机制是什么?块式管理:将内存分为几个固定大小的块,每个块中只包含一个进程。如果程序运行需要内存的话,操作系统就分配给它一块,如果程序运行只需要很小的空间的话,分配的这块内存很大一部分几乎被浪费了。这些在每个块中未被利用的空间,我们称之为碎片。页式管理:把主存分为大小相等且固定的一页一页的形式,页较小,相对相比于块式管理的划分力度更大,提高了内存利用率,减少了碎片。页式管理通过页表对应逻辑地址和物理地址。段式管理:..原创 2021-04-07 10:06:06 · 168 阅读 · 0 评论 -
PostgreSQL之wal_keep_segments参数
wal_keep_segments:用于指定pg_wal目录中保存的过去的wal文件(wal 段)的最小数量,以防备用服务器在进行流复制时需要。(参数解释见:https://postgresqlco.nf/doc/zh/param/wal_keep_segments/)每个wal文件通常为16兆字节。如果连接到发送服务器的备用服务器滞后超过wal_keep_segment个文件,发送服务器可能会删除备用服务器仍然需要的WAL段,在这种情况下流复制连接将被终止。因此,下游连接最终也会失败。(但是,如果原创 2021-04-01 19:49:14 · 7595 阅读 · 2 评论 -
硬件性能调优(翻译)
原文:https://momjian.us/main/writings/pgsql/hw_performance/index.html是Bruce大神的一篇博客。注:原文中的图片都不能显示了,所以本文也没有...POSTGRESQL是一个由一群遍布全球的开发人员在因特网上开发的对象关系数据库。它是Oracle和Informix等商业数据库的开源替代品。POSTGRESQL最初是由加州大学伯克利分校开发的。1996年,一个小组开始在互联网上开发数据库。他们使用电子邮件来分享想法,使用文件.原创 2021-03-31 10:43:28 · 301 阅读 · 0 评论 -
Postgresql之checkpooint
1、checkpoint是什么?又名检查点,其实可以看成是个时间戳,在这个时间戳之前的所有脏数据都已经写盘。2、checkpoint的目的或者好处是什么?为了缩短崩溃恢复时间。3、怎么理解缩短数据库崩溃恢复的时间?在pg中,用户进行更新操作,并不会直接对磁盘上的数据文件进行io操作,而是先修改内存中的数据,然后写入wal文件,所以内存中就有会未刷到磁盘上的脏页,如果这个时候数据库突然宕机,这部分数据就会丢失了。那么数据库在重新启动时,就需要利用wal文件里的数据,对...原创 2021-03-30 13:27:59 · 370 阅读 · 0 评论 -
postgresql wal空间膨胀的可能原因
可能原因:1、未开启归档。或者开启了归档,但是archive_command没有生效。2、参数设置不当,max_wal_size和checkpoint_timeout、achive_mode、archive都可能造成wal空间膨胀。3、数据库中存在主库流复制环境。流复制环境中,主要靠wal日志进行主备同步,当主备环境之间差异过大,或者出问题后,造成主备没有同步,则会保留wal日志不进行删除。(如果有差异的话,wal_keep_segments参数会生效,它会为备库保留相应数量的wal日志文件、甚原创 2021-03-29 13:52:18 · 1523 阅读 · 1 评论 -
Postgresql之备库复制冲突
PostgreSQL 的基于流复制的物理备库是基于wal日志的物理块复制备库,允许开放只读的功能,但是由于主库可能不断的产生wal,这些wal可能会与备库的QUERY产生冲突。简单点来说,就是查询(query)与恢复(备库apply)冲突。可能出现冲突的情况?1、例如主库truncate a 表,备库查询a表。2、主库删除表空间,备库使用这个表空间产生临时文件。例如主库删除TBS,备库的一个大的查询需要写临时文件,并且这个临时文件是写到这个表空间的。3、主库在回收dead tuple的R原创 2021-03-23 13:09:39 · 371 阅读 · 0 评论 -
postgresql 13.0新特性
最近搞了一个公众号PostgreSQL运维技术,欢迎来踩~悄悄放一张:PostgreSQL运维技术原文:https://www.interdb.jp/pg/pgsql5.html典型的特性有:逻辑复制支持分区表Btree索引优化(引入Deduplication技术)增量排序(Incremental Sorting)支持并行VACUUM索引数据库管理下面分别看下逻辑复制支持分区表PostgreSQL 13 版本的逻辑复制新...原创 2021-03-22 13:39:08 · 654 阅读 · 0 评论 -
第五章 PostgreSQL中的并发控制
最近搞了一个公众号PostgreSQL运维技术,欢迎来踩~悄悄放一张:PostgreSQL运维技术原文:https://www.interdb.jp/pg/pgsql5.html本章主要介绍PostgreSQL中的并发控制。注:本文是The Internals of PostgreSQL的第5章(https://www.interdb.jp/pg/pgsql05.html)并发控制是一种当多个事务在数据库中并发运行时,它可以维护一致性和隔离性的机制。一致性和...原创 2021-03-20 19:34:40 · 971 阅读 · 5 评论 -
Azure云MySQL/PostgreSQL库的Python SDK使用示例
参考:https://docs.microsoft.com/en-us/python/api/overview/azure/postgresql-mysql?view=azure-python首先:pip install azure-mgmt-rdbmsdemo文件from azure.mgmt.rdbms.mysql import MySQLManagementClientfrom azure.mgmt.rdbms.mysql.models import *SUBSCRIPTION原创 2021-03-18 14:24:57 · 234 阅读 · 0 评论 -
Postgresql之分区表
参考:http://www.jasongj.com/2015/12/13/SQL3_partition/https://www.postgresql.org/docs/9.1/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION创建步骤1、创建主表。不用为该表定义任何检查限制,除非需要将该限制应用到所有的分区表中。同样也无需为该表创建任何索引和唯一限制。CREATE TABLE almart( date_key da原创 2021-03-10 13:40:32 · 237 阅读 · 1 评论 -
第十一章 PostgreSQL中的流复制
搞了一个公众号PostgreSQL运维技术,欢迎来踩~悄悄放一张:PostgreSQL运维技术原文:https://www.interdb.jp/pg/pgsql11.html同步流复制的特性是从9.1版本开始实现的,这种本地复制特性是基于log shipping,这是一种通用的复制技术。其中主服务器持续地发送wal数据,然后备库在本地对接收到的wal数据进行重放。这一章文字比较多,也是PG实现高可用(一主多从)的基石,很重要......这章包括以下几个主题:流...原创 2021-01-28 14:41:16 · 4411 阅读 · 0 评论 -
pg之使用pg_upgrade进行大版本升级
官网:https://www.postgresql.org/docs/current/pgupgrade.html描述pg_upgrade(以前称为pg_migrator)允许存储在PostgreSQL数据文件中的数据升级到更高的PostgreSQL主版本,而不需要进行主版本升级所需的数据转储/重新加载,例如从9.5.8升级到9.6.4或从10.7升级到11.2。对于较小的版本升级,例如从9.6.2升级到9.6.3或从10.1升级到10.2,它不是必需的。主要的PostgreSQL版本会定期添加原创 2021-01-19 17:10:59 · 2269 阅读 · 0 评论 -
第二章 PostgreSQL进程和内存体系结构
搞了一个公众号PostgreSQL运维技术,欢迎来踩~悄悄放一张:PostgreSQL运维技术本章总结了PostgreSQL的进程架构和内存架构。2.1. Process Architecture(进程架构)PostgreSQL是一个客户端/服务器类型的关系数据库管理系统,具有多进程架构,运行在单个主机上。多个进程协同管理一个数据库集群通常被称为“PostgreSQL server”,它包含以下类型的进程:postgres 服务器进程:postgres服务器进程...原创 2021-01-19 11:37:17 · 566 阅读 · 1 评论 -
第一章 PostgreSQL中的数据库集群、数据库和表
这一章主要介绍四个概念:数据库集群的逻辑结构数据库集群的物理结构表文件的内部布局读写数据元组的方法01—数据库集群的逻辑结构Pg中的集群,也即database cluster,是由PostgreSQL服务端来管理的一组数据库(database)的集合。注意这里是数据库(database)的集合,不是数据库服务(database servers)的集合。一个PostgreSQL服务器运行在单个主机上,管理单个数据库集群。Pg与大多数的关系型数据库一样...原创 2021-01-14 17:49:56 · 1408 阅读 · 0 评论 -
postgresql之shared_buffer
本文参考:https://www.cnblogs.com/wy123/p/13463806.htmlhttps://www.cnblogs.com/zhangfx01/p/10438735.html官网文档:shared_buffers(integer)Sets the amount of memory the database server uses for shared memory buffers. The default is typically 32 megabytes ..原创 2020-12-16 19:41:06 · 2353 阅读 · 0 评论 -
postgresql模拟批量数据插入
参考:https://www.pianshen.com/article/6204134526/创建表:CREATE TABLE t_test( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);模拟批量插入:inse...原创 2020-11-11 10:57:07 · 1039 阅读 · 0 评论 -
psql常用用法总结
连接数据库: su 数据库账号下(创建数据默认创建了数据库账号),执行 /u01/pgsql_20190102/bin/psql postgres -p3021 \l: 查看数据库,=show databases \l+ 显示数据库详细信息 select datname from pg_catalog.pg_database; \d: 查看表的情况,=show tables 也可用\dn, \d table= show create table, \d 索引名 查看索引情况, \du或dg:...原创 2020-11-11 10:10:45 · 807 阅读 · 0 评论 -
postgresql之ERROR: relation “pgbench_branches“ does not exist
postgresql在执行命令:pgbench -n -h /data -p3008 -U root-d table_space_test-c 16 -j 4 -T 5命令报错:ERROR: relation "pgbench_branches" does not exist解决:在执行上面语句之前先执行:pgbench -h /data -p3008 -U root -i -s 20 table_space_test...原创 2020-11-10 10:42:49 · 772 阅读 · 0 评论 -
图像识别的应用场景
智能楼宇中,根据人脸识别,识别员工为本大楼员工时,自动进行打卡,自动按工作流程设定电梯。智能酒店中,根据人脸识别,办理自动入住,根据会员等级自动对接专属服务等。电商行业中,根据图像识别、搜索类似商品。教育行业中,根据人脸特征,记录学生的听课状态(打盹、走神、小动作、举手等)。交通行业中,自动识别违规驾驶员。新零售行业中,根据人脸识别会员,实现到店提醒、导购引导、定制化运营等。公共交通中,实现刷脸支付。游戏行业中,虚拟现实相关游戏。参考...原创 2020-11-04 15:30:41 · 4096 阅读 · 0 评论 -
postgresql运维之远程迁移
背景:高可用架构版本。主备分别部署在机器A和B上,现在要将其分别迁移到机器C和D上。思路:1、首先根据源实例的备份(云盘上可用snapshot),创建一个mirror实例,mirror包含两个节点,分别部署在C和D上。2、在源实例主节点hba.conf中增加mirror主节点的ip的设置,允许源实例主节点接受来自mirror主节点的连接。3、mirror实例主节点,创建recovery.conf文件,设置primary_conninfo指向源主节点。启动mirror主节点,建立源实例主到mi原创 2020-06-30 18:01:35 · 294 阅读 · 0 评论 -
postgresql之replication slot
参考:http://mysql.taobao.org/monthly/2015/02/03/replication slot是什么?replication slots 是从 postgresql 9.4 引入的,主要是提供了一种自动化的方法来确保主控机在所有的后备机收到 WAL 段之前不会移除它们,并且主控机也不会移除可能导致恢复冲突的行,即使后备机断开也是如此。为了防止 WAL 被删,SLOT restart_lsn 之后的WAL文件都不会删除。(wal_keep_segments 则是一个人原创 2020-06-17 16:17:14 · 11031 阅读 · 0 评论 -
postgresql之根据lsn 获取 wal文件名
10.0及以后版本:pg_walfile_name()用法:postgres=# select pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn()), ...原创 2020-05-08 12:00:59 · 1677 阅读 · 0 评论 -
postgresql 12.0版本
pg12.0的recovery.conf官方12.0发行说明里对recovery.conf有如下声明:Move recovery.conf settings into postgresql.conf (Masao Fujii, Simon Riggs, Abhijit Menon-Sen, Sergei Kornilov)recovery.conf is no longer used...原创 2020-05-08 10:21:55 · 1583 阅读 · 0 评论 -
postgresql的时间线
参考:http://mysql.taobao.org/monthly/2015/07/03/https://wiki.postgresql.org/images/e/e5/FOSDEM2013-Timelines.pdf在做即时恢复时,为了能支持恢复到任意时间点,引入了时间线的概念。每当存档恢复完成时,就会创建一个新的timelineis来标识恢复后生成的一系列WAL记录。时间轴...原创 2020-05-07 11:55:39 · 594 阅读 · 0 评论 -
postgresql之判断主备
有几种方法:1、通过pg_controldata输出,主库Database cluster state,为in production, 备库为in archive recovery。主库:#/u01/pgsql/bin/pg_controldata -D/datapg_control version number: 1100Catalog version ...原创 2020-05-07 15:30:15 · 3226 阅读 · 0 评论