PostgreSQL数据库介绍课件

PostgreSQL数据库介绍唐成—2011.04.12目录PostgreSQL是什么？PostgreSQL的体系架构PostgreSQL的备份PostgreSQL的集群方案PostgreSQL

VSMySQLPostgreSQL功能专题PostgreSQL是什么PostgreSQL是什么？PostgreSQL对象-关系型数据库管理系统（有一段时间被称为Postgres95）是从伯克利写的POSTGRES软件包发展而来的。经过十几年的发展，PostgreSQL是世界上可以获得的最先进的开放源码的数据库系统，它提供了多版本并行控制，支持几乎所有SQL构件（包括子查询，事务和用户定义类型和函数），并且可以获得非常广阔范围的（开发）语言绑定（包括C，C++，Java，perl，tcl，和python）。PostgreSQL是自由免费的，并且所有源代码都可以获得。PostgreSQL的开发队伍主要为志愿者，他们遍布世界各地并通过互联网进行联系，这是一个社区开发项目，它不被任何公司控制。如果你在寻找PostgreSQL的掌门人，或是什么中央委员会，或是什么所属公司，你只能放弃了因为一个也不存在，但我们的确有一个核心委员会和CVS管理组，但这些工作组的设立主要是为了进行管理工作而不是对PostgreSQL进行独占式控制，PostgreSQL项目是由任何人均可参加的开发人员社区和所有用户控制的，你所需要做的就是订阅邮件列表，参与讨论即可。PostgreSQL是什么PostgreSQL发展历程伯克利的POSTGRES项目:MichaelStonebraker领导的POSTGRES项目是由防务高级研究项目局（DARPA），陆军研究办公室（ARO），国家科学基金（NSF），以及ESL,Inc共同赞助的。POSTGRES的实现始于1986年，第一个"演示性"系统在1987年便可使用了，并且在1988年的ACM-SIGMOD大会上展出。在1989年六月发布了版本1给一些外部的用户使用。用于源代码维护的时间日益增加占用了太多本应该用于数据库研究的时间，为了减少支持的负担，伯克利的POSTGRES项目在版本4.2时正式终止。Postgres95:在1994年，AndrewYu和JollyChen向POSTGRES中增加了SQL语言的解释器。并随后将Postgres95源代码发布到互联网上供大家使用，成为一个开放源码的，原先伯克利POSTGRES代码的继承者。PostgreSQL6.X:到了1996年，很明显的看出"Postgres95"这个名字已经不能经得起时间的考验了。于是起了一个新名字PostgreSQL用于反映最初的POSTGRES和最新的使用SQL的版本之间的关系。同时版本号也重新从6.0开始，将版本号放回到最初的由伯克利POSTGRES项目开始的顺序中。PostgreSQL是什么PostgreSQL发展历程PostgreSQL7.1-7.1是继6.5之后的又一个巨大的变化，她首先引入了预写式日志的功能。这样，事务就拥有了完善的日志机制，可以提供更好的性能，以及还可以实现更优良的备份和灾难恢复的能力（比如联机热备份和宕机后的自动恢复）。其次是对文本类型的数据段的长度不再限制，从而很大程度上解决了PostgreSQL的大对象的问题。Postgres8.X:可以在windows下运行，事务保存点功能，改变字段的类型，表空间即时恢复:即时恢复允许对服务器进行连续的备份。你既可以恢复到失败那个点，也可以恢复到以前的任意事务。新的Perl服务器端编程语言PostgreSQL9.XHOTstandby功能PostgreSQL是什么PostgreSQL版权是什么？PostgreSQL的发布遵从经典的BSD版权。它允许用户不限目的地使用PostgreSQL，甚至你可以销售PostgreSQL而不含源代码也可以，唯一的限制就是你不能因软体自身问题而向我们追诉法律责任，另外就是要求所有的软体拷贝中须包括以下版权声明。下面就是我们所使用的BSD版权声明内容：PostgreSQL资料库管理系统部分版权（c）1996-2005，PostgreSQL全球开发小组，部分版权（c）1994-1996加州大学董事（Portionscopyright(c)1996-2005,PostgreSQLGlobalDevelopmentGroupPortionsCopyright(c)1994-6RegentsoftheUniversityofCalifornia）允许為任何目的使用，拷贝，修改和分发这个软体和它的文档而不收取任何费用，并且无须签署因此而產生的证明，前提是上面的版权声明和本段以及下面两段文字出现在所有拷贝中。（Permissiontouse,copy,modify,anddistributethissoftwareanditsdocumentationforanypurpose,withoutfee,andwithoutawrittenagreementisherebygranted,providedthattheabovecopyrightnoticeandthisparagraphandthefollowingtwoparagraphsappearinallcopies.）在任何情况下，加州大学都不承担因使用此软体及其文档而导致的对任何当事人的直接的，间接的，特殊的，附加的或者相伴而生的损坏，包括利益损失的责任，即使加州大学已经建议了这些损失的可能性时也是如此。。。。。。。。。。PostgreSQL是什么PostgreSQL在国外很流行。在日本是仅次于apache的开源软件：PostgreSQL数据库架构PostgreSQL数据库的架构PostgreSQL数据库是多进程的架构，每一个客户连接都有一个服务进程为其服务，也有共享内存，这一点与Oracle数据库是相似的。PostgreSQL使用一种客户端/服务器的模式。一个服务器进程，它管理数据库文件，接受来自客户端应用与数据库的连接，并且代表客户端在数据库上执行操作。数据库服务器程序叫postgres。PostgreSQL服务器可以处理来自客户端的多个并发请求。因此，它为每个请求启动("fork")一个新的进程。从这个时候开始，客户端和新服务器进程就不再经过最初的postgres进程进行通讯。因此，主服务器总是在运行，等待连接，而客户端及其相关联的服务器进程则是起起停停。所以PostgreSQL对于大并发的处理能力比较强，同时能够充分利用现在多核CPU的性能。PostgreSQL数据库的架构PostgreSQL数据库的架构PostgreSQL的服务进程pgmasterwriterprocesswalwriterprocessautovacuumlauncherarchiverprocessstatscollectorprocesspostgres服务进程进程私用内存区共享内存区PostgreSQL数据库的架构PostgreSQL的内存分配shared_bufferstemp_bufferswork_memmaintenance_work_memwal_buffersPostgreSQL数据库的架构PostgreSQL数据库的架构通过预写式日志(WAL)实现了与Oracle数据库中Dataguard一样功能的Active/standby数据库架构。WAL日志就相当于oracle的redo日志checkpoint_segments参数控制当切换多少个WAL日志文件后，就发生一次checkpoint；checkpoint_timeout参数控制多长时间后，发生一次checkpoint。full_page_writes参数可以控制是否把整个数据块的变化都记录到WAL日志中。PostgreSQL9.0支持standby在应用日志时，也能读，实现与Oracle11g相类似的功能。PostgreSQL9.0的master与standby传送日志是异步的，PostgreSQL9.1也将实现同步写日志。PostgreSQL数据库的架构PostgreSQL数据库的架构PostgreSQL与Oracle数据库及MySQL的innodb引擎最大的不同，是没有回滚段。PostgreSQL每个表的内部都有一个transactionid（xid,4个字节）字段。每次更新数据行时，并不会删除旧的数据行，而是生成一个新的数据行，新的数据行的xid字段填写当前的transactionid，而每发生一次事务transcactionid都会加1，这类似Oracle中的SCN号。查询时，如果发现一行的xid比当前的xid新，则表明这个行的数据是新事务的，则跳过，这样只返回小于等于当前xid的数据行，这样就实现了数据的一致性。当然这些删除掉的行，仍然占用磁盘空间，这时PostgreSQL提供了vacuum命令手动或自动去清除这些过期数据。PostgreSQL的备份方法逻辑备份：

