postgresql
文章平均质量分 87
江天水一泓
DBA。工作中主要使用MySQL, PostgreSQL,也在学习Oracle。希望能与志同道合的朋友倾情交流。
展开
-
Dealing with corrupted system indexes in PostgreSQL
原文链接:https://blog.dbi-services.com/dealing-with-corrupted-system-indexes-in-postgresql/This is something you do not want to see on an important PostgreSQL system:postgres@centos8pg:/home/postgres/ [pgdev] psqlpsql: error: could not connect to serv.转载 2021-04-13 15:45:52 · 558 阅读 · 0 评论 -
PostGIS 的软件依赖—— PostGIS‘s dependancies
PostGIS是PostgreSQL对象关系数据库的空间数据库扩展器。它添加了对地理对象的支持,允许在SQL中运行位置查询。除了基本的位置感知功能,PostGIS还提供了许多其他竞争空间数据库(如Oracle Locator/ spatial和SQL Server)很少有的功能。PostGIS 依赖了下面这些软件:PostgreSQLGEOSSFCGALGDALPROJprotobuf-cjson-cPostGIS 严格地要求所依赖软件的版本。如果你用源...原创 2021-01-14 14:30:41 · 1284 阅读 · 5 评论 -
How to get a md5 password for a user on PostgreSQL? 如何在PostgreSQL上为用户获取md5密码?
If you look through table pg_authid on PostgreSQL, you can find the values of columnrolpassword are hashed strings. For example, a string like"md58d11c0b8417d3947cb544f1174fd44ce".How to getrolpassword? The formula is:"md5" + md5(password + usernam...转载 2020-11-30 14:06:02 · 363 阅读 · 0 评论 -
pg_qualstats-1.0.x ‘s suggestions are not accurate, So use pg_qualstats-2.0.x
pg_qualstats-1.0.x 's suggestions are not accurate, So use pg_qualstats-2.0.xpg_qualstats is a PostgreSQL extension keeping statistics on predicates found inWHEREstatements andJOINclauses.This is useful if you want to be able to analyze what are...原创 2020-11-11 19:28:27 · 172 阅读 · 0 评论 -
Powershell 脚本-关闭所有残留的PostgreSQL 进程
在 Windows 上,有时PostgreSQL 会遇到这样一种异常:数据库无法正常访问,但从任务管理器中看,仍然有一些名为postgres.exe 在运行。而在任务管理器中,它的状态是“已停止”,但它却无法在任务管理器中启动。于是就陷入了一个死局。解决这个问题的方法是 关闭所有残留的 postgres.exe 进程。下面这个 powershell 脚本可以解决问题,读者只需要配置 参数 $PgServiceName,即数据库服务名即可:# set-ExecutionPolicy...原创 2020-11-02 20:06:44 · 644 阅读 · 0 评论 -
PostgreSQL 小工具——将一个数据库中所有表的设计整理为csv文件
工作中,需要将在PostgreSQL 数据库中的所有表的结构整理成表格。示例如下: person 人员表 字段名 字段类型 是否非空 约束 字段描述 id int Y primary key (id) 主键,id n..原创 2020-11-02 19:34:18 · 306 阅读 · 0 评论 -
使用 barman的备份和归档PostgreSQL
使用 barman的备份和归档PostgreSQL1 前言1.1 Barman简介barman(备份和恢复管理器)是用于PostgreSQL服务器进行灾难恢复的开源管理工具,是以Python编写的。它支持对多台服务器执行远程备份,以降低风险并帮助DBA进行数据库恢复。1.2 Barman的备份方式本文假定读者熟悉理论上的灾难恢复概念,并且具有在PostgreSQL物理备份和灾难恢复方面的基础知识。我们知道 PostgreSQL 的连续备份包含一个或多个基础备份和连续归档的WAL日志。转载 2020-10-16 19:59:55 · 763 阅读 · 0 评论 -
PostgreSQL——对一列做全表更新——update还是alter
在 PostgreSQL 上,如果我们想对一张表的一个列做全表更新,我们有两种方法可选择。一种方法是通过DML修改,即使用 update 语句;另一种方法是通过DDL 修改,即使用 alter语句。现在我们通过一个简单的例子,比较这两种方法的性能。我们分别创建了两张结构相同的表student1和 student2,并分别插入1百万条测试数据。CREATE table student1( id int, name varchar(32), gende...原创 2020-09-24 21:01:13 · 2170 阅读 · 0 评论 -
在一个 plpython 函数中导入的包,在该函数被调用后,会被缓存起来
在一个调用过的 plpython 函数中所导入的包会被缓存起来我在 Window 7 操作系统上 安装了Python 3.3和 PostgreSQL 9.6,并在环境变量中配置好了PYTHONHOME 和 PYTHONPATH,然后将 Python33\DLLs中的python3.dll 移动到 postgresql安装目录的lib中,把它重命名为 python33。现在,我可以在 PostgreSQL 中使用 plpython 了。我在PostgreSQL中创建了下面的函数,...原创 2020-08-22 14:30:56 · 386 阅读 · 1 评论 -
在 PostgreSQL 上的基于触发器的将时序表分区的方案和模板
1. 前言1.1 概述当关系型数据库中一些表中数据量过于庞大时,我们就需要对它进行分区,以提高查询的性能。在postgresql 9.6及其之前, 它没有提供原生的表分区方法,因此我们采用在数据库中通过触发器来分区的方法。而从postgresql 10 开始,数据库提供了原生的分区方法。2. 设计方案2.1 设计思想在基于触发器的分区方法中,我们需要实现下面两个目标:程序向主表中插入数据时,数据库拦截并将数据插入到对应的分表中。 在进行数据库查询,删除,更新操作时,数据库可以定原创 2020-07-21 20:59:21 · 505 阅读 · 0 评论 -
在windows 上 使用 powershell 统计 postgresql的CPU 使用率和私有内存
实际应用中,我们有时需要统计PostgreSQL 的CPU使用率和私有内存。本文介绍在Windows上使用powershell统计PostgreSQL的方法,并将相关步骤整理为一个脚本。 方案的原理: PostgreSQL 是多进程模型的数据库。它在运行时,会启动一个名为“pg_ctl”进程和若干个名为“postgres” 的进程。其中,进程pg_ctl是“祖先”进程,它表示数据库处于运行状态,占用的内存很少;其他所有工作进程的名称都是postgres。我们可以根据PostgreS...原创 2020-07-16 21:21:24 · 1136 阅读 · 0 评论 -
PostgreSQL: How to update large tables
Updating a large table in PostgreSQL, an advanced open-source database management system, is not straightforward. If you have a table with hundreds of millions of rows you will find that simple operations, such as adding a column or changing a column type,转载 2020-07-16 19:32:17 · 253 阅读 · 0 评论 -
在 PostgreSQL 中,违反唯一约束的行也会被写入表中
在 PostgreSQL 中,违反唯一性的行也会被写入表中。这听起来很不可思议。实际上,这些行确实会被写入表中,只是不会显示在查询结果中。我们在PostgreSQL 10 上来验证这一点。首先,我们在创建表 man,代码如下。其中字段id 上有 主键约束。 CREATE TABLE man(id integer NOT NULL,name character varying(32),gender int,b...原创 2020-07-14 20:14:14 · 809 阅读 · 0 评论 -
PostgreSQL 中 min() 和 order by limit 1 的对比
在实际应用中,我们经常需要计算数据库中某张表的某个字段的的最小值/最大值,并用它来作为查询条件。使用SQL求数据库中表字段的最小值/最大值,通常由两种方法:一种是 使用聚集函数 min()/max(), 另一种是使用order BY + “字段” 子句,对该表排序之后,取第一条记录中该字段的值。本文在PostgreSQL 11.4上,对min() 和 order by … limit 1 以及它们的变体做一个全方位的对比。现在有一张表tb_person,它的定义如下:...原创 2020-06-30 15:08:09 · 3166 阅读 · 0 评论 -
pgwatch2-1.7.0 在CentOS7上的安装和使用
1. 前言1.1 pgwatch2 介绍pgwatch2,是由CYBERTEC开发的PostgreSQL数据库的指标收集器和可视化解决方案。 这个灵活的,自包含的监控工具将PostgreSQL监控提高到一个新的水平。尽管有许多PostgreSQL监控解决方案,但其中大多数解决方案因太复杂而难以启动,并且因不灵活而无法自行扩展功能。 PGWatch试图通过提供以前无法提供的灵活性并仅专注于指标收集来改变这种状况,为演示方提供了可用的最好的仪表板工具。PGWatch充当远程度量收集后台程序,以正常用原创 2020-06-24 19:09:19 · 1040 阅读 · 1 评论 -
浅探PostgreSQL中的bit类型
Bit,即比特,是二进制数字中的位。PostgreSQL 中提供了用于表示比特串数据类型bit。在PostgreSQL 中,bit 类型常见用法是bit(n),这里n表示bit串的长度。同时,也有不带(n)的bit和带双引号的”bit”。它们各自的含义是什么呢?又有什么联系和区别?简言之,不带“(n)”的bit表示bit(1),而带双引号的”bit”表示任意长度的位串。我们通过下面的SQ...原创 2019-07-31 17:17:39 · 9958 阅读 · 1 评论 -
浅探PostgreSQL的 char 类型
一般来说,在PostgreSQL中,char 类型是表示固定长度的字符串的类型。例如, char(n) 用来表示长度为N个字符的字符串。不过,PostgreSQL 自身的一些细小缺陷,使得上面的论断事实上并不是很严谨。何以见得?下面的这个探究char类型实验,会揭开问题的面纱。我们通过下面的SQL语句创建一张表:create table tb_char( quote...原创 2019-05-23 17:34:13 · 3370 阅读 · 0 评论 -
PostgreSQL的几种常见问题和解决方法
1. 前言1.1 概述本文介绍了postgresql的几种常见问题,并从现象出发,逐步排查问题,分析导致问题的原因并给出解决方案。本文介绍的问题分为两大类:一类是关于PostgreSQL无法启动的问题,另一类是PostgreSQL启动后,部分数据库对象无法访问的问题。1.2软件环境本文使用的 PostgreSQL 版本是 9.2。1.3一些约定术语PostgreSQ...原创 2019-04-30 15:40:57 · 73059 阅读 · 15 评论 -
PostgreSQL的btree和bloom索引以单列为查询条件时的性能比较
1. 实验环境硬件: 普通PC一台。内存 8G。磁盘总空间200G。操作系统:CentOS 6.9软件:PostgreSQL 10,默认配置。2. 准备工作登录PostgreSQL,首先创建表 testindex,它有四列:postgres=# create table testindexpostgres-# (postgres(#col_int int...原创 2018-12-13 11:57:24 · 527 阅读 · 0 评论 -
在Windows上安装Psycopg2
在Windows上安装Psycopg21. 什么是Psycopg2?Psycopg2 是用 Python 编写的中最受欢迎的 PostgreSQL 数据库适配器。它的主要功能是完整实现 Python DB API 2.0 规范和线程安全 (多个线程可以共享相同的连接)。它是为大量多线程应用程序设计的, 它创建和销毁大量游标, 并生成大量并发 "INSERT" 或 "UPDATE"。P...原创 2018-09-30 10:00:47 · 6522 阅读 · 0 评论 -
PostgreSQL的日志分析工具——Pgbadger的安装和使用
pgBagder 是 PostgreSQL 的新一代日志分析器, 由 Darold (也是 ora2pg 的作者, 强大的迁移工具) 创建的。pgBadger 是一种快速、简便的工具, 用于分析 SQL 通信量, 并使用动态图来创建 HTML5 报告。pgBadger 是了解 PostgreSQL 服务器的行为并确定需要优化哪些 SQL 查询的完美工具。pgBagder 是在linux操...原创 2018-09-27 15:18:53 · 4214 阅读 · 0 评论 -
Linux下PostgreSQL主备环境搭建和切换
1. 概念数据库热备:数据库热备是指为主数据库的创建、维护和监控一个或多个备用数据库,它们时刻处于开机状态,同主机保持同步。当主机失灵时,可以随时启用热备数据库来代替,以保护数据不受故障、灾难、错误和崩溃的影响。流复制(streaming replication):PostgreSQL提供的一种服务器间的数据复制方式。这种方式下,后备服务器连接到主服务器,主服务器则在 WAL 记录产生时即...原创 2018-09-13 16:24:42 · 3972 阅读 · 0 评论 -
在postgresql (Windows版)中使用plpython
PL/Python过程语言允许用Python 语言编写PostgreSQL函数。 我们需要安装python扩展程序。要注意Windows 版本PostgreSQL和Python的版本对应关系。经验证,PostgreSQL 11 支持Python 3.6-3.8;PostgreSQL 9.6只支持Python 3.3;Postgresql 9.2-9.5只支持Python 3.2。对于 P...原创 2018-09-13 16:00:42 · 2307 阅读 · 3 评论 -
Postgresql的三种备份方式
数据库的备份有多种分类方式。按照备份后的文件类型,可以分为物理备份(文件系统级别的备份)和逻辑备份(备份后的文件是sql文件或特定格式的导出文件);按照备份过程中是否停止数据库服务,可分为冷备份(备份过程中停止数据库服务)和热备份(备份过程中数据库服务开启并可供用户访问);按照备份是否是完整的数据库,可分为全量备份(备份是完整的数据库)和增量备份(备份是上一次全量备份后数据库改变的...原创 2018-09-13 15:52:04 · 56794 阅读 · 12 评论 -
PostgreSQL分区表升级-从基于触发器到声明式
1 前言当数据库中单表的数据量过大时,需要考虑分区来提高性能。对于PostgreSQL数据库的用户而言,如果想在PostgreSQL 9.x 或者更早的版本中使用分区表,你需要用触发器(Trigger)或者规则(Rule),加上检查约束(Check)和继承(Inheritance)机制去实现它。而PostgreSQL 在 10.0版本引入了声明式(declarative)分区。相比于基于“触发器+继承+检查约束” (以下简称基于触发器的方案)和基于“规则+继承+检查约束”的分区方案,声明式分区的在性能有原创 2020-06-29 16:31:11 · 1002 阅读 · 2 评论