PostgreSQL上选择MAX(COUNT)的数据出来

昨天晚上,和同事讨论了一个SQL的问题,是如何选择根据某字段分组,然后取出MAX COUNT(XX) 值的数据出来。例如数据是:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
test=# select * from tgroup;
id | age | point
----+-----+-------
1 | 1 | 11
2 | 1 | 32
3 | 2 | 32
4 | 2 | 13
5 | 2 | 33
6 | 2 | 38
7 | 3 | 38
8 | 2 | 38
9 | 2 | 38
10 | 2 | 38
11 | 2 | 38
(11 rows)
test=#

现在要选择出根据 POINT 分组里包含个数最大的值。大概意思是:MAX(COUNT(*)) FROM tgroup GROUP BY POINT;
所以,一开始,我们的SQL语句是(失败):

1
select MAX(COUNT(*)) FROM tgroup group by point;

然而,我们得出的错误是:

1
aggregate function calls cannot be nested

原来,聚集函数是不能嵌套调用的。

然后又想到能不能使用子查询来完成。SQL如下(成功) :

1
select max(sum) from ( select count(*) as sum from tgroup group by point) as t;

再来一条,不使用子查询,而是使用ORDER BY 结合 LIMIT 来完成,SQL语句如下(成功):

1
select count(*) as sum from tgroup group by point order by sum desc limit 1;

最后使用PG里的CTE表达式最容易理解(成功):

1
with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;

那他们的性能是何呢?测试了一千一百五十多万的数据。每条SQL性能如何?

1
2
3
4
5
6
7
test=# select count(*) from tgroup;
count
----------
11534336
(1 row)
test=#

使用子查询的性能(POINT没有索引)

1
2
3
4
5
6
7
test=# select max(sum) from ( select count(*) as sum from tgroup group by point) as t;
max
---------
6291456
(1 row)
Time: 3055.716 ms

ORDER BY 结合 LIMIT(POINT没有索引)

1
2
3
4
5
6
7
8
test=# select count(*) as sum from tgroup group by point order by sum desc limit 1;
sum
---------
6291456
(1 row)
Time: 3047.152 ms
test=#

使用CTE表达式(POINT没有索引)

1
2
3
4
5
6
7
test=# with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;
max
---------
6291456
(1 row)
Time: 25675.005 ms

后面为POINT添加索引,速度只有CTE表达式的加快了一倍(添加索引其实也不太科学,POINT的数据分布不均匀,重复的数据比较多,因为是通过 insert into select 的方式来生成大量数据的,只是想看一下添加索引后的效果):

1
2
3
4
5
6
7
8
test=# with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;
max
---------
6291456
(1 row)
Time: 11735.775 ms
test=#

其他两种方式,并没有什么变化。看执行计划,其他两种依然是使用Seq Scan的方式,而添加了索引后,CTE的方式使用了 CTE Scan + IndexOnlyScan的方式。

1
2
3
4
5
6
7
8
9
10
11
test=# explain with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Aggregate (cost=955503.54..955503.55 rows=1 width=8)
CTE cte
-> WindowAgg (cost=0.43..695980.98 rows=11534336 width=4)
-> Index Only Scan using tgroup_point on tgroup (cost=0.43..522965.94 rows=11534336 width=4)
-> CTE Scan on cte (cost=0.00..230686.72 rows=11534336 width=8)
(5 rows)
Time: 0.909 ms

总结

看来没有 WHERE 或其他条件过滤数据而且数据量非常大的情况下,不适宜使用CTE表达式,因为它本质是一个一次性视图,生成一张这么大的视图,性能也快不到哪里去(可能使用物化视图会好点,不过没有测试过)。在大量数据情况下,还是使用普通的全表扫描比使用生成CTE再全表扫描来得快。这也应验了之前翻译篇文章的强调:CTE表达式的作用,真的不是为了加快查询速度,而仅仅是为了方便。冏 ~~。

[翻译]PostgreSQL版本策略

原文

版本策略

我们始终建议所有用户都运行最新可用的次版本号的发行版,无论你正在使用哪个主版本的PostgreSQL。

PostgreSQL主版本包含新特性并且大概一年发行一次。主版本是通过增加第一个或第二个版本数字来标识的,例如:9.1 到 9.2

主版本通常改变内部系统表和数据文件的格式。这些改变通常是非常复杂的,所以,我们不保证所有存储数据会向后兼容。主版本一般使用 备份/恢复 数据库或者使用 pg_upgrade模块来升级。

次版本是版本号的第三部分的数字。例如:9.2.3 到 9.2.4。PostgreSQL团队仅在修复bugs时才会增加次版本号。所有用户都尽可能应该升级到最新的次版本号。虽然升级总是会有风险,PostgreSQL次版本升级仅修复那些经常遇到、安全性以及数据崩溃的bugs,以减少升级的风险。社区认为,不升级比升级更危险。

升级次版本,不要求备份和恢复数据;仅仅只需停止数据库服务器,安装更新的二进制文件,然后重新启动服务器即可。对于某些发行版本,可能需要手工升级,所以,在升级之前,请必须阅读发行版的提示。

PostgreSQL发行版支持策略

PostgreSQL目的是对主版本号完整地支持5年。

当一个发行版不再受支持时,我们可能(这取决于我们提交者的决定)会继续为源代码进行打上关键的补丁。但项目不会产生正式发行或者二进制包,但是更新后的源代码可以从我们的代码控制系统里获取。