pg_dump/pg_restore类似oracle的exp/imp物理冷备份：把数据库关了，直接拷贝数据库数据文件目录物理热备份：打开数据库到归档模式selectpg_start_backup('test_backup');拷贝文件selectpg_stop_backup('test_backup');PostgreSQL没有rman工具，不过有一个工具pg_rman可以使用。PostgreSQL的高可用方案传统的基于共享存储的高可用方案。使用即时恢复(PITR的WAL日志）的热备份使用触发器捕获变化的数据复制基于语句的复制中间件基本改造PostgreSQL源码复制方案数据仓库中的方案PostgreSQL的高可用方案基于底层硬件的高可用方案共享存储数据库主机数据库备机共享硬件失效切换通过仅保存一份数据库副本来避免花在同步上的开销。这个方案让多台服务器共享使用一个单独的磁盘阵列。如果主服务器失效，备份服务器将立即挂载该数据库，就像是从一次崩溃中恢复一样。这个方案允许快速的失效切换并且不会丢失数据。共享硬件的功能通常由共享存储设备提供。比方说使用一个共享存储，或使用共享文件系统如GFS、veritas共享文件系统、gpfs等等。这种方案的局限性在于如果共享的磁盘阵列损坏了，那么整个系统将会瘫痪。另一个局限是备份服务器在主服务器正常运行的时候不能访问共享的存储器。高可用方案介绍PostgreSQL自身提供的Acitve/standby方案热备份服务器可以通过读取WAL记录流来保持数据库的当前状态。如果主服务器失效，那么热备份服务器将包含几乎所有主服务器的数据，并可以迅速的将自己切换为主服务器。这是一个异步方案，并且只能在整个数据库服务器上实施。连续归档可以配合随时准备取代失效主服务器的一个或多个备份服务器，用于创建一个高可用性(HA)集群。这个能力通常被称为热备份或日志传送(LogShipping)。虽然主服务器和备份服务器只是松散的耦合在一起，但它们必须同时运行。主服务器以连续归档模式运行，备份服务器以连续恢复模式运行并从主服务器不停的读取WAL文件。因为数据库的表无需为此进行任何改变，所以与其它复制方法相比，额外的管理开销很小。并且这种方法对主服务器的性能影响也很小。PrimaryStandby传送wal日志wal日志高可用方案介绍基于触发器的服务器复制这个方案将所有修改数据的请求发送到主服务器。主服务器在表上建了一个触发器，通过触发器捕获变化，然后异步向从服务器发送数据的更改信息。从服务器在主服务器运行的情况下只应答读请求。开源软件Slony-I、Bucardo和skytools中的londiste都是使用的这个方案。它支持针对每个表的粒度并支持多个从服务器。因为它异步、批量的更新从服务器，在失效切换的时候可能会有数据丢失。Slony-I只支持主从模式，Bucardo还支持两master模式。MasterSlavereplicationWriteWrite用户高可用方案介绍基于语句的复制中间件DBDB中间件中间件程序截取每一个SQL查询，并将其发送到某一个或者全部服务器。写请求发送给所有服务器，读请求则仅发送给某一个服务器，从而实现读取的负载均衡。如果只是简单的广播SQL语句，那么类似random(),CURRENT_TIMESTAMP以及序列函数在不同的服务器上将生成不同的结果。如果这种结果是不可接受的，那么中间件或者应用程序必须保证始终从同一个服务器读取这些值并将其应用到写入请求中。另外还必须保证每一个事务必须在所有服务器上全部提交成功或者全部回滚，或者使用两阶段提交(PREPARETRANSACTION和COMMITPREPARED)。Pgpool-II和Sequoia(C-JDBC)是这种方案的实例。高可用方案介绍基于改造PostgreSQL源码的复制方案pglbDBDBDB客户端pgreplicateLoadBalancerClusterServerReplicationServer1235445678读写请求可以发给任意一台数据库服务器，服务器接收到写请求后，它会把变化复制到任意一台服务器上，cybercluster、pgcluster、postgres-XC是这个架构的典型代表。其中pgcluster和cybercluster是同步多master架构。数据仓库中的解决方案greenplum数据库greenplum底层是基本PostgreSQL数据库的，是真正的功能全面的分布式数据库。greenplum是商业软件gridSQLgridsql底层也是PostgreSQL数据库的，本身是一个开源的java程序。这两个软件都实现了跨节点join的问题。数据仓库中的方案PostgreSQLVSMySQLMySQLPostgreSQL最重要的引擎InnoDB很早就由Oracle公司控制。目前整个MySQL数据库都由Oracle控制。BSD协议，没有被大公司垄断。对复杂查询的处理较弱，查询优化器不够成熟很强大的查询优化器，支持很复杂的查询处理。只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-mergejoin)与散列连接(hashjoin)。都支持性能优化工具与度量信息不足提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具。PostgreSQLVSMySQLMySQLPostgreSQLInnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。不存在这个问题。大部分查询只能使用表上的单一索引;在某些情况下，会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢。不存在这个问题表增加列，基本上是重建表和索引，会花很长时间。表增加列，只是在数据字典中增加表定义，不会重建表存储过程与触发器的功能有限。可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱除支持pl/pgsql写存储过程，还支持perl、python、Tcl类型的存储过程：pl/perl，pl/python，pl/tcl。也支持用C语言写存储过程。PostgreSQLVSMySQLMySQLPostgreSQL不支持Sequence。支持不支持函数索引，只能在创建基于具体列的索引。不支持物化视图。支持函数索引，同时还支持部分数据索引，通过规则系统可以实现物化视图的功能。执行计划并不是全局共享的,仅仅在连接内部是共享的。执行计划共享MySQL支持的SQL语法(ANSISQL标准)的很小一部分。不支持递归查询、通用表表达式（Oracle的with语句）或者窗口函数（分析函数）。都支持PostgreSQLVSMySQLMySQLPostgreSQL不支持用户自定义类型或域(domain)支持。对于时间、日期、间隔等时间类型没有秒以下级别的存储类型可以精确到秒以下。身份验证功能是完全内置的，不支持操作系统认证、PAM认证，不支持LDAP以及其它类似的外部身份验证功能。支持OS认证、Kerberos认证、Ident的认证、LDAP认证、PAM认证不支持databaselink。有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多有dblink，同时还有一个dbi-link的东西，可以连接到oracle和mysql上。PostgreSQLVSMySQLMySQLPostgreSQLMysqlCluster可能与你的想象有较大差异。开源的cluster软件较少。复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master.有丰富的开源cluster软件支持。explain看执行计划的结果简单。explain返回丰富的信息。类似于ALTERTABLE或CREATETABLE一类的操作都是非事务性的.它们会提交未提交的事务，并且不能回滚也不能做灾难恢复DDL也是有事务的。PostgreSQL新功能PostgreSQL异步提交（AsynchronousCommit）的功能这个功能oracle中也是到oracle11gR2才有的功能。因为在很多应用场景中，当宕机时是允许丢失少量数据的，这个功能在这样的场景中就特别合适。在PostgreSQL9.0中把synchronous_commit设置为false就打开了这个功能。需要注意的是，虽然设置为了异步提交，当主机宕机时，PostgreSQL只会丢失少量数据，异步提交并不会导致数据损坏而数据库起不来的情况。PostgreSQL9.0中的HotStandby功能也就是standby在应用日志同步时，还可以提供只读服务，这对做读写分离很有用。这个功能是oracle11g才有的功能，但oracle11g这个功能不是很完善，有时会报表不存在的问题。PostgreSQL中的连接池软件，可以防止连接风暴pgbouncer是最轻量级的连接池软件，是使用libevent做tcp/ip数据处理，缺省的时候每个连接只消耗2k内存。一般我们使用PostgreSQL，应用是直接连接到pgbouncer连接池上的，而实际到数据库的连接比较少，这样数据库对大并发的处理能力得到大大提高。而oracle则存在大量连接下处理能力不高的问题。PostgreSQL特性PostgreSQL与oracle或InnoDB的多版本差别分析PostgreSQL与oracle或InnoDB的多版本实现最大的区别在于最新版本和历史版本是否分离存储，PostgreSQL不分，而oracle和InnoDB分，而innodb也只是分离了数据,索引本身没有分开。

