postgresql读写分离的简单介绍

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

求教使用postgresql搭建一个集群，一主多从模式，读写分离

《一键获取可可V8 PHP》是一款可可网络验证系统，该系统需要认证的时候由客户端软件主动调用xmlhttp组件连接服务端进行数据交互，服务端与客户端数据交互的加解密算法采用RSA不对称算法（服务端使用私钥加解密，客户端使用公钥加解密）。

【软件特色】：

1、服务端采用快速安全的php脚本语言和MySQL数据库构建，且可以搭建两个服务端以保证在一个服务端宕机时软件自动连接另一服务端来认证；

2、管理端采用主流的网页模式，无需下载管理客户端，能上网就可以登录管理端操作（智能手机也可以登录管理端）；

3、客户端也就是你的软件，不论你使用哪种编程语言开发软件，都可以将可可网络验证系统无缝集成到您的软件中去；

4、可可网络验证做为商业软件的用户认证系统可更好的保护您的软件，其方便快捷的管理模式，会让你觉得管理软件的各种数据是件非常愉快的事。

相关问答

Q1: 如何使用mvcc postgresql

而DML语句，会操作当前版本。因此做到了读写分离的目的，提高数据库并发能力。

不同的数据库，实现MVCC的方法不同。Oracle和MySQL Innodb 存储引擎类似的使用undo来实现。

对于PostgreSQL数据库来讲，他没有undo，那么，PG又是怎么来实现他自己的MVCC呢？又有那些优缺点呢？

PG用copy tuple和tuple的xmin,xmax,cmin,cmax等标记来实现多版本。

xmin：在创建记录(tuple)时，记录此时，后面每次update也会更新。

xmax: 在删除tuple或者lock时，记录此时；如果记录没有被删除，那么此时为0。

Q2: postgresql哪种搭建集群的方式比较好

楼主是想用PostgreSQL的集群功能吗？

目前可行的就PostgreSQL 的standby功能，

看一下原文档的介绍。目前PostgreSQL做不到负载均衡的集群方案，以前有个读写分离的方案，楼主可以查一下。

Q3: MySQL与PostgreSQL比较哪个数据库更好

特性 MySQL PostgreSQL

实例通过执行 MySQL 命令（mysqld）启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysqld 实例。一个实例管理器可以监视 mysqld 的各个实例。

通过执行 Postmaster 进程（pg_ctl）启动实例。一个实例可以管理一个或多个数据库，这些数据库组成一个集群。集群是磁盘上的一个区域，这个区域在安装时初始化并由一个目录组成，所有数据都存储在这个目录中。使用 initdb 创建第一个数据库。一台机器上可以启动多个实例。

数据库数据库是命名的对象集合，是与实例中的其他数据库分离的实体。一个 MySQL 实例中的所有数据库共享同一个系统编目。数据库是命名的对象集合，每个数据库是与其他数据库分离的实体。每个数据库有自己的系统编目，但是所有数据库共享 pg_databases。

数据缓冲区通过 innodb_buffer_pool_size 配置参数设置数据缓冲区。这个参数是内存缓冲区的字节数，InnoDB 使用这个缓冲区来缓存表的数据和索引。在专用的数据库服务器上，这个参数最高可以设置为机器物理内存量的 80%。 Shared_buffers 缓存。在默认情况下分配 64 个缓冲区。默认的块大小是 8K。可以通过设置 postgresql.conf 文件中的 shared_buffers 参数来更新缓冲区缓存。

数据库连接客户机使用 CONNECT 或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。

身份验证 MySQL 在数据库级管理身份验证。基本只支持密码认证。 PostgreSQL 支持丰富的认证方法：信任认证、口令认证、Kerberos 认证、基于 Ident 的认证、LDAP 认证、PAM 认证

加密可以在表级指定密码来对数据进行加密。还可以使用 AES_ENCRYPT 和 AES_DECRYPT 函数对列数据进行加密和解密。可以通过 SSL 连接实现网络加密。可以使用 pgcrypto 库中的函数对列进行加密/解密。可以通过 SSL 连接实现网络加密。