该策略会在尽最大努力的基础上实行。在极端情况下,它可能在计划的生命周期内就不再被支持了;例如,如果在给定的主版本上发现有一个严重的bug超出了代码稳定性或者要牺牲程序序兼容性来解决的。在这种情况下,该主要版本就要提前退休了。

PostgreSQL最可靠的升级方案[实践]

注意事项

1
2
1. 如果原先的数据库安装了第三方的扩展,请在升级的新版本服务器上也先安装好这些第三方扩展(contrib)
2. 相应的表空间配置也要与原先的一致

升级步骤

1
2
3
4
5
6
7
8
1. 以PG管理员的身份运行以下命令来备份所有数据库信息(包括用户,角色,等)
pg_dumpall > outfile
2. 源码安装PG,请参考安装步骤:
http://dreamer-yzy.github.io/2014/12/03/PostgreSQL%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0%EF%BC%88%E4%B8%80%EF%BC%89/
3. 恢复DB(记得将最新版本的DB服务器运行起来先),运行以下命令
psql -f outfile postgres

打完收工。

[翻译]在Ubuntu服务器上将PostgreSQL从9.1升级到9.3

原文地址

从不同的重大版本升级PostgreSQL(例如,从9.1升级到9.3),基本上有三种方法:

用 pg_dump 升级

如果可以的话,首先推荐的方法是使用新版本(9.3)进行二进制备份一个老(9.1)版本数据,然后在新版本创建的集群中恢复数据。

这种途径,通常,是比较慢的,但是也是最实用的。使它进行得更加快的方法之一是,使用并发。为了并行备份任务,你可以这样子做:

1
$ pg_dump --format=directory --jobs=4 --no-synchronized-snapshots --file=/path/to/mydump mydatabase

