注意事项

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

打完收工。

原文地址

从不同的重大版本升级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和复制工具二者都非常熟悉的专家）

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

上星期请教了条SQL为什么没有使用到索引的问题，引起了我对SQL执行顺序的疑惑，查了不少资料，收集到比较认可的答案如下，引用资料也在后面标识了。有什么不同的见解，还请大牛指出。

各部分的执行顺序

1
2
3
4
5
6
7
8
9
10
11

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP(LIMIT)

来源资料：
Stackoverflow whats-the-execute-order-of-the-different-parts-of-a-sql-select-statement

Stackoverflow order-of-execution-of-the-query

MSDN order-of-execution-of-sql-queries

翻译于dba.stackexchange.com
版权归原作者所有，本人只是业余爱好翻译。

这个问题是相当广泛的，但尽我所能给你一个普通的回答。

CTEs

• 不可以建索引（但可以使用引用到的对象的索引）
• 不能有约束
• 本质上是一个一次性视图
• 只在下一个执行查询时存在
• 可以递归
• 没有专门的统计状态数据（只依赖于底层对象）

临时表

• 是存在于临时数据库的真正物化的表
• 可以建索引
• 可以有约束
• 在当前连接的会话中会一直存在
• 可以被其他查询或子查询引用
• 引擎可以有专门为它准备的统计数据

至于何时使用哪个，他们有不同的应用场景。如果你会有一个非常大的结果集，或者需要不上一次地引用它，那请使用临时表。如果需要使用递归，一次性，或者做一些简单的逻辑计算，那就使用CTE。

注意，CTE永远不应该用于性能。使用CTE并不会提高你的性能，因为，它仅仅是个一次性视图。你可以用它做一些其他的事，但是为了加快查询真的并不是它的用途。

源文地址

作者: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窗口函数

窗口函数提供跨行相关的当前查询行集执行计算的能力。仅当调用跟着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;

内置的窗口函数

在 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，真的是非常好用。

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 官网号称：它是最世界最先进的开源数据库（顺便说一下，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 目录说明

代价模型

总代价 = IO 代价 + CPU 代价
COST = P * a_page_cpu_time + W * T

P：计划运行时访问的页数，a_page_cpu_time 是每个页读取的时间花费，其积反映了IO代价
T：访问的元组。反映了CPU花费。（存储层是以页面为单位，数据以页面的形式读入内存，每个页面上可能有多个元组，访问元组需要解析元组结构，才能把元组上的字段读出，这消耗的是CPU）。如果是索引扫描，则还会包括索引读取的花费。
W：权重因子。表明IO到CPU的相关性，又称选择率（selectivity）。选择率用于表示在关系R中，满足条件“A<op>a”的元组数与R的所有元组N的比值。

单表扫描算法

全表扫描，局部扫描。单表扫描与IO操作密切相关。

1）顺序扫描（SeqScan）。当无索引可用，或访问表中的大部分数据，或表的数据量很小时，使用顺序扫描效果较好。

2）索引扫描（IndexScan）。根据索引键读索引，找出物理元组的位置。【如果选择率很高，不适宜使用索引扫描】

3）只读索引扫描（IndexOnlyScan）。根据索引键读索引，索引中的数据能够满足条件判断，不需要读取数据页面。比索引扫描少了读取数据的IO花费。

4）行扫描（RowIdScan）。用于直接定位表中的某一行。对于元组，通常为元组增加特殊的列，通过特殊的列计算出元组r物理位置，然后直接读取元组对应的页面，获取元组。在PostgreSQL中称为【Tid】扫描，此种方式是在元组头上增加名为【CTID】的列，用这列的值可直接计算本条元组的物理存储位置。

5）并行表扫描（ParallelTableScan）。对同一个表，并行地、通过顺序的方式获取表的数据，结果是得到一个完整的表数据。

6）并行索引扫描（ParallelIndexScan）。对同一个表，并行地、通过索引的方式获取表的数据，将结果合并在一起。

7）组合多个索引扫描（MultipleIndexScan）。

顺序扫描代价估算公式

COST = N_page * a_tuple_IO_time + N_tuple * a_tuple_CPU_time

索引扫描代价估算公式

COST = C_index + N_page_index * a_tuple_IO_time
C_index：索引的IO花费。C_index = N_page_index * a_page_IO_time
N_page_index：索引作用下的可用元组数。N_page_index = N_tuple * 索引选择率

索引