审计可以对 querylog 执行 grep。可以在表上使用 PL/pgSQL 触发器来进行审计。

查询解释使用 EXPLAIN 命令查看查询的解释计划。使用 EXPLAIN 命令查看查询的解释计划。

备份、恢复和日志 InnoDB 使用写前（write-ahead）日志记录。支持在线和离线完全备份以及崩溃和事务恢复。需要第三方软件才能支持热备份。在数据目录的一个子目录中维护写前日志。支持在线和离线完全备份以及崩溃、时间点和事务恢复。可以支持热备份。

JDBC 驱动程序可以从参考资料下载 JDBC 驱动程序。可以从参考资料下载 JDBC 驱动程序。

表类型取决于存储引擎。例如，NDB 存储引擎支持分区表，内存引擎支持内存表。支持临时表、常规表以及范围和列表类型的分区表。不支持哈希分区表。由于PostgreSQL的表分区是通过表继承和规则系统完成了，所以可以实现更复杂的分区方式。

索引类型取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。支持 B-树、哈希、R-树和 Gist 索引。

约束支持主键、外键、惟一和非空约束。对检查约束进行解析，但是不强制实施。支持主键、外键、惟一、非空和检查约束。

存储过程和用户定义函数支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和 C++ 编写。用户定义函数可以用 SQL、C 和 C++ 编写。没有单独的存储过程，都是通过函数实现的。用户定义函数可以用 PL/pgSQL（专用的过程语言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。

触发器支持行前触发器、行后触发器和语句触发器，触发器语句用过程语言复合语句编写。支持行前触发器、行后触发器和语句触发器，触发器过程用 C 编写。

系统配置文件 my.conf Postgresql.conf

数据库配置 my.conf Postgresql.conf

客户机连接文件 my.conf pg_hba.conf

XML 支持有限的 XML 支持。有限的 XML 支持。

数据访问和管理服务器 OPTIMIZE TABLE —— 回收未使用的空间并消除数据文件的碎片

myisamchk -analyze —— 更新查询优化器所使用的统计数据（MyISAM 存储引擎）

mysql —— 命令行工具

MySQL Administrator —— 客户机 GUI 工具 Vacuum —— 回收未使用的空间

Analyze —— 更新查询优化器所使用的统计数据

psql —— 命令行工具

pgAdmin —— 客户机 GUI 工具

并发控制支持表级和行级锁。InnoDB 存储引擎支持 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。使用 SET TRANSACTION ISOLATION LEVEL 语句在事务级设置隔离级别。支持表级和行级锁。支持的 ANSI 隔离级别是 Read Committed（默认 —— 能看到查询启动时数据库的快照）和 Serialization（与 Repeatable Read 相似 —— 只能看到在事务启动之前提交的结果）。使用 SET TRANSACTION 语句在事务级设置隔离级别。使用 SET SESSION 在会话级进行设置。

MySQL相对于PostgreSQL的劣势：

MySQL

PostgreSQL

最重要的引擎InnoDB很早就由Oracle公司控制。目前整个MySQL数据库都由Oracle控制。

BSD协议，没有被大公司垄断。

对复杂查询的处理较弱，查询优化器不够成熟

很强大的查询优化器，支持很复杂的查询处理。

只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。

都支持

性能优化工具与度量信息不足

提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具。

InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。

不存在这个问题。

大部分查询只能使用表上的单一索引;在某些情况下，会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢。

不存在这个问题

表增加列，基本上是重建表和索引，会花很长时间。

表增加列，只是在数据字典中增加表定义，不会重建表

存储过程与触发器的功能有限。可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱

除支持pl/pgsql写存储过程，还支持perl、python、Tcl类型的存储过程：pl/perl，pl/python，pl/tcl。

也支持用C语言写存储过程。

不支持Sequence。

支持

不支持函数索引，只能在创建基于具体列的索引。

不支持物化视图。

支持函数索引，同时还支持部分数据索引，通过规则系统可以实现物化视图的功能。