你可以为每个数据库进行这样子的备份,并调整 --jobs=4 参数值到任何值(测试从2到CPU核心数,并看看哪个更快)。当然,在备份期间,没人应该连接到数据库,任何的修改将会导致备份中断(因为非安全选项 --no-synchronized-snapshots

之后,你可以使用pg_restore来恢复到新的实例中:

1
2
$ createdb <options> -T template0 mydatabase
$ pg_restore --exit-on-error --jobs=4 --dbname=mydatabase /path/to/mydump

之后, 建议在数据库上执行ANALYZE命令:

1
$ vacuumdb --analyze-only mydatabase

(如果你可以等待时间的话,仅仅运行 --analyze 来同时进行 VACUUM 数据库并更新可视数据字典)

使用 pg_upgrade 升级

另一种方法,是使用扩展包中的 pg_upgrade。它提供了一个非常快速的方法来升级PostgreSQL,就是使用 --link 方法。
使用之前,你必须备份整个数据目录,因为在 --link 模式下,如果出错,你可能丢失所有数据(包括新旧数据)。并且,请完整地阅读文档,特别是底部的提示(pg_upgrade有许多限制)

使用基于复制工具的触发器来升级

另一种升级版本的选项,是使用基于触发器的复制工具 。比如Slony, Bucardo和Londiste

这个选项可能用于最少停机时间,但是也是最难操作的。

这样做的话,需要建立一个 master-slave,主库是你当前版本(9.1),从库是新版本(9.3)。之后,等待第一次同步(系统仍然在生产环境),之后你关闭所有连接到数据库(停机时间从这里开始),等待从库赶上,然后提升(从库)到主库,然后重定向所有客户/应用程序到新版本数据库。打完收工。

Slony文档提供了一步一步地教你使用Slony来升级PostgreSQL.

应该选择哪个?

Well, as always depends, resuming:
好了,这要取决于什么,总结一下:

dump+restore是最可靠的,但通常也是最慢的一种(尽管,并行性可以带来更好的结果)

pg_upgrade是对于比较少的停机时间来说是最好的选择之一(如果你能使用的话,看看它的限制)。它通常只需要花数分钟的时间,甚至对于大的数据库也是这样。

触发器复制,毫无疑问是最少停机时间(几乎为0)的做法,但是它真的好难实行,并且我仅仅建议专家(PostgreSQL和复制工具二者都非常熟悉的专家)

希望我可以帮到你。祝你好运。

[翻译]MySQL与PostgreSQL9.0的复制功能对比

源文地址

作者:Robin Schumacher 和 Gary Carter,EnterpriseDB 公司

原文:
Replication is one of the most popular features used in RDBMS’s today. Replication is used for disaster recovery purposes (i.e. backup or warm stand-by servers), reporting systems where query activity is offloaded onto another machine to conserve resources on the transactional server, and scale-out architectures that use sharding or other methods to increase overall query performance and data throughput.

翻译:
今天,“复制”是关系数据库的最受欢迎的功能之一。复制的目的是用于灾难恢复(也就是备份或者叫“热”备用服务器),用于在事务服务器上报告系统,将查询活动转移到其他机器,以节约资源。并且这种分区或其他方法的横向扩展构架将会提升整体的查询性能以及数据的吞吐率。

原文:
Replication is not restricted to only the major proprietary databases; open source databases such as MySQL and PostgreSQL also offer replication as a feature. While MySQL has offered built-in replication for a number of years, PostgreSQL replication used to be accomplished via community software that was an add-on to the core Postgres Server. That all changed with the release of version 9.0 of PostgreSQL, which now offers built-in streaming replication that is based on its proven write ahead log technology.

翻译:
复制功能,并不仅仅限制在专利的商业数据库,一些开源的比如:MySQL 和 PostgreSQL 也提供复制的功能。当MySQL已经内建提供复制功能好几年时,PostgreSQL的复制功能还只是社区通过附加组件形式被添加到核心PostgreSQL服务器。但是,直到PostgreSQL9.0版本时,一切都发生改变了,PostgreSQL现在提供内建一种基于WAL(预写日志)技术的流复制功能。

原文:
With the two most popular open source databases now providing built-in replication, questions are being asked about how they differ in their replication technologies. What follows is a brief overview of both MySQL and PostgreSQL replication, with a brief compare and contrast of the implementations being performed immediately afterwards.

翻译:
现在,这两个最流行的开源数据库都已经提供了内建的复制功能,那么问题来了:他们两者之间的复制功能有什么区别?下面对MySQL和PostgreSQL作一些简要概述,作一个简单的比较和它们的实现区别之后来简单实践一下。

原文:
An Overview of MySQL Replication

Asynchronous replication was introduced into Oracle’s MySQL with version 3.23 and today it remains the primary feature employed by many MySQL users to create scale-out architectures, standby servers, read-only data marts, and more. The various supported MySQL replication topologies include:

• Single master to one slave

• Single master to multiple slaves

• Single master to one slave to one or more slaves

• Circular replication (A to B to C and back to A)

• Master to master

The major replication topology not currently supported in Oracle’s MySQL today is multi-source replication: having one or more master servers feed a single slave.

A graphical view of how MySQL replication functions can be represented as follows:

翻译:
MySQL的复制功能概要

异步复制在MySQL的3.23版本时就被引入了。今天,异步复制仍然是许多MySQL用户用于横向扩展、备库、只读数据等等的主要功能。MySQL支持的各种拓扑结构包括:

• 单主,一从

• 单主,多从

• 单主一从到多从

• 环形复制(A - B - C - A)

• 主对主

对于现在的MySQL主要不支持的拓扑结构是“多源复制”,也就是:拥有一个或多个主服务器,但只有一个从库。

MySQL复制功能的图形化表示如下:

原文:
Object, data, and security operations run on the master are copied to the master server’s binary log. A user has the option of replicating an entire server, one or more databases, or just selected tables (although filtering by table is only done on the slave). The slave server obtains information from the master’s binary log over the network , copies the commands and/or data, and first applies them to the slave’s relay binary log. That log is then read by another process – the SQL thread – that applies the replicated operations/data to the slave database and its binary log.

翻译:
在主库上,对象、数据和安全的操作全部都会被复制到主库服务器的二进制日志。用户可以选择复制整个服务器、一个或多个数据库、或仅仅选择某些表来进行复制(尽管表过滤只是在从库完成)。从库通过网络传输从主库获取二进制日志信息,复制的命令或数据会首先应用到从库的 relay 二进制日志里。这个 relay 二进制日志,会被其他工具处理 —— SQL 线程,SQL线程会将replay二进制日志里复制的操作命令和数据应用到从库自己的二进制日志里。

原文:
Prior to release 5.1, MySQL replication was statement-based, meaning that the actual SQL commands were replicated from the master to one or more slaves. However, certain use cases did not lend themselves to statement-based replication (e.g. non-deterministic function calls) so in MySQL 5.1 row-based replication was introduced. A user now has the option of setting a configuration parameter to use either statement or row-based replication.

翻译:
在5.1版本之前,MySQL 复制是基于语句的,意味着实际的SQL命令是通过复制从主库到一个或多个备库中的。然而,某些使用情况不能让它们基于语句复制(例如,非确定函数调用)。所以,在MySQL 5.1版本提供了基于行复制功能。用户现在可以通过配置相应的参数来决定是使用基于语句还是基于行复制。

原文:
The primary bottleneck for busy MySQL replication configurations is the single-threaded nature of its design: replication operations are not multi-threaded at the moment, although MySQL has declared it is coming in a future release. This limitation can cause some slave servers under heavy load to get far behind the master in regards to applying binary log information.

翻译:
在繁忙时的MySQL复制配置里,主要的瓶颈是由于它自身的单线程设计性质:复制操作都不是多线程的,尽管MySQL已经声称将来会将这个多线程特性添加到未来的版本中。这个限制可能导致从库在高负载情况下应用执行二进制日志将会远远落后于主库的日志信息。

原文:
Setting up MySQL replication is a fairly painless process. Although various setup procedures exist, in general, the following is a basic outline of how it is done:

• The master and slave servers are identified

• The master server is modified to include a replication security account

• The master server’s MySQL configuration file is modified to enable binary logging. A few other parameters are included as well (e.g. a unique server ID, type of replication such as statement or row-based, etc.)

• The slave server’s MySQL configuration file is modified to include a unique server ID

• The master server is restarted

• The master server’s log file position is recorded

• The master’s data is copied to the slave to initially seed the slave server. This can be done via a cold backup/restore, using the mysqldump utility, locking the master tables and doing a file copy, etc.

• The slave server is restarted

• The MySQL CHANGE MASTER command is executed on the slave server to set the master host name on the slave server as well as other parameters such as the master account username and password, the log file name, and beginning log file position

翻译:
设置MySQL的复制功能是一个非常痛苦的过程。尽管有许多种设置规则存在,但通常,以下是一些通常的步骤:

• 主库和从库都要标识

• 主库添加一个用于安全复制的账号

• 在主库中修改配置文件以开启二进制日志功能,还有一些其他的参数也要开启。 (比如:唯一的服务器ID, 复制的类型,等等)

• 在从库中修改配置文件,添加一个唯一的服务器ID。

• 重启主服务器

• 主库的日志文件位置被记录。

• 主库的数据将被初始化到从库中。这是通过冷备份/恢复完成的,例如使用:mysqldump 工具,锁住主库的表然后完成文件复制,等等。

• 重启从库

• MySQL CHANGE MASTER 命令是在从库服务器上执行设置将主库的主机名以及其他的参数如:主库的用户名,密码,日志文件名以及日志文件起始位置设置到从库。

原文:
Once set up, MySQL replication is quite reliable. Being asynchronous in nature, however, there are use cases that could result in data loss between a master and slave. To help combat these situations, MySQL 5.5 introduced semi-synchronous replication where a pending transaction is sent from a master to a slave, but not committed on the slave; it merely ‘lands’ safely on the slave to be run as soon as possible. Once the master is notified that the transaction is safely recorded on the slave, then the transaction is committed on the master.

翻译:
一旦设置完毕,MySQL复制是相当可靠的。因为本质上是异步的,所以有一些使用情况下还是会导致在主库和从库之间丢失数据。为了帮助解决这个问题,MySQL 5.5 提供了“半同步”复制,它会将事务从主库发送到从库,但在从库并未提交(只是记录到relay二进制日志)。它仅仅是尽可能快速地被安全送到从库。一旦主库收到事务已经被安全地记录在从库上,然后主库才会正式提交事务。

原文:
In terms of MySQL replication limitations and missing features, besides the already mentioned single threaded nature of the implementation and the inability to perform multi-source replication, other wish-list items include a full synchronous option, conflict detection and resolution, time-delayed replication, changing the binary log to a storage engine, better replication filtering on the master, global statement ID’s, and graphical tools to manage replication functions.

翻译:
MySQL在复制功能方面有许多限制以及不足,除了之前已经提过的单线程性质,还有不能进行多源进制,其他的希望加入的特性包括:完全同步选项,冲突检测及解决办法,延时复制,更改二进制日志的存储引擎,在主库上更好的复制过滤功能,全局语句的ID标识以及管理复制函数的图形化工具。

原文:
There are third-party providers of MySQL replication solutions that overcome some of the current shortcomings in what is provided out-of-the-box with MySQL. One example is Continuent’s Tungsten product.

For more information about Oracle’s MySQL replication, see: http://dev.mysql.com/doc/refman/5.5/en/replication.html.

翻译:
有许多第三方提供即开即用的MySQL复制解决方案来克服当前MySQL内建复制功能的缺点。一个例子是:Continuent’s Tungsten 的数据库产品。
更多关于MySQL复制功能,请看:MySQL5.5复制

原文:
An Overview of PostgreSQL Replication

PostgreSQL replication is based on a mature and long used technology called write ahead log (WAL) archiving. WAL technology has been in use since version 7.1 and has been used in features such as backup and restore and warm standby servers (i.e. slave servers offline kept in synch with the master to step in during crash recovery) for high availability.

PostgreSQL 9.0 introduced significant enhancements producing extremely fast WAL processing that results in near real-time replication and hot standby capabilities for slave servers. The supported PostgreSQL replication topologies include:

• Single master to one slave

• Single master to multiple slaves

A graphical view of how PostgreSQL replication functions can be represented as follows:

翻译:
PostgreSQL的复制功能概要

PostgreSQL 复制功能是基于一个成熟并且被长时间使用的技术,叫WAL(预写日志)归档。WAL技术已经在PostgreSQL 7.0版本被使用,并且是用在备份/恢复和热备用服务器的高可用中。(比如,从库离线【注,这里我认为是作者写错了,应该是online在线,而不是offline离线】保持与主库同步,并且在主库崩溃时介入以进行恢复)。

PostgreSQL9.0 版本显著地改进以产生极快的WAL日志处理,结果就是一个几乎是近实时复制并且是双机(主从)热备功能的从库。PostgreSQL支持的复制拓扑结构包括:

• 单主单从

• 单主多从

A graphical view of how PostgreSQL replication functions can be represented as follows:

PostgreSQL复制功能的图形化表示如下:

原文:

All objects and data (including schema) and security operation executed on the master are written to the WAL log directly on the slave machine for safety (avoiding complete data loss in the event of a catastrophic master failure). WAL also ensures that no transaction is committed on the master until a successful write of the WAL log has occurred. No filtering is currently possible (although replication with filtering is possible with the xDB Replication Server from EnterpriseDB) so a complete copy of the master is replicated on the slave.

翻译:
所有的对象和数据(包括模式)和在主库安全的操作都被写到WAL日志会立即安全地同步地从库(完全避免了在主库发生灾难时导致的数据丢失)。WAL也会确保在主库上不会有事务被提交,直到该事务已经成功地写到入WAL日志。当前版本并没有选择性复制功能(尽管选择性复制功能可能在 EnterpriseDB 的 xDB复制服务器提供了),所以主库会完整地被复制到从库。

原文:
The slave then applies the WAL log by directly rewriting the raw table data on disk, which is much faster than statement based replication. It is also safer since statements such as:

INSERT INTO table (column) VALUES (SELECT function());

may have unexpected and inconsistent results if the function returns different values on different servers - perhaps because it involves a generated timestamp or uuid.

翻译:
从库然后直接应用磁盘上的WAL日志来重写元数据,这点比基于语句复制更加快速。并且,遇到以下这种SQL时都是安全的:

INSERT INTO table (column) VALUES (SELECT function());

如果这个function函数在主从两个不同的服务器之间产生不同的值,这可能有意想不到和不一致的结果。比如可能它调用一个产生UUID或Timestamp的函数。

原文:
The primary limitations of PostgreSQL replication are topology based. It cannot currently do cascading replication or filter tables by rows for replication. Again, these are capabilities available in a separate replication solution from EnterpriseDB called xDB Replication Server.

翻译:
PostgreSQL复制的主要限制是它的拓扑结构。它目前不能进行联级复制,也不能通过行来过滤表进行复制。同样,这些功能可以通过 EnterpriseDB 里单独提供一种叫 xDB 复制服务器来解决。

原文:
Setting up PostgreSQL replication is very straightforward. WAL logging is always enabled with minimal configuration needed by the user to utilize replication. The basic process to get replication going is:

• The master and slave servers are identified

• The postgresql.conf file on the master is edited to turn on streaming replication

• The pg_hba.conf file on the master is edited in order to let the slave connect

• The recovery.conf and postgresql.conf files on the slave are edited to start up replication and hot standby

• The master is shutdown and the data files are copied to the slave

• The slave is started first

• The master is started

翻译:
设置PostgreSQL复制是非常简单的。开启WAL日志是用户使用复制的最小化配置。使用复制的基本流程如下:

• 主库和从库都要标识

• 编辑在主库的postgresql.conf 配置文件里,以开启流复制

• 编辑在主库的pg_hba.conf 配置文件,以让从库连接到主库

• 编辑在从库的 recovery.conf 配置文件和postgresql.conf 配置文件,以开始复制和热备用

• 关闭主库,并且复制data目录所有文件到从库

• 先启动从库

• 再启动从库

原文:
The secret sauce to PostgreSQL 9.0’s extremely reliable WAL based replication is a set of enhancements to efficiently stream very small WAL segments compared to earlier versions. Like MySQL there are cases where data loss could occur – however, depending on how you configure the system, your hardware architecture, and load, its possible the data loss could be as small as a single transaction.

PostgreSQL does not currently have native synchronous replication. However, there are multiple replication options available from other community and third-party software providers. PostgreSQL offers multiple solutions for multi-master replication, including solutions based on two phase commit. Offerings include Bucardo, rubyrep, PgPool and PgPool-II and Tungsten Replicator as well as some proprietary solutions. Another promising approach, implementing eager (synchronous) replication is Postgres-R, however it is still in development. Yet another project implementing synchronous replication is Postgres-XC, which is a shared-nothing, transactional scale-out solution that is still under development.

For more information on PostgreSQL replication see:

PostgreSQL Documentation: http://www.enterprisedb.com/docs/en/9.0/pg/high-availability.html

Bucardo: http://bucardo.org/wiki/Bucardo

PgPool-II: http://pgpool.projects.postgresql.org/

Tungsten Replication: http://www.continuent.com/community/tungsten-replicator

翻译:
PostgreSQL9.0非常可靠的秘密武器在于基于WAL日志复制,它是一套增强的高效流,利用非常小的一段WAL来进行早期版本的比较。像MySQL在有些情况下可能会导致数据丢失,然而,这取决于你的系统、你的硬件构架以及负载如何,它丢失的数据可能非常小,如:一个事务的数据。

PostgreSQL目前还没有本地同步复制。然而,有许多种通过社区和第三方软件提供商提供的可用复制方案。PostgreSQL 提供许多“多主复制”的解决方案,包括基于两阶段提交的解决方案。包括:Bucardo, rubyrep, PgPool and PgPool-II and Tungsten Replicator 以及其他一些专有的解决方案。另一种非常有前景的解决办法,实现 饥饿(同步)复制的Postgres-R产品,然而,它目前还在开发中。还有另一个项目实现同步复制的是Postgres-XC,它是一个无共享、事务横向扩展的解决方案,目前也是处于开发中。

更多关于PostgreSQL复制,请看:
PostgreSQL 文档: http://www.enterprisedb.com/docs/en/9.0/pg/high-availability.html

Bucardo: http://bucardo.org/wiki/Bucardo

PgPool-II: http://pgpool.projects.postgresql.org/

Tungsten Replication: http://www.continuent.com/community/tungsten-replicator

原文:
A Brief Compare and Contrast of MySQL and PostgreSQL Replication

Those wanting to use an open source database for a particular application project that requires replication have two good choices in MySQL and PostgreSQL. But, the question naturally arises, which should be used? Is one just as good as the other?

As demonstrated above, there are both feature and functional differences between how MySQL and PostgreSQL implement replication. However, for many general application use cases, either MySQL or PostgreSQL replication will serve just fine; technically speaking, from a functional and performance perspective, it won’t matter which solution is chosen. That said, there still are some considerations to keep in mind in deciding between the different offerings. Some of these include the following:

• Oracle’s MySQL offers both statement and row-based replication, whereas PostgreSQL only uses the latter based on write ahead log information. There are pro’s and con’s to using statement-based replication, which MySQL has documented here: http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.html. It is generally acknowledged that row or WAL-based replication is the safest and most reliable form of replication. It does, however, result in larger log files for MySQL than the statement-based option does.

• MySQL currently supports more replication topologies than PostgreSQL (e.g. ring, etc.). However PostgreSQL does have a number of community supported replication offerings that help close this gap (e.g. Bucardo’s master-to-master solution).

• In regard to data loss, MySQL 5.5 offers the semi-synchronous option, which helps minimize the risk of master-slave synchronization problems due to a master server going down. For PostgreSQL, a full synchronous replication option is in development and scheduled for release sometime in 2011.

• As to replication filtering, MySQL provides filtering on the slave server, whereas with PostgreSQL, no filtering is available; in other worlds, the entire database from the master is replicated to the slave. With MySQL, all the information is sent, but then options exist to selectively apply the replicated events on the slave. However, as the MySQL binary log is not used for crash recovery purposes in the same way as PostgreSQL’s WAL is, a user can configure a MySQL master so only certain databases are logged and, in that sense, a filter for the master server is available.

• Both MySQL and PostgreSQL replication are single-threaded at the current time.

• With respect to monitoring replication, MySQL provides a number of SHOW commands to understand the state of replication between a master and slave. To date, PostgreSQL offers functions to compute the differences in log positions between the master and slave servers, but that is all that is currently provided in 9.0.

• For failover and load balancing, the PostgreSQL community provides pgPool, which is middleware that provides connection pooling, load balancing, failover, and more between replicated servers. MySQL 5.5 supports connection pooling in the Enterprise edition, but failover and load balancing must be handled via a third-party product or custom development.

翻译:
简单对比一下MySQL和PostgreSQL复制

那些想为一个需要复制功能的特定应用的项目使用开源数据库的人,MySQL和PostgreSQL是两个很好的选择。但是,问题自然而然产生了,我们应该使用哪个?还是说这两个一样好?

综上所述,MySQL和PostgreSQL都有复制功能,但是有不同的实现。然而,对于一般的应用来说,无论是MySQL还是PostgreSQL复制功能会工作得挺好;从技术上说,以及从功能和性能来看,它不会不管要选哪个数据库。这意味着,在不同产品之间还有一些值得注意的事项,其中包括以下内容:

• MySQL提供基于语句和基于行的复制,而PostgreSQL只有基于WAL日志信息。有赞成也有反对使用基于语句复制的,MySQL有文档介绍:http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.html 。一般认为,基于行或基于WAL 复制是最安全和最可靠的复制形式。的确如此,然而,这会导致比基于语句复制的形式产生更大的日志文件。

• MySQL目前比PostgreSQL支持更加多的复制拓扑结构(比如:环形等)。然而,PostgreSQL有许多种社区支持的复制选项,这就缩小了这个因拓扑结构种类而导致的距离(比如 Bucardo 的 主-主解决方案)

• 考虑到数据丢失,MySQL 5.5 提供了半同步选项,这有助于减小因主库崩溃而导致主从同步问题的风险。对于PostgreSQL,完全同步复制特性正在开发,并且计划于2011年正式可用。

• 对于过滤复制,MySQL 提供了在从库过滤,而在PostgreSQL,并不能使用过滤,换句话说,就是完整地将数据库从主库复制到从库。对于MySQL,所有信息都会被发送到从库,但如果开启过滤复制,从库会有选择地将事件应用到从库。然而,对于MySQL二进制日志并不是用于灾难恢复的,但PostgreSQL的WAL是可以用于灾难恢复的,用户可以配置MySQL主库指定哪些数据库会被记录到日志,在这种意义上,过滤器对于主库是可用的。

• 目前,MySQL和PostgreSQL复制都是单线程的

• 对于复制的监控,MySQL提供了许多 SHOW 命令去了解主从复制的状态。至今,PostgreSQL提供一些函数去计算主从日志位置的区别,但是当前只是在PostgreSQL9.0版本才提供的。

• 对于故障切换和负载均衡,PostgreSQL社区提供pgPool的中间件,pgPool提供连接池,负载均衡,故障切换和更多种复制形式。MySQL 5.5 在企业版里提供连接池,但是对于故障切换和负载均衡必须通过第三方产品或定制开发。

原文:
Conclusions

As was previously stated, for many application use cases, both Oracle’s MySQL and PostgreSQL replication will be an equally good choice. The best way to determine which is right for you is to download both and put each through a comprehensive evaluation.

You can download Oracle’s MySQL at http://www.mysql.com/downloads/, while both community and EnterpriseDB’s offerings of PostgreSQL can be found at: http://www.enterprisedb.com/products/download.do.

By Robin Schumacher and Gary Carter, www.enterprisedb.com

18 Nov 2010

翻译:
结论

正如前面所指出一样,对于许多应用程序的用例,MySQL和PostgreSQL都是非常好的选择。要决定哪一个最适合你,最好的办法就是同时下载它们两个,然后使用进行综合评估。

你可以在 http://www.mysql.com/downloads/ 下载MySQL,PostgreSQL的社区版和EnterpriseDB提供的PostgreSQL版本都可以在这个地址里找到:http://www.enterprisedb.com/products/download.do

作者:Robin Schumacher and Gary Carter, www.enterprisedb.com
18 Nov 2010


注:版权是原作者所有,我只是出于业余爱好进行翻译。

这也是我的处女版翻译文章,有许多不足或表达不清晰的地方,恳请各位指出,我会加以修改,一起为开源、为PostgreSQL作出一份力量。
于2014年12月9号星期二晚,广州

PostgreSQL窗口函数

什么是窗口函数

PostgreSQL窗口函数

窗口函数提供跨行相关的当前查询行集执行计算的能力。仅当调用跟着OVER子句的聚集函数,作为窗口函数;否则它们作为常规的聚合函数。

我个人的理解:窗口也是一种分组,但和 group by 的分组不同。窗口,可以提供分组之外,还可以执行对每个窗口进行计算。可以相像成是group by 后,然后对每个分组进行计算,而不像Group by ,只是单纯地分组。

窗口函数的语法

1
2
3
4
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )

