2018年03月_瀚高PG实验室

原创 postgresql是否支持双参数limit SQL语句？

2018-03-31 11:16:45 4659

原创 generate_series模拟数据环境

1、普通整数数据create table te1 (id int);insert into te1 select generate_series(1,1000);highgo=# select * from te1 limit 5; id ---- 1 2 3 4 5(5 rows)2、常用规律数据模拟create table te2 (id int,name t...

2018-03-31 11:11:28 1363

原创 generate_series时间类型的扩展应用

小数：highgo=# SELECT generate_series(1.2, 5, 1.3); generate_series ----------------- 1.2 2.5 3.8(3 rows)highgo=# SELECT generate_series(1.2, 5.5, 1.3); gene...

2018-03-31 09:31:11 960

原创创建复制槽失败

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状highgo=#SELECT* FROM pg_create_physical_replication_slot('label');ERROR: 55000: replication slots can only be used if max_replication_slots > 0问题原因max_r...

2018-03-30 16:01:13 520

在使用PostgreSQL的过程中，通过psql或者应用程序连接数据库，经常会遇到如下报错，再此我们来了解一下这些报错信息及处理方法。第一种：FATAL: no pg_hba.conf entry for host "10.10.10.10", user "highgo", database "highgo"这条消息最可能出现的情况是你成功地连接了服务器，但它拒绝了接下来的联系。就像消息本身所建...

2018-03-30 15:01:22 3307

原创 pg_hba.conf的所有认证方式

postgresqlpg_hba.conf 文件host all all 0.0.0.0/0 trust我们可以指定当一个连接匹配这条记录时，要使用的认证方式。1.trust无条件地允许连接。这种方法允许任何可以与PostgreSQL数据库服务器连接的用户以他们期望的任意PostgreSQL数据库用户身份登入，而不需要口令或者其他任何认证。2.rejec...

2018-03-30 14:50:23 3841

原创 psql切换数据库和用户

使用psql怎么切换数据库或者用户呢？通过如下方式可以通过特定用户连接特定数据库：[highgo@rhel ~]$ psql -d highgo -U highgo-d就是指定的数据库名，-U就是指定的用户名。如果通过psql已经连接进来了，我们可以怎么切换呢？通过如下方式：highgo=# \c highgo xyhPassword for user xyh: You are now con...

2018-03-30 11:31:02 18128

原创 postgresql报错ERROR: integer out of range错误: 整数超出范围

在pg中我们进行如下操作：highgo=# select 10*1024*1024*1024;错误: 整数超出范围尝试进行类型转换：highgo=# select cast(10*1024*1024*1024 as bigint);错误: 整数超出范围这种情况下，我们可以进行如下操作：highgo=# select 10*1024*1024*1024::bigint; ?column...

2018-03-30 11:07:02 11973

原创检查点和WAL

WAL的定义PostgreSQL中事务被保存的记录叫做预写日志（WAL）。当内存中的数据页被修改时，相应的变化会被记录到WAL中。WAL是一种确保数据完整性的标准方法。WAL的核心理念就是对数据文件的更改必须在相关更改被记录之后才能写入，也就是说，在日志记录描述这些更改已经被刷新到永久存储设备中之后。使用这种方式后，我们就不需要在每次事务提交的时候把数据页刷新到磁盘中。使用WAL会使磁盘写入的数量...

2018-03-27 17:54:46 767

原创 PostgreSQL数据库升级

数据库升级方法通常情况下，有四种可能的方式来完成数据库的升级工作：1、第一种方式是让数据库保持他们的存储格式不变或者至少是兼容不同的版本。然而，这种方式很难获得长期的保证，因为新的特征可能需要改变数据存储的方式或者加入更多的元数据信息才能正常工作。而且，性能通常可以通过使数据结构最优化来得以提高。2、第二种方式是先做一个旧服务器的逻辑拷贝，然后导入到新服务器中。这是一种最传统的方式，但需要旧服务器...

2018-03-27 17:42:14 1180

原创 PostgreSQL中的高速缓存