执行计划并不是全局共享的, 仅仅在连接内部是共享的。

执行计划共享

MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。不支持递归查询、通用表表达式（Oracle的with 语句）或者窗口函数（分析函数）。

都支持

不支持用户自定义类型或域(domain)

支持。

对于时间、日期、间隔等时间类型没有秒以下级别的存储类型

可以精确到秒以下。

身份验证功能是完全内置的，不支持操作系统认证、PAM认证，不支持LDAP以及其它类似的外部身份验证功能。

支持OS认证、Kerberos 认证、Ident 的认证、LDAP 认证、PAM 认证

不支持database link。有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多

有dblink，同时还有一个dbi-link的东西，可以连接到oracle和mysql上。

Mysql Cluster可能与你的想象有较大差异。开源的cluster软件较少。

复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master.

有丰富的开源cluster软件支持。

explain看执行计划的结果简单。

explain返回丰富的信息。

类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务，并且不能回滚也不能做灾难恢复

DDL也是有事务的。

PostgreSQL主要优势：

1. PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。oracle数据库不用说了，是商业数据库，不开放。而MySQL数据库虽然是开源的，但现在随着SUN被oracle公司收购，现在基本上被oracle公司控制，其实在SUN被收购之前，MySQL中最重要的InnoDB引擎也是被oracle公司控制的，而在MySQL中很多重要的数据都是放在InnoDB引擎中的，反正我们公司都是这样的。所以如果MySQL的市场范围与oracle数据库的市场范围冲突时，oracle公司必定会牺牲MySQL，这是毫无疑问的。

2. 与PostgreSQl配合的开源软件很多，有很多分布式集群软件，如pgpool、pgcluster、slony、plploxy等等，很容易做读写分离、负载均衡、数据水平拆分等方案，而这在MySQL下则比较困难。

3. PostgreSQL源代码写的很清晰，易读性比MySQL强太多了，怀疑MySQL的源代码被混淆过。所以很多公司都是基本PostgreSQL做二次开发的。

4. PostgreSQL在很多方面都比MySQL强，如复杂SQL的执行、存储过程、触发器、索引。同时PostgreSQL是多进程的，而MySQL是线程的，虽然并发不高时，MySQL处理速度快，但当并发高的时候，对于现在多核的单台机器上，MySQL的总体处理性能不如PostgreSQL，原因是MySQL的线程无法充分利用CPU的能力。

目前只想到这些，以后想到再添加，欢迎大家拍砖。

PostgreSQL与oracle或InnoDB的多版本实现的差别

PostgreSQL与oracle或InnoDB的多版本实现最大的区别在于最新版本和历史版本是否分离存储，PostgreSQL不分，而oracle和InnoDB分，而innodb也只是分离了数据,索引本身没有分开。

PostgreSQL的主要优势在于：

1. PostgreSQL没有回滚段，而oracle与innodb有回滚段，oracle与Innodb都有回滚段。对于oracle与Innodb来说，回滚段是非常重要的，回滚段损坏，会导致数据丢失，甚至数据库无法启动的严重问题。另由于PostgreSQL没有回滚段，旧数据都是记录在原先的文件中，所以当数据库异常crash后，恢复时，不会象oracle与Innodb数据库那样进行那么复杂的恢复，因为oracle与Innodb恢复时同步需要redo和undo。所以PostgreSQL数据库在出现异常crash后，数据库起不来的几率要比oracle和mysql小一些。

2. 由于旧的数据是直接记录在数据文件中，而不是回滚段中，所以不会象oracle那样经常报ora-01555错误。

3. 回滚可以很快完成，因为回滚并不删除数据，而oracle与Innodb，回滚时很复杂，在事务回滚时必须清理该事务所进行的修改，插入的记录要删除，更新的记录要更新回来(见row_undo函数)，同时回滚的过程也会再次产生大量的redo日志。

4. WAL日志要比oracle和Innodb简单，对于oracle不仅需要记录数据文件的变化，还要记录回滚段的变化。