窗口函数的调用总是包含一个OVER子句,即窗口函数的名称和参数。 该语法区别于普通函数或聚合功能。OVER子句决定究竟将 查询的行如何通过窗口函数拆分处理。OVER子句内的PARTITION BY分区指定 行划分成组,或分区,共享相同的PARTITION BY值。 对于每一行,窗口函数通过同一个分区作为当前行的行进行计算

如果OVER不使用PARTITION BY时即代表整个表。

典型的窗口函数用法

1
select path, cost, sum(cost) over (partition by path ) as sum_cost from tcost;

内置的窗口函数

Function Return Type Description
row_number() 行号 bigint number of the current row within its partition, counting from 1 。返回当前窗口的行数,计数从1开始。主要就是遇到相同排名时的区别,即相同的数值,排名是不同的,而且也不是确定的。
rank() 排名(保持间隔) bigint rank of the current row with gaps; same as row_number of its first peer。当前窗口中,相同的数值排名是相同的,但是还是会保留间隔的。比如:1,1,3。如果是 row_number 会是 1,2,3。也可能是:1,2,3这样子下去,这个看数据是否有相同。
dense_rank() 排名(不保持间隔) bigint rank of the current row without gaps; this function counts peer groups。这个函数与 rank() 一样,只是它不会保持间隔的,相同的数据在同一排名,然后会是下一个排名。如:1,1,2,3,3,4等。
percent_rank() 排名的百分比 double precision relative rank of the current row: (rank - 1) / (total rows - 1)。这条是得出结果的公式。可知相同的排名,结果是一样的。
cume_dist() double precision relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)。即小于等于当前行值的行数/总行数。结果为 0<结果<=1
ntile(num_buckets integer) 可以将结果集放到我们指定数目的组中 integer integer ranging from 1 to the argument value, dividing the partition as equally as possible。组的数目从1开始计。分组的依据:1,每组的记录数不能大于它上一组的记录数。2,所有组中的记录要么都相同,要么从某组开始后面所有组的记录数都与该组的记录数相同
lag(value any [, offset integer [, default any ]]) same type as value returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null 。向前获得相对于当前记录指定距离的那条记录的数据
lead(value any [, offset integer [, default any ]]) same type as value returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null。向后获得相对于当前记录指定距离的那条记录的数据
first_value(value any) same type as value returns value evaluated at the row that is the first row of the window frame。获取当前窗口的第一个值。
last_value(value any) same type as value returns value evaluated at the row that is the last row of the window frame 。获取当前窗口的最后一个值。
nth_value(value any, nth integer) same type as value returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row。获取窗口中第N个值。