caching可以说是优化数据库系统表现的一个重要方面。为什么需要cache?不同的计算机元器件有不同的运行速度。在数据库系统中，我们主要关心的是磁盘I/O。磁盘存储器相较于SSD来说对于随机I/O的性能相当糟糕。大多数OLTP的工作量都是随机I/O，因此将它们从磁盘中取出会非常的慢。为了克服这个缺陷，PostgreSQL将数据缓存到内存中，来改善性能。甚至对于SSD来说，内存也是更快的。所以几乎...

2018-03-27 16:35:48 3846

原创 PostgreSQL额外提供的模块之pg_buffercache

pg_buffercache模块提供了一种实时检测共享缓冲区的方法。这个模块提供了一个C函数：pg_buffercache_pages，它返回一个记录的集合和一个视图：pg_buffercache，它包装了这个函数来更方便的使用。默认情况下，公共访问会从这两者中撤回，来预防潜在的安全问题。pg_buffercache这个视图中各列定义的详细解释如下：名称描述bufferidID，范围是1到...

2018-03-27 16:01:37 1971

原创 PostgreSQL 之日志审计（pg_log）

审计是指记录用户的登陆退出以及登陆后在数据库里的行为操作。Postgres 的日志（pg_log）功能十分丰富，接下来首先会介绍它的标准日志审计功能设置highgo=# show logging_collector; --是否开启日志收集，默认offlogging_collector -------------------on(1 row)highgo=# show log_destin...

2018-03-27 09:55:59 14028 2

原创 Postgresql 之分页技术（二）

分页技术（一），已经证明limit-offset带来的性能下降，即使增加了order by 和索引，也同样性能下降的问题。稍后我将例子给证明。解决这个问题的方法是：排序+过滤条件分页创建测试表，插入1千万记录，并创建索引：highgo=# drop table testhyl;DROP TABLEhighgo=# create table testhyl(id serial,name var...

2018-03-27 09:22:15 1172 2

原创 PostgreSQL 之 autovacuum的触发条件

autovacuum 是 postgresql 里非常重要的一个服务端进程，能够自动地执行，在一定条件下自动地对 dead tuples 进行清理并对表进行分析autovacuum参数控制 autovacuum 进程是否打开,默认为 "on"根据postgresql.conf相关配置，理解autovacuum会在两种情况下会被触发：1.表上(update,delte 记录) >= auto...

2018-03-27 09:20:22 11939

原创 PostgreSQL 之统计信息

PG用于收集统计信息的进程stats collector process 源码路径："src/backend/postmaster/pgstat.c"统计信息的存放地启动时, 读入已存在的统计文件, 或初始化0. 数据库运行过程中存储在内存和temp文件; 启动时 : shared buffer -> "src/backend/postmaster/pgstat.c" -> ...

2018-03-27 08:55:18 4595

原创 could not connect to server: No such file or directory

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：Linux x86-64 Red Hat Enterprise Linux 7,Linux x86-64 Red Hat Enterprise Linux 6,Linux x86-64 Red Hat Enterprise Linux 5版本：4.1.1症状使用psql连接数据库报错：could not connect to server:...

2018-03-26 14:33:41 2059

原创 Postgresql动态SQL

PostgreSQL支持动态SQL，以PL/Pgsql为例，语法如下：EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];上式中的可选项target表示a record variable, a row variable, or a comma-separated list of simple v...

2018-03-26 00:57:36 7860

原创 postgresql的函数

postgresql的函数会返回最后一条语句的结果的第一行数据；若要返回结果集，需要显示声明要返回某类型的结果集或Table。函数体以双""符号或单引号包裹，若用单引号包裹则需要注意特殊字符转义。可以在函数体内以参数名（9.2及以后版本支持）或"n"的方式引用参数。举个例子：CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS...

2018-03-26 00:36:08 2203

原创 postgresql中的Json格式

PostgreSQL支持Json格式数据，有两种类型：json和jsonb。两者在效率上有所区别，而这是因为jsonb存储的是格式化后的二进制数据，所以在写入时，json类型比较快，而在检索时（注意这里说的检索不是简单的读取整个数据，而是比如检索json数据中某个键的值的场景），jsonb效率较高。一般情况下，使用jsonb就可以了。json数据是为了弥补关系型数据在伸缩性扩展性上的不足，但是文档...

2018-03-25 23:39:57 2825

原创集合返回函数（二）