PostgreSQL的多版本的主要劣势在于：

1、最新版本和历史版本不分离存储，导致清理老旧版本需要作更多的扫描，代价比较大，但一般的数据库都有高峰期，如果我们合理安排VACUUM，这也不是很大的问题，而且在PostgreSQL9.0中VACUUM进一步被加强了。

2、由于索引中完全没有版本信息，不能实现Coverage index scan，即查询只扫描索引，直接从索引中返回所需的属性，还需要访问表。而oracle与Innodb则可以;

进程模式与线程模式的对比

PostgreSQL和oracle是进程模式，MySQL是线程模式。

进程模式对多CPU利用率比较高。

进程模式共享数据需要用到共享内存，而线程模式数据本身就是在进程空间内都是共享的，不同线程访问只需要控制好线程之间的同步。

线程模式对资源消耗比较少。

所以MySQL能支持远比oracle多的更多的连接。

对于PostgreSQL的来说，如果不使用连接池软件，也存在这个问题，但PostgreSQL中有优秀的连接池软件软件，如pgbouncer和pgpool，所以通过连接池也可以支持很多的连接。

Q4: 为什么postgrelsql的性能没有mysql好

一、 PostgreSQL 的稳定性极强， Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步，然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的，相比之下，PG数据库这方面要好一些。

二、任何系统都有它的性能极限，在高并发读写，负载逼近极限下，PG的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而 MySQL 明显出现一个波峰后下滑（5.5版本之后，在企业级版本中有个插件可以改善很多，不过需要付费）。

三、PG 多年来在 GIS 领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PG有大量字典、数组、bitmap 等数据类型，相比之下mysql就差很多，instagram就是因为PG的空间数据库扩展POSTGIS远远强于MYSQL的my spatial而采用PGSQL的。

四、PG 的“无锁定”特性非常突出，甚至包括 vacuum 这样的整理数据空间的操作，这个和PGSQL的MVCC实现有关系。

五、PG 的可以使用函数和条件索引，这使得PG数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。

六、PG有极其强悍的 SQL 编程能力（9.x 图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，比如分析函数（ORACLE的叫法，PG里叫window函数），还可以用多种语言来写存储过程，对于R的支持也很好。这一点上MYSQL就差的很远，很多分析功能都不支持，腾讯内部数据存储主要是MYSQL，但是数据分析主要是HADOOP+PGSQL。

七、PG 的有多种集群架构可以选择，plproxy 可以支持语句级的镜像或分片，slony 可以进行字段级的同步设置，standby 可以构建WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。

八、一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问。而 PG 的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库都可以省了。

九，对于WEB应用来说，复制的特性很重要，mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate,是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时，pgsql还提供stream复制。

十，pgsql对于numa架构的支持比mysql强一些，比MYSQL对于读的性能更好一些，pgsql提交可以完全异步，而mysql的内存表不够实用（因为表锁的原因）

最后说一下我感觉 PG 不如 MySQL 的地方。

第一，MySQL有一些实用的运维支持，如 slow-query.log ，这个pg肯定可以定制出来，但是如果可以配置使用就更好了。

第二是mysql的innodb引擎，可以充分优化利用系统所有内存，超大内存下PG对内存使用的不那么充分，

第三点，MySQL的复制可以用多级从库，但是在9.2之前，PGSQL不能用从库带从库。

第四点，从测试结果上看，mysql 5.5的性能提升很大，单机性能强于pgsql，5.6应该会强更多.

第五点，对于web应用来说,mysql 5.6 的内置MC API功能很好用，PGSQL差一些。

另外一些：

pgsql和mysql都是背后有商业公司，而且都不是一个公司。大部分开发者，都是拿工资的。

说mysql的执行速度比pgsql快很多是不对的，速度接近，而且很多时候取决于你的配置。

对于存储过程，函数，视图之类的功能，现在两个数据库都可以支持了。

另外多线程架构和多进程架构之间没有绝对的好坏，oracle在unix上是多进程架构，在windows上是多线程架构。