PostgreSQL选择某组sum结果最小的所有数据

在 PostgreSQL 技术群里,今天发现有个人在群里“求救”,说想要执行一条SQL语句,获取某表中以某字段为组,并且sum(其他字段)结果最小的,所有结果。

比如,有张表如下:

1
path(int) cost(decimal)

想要的结果是,以path所有相同的字段分组,并且 sum(cost)字段,选择出sum(cost)值最小的,所有path字段。如:

我写的SQL:

1
2
with cte as (select path, cost, sum(cost) over (partition by path ) as sum_cost from tcost)
select path , cost from cte where sum_cost = (select min(sum_cost) from cte);

我的解题思路

在没有接触到PostgreSQL之前,我一直使用MySQL,每次想到“组”这个字,总会想到 group by 。虽然可能使用group by 也可能实现相同的结果,但是经常需要表自身join表,所以性能方面对于数据量大的表话,是满足不了要求的,即使是有索引。因为索引最适合于那种存在索引,而且选择率低的表,否则的话,索引的优势其实和全表扫描差不多,甚至有时候,常常是全表扫描比索引的全表扫描性能还要好。(当然,在PostgreSQL中,如果是只读索引来扫描的话,性能是最好的)。因为MySQL的 InnoDB 是索引组织表,所以索引全表和普通的全表扫描,性能几乎是没有差别。但是在PostgreSQL中,这种差别就很明显了,选择率大的索引全表扫描,比顺序全表扫描SeqScan慢好多。

