PostgreSQL
文章平均质量分 53
PostgreSQL号称“世界上最先进的开源关系型数据库“,是一个功能非常强大的、源代码开放的客户/服务器 对象关系型数据库管理系统(ORDBMS)。
这里主要介绍一些PostgreSQL的常用的技巧。
秦时明月之君临天下
道阻且长,行则将至。
展开
-
PostgreSQL锁和阻塞发现与处理
或者通过数据库日志(开启log_statements=‘all’,SQL审计)追踪事务中所有的SQL,分析事务之间的锁冲突。通过数据库日志(开启lock_timeout, log_lockwait参数)跟踪锁等待信息。state为idle in transaction,说明该会话执行完了但没有提交。如果觉得前面分步操作太麻烦,可以用下面的sql一次查出(比较长,可以建成视图)根据pid和relation找到阻塞源(granted=t)根据被阻塞/阻塞源的pid查正在执行的语句。找到所在会话执行提交或回滚。转载 2024-02-28 01:11:10 · 173 阅读 · 0 评论 -
PostgreSQL在Windows重装后重新恢复数据的方法
windows系统重装后我们的postgresql服务器就不会正常启动,因为在系统中注册的服务重装后已经没有了,但是你的postgresql不是安装在系统盘区上的,那么postgresql服务器的文件和数据就还是存在的,比如原来的安装目录是D:\PostgreSQL,那么我们怎么让服务器服务自动运行起来,这样我们的程序才能连接数据库了,而且数据还不会丢失。最简单的办法重装,把原来的文件全部删除,如果原来有数据备份,把备份数据恢复就可以了,那如果重装系统前没有备份数据呢?-D代表数据库文件目录 ,转载 2023-12-17 14:37:57 · 485 阅读 · 0 评论 -
查看PostgreSQL数据库进程占用内存的方法
使用top命令查看内存时,会发现有一些PostgreSQL进程占用了很大的内存,用ps aux常看各个进程的内存时,如果把各个进程的内存加起来时,发现超过了总内存,所以很困惑。转载 2023-11-26 01:06:05 · 851 阅读 · 0 评论 -
PostgreSQL将文本转换成固定的长整型
PostgreSQL将文本转换成固定的长整型原创 2023-10-24 23:47:38 · 426 阅读 · 0 评论 -
PostgreSQL如何查询表大小
查询 PG 表的大小通常需要使用函数/视图来实现,分为单独查询和批量查询的场景,下面简单列一下转载 2023-08-27 18:33:00 · 4160 阅读 · 0 评论 -
PostgreSQL could not identify an equality operator for type json
看这字面上的意思是:pgsql没有能够对json类型字段进行等值操作的操作符。因为这里面我用的是`UNION`,而不是`UNION ALL`,因此会有个去重的操作,而去重的话,就必需将它们进行等值比较,查看是否相等(重复);而pg没有对应的操作符能够支持json字段的该功能,因此报错了。原创 2023-08-20 16:27:22 · 945 阅读 · 0 评论 -
PostgreSQL-Character with value 0x09 must be escaped.
这个ASCII的值,是换行符。那这应该是因为json不支持换行导致的,我们将换行符转换成空格就行了。中文即使:值为0x09的字符必须转义。原创 2023-07-24 00:44:47 · 834 阅读 · 0 评论 -
PostgreSQL查找配置文件位置、数据所在目录
转自:https://blog.csdn.net/DongGeGe214/article/details/121489384。后面的参数即为配置文件所在位置。后面的参数即为数据目录,转载 2023-04-05 21:39:30 · 2821 阅读 · 0 评论 -
PostgreSQL常用的两个字符串分割函数
常用的两个字符串分割函数:regexp_split_to_table、regexp_split_to_array,具体作用其实显而易见,一个是分割成表,一个是数组。原创 2023-03-30 23:37:55 · 1703 阅读 · 0 评论 -
PostgreSQL之从表中随机取数据
从表中随机取一条数据原创 2022-12-10 17:45:05 · 2364 阅读 · 0 评论 -
PostgreSQL怎么查看数据库用户系统权限、对象权限
使用pg数据库可以通过如下方法查询用户的系统权限和对象权限。转载 2022-10-21 01:25:31 · 12507 阅读 · 0 评论 -
Navicat Charts Creator图表工具探索
Navicat Charts Creator是Navicat系统的一个软件,是用于制作图表的工具,功能很强大。我是在闲逛的时候发现的这个软件(提供 14 天 免费的全功能 Navicat 试用版),官网:。原创 2022-10-04 00:43:23 · 1289 阅读 · 0 评论 -
PostgreSQL事务ID回卷
在postgresql中,由于没有像oracle、mysql那样的undo来实现多版本并发控制,而是当执行dml操作时在表上创建新行,并在每行中用额外的列(xmin,xmax)来记录事务号(xmin为insert或回滚时的事务号、xmax为update或delete的事务号,注意xmin还会记录回滚时的事务号),以此实现多版本并发控制,当然基于此也会导致postgresql中一个比较常见的问题——表膨胀。当前事务只能看到比表上xmin事务号小的记录,txid(事务id)的最大值为32位,即2^32为429转载 2022-08-29 12:53:31 · 1491 阅读 · 0 评论 -
CentOS安装postgresql12-devel.x86_64报错-需要:llvm-toolset-7-clang >= 4.0.1
去搜了下,发现是需要安装。包,我第一想法就是安装它,然后再按照 就正常了。原创 2022-08-25 01:15:24 · 1857 阅读 · 2 评论 -
PostgreSQL命令行-psql竖式输出查询结果
psql 默认是水平输出查询结果,一条记录占一行。而且和 mysql 不同的是,它还不换行,如果一行内容太多,后面的就看不见了。如果想切换城竖式显示,只需要使用 \x 命令即可。原创 2022-07-17 00:46:31 · 2732 阅读 · 0 评论 -
PostgreSQL之如何进行SQL优化?
如何使用索引?(what 什么是索引?why 为什么需要索引?how 如何创建索引?when 什么时候走索引?)这篇文章会告诉你!原创 2022-07-09 15:03:31 · 8764 阅读 · 0 评论 -
PostgreSQL之如何敲开PG的大门?
PostgreSQL号称"世界上最先进的开源关系型数据库",在国内越来越受欢迎,那么我们应该如何去学习它呢?这篇文章介绍了如何用正确的方式敲开PG的大门。原创 2022-07-09 14:18:13 · 845 阅读 · 0 评论 -
PostgreSQL日期时间特殊值
记录一些PostgreSQL日期时间特殊值。原创 2022-07-02 23:08:53 · 449 阅读 · 0 评论 -
PostgreSQL插件-pg_stat_statements-安装和使用
该模块提供了一种跟踪服务器执行的所有 SQL 语句的规划和执行统计信息的方法。必须通过将模块添加到 中的shared_preload_libraries来加载该模块,因为它需要额外的共享内存。这意味着需要重新启动服务器才能添加或删除模块。此外,必须启用查询标识符计算才能使模块处于活动状态,如果将 compute_query_id 设置为 或 ,或者加载任何计算查询标识符的第三方模块,则会自动完成该计算。当 处于活动状态时,它将跟踪服务器所有数据库的统计信息。要访问和操作这些统计信息,该模块提供了视图 和 ,翻译 2022-06-23 21:40:43 · 3759 阅读 · 2 评论 -
PostgreSQL插件-pg_stat_statements-查找最耗费资源的SQL(Top SQL)
数据库是较大型的应用,对于繁忙的数据库,需要消耗大量的内存、CPU、IO、网络资源。SQL 优化是数据库优化的手段之一,而为了达到 SQL 优化的最佳效果,您首先需要了解最消耗资源的 SQL(Top SQL),例如 IO 消耗最高的 SQL。数据库资源分为多个维度、CPU、内存、IO 等,为能够从各个维度层面查找最消耗数据库资源的 SQL,您可以使用 pg_stat_statements 插件统计数据库的资源开销和分析 Top SQL。本文将通过示例介绍如何创建 pg_stat_statements 插件、转载 2022-06-12 22:18:57 · 1913 阅读 · 1 评论 -
【转发】PostgreSQL查询不走索引的情况
查询不走索引的情况:1、条件字段选择性弱,查出的结果集较大,不走索引;2、where条件等号两边字段类型不同,不走索引;3、索引字段 is null 不走索引;4、对于count(*)当索引字段有not null约束时走索引,否则不走索引;5、like 后面的字符当首位为通配符时不走索引;6、使用不等于操作符如:、!= 等不走索引;7、索引字段前加了函数或参加了运算不走索引;8,部分索引但查询条件包括不属于部分索引的数据如果where条件都没有以上所述,那么考虑优化器分析的统计信息陈旧..转载 2022-06-12 21:51:41 · 3648 阅读 · 0 评论 -
数据库语言(DDL,DML,DQL,DCL)
一、DDL(data definition language)数据定义语言:DDL语句不用commit数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如:CREATE TABLE(表)/VIEW(视图)/INDEX(索引)/SYN(同义词)/CLUSTER(簇)1、创建(create):create index,create tablespace……2、删除(drop,truncate):truncate删除整个数据,drop删除整个表(数据+表结构)两者都不用comm转载 2022-06-05 00:42:10 · 1907 阅读 · 0 评论 -
PostgreSQL设置字段默认值
--为表my_table 添加gid字段,设置默认值为1ALTER TABLE my_table ADD COLUMN gid INTEGER DEFAULT 1; --修改my_table 的gid 字段默认值为2ALTER TABLE my_table ALTER COLUMN gid SET DEFAULT 2; --删除my_table的gid字段的默认值ALTER TABLE my_table ALTER COLUMN gid DROP DEFAULT;--删除字段alter原创 2022-05-29 12:36:25 · 9301 阅读 · 1 评论 -
PostgreSQL数值类型不按照科学计数法格式输出
在pgsql里面,默认的数值类型,如果超出一定数值后,会按照科学计数法的形式进行显示(我使用Navicat,不清楚是不是软件限制),但是有时候我们不需要这样,而是希望直接进行显示,可以使用类型转换来实现这个目的。如下:求 bigint 的最大值:-- bigint SELECT 2^(8 * 8 - 1) -1; -- 9.223372036854776e+18SELECT (2^(8 * 8 - 1) -1)::varchar; -- 不管用SELECT (2^(8 * 8 - 1) -1):原创 2022-05-28 23:57:06 · 1837 阅读 · 1 评论 -
PostgreSQL序列
PostgreSQL序列相关-- 2022年5月28日20:45:57-- 查看数据库中有哪些序列-- r =普通表, i =索引,S =序列,v =视图,m =物化视图, c =复合类型,t = TOAST表,f =外部表select * from pg_class where relkind='S';select * from pg_class where relname='pg_sequence'; -- 表select * from pg_class where relname=原创 2022-05-28 23:49:57 · 765 阅读 · 0 评论 -
PostgreSQL:: FATAL: could not write init file
Navicat 连接 pgsql 时,报错了(之前都是好的):网上搜了下,原因是磁盘空间被用尽,需要清理磁盘空间。清理后,果然就连上了。原创 2022-05-24 21:20:16 · 895 阅读 · 0 评论 -
【转载】PostgreSQL执行计划
简介PostgreSQL是“世界上最先进的开源关系型数据库”。因为出现较晚,所以客户人群基数较MySQL少,但是发展势头很猛,最大优势是完全开源。MySQL是“世界上最流行的开源关系型数据库”。当前客户基数大,随着被Oracle收购,开源程度减小,尤其是近期单独拉了免费的MariaDB分支,更表明MySQL有闭源的倾向;至于两者孰优孰劣,不是本文要讨论的重点,在一般的使用中,没什么大的差别,下面我们只讨论PG中执行计划。执行计划pg在查询规划路径过程中,查询请求的不同执行方案是通过建立不同的路径来转载 2022-05-22 00:42:02 · 5719 阅读 · 0 评论 -
PostgreSQL视图和物化视图
我们常用说的视图一般是指 view,即普通视图;而物化视图则是materialized view(materialized 使物质化,使具体化(materialize 的过去式和过去分词))。两者都是视图,但是名称不一样,说明两者还有有所差异的。下面就来简单介绍一下PostgreSQL中的视图和物化视图的差异。...原创 2022-04-30 21:52:36 · 4788 阅读 · 4 评论 -
PostgreSQL · 最佳实践 · EXPLAIN 使用浅析(优化器,查询计划)
PgSQL · 最佳实践 · EXPLAIN 使用浅析背景在使用数据库时,经常会有开发者有这样的疑问:“我的表对应字段已经创建了索引,为什么这个SQL 语句执行还是这么慢?” 虽然数据库SQL 执行慢有很多原因,但是对于PostgreSQL DBA 来说,好像有个共识,遇到用户慢SQL优化的问题,先拿EXPLAIN 命令查看下对应的查询计划,从而可以快速定位慢在哪里。这就引出了本文的主角—PostgreSQL 的EXPLAIN 命令。EXPLAIN 语法在PostgreSQL 中,EXPLAIN 命转载 2022-04-24 22:59:30 · 2865 阅读 · 2 评论 -
PostgreSQL中的toast表
postgresql为“大字段“的物理存储提供了TOAST功能,通过合适的配置策略能够减少IO次数和扫描块数,进而提升查询速度。TOAST:The Oversized-Attribute Storage Technique特点:PostgreSQL采用固定页面大小(通常是8Kb,不象oracle在运行期间有多种选择),元组不能跨越多个页面,无法实现“大字段值“的直接存储。TOAST提供了解决方法,允许大的字段值被压缩或分裂为多个物理行。postgresql只为部分数据类型支提供TOAST支持,为支持转载 2022-04-24 22:55:35 · 869 阅读 · 0 评论 -
PostgreSQL非阻塞性创建索引CREATE INDEX CONCURRENTLY
正常的 create index 是会阻塞 dml 操作的,在生产环境需要添加 concurrently 参数。CREATE INDEX CONCURRENTLY idx_index_name ON TABLE_NAME ( COLUMN_NAME );CONCURRENTLY参数说明CONCURRENTLY当使用了这个选项时,PostgreSQL在构建索引时 不会取得任何会阻止该表上并发插入、更新或者删除的锁。而标准的索引 构建将会把表锁住以阻止对表的写(但不阻塞读),这种锁定会持续到索 引创建原创 2022-04-10 12:23:20 · 2429 阅读 · 1 评论 -
PostgreSQL数据库查询当前数据库、当前用户
查询当前数据库:sql语句:select current_database();查询当前环境所有数据库:select * from pg_database;查询当前用户:sql语句:select user; -- 或者select current_user;原创 2022-03-22 20:36:01 · 6279 阅读 · 0 评论 -
Navicat12连接PostgreSQL12数据库时报 ERROR:column p.proisagg dose not exist
连接Postgre数据库时报错在使用Navicat12和PgAdmin连接PostgreSQL11 版本数据库时,出现了以下错误。ERROR:column “proisagg” dose not exist…解决办法经调查发现,出现此错误的原因是当前使用的工具版本太低。需要升级Navicat和pgAdmin工具。可以升级到Navicat15或者是更高版本(当前应该是16了 https://www.navicat.com.cn/)升级之后就不会出现这个错误了。参考:error-column-p-原创 2022-03-16 11:39:58 · 3708 阅读 · 0 评论 -
PostgreSQL数据库插件Extension
查看当前服务器可用的Extension扩展列表postgres=# select name from pg_available_extensions;安装可用的Extension扩展postgres=# create extension pg_stat_statements ;删除Extension扩展,查看验证postgres=# drop extension pg_stat_statements ;postgres=# \dxList of installed exte.原创 2022-03-13 21:58:28 · 1568 阅读 · 0 评论 -
PostgreSQL查询用户密码&&密码解密&&修改密码
1、切换用户 postgressu postgres2、登录postgrespsql3、查看用户信息和密码SELECT rolname,rolpassword FROM pg_authid;这里创建了两个用户:admin和postgres,密码用MD5加密了4、在线MD5解密在线MD5解密:https://www.somd5.com/将加密后的密码复制到在线解密网(注意去掉md5前缀)解密后的密码是:postgrespostgres,本机设置的密码是postgres部分密码无法转载 2022-03-13 21:41:49 · 17377 阅读 · 0 评论 -
PostgreSQL系列生成函数generate_series
定义generate_series ( start integer, stop integer [, step integer ] ) → setof integergenerate_series ( start bigint, stop bigint [, step bigint ] ) → setof bigintgenerate_series ( start numeric, stop numeric [, step numeric ] ) → setof numeric从start到stop原创 2022-03-13 21:20:54 · 3857 阅读 · 5 评论 -
PostgreSQL查看版本信息
1.查看客户端版本psql --version2.查看服务器端版本2.1 查看详细信息select version();-- PostgreSQL 10.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit2.2 查看版本信息show server_version;-- 10.172.2 查看数字版本信息包括小版号SHOW server_version_n原创 2021-12-21 14:09:45 · 9381 阅读 · 0 评论 -
PostgreSQL查看某个表的所有索引信息
select * from pg_indexes where tablename='student';select * from pg_statio_all_indexes where relname='student';原创 2021-12-20 15:20:08 · 4449 阅读 · 0 评论 -
PostgreSQL的NUMERIC精确度问题
在对数据时,发现在使用统一计算公式的情况下,数据库计算出的结果和Excel计算的结果不一致。注:最后的计算结果精确到一位小数。案例一数据库计算:select -27.0943/30*15;select (-27.0943/30*15)::numeric(10,1);-- -13.54714999999999999995-- -13.5Windows计算器计算:-13.54715-13.5案例二在案例一的基础上,分子先精确到一位小数。数据库计算:select -27.1/30*原创 2021-11-29 13:00:06 · 3622 阅读 · 0 评论 -
PostgreSQL对不足位数的查询结果进行前后补0
Postgresql对不足位数的查询结果进行前后补0,lpad,rigthpad函数的应用原创 2021-10-27 18:32:18 · 4656 阅读 · 0 评论