generate_series函数集中常用的输入和返回的值：一、无步进：highgo=# select generate_series(1, 10);generate_series ----------------- 1 2 3 4 5...

2018-03-23 17:36:37 248

原创集合返回函数（一）

系列生成函数（series generating functions）系列的函数是集合返回函数（possibly return more than one row）最为常见和常用的，也是postgressql的内置函数，下面介绍一下此类函数：函数参数类型返回类型描述 generate_series(start, stop) int 或 bigint setof int 或...

2018-03-23 17:16:33 530

原创什么是Postgres-BDR

Postgres-BDRPostgreSQL的双向复制（Postgres-BDR或BDR）是PostgreSQL达到完整生产状态的第一个开源多主复制系统。BDR由2ndQuadrant开发，专门设计用于分布在不同地理位置的集群，使用高效异步逻辑复制，支持分布式数据库中2到48个节点以上的任何节点。具有地理分布的数据库的组织将理解复制的复杂性，以及实现高效和准确的数据副本的目标。与其他解决方案不同，...

2018-03-23 16:21:05 3297

原创什么是pglogical-功能优点原理与使用场景

pglogicalv2 - PostgreSQL的新一代逻辑复制！pglogical是一个完全作为PostgreSQL扩展实现的逻辑复制系统。完全集成，它不需要触发器或外部程序。这种物理复制的替代方法是使用发布/订阅模型复制数据以进行选择性复制的一种高效方法。pglogical 2有哪些新功能？pglogical 2.2版本带来了新功能，可以用于更多的使用案例，以及下面列出的几个错误修复和改进：...

2018-03-23 16:01:56 3962

原创 psql: could not connect to server

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状使用psql远程连接数据库报错：Connection refused 和 No route to host。1、psql: could not connect to server: Connection refused[highgo@hgdb ~]$ psql -h 192.168.10.123 -p 586...

2018-03-23 15:23:51 3214

翻译 repmgr简介及配置

什么是REPMGR？repmgr是一个开源工具套件，用于管理PostgreSQL服务器集群中的复制和故障转移。它使用工具来增强PostgreSQL的内置热备份功能，以设置备用服务器，监控复制以及执行管理任务，例如故障转移或手动切换操作。repmgr是2010年由2ndQuadrant推出的PostgreSQL故障切换最流行的工具。 repmgr帮助DBA和系统管理员管理PostgreSQL数据库...

2018-03-22 13:33:30 9435 2

翻译 Postgres-XL介绍-性能特征与功能

Postgres-XL是一个大规模并行数据库，建立在PostgreSQL 9.5之上，与PostgreSQL 9.5高度兼容。它的不同之处在于它同时支持商业智能工作负载和高容量事务写入和读取工作负载。 Postgres-XL被设计为可横向扩展且足够灵活的处理各种工作负载，包括： OLTP写密集型工作负载商业智能要求具有MPP并行性的OLAP操作数据存储包含JSON的键值存储GIS地理空间混合工作负...

2018-03-22 11:39:37 4448

原创 create role 后，使用该角色不能连接数据库

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状创建角色后，使用psql不能连接数据库highgo=# create role ccc;CREATE ROLE[highgo@hgdb ~]$ psql -U ccc -d highgopsql: FATAL: 28000: role "ccc" is not permitted to log in问题原因使用c...

2018-03-22 09:17:32 1467

原创 PostgreSQL中的统计信息、选择度和基数

SQL是一种声明性语言，用户请求他们需要的东西而不需要明确告知计算机应该用什么方式得到相应的结果。这些工作是由DBMS(数据库管理系统)来完成的。DBMS将DML声明转化成为操纵所需记录集合的程序，它要确保返回正确的结果以及在理想情况下，速度越快越好。这里的速度越快越好意味着：1、将磁盘访问降到最低。2、给与顺序读一个优先级（对于机械硬盘尤其重要）。3、降低CPU操作数。4、降低内存占用。为了实现...

2018-03-19 17:40:08 2387

原创 PostgreSQL中的hstore模块

之前已经介绍过hstore模块的作用和操作符。这次我们继续了解此模块的其他内容。hstore函数这里我们列举一些hstore模块提供的函数。函数返回值描述例子 hstore(record) hstore 从一个记录或行中创建hstore 见图1 hstore(text[]) hstore 从数组中创建hstore hstore(text[], text[]) hstore ...