说远了,回到题目上来。这思路虽然也是要分组,但这种分组跟group by 的分组差得比较远,这种需要一种“窗口函数”(Window Function,在Oracle里叫分析函数)来处理这钟需求,而且这种窗口函数的性能是比那种需要自表连接的性能快好多的,即使是没有索引情况下。之前在群里也遇到这种情况,利用窗口函数几秒钟就可以出结果,但那种自连接的(特别在数据量大的情况下)要几十分钟。这种窗口分组来处理数据,可以避免好多性能问题,而且非常易于理解。

所以,对于PostgreSQL,一有那种需要那种类似窗口的分组操作,首先要想到 Window Function,真的是非常好用。

PostgreSQL 进程构架

ps aux | grep postgres

1
2
3
4
5
6
postgres 31964 0.0 0.3 162180 12436 pts/0 S 18:09 0:00 /usr/local/pg/bin/postgres -D /usr/local/pg/data
postgres 31966 0.0 0.0 162312 2192 ? Ss 18:09 0:00 postgres: checkpointer process
postgres 31967 0.0 0.0 162180 1820 ? Ss 18:09 0:00 postgres: writer process
postgres 31968 0.0 0.0 162180 960 ? Ss 18:09 0:00 postgres: wal writer process
postgres 31969 0.0 0.0 163032 2064 ? Ss 18:09 0:00 postgres: autovacuum launcher process
postgres 31970 0.0 0.0 18000 948 ? Ss 18:09 0:00 postgres: stats collector process
1
2
3
4
5
6
7
postgres: logger process: 在PostgreSQL中称为SysLogger(8.0),用于整个系统的日志输出;
postgres: checkpointer process: 在PostgreSQL中称为Checkpointer(9.2),用于处理checkpoints;
postgres: writer process: 在PostgreSQL中称为BgWriter,用于将脏页刷出到磁盘;
postgres: wal writer process: 在PostgreSQL中称为WalWriter(8.3),处理预写日志输出;
postgres: autovacuum launcher process: 在PostgreSQL中称为AutoVacuum(8.1),用于系统的自动清理;
postgres: archiver process: 在PostgreSQL中称为PgArch,用于预写日志归档;
postgres: stats collector process: 在PostgreSQL中称为PgStat,用于统计数据收集。