PostgreSQL的主要优势在于：

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

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

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

4.WAL日志要比oracle和Innodb简单，对于oracle不仅需要记录数据文件的变化，还要记录回滚段的变化。PostgreSQL特性PostgreSQL与oracle或InnoDB的多版本差别分析PostgreSQL的主要劣势在于：

1、最新版本和历史版本不分离存储，导致清理老旧版本需要作更多的扫描，代价比较大，但这个问题一般并不是突出，因为VACUUM中也有很多的优化。如PostgreSQL8.3中加入了HOT技术。使用HOT后，若所有索引属性都没被修改（索引键是否修改是在执行时逐行判断的，因此若一条UPDATE语句修改了某属性，但前后值相同则认为没有修改），且新版本与原版本存储在一个页面上则不会产生新的索引记录

2、由于索引中完全没有版本信息，不能实现Coverageindexscan，即查询只扫描索引，直接从索引中返回所需的属性，还需要访问表。而oracle是完全实现了Coveraindexscan，Innodb是部分实现了（在某种情况下也是可以的，但不是所有情况下都可以了）。两种实现差异对事务的影响：对于update操作，PostgreSQL需要新生成一行，会导致原表膨胀，而对于oracle和innodb，是把旧行中变化前的字段值记录在回滚段中，所以原表的大小不会发生明显的变化。对于PostgreSQL数据库来说，需要对表做vacuum以清理表中的垃圾数据，而对于oracle和innodb中的回滚段中的数据需要经常做收缩。PostgreSQL特性进程模式与线程模式的对比PostgreSQL和oracle是进程模式，MySQL是线程模式。进程模式对多CPU利用率比较高。进程模式共享数据需要用到共享内存，而线程模式数据本身就是在进程空间内都是共享的，不同线程访问只需要控制好线程之间的同步。线程模式对资源消耗比较少。所以MySQL能支持远比oracle多的更多的连接。对于PostgreSQL的来说，如果不使用连接池软件，也存在这个问题，但PostgreSQL中有优秀的连接池软件软件，如pgbouncer和pgpool，所以通过连接池也可以支持很多的连接。PostgreSQL特性堆表与索引组织表的的对比Oracle支持堆表，也支持索引组织表PostgreSQL只支持堆表，不支持索引组织表Innodb只支持索引组织表索引组织表的优势：表内的数据就是按索引的方式组织，数据是有序的，如果数据都是按主键来访问，那么访问数据比较快。而堆表，按主键访问数据时，是需要先按主键索引找到数据的物理位置。索引组织表的劣势：索引组织表中上再加其它的索引时，其它的索引记录的数据位置不再是物理位置，而是主键值，所以对于索引组织表来说，主键的值不能太大，否则占用的空间比较大。对于索引组织表来说，如果每次在中间插入数据，可能会导致索引分裂，索引分裂会大大降低插入的性能。所以对于使用innodb来说，我们一般最好让主键是一个无意义的序列，这样插入每次都发生在最后，以避免这个问题。PostgreSQL特性PostgreSQL开源与MySQL开源的的差别MySQL不是一个纯粹的开源数据库。MySQL数据库是一个公司的一产品，是一个公司做了绝大部分开发的工作，所以MySQL不是完全意义上的数据库，这是个劣势，不能得到绝大多数人的支持，或让社区更多人参与进来。PostgreSQL与MySQL不同，PostgreSQL一开始就是定位在ORACLE这些大数据库能做的事情。所以MySQL势力越来越小，而PostgreSQL却在变大。但随着时间的发展，谁都不知道会怎样MYSQL目标定位是在网络应用的用户层面上，而互联网企业要求的是一个快速反应时间和较小的用户量，但相对大用户来说，MySQL就有点捉襟见肘。

/200912/EnterpriseDB/PostgreSQL特性PostgreSQL缺点与oracle和MySQL相比最大的缺点是人气不旺PostgreSQL中的中文资料远远少于oracle和MySQL。PostgreSQL在国外使用还是比较广泛的，特别在日本。但在中国人气相对oracle和mysql还是有比较大的差距，只是近几年来，在国内使用PostgreSQL的公司也越来越多了。需要对vacuum做仔细的安排，特别对于更新频繁的数据库vacuum是由PostgreSQL多版本设计决定的。vacuum不能回收表已经占用的空间。不支持裸设备要求OS下有一个健壮的文件系统。在Linux下我们一般选XFS，对于solaris下选ZFS。