2018-03-19 16:24:05 4209

原创 Series Generating Functions

generate_series允许我们生成一个数据的集合，而且可以随意的设置增长值。generate_series支持两种数据类型：integer类型和 timestamp类型。级数生成函数函数描述 generate_series(start, stop) 生成一系列值，范围从start到stop，step值为1 generate_series(start, stop,step) 生成一系...

2018-03-19 15:41:22 268

原创 PostgreSQL中的VACUUM命令

每当PostgreSQL数据库中的表中的行被更新或删除时，死亡行会被遗留下来。VACUUM则会把它们除去来使空间能被重新利用。如果一个表没有被清空，它会变得臃肿，浪费磁盘空间而且会降低顺序表扫描的速度，而且在较小范围内也会降低索引扫描的速度。VACUUM命令只可以移除这些不再被需要的行版本（也被称为元组）。如果被删除事务的事务ID（存储在xmax系统列中）比仍然活跃在PostgreSQL数据库（或...

2018-03-19 15:21:53 6557

原创 checkpoints are occurring too frequently

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状数据库系统日志经常报错如下所示：checkpoints are occurring too frequently (3 seconds apart),Consider increasing the configuration parameter "max_wal_size"问题原因检查点发生的太频繁，max_wal...

2018-03-19 09:34:52 1378

原创 explain的输出格式

如何修改explain的输出格式默认情况下，explain输出的执行计划是文本格式，使用format选项可以修改的explain的输出格式，除text外，还支持json、xml、YAML三种格式。示例如下：输出json格式：highgo=# explain (formatjson) select * fromemp;QUERY PLAN-----------------...

2018-03-18 23:59:23 657

原创 Postgresql explain的analyze和buffers选项

postgreSQL使用explain查看SQL的执行计划时，因为SQL并未真实执行，看到的执行计划只是估算值，可能与实际情况差距较大。如果需要查看真实的执行性计划，就需要使用选项analyze获取更精确的执行计划。示例如下：highgo=# explainselect * from emp1 whereempno=7369 ;QUERY PLAN...

2018-03-18 23:59:05 6559 1

原创 PostgreSQL explain简单示例

explain输出结果解释下面是一个最简单的explain示例highgo=# explainselect * fromemp;QUERY PLAN--------------------------------------------------------SeqScan onemp(cost=0.00..15.60 rows=56...

2018-03-18 23:58:41 861

原创 postgreSQL explain命令

explain命令语法：EXPLAIN [ (选项[, ...] ) ]语句EXPLAIN [ ANALYZE ] [ VERBOSE ]语句选项可以是下列内容之一:ANALYZE [布尔]VERBOSE [布尔]COSTS [布尔]BUFFERS [布尔]TIMING [布尔]FORMAT { TEXT | XML | ...

2018-03-18 23:55:59 1526

原创 Postgresql 之分页技术（一）

一、Limit-offset分页语法如下：SELECT select_list FROM table_expression[ ORDER BY ... ][ LIMIT { number | ALL } ] [ OFFSET number ]二、Limit-offset分页查询示例：创建一个测试表：postgres=#CREATE TABLE student (studentID CHAR(6...

2018-03-17 19:22:14 10263 1

原创 Postgresql 监控sql之 pg_stat_statements模块

一、pg_stat_statements模块简介pg_stat_statements模块提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息，可以用于统计数据库的资源开销，分析TOP SQL。该模块必须通过在postgresql.conf的shared_preload_libraries中增加pg_stat_statements来载入，因为它需要额外的共享内存。这意味着增加或移除...

2018-03-17 19:15:37 27385 3

原创 Postgresql数据类型之UUID

1.UUID概述数据类型uuid存储由RFC 4122、ISO/IEC 9834-8:2005以及相关标准定义的通用唯一标识符（UUID）（某些系统将这种数据类型引用为全局唯一标识符GUID）。这种标识符是一个128位的量，它由一个精心选择的算法产生，该算法能保证在已知空间中任何其他使用相同算法的人能够产生同一个标识符的可能性非常非常小。因此，对于分布式系统，这些标识符相比序列生成器而言提供了一...

2018-03-17 19:11:35 11430 1

空空如也

空空如也