本质上是通过索引直接定位表的物理元组，加快数据获取的方式，所以索引优化的手段是物理查询优化。

如何利用索引

1）索引列作为条件出现在 WHERE，HAVING， ON 子句中。

2）索引列是被连接的表（内表）对象的列且存在于连接条件中

除了上述两种情况，还有一些特殊情况可以使用索引，如：排序操作、在索引列上求MIN、MAX值等。

（1）对表做查询，没有列对应作为过滤条件（如出现在WHERE子句中），只能做顺序扫描。

（2）对表做查询，有列对象且索引列作为过滤条件，可做索引扫描。

（3）对表做查询，有列对象作为过滤条件，但索引列被运算符“-”处理，查询优化器不能在执行前进行取反运算，这时不可利用索引扫描，只能做顺序扫描。

（4）对表做查询，有列对象作为过滤条件，且目标列没有超出索引列，可做只读索引扫描，这种扫描方式比单纯的索引扫描的效率更高。

（5）对表做查询，有索引存在，但选择条件不包括索引列对象，只能使用顺序扫描。

（6）对表做查询，有索引存在，选择条件包括索引列对象，可使用索引扫描，对选择条件中不存在索引的列作为过滤器被使用。

（7）对表做查询，有索引存在，选择条件包括索引列对象，但索引列对象位于一个表达式中，参与了运算，不是“key=常量”格式，则索引不可使用，只能是顺序扫描。如：
select a. from a where a.a1 + a.a3 = 2；（a1列是索引），这时只能做顺序扫描。
或
select a. from a where a.a1 = 2 - a.a3 ；（a1列是索引），这时只能做顺序扫描。

（8）对表做查询，有索引列对象作为过滤条件，操作符是范围操作符 > 或 < ，可做索引扫描。

（9）对表做查询，有索引列对象作为过滤条件，操作符是范围操作符 <> ，不可做索引扫描。

（10）对表做查询，有索引列对象作为过滤条件，操作符是范围操作符BETWEEN-AND ，可做索引扫描。

索引列的位置对使用索引的影响

（1）索引列出现在目标列，通常不可使用索引（但不是全部情况下不能使用索引）

（2）聚集函数MIN / MAX用在索引列上，出现在目标列，可使用索引。

（3）索引列出现在WHERE子句中，可使用索引。

（4）索引列出现在 JOIN / ON 子句中，作为连接条件，有时不可使用索引。（这取决于代价估算模型）

（5）索引列出现在 JOIN / ON 子句中，作为限制条件满足“key <op> 常量 ”格式可用索引。

（6）（5）索引列出现在 WHERE子句中，但与子查询比较，格式上不满足"key <op> 常量"，不可用索引。

索引列对GROUP BY子句的影响

（1）索引列出现在 group by 子句中，不触发索引扫描。

（2）WHERE子句出现索引列，【且】GROUP BY 子句出现索引列，索引扫描被使用。

（3）WHERE子句中出现非索引列，且GROUP BY子句出现索引列，索引扫描不被使用。

索引列对HAVING子句的影响

（1）WHERE子句出现非索引列，且GROUP BY和HAVING子句出现索引列，索引扫描被使用。

索引列对ORDER BY子句的影响

（1）ORDER BY子句出现索引列，可使用索引。

（2）ORDER BY子句使用非索引列，不可使用索引扫描。

索引列对 DISTINCT 的影响

（1）DISTINCT 子句管辖范围内出现索引列，不可使用索引。

联合索引对索引使用的影响

（1）使用联合索引的全部索引键，可触发索引的使用。

（2）使用联合索引的前缀部分索引键。如：key_part_1 <op> 常量。可触发索引的使用。

（3）使用部分索引，但不是联合索引的前缀部分，如“key_part_2 <op> 常量"，不可触发索引的使用。

（4）使用索引索引的全部索引键，但索引键不是AND操作，不可触发索引的使用。

多个索引对索引使用的影响

（1）WHERE子句出现两个可利用的索引，优选最简单的索引。（但这也是要根据代价估算模型来决定的）

（2）WHERE子句出现两个可利用的索引且索引键有重叠部分，优选最简单的索引。

聚簇索引

是指表的一个或多个列作为索引的关键字，以关键字的具体值为依据，把所有具有相同值的元组连续放在外存上。当从磁盘扫描读取的块进入内存时，相同值的其他元组在内存中的概率增大，能有效减少IO。即：聚簇索引确定表中数据的物理顺序。聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。