- 博客(119)
- 资源 (1)
- 收藏
- 关注
RSS订阅转载 PostgreSQL general public partition table trigger
触发器在数据库中的使用非常广泛, 例如用来跟踪用户行为, 用户登录过滤(Oracle中), 数据复制, 数据分区等等. 以前写过一些类似的应用场景案例分享, 感兴趣的朋友可以参考本文的末尾部分, 有相关的链接. 特别是在数据分区应用场景中, PostgreSQL目前没有将分区这部分代码作为COMMAND来实现. 因此DBA可能经常需要写繁琐的触发器, 例如昨天我们这边一位同
2015-10-14 22:14:52
575
转载 PostgreSQL aggregate function customize
昨天聊到了一个关于多行转换成单行以提高查询性能的场景。http://blog.163.com/digoal@126/blog/static/163877040201211174617734/这里用到了PostgreSQL的聚合函数, 将多行聚合成单行.当然其他数据库也有聚合函数, 但是PostgreSQL 支持自建聚合函数, 这就给开发带来了较大的便利.例如PostgreSQL 8
2015-10-14 22:13:33
994
转载 PostgreSQL trigger introduction - 2
以PostgreSQL 9.2为例, 介绍触发器的使用.接上一篇 : http://blog.163.com/digoal@126/blog/static/1638770402013283547959/包含如下内容 : 一、1. 什么是触发器? 触发器有什么用? 创建触发器的语法?2. 什么是触发器函数, 触发器函数可以用哪些语言编写
2015-10-14 22:12:47
523
转载 PostgreSQL trigger introduction - 1
以PostgreSQL 9.2为例, 介绍触发器的使用. 分两部分.第二部分链接 : http://blog.163.com/digoal@126/blog/static/1638770402013211102130526/包含如下内容 : 一、1. 什么是触发器? 触发器有什么用? 创建触发器的语法?2. 什么是触发器函数, 触发器
2015-10-14 22:10:58
950
转载 PostgreSQL Server Encoding sql_ascii attention
群里一位兄弟问到的一个问题 : " 我有一个postgresql，比较大，编码是sqlascii码，我想转换成有utf8 ，有方案可行吗? "如果使用场景中未用到non-ASCII编码的字符, 那很幸运, 导出导入就可以了.但是如果场景中使用了non-ASCII编码的字符, 那就没那么幸运了, 因为SQL_ASCII字符集不会对non-ASCII字符做合法性检验, 同时不做任何的编
2015-10-14 22:10:21
2296
转载 PostgreSQL customize inet_aton, inet_ntoa function in text type like MySQL.
PostgreSQL的类型支持比较强大, 除了主流的文本, 日期, 时间, 数字, 还支持JSON, 货币, 网络, 几何, 枚举, 数组, 范围, XML, 布尔, 全文检索, 比特, 复合等数据类型. MySQL的类型支持比较薄弱, MySQL里面没有专门的网络数据类型, 如果要存储网络信息, 一般的做法是转换成INT类型来处理.PostgreSQL当然没有必要这么做, 因为已
2015-10-14 22:08:36
1613
转载 PostgreSQL json jsonb 支持的value数据类型，如何构造一个jsonb
PostgreSQL 支持JSON已久，那么JSON的value中支持哪些类型呢？目前支持string, numeric, boolean (true, false), null。 区分大小写哦。/* * Recursive Descent parse routines. There is one for each structural * element in a
2015-10-14 22:07:41
7795
转载 PostgreSQL earth distance module
PostgreSQL提供了一个扩展模块earthdistance，实际上是将地球构造为一个标准的圆球体（实际上是扁球体），利用cube或point来表示地球上的点。其中cube是用来记录球坐标的，通过坐标来表示地球上的点。（实际上是做了一定约束的cube, 即域类型earth）-- Define domain for locations on the surface of the
2015-10-14 22:05:35
707
转载 PostgreSQL 在3D 数据内容管理中的应用
前段时间在中华数据库大会遇到一位朋友询问关于如何使用PostgreSQL存储3D建模的数据，应用场景大概是找到最合脚的鞋子。大概是将生产线上所有鞋子的内部空间数据化，同时将人的脚的三维数据化，存入数据库。找到脚和鞋子最匹配的组合。这里涉及到3D数据的建模和存储，以及数据的使用。好在PostgreSQL有非常丰富的接口，例如用户可以自定义数据类型，以及自定义数据访问方法，自定
2015-10-14 22:03:42
1562
转载 orafce extension for PostgreSQL, Oracle's compatibility functions and packages
orafce是针对Oracle做的兼容性包。如果你需要从Oracle 迁移到PostgreSQL，并且用到了这些函数或package的话，就方便多了，可以减少代码的改动量。可以用源码安装，或者如果你是用repo安装的话，也有这个包。1. http://pgxn.org/dist/orafce2. http://yum.postgresql.org/9.4/redhat/rhel
2015-10-14 22:02:25
1647
转载 PostgreSQL (User defined Operator) UDO & Operator Optimization Information
PostgreSQL 支持自定义操作符，本质上是调用函数来实现的。语法如下：CREATE OPERATOR name ( PROCEDURE = function_name [, LEFTARG = left_type ] [, RIGHTARG = right_type ] [, COMMUTATOR = com_op ] [, NEGATOR = neg
2015-10-14 22:00:52
649
转载 use PostgreSQL trigger manage stock & offer infomation
使用触发器来管理订单和库存信息，可以简化数据交互过程。这个例子来自postgresql server programming。例如库存表存储一家水果超市的水果的库存总量数据，以及已供应的量数据，库存总量必须大于等于已供应的量。订单数据表存储每一笔水果的订单信息。涉及的操作包括新增订单，修改订单，撤销订单。新增订单，则需要增加库存表的已供应量字段信息，同时需要
2015-10-14 21:57:42
465
转载 PostgreSQL use UDF combine NoSQL DB's Computing Power
PostgreSQL数据库如何利用NoSQL数据库的强大计算能力或某些特性呢？这个问题实际上是因为一种数据库不可能做到适合任何场景，一个企业的不同业务类型可能使用的数据产品也千差万别。昨天和一位朋友聊到一个应用场景，他们有部分数据是存储在aerospike的，在aerospike中做BIT运算是很快的，但是当需要多个条件的组合时（多级索引），可能就不是那么适合了。这个时候Postg
2015-10-14 21:56:16
979
转载 结巴分词
结合PostgreSQL plpython和language transform可以很方便的实现中文分词。https://github.com/fxsjy/jiebahttp://www.postgresql.org/docs/9.5/static/sql-createtransform.htmlhttp://www.postgresql.org/docs/9.5/
2015-10-14 21:55:29
1337
转载 use PostgreSQL async Notification as a chat group
PostgreSQL 提供了一个很有意思的异步消息通信功能。利用这个功能，可以实现很多有趣的想法，例如离线聊天组，数据逻辑复制，审计日志等。notify就是往一个指定的通道发消息。postgres=# \h notifyCommand: NOTIFYDescription: generate a notificationSyntax:NOTIFY channel
2015-10-14 21:54:21
832
转载 PostgreSQL partial/sub commit within function
PostgreSQL的函数是原子操作，所以不能像Oracle那样在函数中实现分段提交。但是如果你要从Oracle迁移到PostgreSQL的话，必然会面临这样的问题，那么怎么办呢？有几种方法可以来实现，下面是例子：1. 通过exception来实现分段提交。create table tbl (id int primary key, info text);create
2015-10-14 21:53:14
818
转载 don't mistake the float/double's display & real value.
使用单精或双精类型时, 我们查询出来的值可能与真实存储的值有一定差别.这里体现了眼见不为实的特征.以下是float4, float8的输出函数.src/backend/utils/adt/float.c/* * float4out - converts a float4 number to a str
2015-10-14 21:51:50
446
转载 PostgreSQL 逻辑复制插件 UDR，可以愉快的玩类似MySQL的binlog复制了。
PostgreSQL 的流复制素来以高效，实时，稳定著称；为企业解决了很多问题，例如容灾，备份，HA，读写分离等等。但是流复制有一个无法克服的弊端，下游节点只能做到只读，并且只能复制整个集群（使用walbouncer可以做到基于表空间或库级别的物理流复制）。http://www.cybertec.at/en/products/walbouncer-enterprise-grade-part
2015-10-14 21:50:07
5400
转载 cgroup告诉你如何计算 PostgreSQL 数据库实例用了多少内存
当我们在一个操作系统中，启动了多个数据库实例时，我们也许会控制各个实例可以使用的内存，通过cgroup来控制是一种手段。显然，使用cgroup也可以知道你的实例使用了多少内存。例子：在内存组新建一个组[root@digoal ~]# cd /cgroup/memory[root@digoal memory]# mkdir cg1[root@digoal memory
2015-10-14 21:48:51
1296
转载 PostgreSQL jdbc 9.4 支持load balance 和 connection failover了
好消息，PostgreSQL的jdbc驱动支持load balance 和 connection failover了。通过配置多对主机和端口的信息，可以实现简单的连接FALIOVER，直到取到一个正常的连接为止。Connection Fail-overTo support simple connection fail-over it is possible to d
2015-10-14 21:47:24
2239
转载 从文件系统恢复遗失的UNLOGGED table's datafile.
注意PostgreSQL的unlogged table是不记录xlog的，所以在备库上没有unlogged table的数据记录。同时，数据库在进入恢复状态时，为了保证数据的一致性，postgresql会自动清除unlogged table的数据文件。那么问题来了，万一你不小心误创建了unlogged table，你可能一开始会没有感知，但是一旦发生以下情况，你会发现数据不见了。
2015-10-14 21:46:06
677
转载 请勿滥用unlogged table & hash index
unlogged table和hash index同样都不会写XLOG，所以如果你用流复制来搞HA，一定概要搞清楚一个问题，切换到备库的话unlogged table数据会被清掉，而hash index也没有，走hash index会失败。unlogged table 的风险以及修复手段可以见 ：http://blog.163.com/digoal@126/blog/sta
2015-10-14 21:44:10
717
转载 请勿滥用 2PC, 忘记提交prepared transaction对PostgreSQL造成的危害.
我在数据库中开启了一个2PC事务，但是不去管他，会发生什么呢？有什么危害？postgres=# begin;BEGINpostgres=# insert into t6 values (1);INSERT 25622 1postgres=# prepare transaction 'a';PREPARE TRANSACTIONpostgres=# select * from txid_cu
2015-10-14 21:42:46
986
转载 使用pg_xlogdump找到精准的误操作XID
一般用户在发生误操作后，可能会非常模糊的记得一个大概操作的时间点，那么通过这个线索，我们怎么能定位到用户误操作的精准事务号呢？从而使用PITR恢复到发生误操作前的数据库状态。我们可以使用审计日志，找到精准的时间，假设你开了所有SQL的审计日志(log_statement = 'all')。然后找到精准的XID，需要使用pg_xlogdump。一个例子：用户在
2015-10-14 21:40:35
1950
转载 online DDL (or NOWAIT DDL) in PostgreSQL
为什么需要online DDL呢？或者nowait DDL呢？PostgreSQL为了保证一个请求不至于无休止的等待锁，锁等待使用了队列机制，并且这个在等待队列中的锁也会和以后的会话将要请求的锁发生冲突。虽然解决了无休止的等待问题，同时也带来一个比较棘手的问题。例如，当数据库中存在一个长事务时，如果你要对这个长事务持锁（假设是一个很小的锁）的对象执行DDL（获取Ac
2015-10-14 21:38:59
2056
转载 PostgreSQL can continue when postmaster killed
数据库主进程挂了，原有的连接还能继续操作数据库，你信吗？PostgreSQL可以做到，并且原来的进程对数据库的操作是持久化的，不会丢数据哦。这得益于PostgreSQL的进程结构设计。而且postmaster进程只负责了简单的工作，例如监听端口。(有人会问了，wal writer, backgroup writer进程都挂了，数据还能持久化？)没错，因为backend process也可以
2015-10-14 21:37:54
1133
转载 PostgreSQL sort or not sort when group by?
朋友的一个CASE，在一个查询中为什么group by用到了sort?其实这也是优化器的选择问题，只要成本最低，就有可能选择sort。当然如果hashagg的成本更低，那么就会选择hashagg。CASE：postgres=# create table t1(c1 int,c2 int,c3 int,c4 int);postgres=# insert into t1 s
2015-10-14 21:36:39
618
转载 londiste3 copy table' snapshot & PostgreSQL logical replication's snapshot 不同之处
PostgreSQL有物理复制，也有逻辑复制。物理复制是持续的从XLOG读取block data在对应的物理文件实行recovery的过程。建立物理备份的standby时，会强制开启一个检查点，然后拷贝数据文件，拷贝完后的数据库其实是不一致的，没有关系，只要从检查点开始的所有XLOG都在，就可以恢复。检查点我们可以通俗的认为是一个snapshot。那么对于逻辑复制，snap
2015-10-14 21:34:17
733
转载 don't shutdown before pg_stop_backup()
在创建备份或STANDBY时，我们通常可以有两种方式一种是通过pg_basebackup，另一种是使用pg_start_backup然后COPY文件的方式。在使用第二种方式时，正确的流程应该是：pg_start_backupCOPYpg_stop_backup但是我们试想一下，如果在COPY完后，调用pg_stop_backup前，主库关闭了，然后又起来了。
2015-10-14 21:32:54
861
转载 PostgreSQL zhparser & english parser, query, insert performance
本文主要测试一下PostgreSQL 中英文分词的性能，以及带索引的情况下的插入，查询性能。测试环境：CPU：Intel(R) Xeon(R) CPU X7460 @ 2.66GHzMEM：60GOS：CentOS 6.x x64PostgreSQL 9.4.1中文分词使用 http://blog.163.com/digoal@126/blog/s
2015-10-14 21:31:37
1315
转载 PostgreSQL 9.5 new feature - BRIN (block range index) index
PostgreSQL 9.5引入的一个全新的索引访问方法BRIN（block range index），这个索引存储了表的连续数据块区间以及对应的数据取值范围。比如一张表有1000个数据块，我们建议一个BRIN在ID（假设这个表有ID字段）上的索引。BRIN默认是每128个连续数据块区间存储一个字段取值的区间，所以这个索引的信息量是将1000个数据块划分为几个连续的128个块的区间
2015-10-14 21:29:47
853
转载 How to prevent object bloat in PostgreSQL
经常看到有人说表又膨胀了，那么导致对象膨胀的常见原因有哪些呢？1. 未开启autovacuum对于未开启autovacuum的用户，同时又没有合理的自定义vacuum调度的话，表的垃圾没有及时回收，新的数据又不断进来，膨胀是必然的。（新的数据包括插入和更新，更新产生新版本的记录）2. 开启了autovacuum, 但是各种原因导致回收不及时，并且新的
2015-10-14 21:26:29
589
转载 PostgreSQL & Oracle - 2 OLTP "update/select based primary key" & insert
本文主要是针对Oracle的测试， PostgreSQL的测试和测试结果请参考上一篇：http://blog.163.com/digoal@126/blog/static/163877040201541104656600/Oracle版本为12c。硬件和软件环境与上一篇测试PostgreSQL的保持一致，同一台主机，测试时关闭其他任何有干扰的服务和软件。调整Or
2015-10-14 21:23:49
593
转载 PostgreSQL & Oracle -1 OLTP "update/select based primary key" & insert
基于同一台主机和存储，分别测试PostgreSQL 9.4.1, Oracle 12c 的小事务处理能力。测试结果仅供参考，有兴趣的同学可以自行测试或者更改测试用例来玩。(因测试使用工具不一样，工具本身的损耗不一样，结果可能没有可比性。)(即使用同样的工具，驱动的性能可能也不一样，很难做到完全没有偏颇。)(所以，本文目的旨在挑战产品自身的极限或者发现自身的问题和缺陷，而非两种产品的
2015-10-14 21:22:03
699
转载 CASE : zfs snapshot backup for PostgreSQL when use multi-zfs filesystem
我之前写过几篇关于使用ZFS SNAPSHOT和PostgreSQL standby用于PostgreSQL的备份案例.http://blog.163.com/digoal@126/blog/static/16387704020141110105858171/http://blog.163.com/digoal@126/blog/static/1638770402014518947
2015-10-14 21:20:30
433
转载 recovery test script for zfs snapshot clone + postgresql stream replication + archive
前面我写过一篇关于使用ZFS的快照特性和PostgreSQL流复制来完成数据库增量PITR备份的文章 : http://blog.163.com/digoal@126/blog/static/163877040201451894734122/本文将基于这个备份机, 写一个自动的恢复测试脚本.(假设快照每天一个, 并规则命名)2个脚本如下 : #
2015-10-14 21:19:52
560
转载 PostgreSQL xlog $PGDATA and zfs snapshot based central backup & PITR case
最近在写一个集中式的PostgreSQL基于块的增量备份的CASE.(因Oracle有块级别增量备份, PG这块是个空白, 不过可以通过第三方软件来实现)视频参考6月28日的杭州PG交流视频http://pan.baidu.com/share/home?uk=1982970774#category/type=0方案参考http://blog.16
2015-10-14 21:18:16
658
转载 fast & safe upgrade to PostgreSQL 9.4 use pg_upgrade & zfs
[更新]已使用pg_upgrade顺利将一个8TB的生产数据库(包含表, 索引, 类型, 函数, 外部对象等对象大概10万个)从9.3升级到9.4, 升级比较快(约2分钟), 因为数据库较大后期analyze的时间比较长, 不过你可以将常用的表优先analyze一下, 就可以放心大胆的提供服务了.PostgreSQL 9.4于昨天(2014-12-18)正式
2015-10-14 21:16:45
995
转载 Primary & Standby with 2 HOST HA for PostgreSQL (auto failover failback)
元旦2天, 弄了个小玩意, 适合2台主机, 使用PostgreSQL流复制组建HA.实现自动的failover和failback.已经提交到github, 主备角色各一个虚拟IP.https://github.com/digoal/PostgreSQL_HA_with_primary_standby_2vip有此类需求的朋友可以下载测试. 之前还
2015-10-14 21:14:52
1258
转载 PostgreSQL aggregate function 4 : Hypothetical-Set Aggregate Functions
本文讲一下聚合函数的最后一个分类, Hypothetical-Set Aggregate Functions.这类聚合函数还有对应的窗口函数, 首先来看一下对应窗口函数的用法.rank()bigintrank of the current row with gaps; same as row_number of its first peer
2015-10-14 21:11:02
504
PostgreSQL的Window分析函数源码优化
2015-11-04
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人