PostgreSQL基础学习(一)

简介

PostgreSQL官网

PostgreSQL 官网号称:它是最世界最先进的开源数据库(顺便说一下,MySQL官网说它自己是最流行的开源数据库)。我觉得这一句话,也已经非常足够概括 PostgreSQL 了(MySQL 官网的那句话,也概括了它自己的特点,一个是最先进,一个是最流行)。也有号称是 Oracle 的开源版,因为PostgreSQL被经常拿来与Oracle这个重量级的商业数据库比较。的确,PostgreSQL 与 Oracle 兼容性非常强,Oracle 的 DBA 可以非常快地上手 PostgreSQL。在 PostgreSQL 技术群里的大牛大多都是从 Oracle 转到 PostgreSQL,使我印象非常深刻就是 @德哥,绝对是PostgreSQL的大神级别。我也是跟着德哥的视频一边看,一边学习的。

安装(以 Ubuntu 下源码安装 PostgreSQL 9.3.5 为例)

下载

PostgreSQL 源码下载地址

安装与使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#安装必要的编译环境
sudo apt-get install build-essential libreadline-dev
#解压
tar -xvjf postgresql-9.3.5.tar.bz2
#创建一个目录,将PostgreSQL安装到这个目录
sudo mkdir /usr/local/pg
#配置
cd postgresql-9.3.5
./configure --prefix=/usr/local/pg
#开始编译
make -j
-j:开启多核编译
如果有:All of PostgreSQL successfully made. Ready to install. 表示编译成功
#安装
sudo make install
如果出现:PostgreSQL installation complete. 表示安装成功
#添加 PostgreSQL 用户
sudo useradd -s /bin/bash -d /home/postgres postgres
#创建 PostgreSQL 的 data 数据目录
sudo mkdir /usr/local/pg/data -p
#修改拥有者身份
sudo chown postgres:postgres /usr/local/pg/ -R
#初始化数据库
su - postgres
/usr/local/pg/bin/initdb -D /usr/local/pg/data
#启动数据库
/usr/local/pg/bin/postgres -D /usr/local/pg/data >logfile 2>&1 &
或者
/usr/local/pg/bin/pg_ctl -D /usr/local/pg/data -l logfile start
#创建数据库
/usr/local/pg/bin/createdb test
#连接数据库
/usr/local/pg/bin/psql test

