PG9.1+pgpool-II3.1--之HA (Hot-Standby+Streaming Replication)
已有 7462 次阅读
|个人分类:|系统分类:|关键词:PG9.1+pgpool-II3.1--之HA (Hot-Standby+Streaming Replication)
本教程是PostgreSQL Cluster系列教程的一部分，该系列包括：& （该教程主要阐述DBA如何基于WAL日志做备份恢复）
(file-based log shipping)P (when meeting large amounts of requests)
(when meeting large amounts of data)PostgreSQL9.1 HA --- 之Slony我们知道若仅仅使用Postgresql的warm-standby方式来做HA有三种机制：file-based log shippingstreaming replicationsynchronous replication当然你要是使用Hot-Standby的方式也会有相应这三种，本文作者尝试过PostgreSQL9.1+pgpool-II3.1+Warm-Standby+Streaming Replication的方式（具体可参考），
发现pgpool-II在做master和warm-standby切换的时有些小问题，即warm-standby在做online
recovery恢复后，实际上已经正常运行，但是pgpoolAdmin无法显示其运行状态（大家可以试试，或许是因为warm-standby不能接
受客户端连接的原因，这是我的瞎猜，也可能是我配置的不合适）。那我们本文主要说说如何使用hot-standby来做HA（有人说hot-standy
不是做load balancing的吗，不错，但是细想想，promote一下hot-standby不也可以做master吗）。那既然要用
hot-standby来做，就涉及到是用file-based log shipping，还是用streaming
replication，synchronous
replication，基于文件的方式在主从切换（Failover，Switchover，Failback是在pgool里看到的，从字面上看应该
是恢复到最开始的主从架构）的时候需要写脚本稍微麻烦一些（例如master-&hot-standby需要archieve的位置就与hot-
standby-&master的地方不同，以及其它麻烦，此处不表），故本文主要是写后两者，而根据前面的文章考虑到synchronous
replication仅仅是需要对streaming replication做一些简单的修改就可实现，我们也不再探讨synchronous
replication的方式（有兴趣的朋友可以来写写），这样本文主要探讨在pg9.1 + pgpool-II3.1上基于hot-standby +
streaming replication来配置HA。接着说pgpool-II，根据官方： It provides the following features:&&& * Connection Pooling&&& * Replication&&&&&
pgpool-II can manage multiple PostgreSQL servers. Activating the
replication feature makes it possible to create a realtime backup on 2
or more PostgreSQL clusters, so that the service can continue without
interruption if one of those clusters fails.&&& * Load Balance&&& * Limiting Exceeding Connections&&& * Parallel Query本文我们关注Replication，为什么建HA时要用pgpool-II呢，这是因为我们想让pgpool-II自动的管理master和standby库（当然我们也可以自己写一些脚本，把这些配置都自动化），即自动化管理在教程“”中提到的HA里的三个方面：正常运行，即standby一直处于online recovery状态Failover阶段，即master由于某种原因关闭掉，standby提升为masterSwitchover阶段，即master的角色设定为新的standy，即一直处于online recoveryFailback 阶段（根据pgpool来做的补充，恢复到最初正常运行状态）当然此处我们是基于hot-standby的方法。先说“A.正常运行”阶段，我们来分析分析，若没有pgpool-II我们怎么自己写shell自动化。先说环境：
/home/postgres/db/master/pgsql
目录是master数据库的目录，端口为6432（之前的教程是5432，我们后面在prallel
mode时把5432的库安装在/usr/local/pgsql下，专供pgpool-II存系统库用，即SystemDB使用）/home/postgres/db/standby/pgsql 目录是一台standy数据库的目录，端口为7432/home/postgres/base 是master-&standby的基础备份库的目录 “正常运行”下，我们是如何基于streaming replication的方法配置hot-standby的呢，这里是简略步骤：安装pg9.1在master，standby上对master库做配置（在postgresql.conf中：wal_level = hot_standby ；max_wal_senders = 1， 在pg_hbal.conf中：host
replication repluser 127.0.0.1/32 password,当然你可以让认证更加简单或复杂）（我们再想想，是不是standby里的postgresql.conf也可以设置同样的参数呢？答案是“是的”。），启动master库对master库做基础备份（SELECT
pg_start_backup('bak'); tar czvf
/home/postgres/base/base_data.tar.gz
/home/postgres/db/master/pgsql/data/； SELECT pg_stop_backup();）对standby库使用基础备份的data目录（tar -xzvf /home/postgres/base/base_data.tar.gz ），修改配置（创建recovery.conf文件，使之含有：standby_mode = on& trigger_file = '/home/postgres/trigger/pgsql.recovery.trigger'，在postgresql.conf中：hot_standby = on），启动standby库好，是不是第3，4步可以自动化呢，若我们把这两步合并在一起，即直接把master的data目录用rsync到standby的data目录里，是不是不需要/home/postgres/base 目录了呢。好，我们把第3，4步的自动化shell脚本记成（脚本参考修改自：）。
实际上，可以在pgpool.conf里recovery_1st_stage_command =
'basebackup.sh'设置(pgpool-II会去master的data目录里找该shell文件，当然pgpool-II在该目录下还会寻
找pgpool_remote_start脚本文件，以让master远程启动standby)，这是后话。那么在“B.Failover”阶段怎么自动化呢？我们这么设想一下，当master出现故障死掉后，pgpool-II检测不到master的状态信息，就在相应的trigger目录下新建一个相应的trigger文件（我们把这些命令封装成文
件，在pgpool.conf里failover_command = '/usr/local/etc/failover.sh %d "%h" %p
%D %m %M "%H" %P'设置）以promote
standby为master角色。就这么简单。有朋友会说了，什么时候把该trigger删除掉呢？好我们就进入“Switchover”阶段。“C.Switchover”
阶段主要是master修好后重新进入cluster，pgpool-II检测到后，设置原master为新的hot-standby，这就需要首先删除
trigger文件（将被设定为hot-standby的原master也将使用该trigger文件）（在pgpool.conf里
failback_command = '/bin/rm -f
/home/postgres/trigger/pgsql.recovery.trigger'设置），在原master的data目录下新建一个
recovery.conf文件（内容和原hot-standby的一样），是不是就可以了。在“D.Failback”
阶段，是不是pgpool-II确认master和standby一致性后，只需要把master下的pgpool.conf删除掉，把hot-
standby下pgpool.conf恢复一下，然后重启二者就可以了?哈，这是俺瞎猜的，pgpool-II可能还会做更多的工作（有哪位朋友看看
pgpool-II的源码来写写）。罗嗦了这么多，我们得上张图，要不然有些朋友会觉得枯燥，该图包括本文主要参考自，建议朋友们先提前学习一下该文，本文稍作修改，让操作更加简单化，以让初学者很容易的上机操作。上图不言自明，那接下来怎么配呢？先说机器配置，建议你都先创建好这些目录： /home/postgres/db/master/pgsql 目录是master数据库的目录，端口为6432/home/postgres/db/standby/pgsql 目录是一台standy数据库的目录，端口为7432/usr/local/pgsql 目录是pgpool-II使用的SystemDB数据库的目录，端口为5432/home/postgres/var/run/pgpool 是pgpool-II运行时放pid的目录/home/postgres/var/log/pgpool/trigger 是主从切换时放trigger文件的目录假定pgpool-II-3.1.2.tar.gz,pgpoolAdmin-3.1.1.tar.gz放在/home/postgres/develop下本系列教程中使用的都是RHEL 6 在动手配置之前，我们得说说各种用户角色。在中我们提到用流复制的话要有个CREATE ROLE repluser REPLICATION LOGIN PASSWORD 'pwd_repluser'账户，此处为了简化我们让系统我们假定原有的postgres用户来通讯，即：#host&& replication&&&&& postgres&&&&&&&& 0.0.0.0/0&&&&&&&&&&&&&& trustlocal&& replication&&&&& postgres&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& trust所以也就不用建立repluser角色了。由于我们要装pgpoolAdmin3.1.1，其必定要与pgpool通讯，这时通讯的用户名密码存储在pgpool的配置文件/usr/local/etc/pcp.conf里，密码用md5生成（具体怎么生成参考：），这里我们的用户名密码都为postgres，示例如下：# USERID:MD5PASSWDpostgres:e8ac69dfc5实
际上pgpoolAdmin运行在Tomcat下，而Tomcat运行时用户是apache，pgpoolAdmin还需要使用apache用户来运行
/usr/local/bin/pcp_*等等命令，这时需要给这些命令赋予apache用户相应的权限。当然，apache用户还会被pgpool用来
和master/standby通讯，故在pgpool.conf里还得设置health_check_user =
'apache'，这就需要在master/standby里都创建该用户。好，下面我们来一步步安装配置。1. 安装pgpool-II3.1.2和pgpoolAdmin3.1.1以postgres用户登录，先安装专为pgpool-II使用的PostgreSQL9.1(直接用make world安装包含dblink的扩展模块)：cd /home/postgres/develop/rm -fr postgresql-9.1.2tar zxf postgresql-9.1.2.tar.gzcd postgresql-9.1.2./configure
--prefix=/usr/local/pgsql
--with-includes=/usr/local/readline/include
--with-libraries=/usr/local/readline/libmake worldmake install-world/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
createuser -p 5432 pgpool
createdb -p 5432 -O pgpool pgpool
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
/usr/local/pgsql/bin/psql -p 5432 -U pgpool pgpool
CREATE EXTENSION dblink
然后由于pgpool将安装在/usr/local/bin, /usr/local/lib, /usr/local/include, /usr/local/share/man/man8等目录下，故先对这些目录设置相应的权限：suchmod -R 777 /usr/local/bin
/usr/local/lib /usr/local/etc& /usr/local/include /usr/local/share安装pgpool-II，并使用默认参数启动：cd /home/postgres/develop/tar zxf pgpool-II-3.1.2.tar.gzcd pgpool-II-3.1.2./configuremakemake installcd /usr/local/etccp pgpool.conf.sample pgpool.confcp pcp.conf.sample pcp.conf修改pcp.conf，增加一行（即用户名postgres密码pgpoolAdmin）：postgres:6b9883ad2fdc7256b95cfafbbe8e455bmkdir /var/run/pgpoolpgpool -n & #尝试启动pgpool-IIpgpool stop #尝试关闭/usr/local/pgsql/bin/psql -f /usr/local/share/pgpool-II/system_db.sql -p 5432 -U pgpool pgpool #把systemDB安装到上面的5432端口postgresql中为避免后面的库找不到，我们设置一些环境变量：sugedit /etc/profile最后增加：export LIBDIR=/usr/local/lib:/usr/local/pgsql/lib:$LIBDIR export LD_LIBRARY_PATH=/usr/local/lib:$LD_LIBRARY_PATH然后安装pgpoolAdmin，由于其需要安装在应用服务器中，我们下载并安装，解压缩到目录/home/postgres/website/。看看是否可以启动Tomcat（当然这需要jdk，此处不再说明如何安装）:chmod -R 777 /home/postgres/website/apache-tomcat-7.0.16/binsh /home/postgres/website/apache-tomcat-7.0.16/bin/startup.sh sh /home/postgres/website/apache-tomcat-7.0.16/bin/shutdown.sh #关闭tomcat由于pgpoolAdmin需要使用php的库，你从下载，解压缩到/home/postgres/website/apache-tomcat-7.0.16/lib 目录，并配置/home/postgres/website/apache-tomcat-7.0.16/conf/web.xml（参考）,你可以在最后的&/web-app&标记之前增加：&web-app xmlns=... &
&listener&&listener-class&php.java.servlet.ContextLoaderListener&/listener-class&&/listener&
&servlet&&servlet-name&PhpJavaServlet&/servlet-name&&servlet-class&php.java.servlet.PhpJavaServlet&/servlet-class&
&/servlet&
&servlet&&servlet-name&PhpCGIServlet&/servlet-name&&servlet-class&php.java.servlet.fastcgi.FastCGIServlet&/servlet-class&
&init-param&&param-name&prefer_system_php_exec&/param-name&&param-value&On&/param-value&&/init-param&
&init-param&&param-name&php_include_java&/param-name&&param-value&Off&/param-value&&/init-param&
&/servlet&
&servlet-mapping&&servlet-name&PhpJavaServlet&/servlet-name&&url-pattern&*.phpjavabridge&/url-pattern&
&/servlet-mapping&
&servlet-mapping&&servlet-name&PhpCGIServlet&/servlet-name&&url-pattern&*.php&/url-pattern&&/servlet-mapping&
&/web-app&
并确保/home/postgres/website/apache-tomcat-7.0.16/conf/server.xml中的docBase="webapps“。好，下载pgpoolAdmin-3.1.1.tar.gz，并解压缩到/home/postgres/website/apache-tomcat-7.0.16/webapps中，然后：cd /home/postgres/website/apache-tomcat-7.0.16/webapps/pgpooladminmkdir templates_c
chmod 777 templates_c suchown apache /home/postgres/website/apache-tomcat-7.0.16/webapps/pgpooladmin/conf/pgmgt.conf.phpchmod 777
/home/postgres/website/apache-tomcat-7.0.16/webapps/pgpooladmin/conf/pgmgt.conf.php然后重新启动Tomcat，通过浏览器打开如下地址：http://localhost:8080/pgpooladmin/install/index.php，是不是如下图正常：若是上图的模样则表明成功，然后一路点next选择相应的配置，若对某些文件有权限问题，请做相应修改，例如（具体可参考）：cd /usr/local/etcchown apache pgpool.confchmod 644 pgpool.confchown apache pcp.confchmod 644 pcp.confsuchmod 755 /usr/local/bin/pgpool
chmod 755 /usr/local/bin/pcp_*好，如果你见到下面这个界面，则表示配置成功：接
下来进入pgpoolAdmin的管理页面http://localhost:8080/pgpooladmin/status.php：默认用户名
postgres密码pgpoolAdmin（后面我们将演示如何修改pgpool以允许pgpoolAdmin以不同的用户名密码登录）：点击Start pgpool是不是启动了？再点击Stop pgpool按钮是不是关闭了？如果都奏效，这说明咱安装成功。2.千辛万苦我们把pgpool和pgpoolAdmin安装上了，接下来我们就得把Master和Standby的数据库安装上，这就很简单了，Master：cd /home/postgres/develop/rm -fr postgresql-9.1.2tar zxf postgresql-9.1.2.tar.gzcd postgresql-9.1.2./configure
--prefix=/home/postgres/db/master/pgsql
--with-includes=/usr/local/readline/include
--with-libraries=/usr/local/readline/lib --with-pgport=6432makemake install/home/postgres/db/master/pgsql/bin/initdb -D /home/postgres/db/master/pgsql/dataStandby：cd /home/postgres/develop/rm -fr postgresql-9.1.2tar zxf postgresql-9.1.2.tar.gzcd postgresql-9.1.2./configure
--prefix=/home/postgres/db/standby/pgsql
--with-includes=/usr/local/readline/include
--with-libraries=/usr/local/readline/lib --with-pgport=7432makemake install/home/postgres/db/standby/pgsql/bin/initdb -D /home/postgres/db/standby/pgsql/data尽管编译时候把端口号编译进去了，但是为防止可能出现的问题，此处仍建议修改master的postgresql.conf的端口号：port = 6432和standby的postgresql.conf的端口号：port = 7432分别在master和standby上创建apache用户：/home/postgres/db/master/pgsql/bin/createuser --no-createdb --no-superuser --createrole apache/home/postgres/db/standby/pgsql/bin/createuser --no-createdb --no-superuser --createrole apache由于pgpool需要和master和standby进行流复制时的通讯，先把pgpool-recovery,pgpool-regclass,pgpool-walrecrunning库安装到/usr/local/pgsql/lib：cd /home/postgres/develop/pgpool-II-3.1.2/sql/pgpool-recoverymakemake install
cd ../pgpool-regclassmakemake install
cd ../pgpool-walrecrunningmakemake install还需要在master和standby上安装相应的函数：master：cp /usr/local/pgsql/lib/pgpool-*.*
/home/postgres/db/master/pgsql/lib//home/postgres/db/master/pgsql/bin/postmaster -D /home/postgres/db/master/pgsql/datacd /home/postgres/develop/pgpool-II-3.1.2/sql/pgpool-recovery
/home/postgres/db/master/pgsql/bin/psql -f pgpool-recovery.sql template1 --port=6432
/home/postgres/db/master/pgsql/bin/psql -f pgpool-recovery.sql postgres --port=6432cd ../pgpool-regclass
/home/postgres/db/master/pgsql/bin/psql -f pgpool-regclass.sql template1 --port=6432
/home/postgres/db/master/pgsql/bin/psql -f pgpool-regclass.sql postgres --port=6432cd ../pgpool-walrecrunning
/home/postgres/db/master/pgsql/bin/psql -f pgpool-walrecrunning.sql template1 --port=6432
/home/postgres/db/master/pgsql/bin/psql -f pgpool-walrecrunning.sql postgres --port=6432standby上类似：cp /usr/local/pgsql/lib/pgpool-*.*
/home/postgres/db/standby/pgsql/lib//home/postgres/db/standby/pgsql/bin/postmaster -D /home/postgres/db/standby/pgsql/datacd /home/postgres/develop/pgpool-II-3.1.2/sql/pgpool-recovery
/home/postgres/db/standby/pgsql/bin/psql -f pgpool-recovery.sql template1 --port=7432
/home/postgres/db/standby/pgsql/bin/psql -f pgpool-recovery.sql postgres --port=7432cd ../pgpool-regclass
/home/postgres/db/standby/pgsql/bin/psql -f pgpool-regclass.sql template1 --port=7432
/home/postgres/db/standby/pgsql/bin/psql -f pgpool-regclass.sql postgres --port=7432cd ../pgpool-walrecrunning
/home/postgres/db/standby/pgsql/bin/psql -f pgpool-walrecrunning.sql template1 --port=7432
/home/postgres/db/standby/pgsql/bin/psql -f pgpool-walrecrunning.sql postgres --port=74323.然后进入激动人心的HA的配置了（激动是没用的，DBA需要的是不激动，^_^）首先是配置master和standby，二者的postgresql.conf一样一样滴：wal_level = hot_standbymax_wal_senders = 1hot_standby = on二者pg_hba.conf也一样(若更复杂的请自行设置)：#host&&& replication&&&& postgres&&&&&&& ::1/128&&&&&&&&&&&&&&&& trustlocal&& replication&&&&& postgres&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& trust在master的data目录下放上面介绍的文件和这个让master远程启动standby的文件。然后是配置pgpool了，先捡容易的pcp.conf修改：首先使用md5工具生成密码postgres的md5：&/usr/local/bin/pg_md5 postgrese8ac69dfc5然后修改pcp.conf：# USERID:MD5PASSWDpostgres:e8ac69dfc5接着修改/usr/local/etc/pgpool.conf：listen_addresses = '*'backend_hostname0 = 'localhost'backend_port0 = 6432backend_weight0 = 1backend_data_directory0 = '/home/postgres/db/master/pgsql/data'backend_flag0= 'ALLOW_TO_FAILOVER'backend_hostname1 = 'localhost'backend_port1 = 7432backend_weight1 = 1backend_data_directory1 = '/home/postgres/db/standby/pgsql/data'backend_flag1= 'ALLOW_TO_FAILOVER'pid_file_name = '/home/postgres/pgpool/pgpool.pid'logdir = '/home/postgres/pgpool'master_slave_mode = onmaster_slave_sub_mode = 'stream'health_check_user = 'apache'health_check_password = ''failover_command = '/usr/local/etc/failover.sh %d "%h" %p %D %m %M "%H" %P'failback_command = '/bin/rm -f /home/postgres/var/log/pgpool/trigger/'recovery_user = 'postgres'recovery_password = 'postgres'recovery_1st_stage_command = 'basebackup.sh'recovery_2nd_stage_command = ''内容有些多，我把放到这里供参考（你也可以通过pgpoolAdmin来修改这些参数）。4.检验依次启动master，pgpool，apache：/home/postgres/db/master/pgsql/bin/postmaster -D /home/postgres/db/master/pgsql/datapgpool -n &&sh /home/postgres/website/apache-tomcat-7.0.16/bin/startup.sh打开浏览器：http://localhost:8080/pgpooladmin/login.php，输入用户名postgres，密码postgres。你应该可以看到这个页面：点击7432端口的Recovery，若成功则为：然后通过如下命令模拟master死掉：/home/postgres/db/master/pgsql/bin/pg_ctl stop -D /home/postgres/db/master/pgsql/data你就会看到：然
后点promote，似乎不能成功（因为hot-standby的原因？我试过warm-standby是可以promote的，不知到hot-
standby是否promote？有谁能继续写写，我的土办法是在standby下面强行建立recovery.conf文件，启动master，然后
启动standby，然后启动pgpool，最后是tomcat里的pgpoolAdmin）。好，回到我们最开始的状态，通过连接pgpool执行：[postgres@localhost ~]$ psql -p 9999psql (8.4.4, server 9.1.2)WARNING: psql version 8.4, server version 9.1.&&&&&&&& Some psql features might not work.Type "help" for help.mydb=# insert into foo select * from generate_series(1,1000000);& #假定已经有了mydb库INSERT 0 1000000mydb=# select count(*)& count& ---------&5000000(1 row)然后单独链接hot-standby：[postgres@localhost ~]$ /home/postgres/db/master/pgsql/bin/psql mydb --port=7432psql (9.1.2)Type "help" for help.mydb=# select count(*)& count& ---------&5000000(1 row)mydb=#& insert into foo select * from generate_series(1,1000000);& ERROR:& cannot execute INSERT in a read-only transaction是不是成功了。最
后说说结论，若DBA想使用pgpool-II的Master-Slave模式来做HA的话，由于配置相对注意的事情比较多，应多次反复练习配置，理解其
内涵，以避免在各种情况下可能出现的问题。另外既然pgpool-II默认的Master-Slave模式使用slony来做，就应多试试pgpool-
II+slony的方式。总之要多练习，谨慎行事。至此完毕。参考：[1] [2] [3] [4] [5] [6] [7] [8] [9] [10]
本文引用地址：&此文来自科学网孙鹏博客
上一篇：下一篇：
当前推荐数：2
评论 ( 个评论)
作者的其他最新博文
热门博文导读
Powered by
Copyright &【转载】PostgreSQL与MySQL比较 - Johnson曾 - 博客园
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 编写。
系统配置文件
Postgresql.conf
数据库配置
Postgresql.conf
客户机连接文件
pg_hba.conf
有限的 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的劣势：
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，所以通过连接池也可以支持很多的连接。堆表与索引组织表的的对比
Oracle支持堆表，也支持索引组织表 PostgreSQL只支持堆表，不支持索引组织表 Innodb只支持索引组织表 索引组织表的优势： 表内的数据就是按索引的方式组织，数据是有序的，如果数据都是按主键来访问，那么访问数据比较快。而堆表，按主键访问数据时，是需要先按主键索引找到数据的物理位置。 索引组织表的劣势： 索引组织表中上再加其它的索引时，其它的索引记录的数据位置不再是物理位置，而是主键值，所以对于索引组织表来说，主键的值不能太大，否则占用的空间比较大。 对于索引组织表来说，如果每次在中间插入数据，可能会导致索引分裂，索引分裂会大大降低插入的性能。所以对于使用innodb来说，我们一般最好让主键是一个无意义的序列，这样插入每次都发生在最后，以避免这个问题。 由于索引组织表是按一个索引树，一般它访问数据块必须按数据块之间的关系进行访问，而不是按物理块的访问数据的，所以当做全表扫描时要比堆表慢很多，这可能在OLTP中不明显，但在数据仓库的应用中可能是一个问题。 　 PostgreSQL9.0中的特色功能：&&
&&& PostgreSQL中的Hot Standby功能 &&& 也就是standby在应用日志同步时，还可以提供只读服务，这对做读写分离很有用。这个功能是oracle11g才有的功能。&&& PostgreSQL异步提交（Asynchronous Commit）的功能： 　 这个功能oracle中也是到oracle11g R2才有的功能。因为在很多应用场景中，当宕机时是允许丢失少量数据的，这个功能在这样的场景中就特别合适。在PostgreSQL9.0中把synchronous_commit设置为false就打开了这个功能。需要注意的是，虽然设置为了异步提交，当主机宕机时，PostgreSQL只会丢失少量数据，异步提交并不会导致数据损坏而数据库起不来的情况。MySQL中没有听说过有这个功能。&&&& PostgreSQL中索引的特色功能： &&&& PostgreSQL中可以有部分索引，也就是只能表中的部分数据做索引，create index 可以带where 条件。同时PostgreSQL中的索引可以反向扫描，所以在PostgreSQL中可以不必建专门的降序索引了。
原文章地址：