配置环境变量

为了避免每次使用绝对路径以及一些参数问题,可以使用一些环境变量来代替,PostgreSQL 在没有指定参数时,就会读取这些环境变量的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
vi /home/postgres/.bash_profile
#这个是默认的PostgreSQL端口
export PGPORT=5432
#这个是PostgreSQL数据目录
export PGDATA=/usr/local/pg/data
#所使用的语言
export LANG=en_US.utf8
#PostgreSQL 安装目录
export PGHOME=/usr/local/pg
#PostgreSQL 连接库文件
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
#将PostgreSQL的命令行工具添加到 PATH 环境变量 ,这样子就不用每次使用绝对路径了
export PATH=$PGHOME/bin:$PATH
#PostgreSQL的 man 手册
export MANPATH=$PGHOME/share/man:$MANPATH
#PostgreSQL的默认用户
export PGUSER=postgres
#这个是PostgreSQL默认主机地址
export PGHOST=127.0.0.1
#连接数据库时默认的数据库名
export PGDATABASE=postgre

PostgreSQL所有环境变量

PostgreSQL 的 bin 目录常用工具说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
#clusterdb
— 对一个PostgreSQL数据库进行建簇
#createdb
— 创建一个新的 PostgreSQL 数据库
#createlang
— 定义一种新的 PostgreSQL 过程语言
#createuser
— 定义一个新的 PostgreSQL 用户帐户
#dropdb
— 删除一个现有 PostgreSQL 数据库
#droplang
— 删除一种 PostgreSQL 过程语言
#dropuser
— 删除一个 PostgreSQL 用户帐户
#ecpg
— 嵌入的 SQL C 预处理器
#pg_basebackup
-- 做一个PostgreSQL集群的基础备份
#pg_config
— 检索已安装版本的 PostgreSQL 的信息
#pg_dump
— 将一个PostgreSQL数据库抽出到一个脚本文件或者其它归档文件中
#pg_dumpall
— 抽出一个 PostgreSQL 数据库集群到脚本文件中
#pg_restore
— 从一个由 pg_dump 创建的备份文件中恢复 PostgreSQL 数据库。
#psql
— PostgreSQL 交互终端
#reindexdb
-- 重新建立一个数据库索引
#vacuumdb
— 收集垃圾并且分析一个PostgreSQL 数据库
#pg_receivexlog
从另一台运行PG的数据库里接收 wal 日志
#pg_resetxlog
重置一个 PostgreSQL 数据库集群的预写日志以及其它控制内容

PostgreSQL 的 data 目录说明