客观公正地评价MySQL和PostgreSQL的优劣

时间：2008-05-12 来源：sdccf

来源：网海拾贝

PostgreSQL最早是由UC Berkley大学计算机系开发的，当初由 Michael Stonebraker 教授领导的 Postgres 项目，是由高级防务研究项目局（Defense Advanced Research Projects Agency）（DARPA），陆军研究处（ARO ），国家科学基金会（National Science Foundation）（NSF），和 ESL 公司赞助进行的。它的许多先进的“对象－关系”概念现在已经在一些商业数据库里得到应用，PostgreSQL支持SQL92/SQL3，事务完整性和可扩展性。它现在是一个源于Berkley代码并公开源代码的数据库。

Postgres 通过一种让用户可以很容易扩展系统的方法整合了下面四种基本概念，使其能提供可观的附加功能：

类/表（classes）
继承（inheritance）
类型（types）
函数（functions）

其他特性还提供了附加的功能和灵活性：

约束（constraints）
触发器（triggers）
规则（rules）
事务完整（transaction integrity）

这些特性将 Postgres置于对象－关系 (object-relational) 型数据库的范畴．尽管Postgres有一些面向对象的特性，它仍然属于关系型数据库的范畴，事实上，一些商用数据库最近已经集成了一些Postgres 所领先的特性．

1.对系统的要求：

安装在RedHat Linux 6.0下测试通过。

要用“root”用户进行安装。

PostgreSQL的版本是7.0

而且一定要先安装egcs-c++-1.1.2-24.i386.rpm软件包。

尽管运行 PostgreSQL 的最小内存需求少到只有 8MB，如果我们把内存增加到96MB，可以看到显著的性能改善。

检查你有足够的磁盘空间。你需要为源代码树准备大约30M字节的空间，为安装目录准备大约5M字节。为一个空数据库准备约 1 M字节，否则包含同样数目数据的平面文件将大约是这个数目的五倍。在回归测试过程中，你会暂时需要约 20 M字节．

检查你有足够的磁盘空间。你需要为源代码树准备大约30M字节的空间，为安装目录准备大约5M字节。为一个空数据库准备约 1 M字节，否则包含同样数目数据的平面文件将大约是这个数目的五倍。在回归测试过程中，你会暂时需要约 20 M字节．

在编译PostgreSQL之前。首先看一下“egcs-c++-1.1.2-24.i386.rpm”是不是已经安装。没有的话，那就赶紧装吧。“egcs-c++-1.1.2-24.i386.rpm”直接可从Redhat的光盘里获得，在“RedHat/RPMS”下。

验证egcs-c++-1.1.2-24.i386.rpm是否安装，用命令：

#rpm -qa|grep egcs

安装egcs用：

# rpm -Uvh egcs-c++-version.i386.rpm

2.下载Postgresql源码包：

Postgresql到本书发表为止已经推出了7.0.2的版本：

由于postgres7.0.2不支持RPM安装格式，故我们只介绍源码方式的安装

ftp://ftp.postgresql.org/pub/v7.0.2/postgresql-7.0.2.tar.gz

3.安装Postgresql：

由于postgres7.0.2不支持RPM安装格式，故我们只介绍源码方式的安装；

1、解包：

#tar xzvf postgresql-7.0.2.tar.gz
#cd postgresql-7.0.2

2、创建postgresql用户

以 root，bin 或者其他有特殊权限的用户身份运行 PostgreSQL 存在安全问题，因而是不允许的。这是运行服务器的用户。用于生产时你可以创建一个独立的非特权的用户（常用 postgres）

#useradd -M -o -r -d /var/lib/pgsql -s /bin/bash -c "PostgreSQL Server" -u 40 postgres

修改好postgres用户的密码，以postgres用户登录系统；

3、设置编译参数：

$CC="egcs"

进入解包后的postgresql源文件目录：
$ ./configure
configure不加任何选项，系统将按默认的设置安装postgresql，查看configre的相关选项可以使用：configure -help

一些最常用的如下：

--prefix=BASEDIR
为安装 PostgreSQL 选择一个不同的基础路径。缺省是 /usr/local/pgsql。

--enable-locale
如果你想用本地化支持。

--enable-multibyte
允许使用多字节字符编码。这个选项主要用于象日语，韩语或中文这样的语言。

--with-perl
添加 Perl 模块接口。请注意 Perl 接口将安装到 Perl 模块的常用位置（典型的是在 /usr/lib/perl），所以要成功使用这个选项，你必须有 root 权限。

--with-odbc
制作 <SPAN onmouseup="DFWYExplainerObj.Window = self; DFWYExplainerObj.ShowExplainer('normal');return false;" title="常规词语，单击这里可以查看解释" style="BACKGROUND-IMAGE: url(C:PROGRA~1!SUNVDFKC3000
ormmark.gif); BACKGROUND-POSITION: left bottom; BACKGROUND-REPEAT: repeat-x; CURSOR: hand" )>ODBC</SPAN> 驱动包．

--with-tcl
制作 Tcl/Tk 需要的接口库和程序，包括 libpgtcl，pgtclsh，和 pgtksh。

这些编译参数告诉编译器如何编译PostgreSQL：

4、编译源码：

$make

5、按照ocnfigure的配置按照程序：

#su
#make install

在这里我们架设postgresql安装在默认的位置/usr/local/

6、配置共享库：

告诉你的系统如何找到共享库。如何实现这些因平台而异。看起来可以在任何地方生效的方法是设置环境变量 LD_LIBRARY_PATH：
# LD_LIBRARY_PATH=/usr/local/pgsql/lib
# export LD_LIBRARY_PATH

你可能把这些放到一个 shell 启动文件里，象 ~/.bash_profile。
在一些系统里，下面的方法是最好的方法，但是你必须有 root 权限。编辑文件 /etc/ld.so.conf，增加一行

/usr/local/pgsql/lib

然后运行命令:

#/sbin/ldconfig

7、用postgres数据库超级用户完成数据库的安装：

你必须用 PostgreSQL 超级用户帐号登录执行这一步。以 root 是不能进行这一步的；

# mkdir /usr/local/pgsql/data
# chown postgres /usr/local/pgsql/data
#su postgres
$ /usr/local/pgsql/initdb -D /usr/local/pgsql/data
We are initializing the database system with username postgres (uid=40).
This user will own all the files and must also own the server process.
Creating Postgres database system directory /var/lib/pgsql/base
Creating template database in /var/lib/pgsql/base/template1
Creating global classes in /var/lib/pgsql/base
Adding template1 database to pg_database...
Vacuuming template1
Creating public pg_user view
Creating view pg_rules
Creating view pg_views
Creating view pg_tables
Creating view pg_indexes
Loading pg_description

-D 选项声明数据存储的位置。你可以使用任何你想用的路径，它不必在安装目录里。在运行 initdb 前只要确保数据库超级用户帐户可以写（或者创建）那个目录就行了。

8、启动postgresql服务；

前面的步骤应该已经告诉你如何启动数据库服务器。现在就做。
$ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
这样将在前台启动数据库服务器。要把它放到后台，使用 -S。

4.配置Postgresql的脚本文件

配置“/etc/rc.d/ini.d/postgresql”脚本文件，用来启动和停止PostgreSQL服务器。

创建“postgresql”脚本文件（touch /etc/rc.d/init.d/postgresql）并加入：

#! /bin/sh
# postgresql This is the init script for starting up the PostgreSQL
# server
# chkconfig: 345 85 15
# description: Starts and stops the PostgreSQL backend daemon that handles
# all database requests.
# processname: postmaster
# pidfile: /var/run/postmaster.pid
#
# Source function library.
. /etc/rc.d/init.d/functions
# Get config.
. /etc/sysconfig/network
# Check that networking is up.
# Pretty much need it for postmaster.
[ ${NETWORKI<SPAN onmouseup="DFWYExplainerObj.Window = self; DFWYExplainerObj.ShowExplainer('normal');return false;" title="常规词语，单击这里可以查看解释" style="BACKGROUND-IMAGE: url(C:PROGRA~1!SUNVDFKC3000
ormmark.gif); BACKGROUND-POSITION: left bottom; BACKGROUND-REPEAT: repeat-x; CURSOR: hand" )>NG</SPAN>} = "no" ] && exit 0
[ -f /usr/bin/postmaster ] || exit 0
# This script is slightly unusual in that the name of the daemon (postmaster)
# is not the same as the name of the subsystem (postgresql)
# See how we were called.
case "$1" in
start)
echo -n "Checking postgresql installation: "
# Check for the PGDATA structure
if [ -f /var/lib/pgsql/PG_VERSION ] && [ -d /var/lib/pgsql/base/template1 ]
then
# Check version of existing PGDATA
if [ `cat /var/lib/pgsql/PG_VERSION` != '6.5' ]
then
echo "old version. Need to Upgrade."
echo "See /usr/doc/postgresql-6.5.2/README.rpm for more information."
exit 1
else
echo "looks good!"
fi
# No existing PGDATA! Initdb it.
else
echo "no database files found."
if [ ! -d /var/lib/pgsql ]
then
mkdir -p /var/lib/pgsql
chown postgres.postgres /var/lib/pgsql
fi
su -l postgres -c '/usr/bin/initdb --pglib=/usr/lib/pgsql --pgdata=/var/lib/pgsql'
fi
# Check for postmaster already running...
pid=`pidof postmaster`
if [ $pid ]
then
echo "Postmaster already running."
else
#all systems go -- remove any stale lock files
rm -f /tmp/.s.PGSQL.* > /dev/null
echo -n "Starting postgresql service: "
su -l postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql'
sleep 1
pid=`pidof postmaster`
if [ $pid ]
then
echo -n "postmaster [$pid]"
touch /var/lock/subsys/postgresql
echo $pid > /var/run/postmaster.pid
echo
else
echo "failed."
fi
fi
;;
stop)
echo -n "Stopping postgresql service: "
killproc postmaster
sleep 2
rm -f /var/run/postmaster.pid
rm -f /var/lock/subsys/postgresql
echo
;;
status)
status postmaster
;;
restart)
$0 stop
$0 start
;;
*)
echo "Usage: postgresql {start|stop|status|restart}"
exit 1
esac
exit 0

现在让脚本可执行并设置它的缺省权限：

[root@Aid]# chmod 700 /etc/rc.d/init.d/postgresql

用下面命令创建“rc.d”目录下PostgresSQL的符号链接：

[root@Aid]# chkconfig --add postgresql

系统会在启动时自动把Postgresql后台进程启动，也可以通过

/etc/rc.d/init.d/postgresql start|stop|restart

进行人工控制；

5.Postgresql数据库的用户管理和存取权限

和Mysql不同的是，Postgresql的用户管理和权限控制，是截然不同的一套体制，有点类似于传统的关系数据库；

在"pg_hba.conf"文件的PG_DATA段可以用ip地址和用户名限制对能数据库的连接，这里你可以指定什么样的IP地址能够连接数据库，什么样的IP地址是不允许使用数据库资源的；

在数据库用户以及数据库方面postgresql用系统命令进行管理：

createuser 允许声明可访问 Postgres的用户．destroyuser删除用户以及拒绝他们访问Postgres。

这些命令只影响用户与 Postgres；而对用户的其他操作系统级的权限或状态没有影响

用“createuser”命令在数据库中定义一个新超级用户：

# su postgres
$ createuser
Enter name of user to add ---> admin
Enter user's postgres ID or RETURN to use unix user ID: 500 ->
Is user "admin" allowed to create databases (y/n) y
Is user "admin" a superuser? (y/n) y
createuser: admin was successfully added

用“destroyuser”命令在数据库中删除用户：

# su postgres
$ destroyuser
Enter name of user to delete ---> admin
destroyuser: delete of user admin was successful.

用“createdb”命令创建新的数据库：

# su postgres
$ createdb dbname

用“destorydb”命令删除数据库：
# su postgres
$ destorydb dbname

6 使用psql操作postgresql数据库

postgresql和mysql一样拥有一套独立的客户端程序，使用标准的SQL语句对数据库进行操作和管理；

$psql databasename
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by egcs ]
type ? for help on slash commands
type q to quit
type g or terminate with semicolon to execute query
You are currently connected to the database: databasename

告诉用户已经连接到数据库databasename

7.postgresql数据库的备份

Postgres 提供两个工具备份你的系统：pg_dump 备份独立的数据库以及 pg_dumpall 在一个步骤里备份你的数据库节点。

可以用下面的命令备份一个独立的数据库：

$ pg_dump dbname > dbname.pgdump

然后可以用下面命令恢复

$cat dbname.pgdump | psql dbname

这个技巧可以用于把数据库移动到一个新位置，然后重新命名现有数据库。

Postgres 允许表的尺寸大于你的系统的最大文件尺寸，可能把表输出到一个文件会有问题，生成的文件很可能比你的系统允许的最大文件大。

使用压缩的输出格式：

$ pg_dump dbname | gzip > filename.dump.gz

重载：

$ createdb dbname
$ gunzip -c filename.dump.gz | psql dbname

或

$ cat filename.dump.gz | gunzip | psql dbname

使用分割（split）：

$ pg_dump dbname | split -b 1m - filename.dump.

重载：

$createdb dbname
$ cat filename.dump.* | pgsql dbname

当然，文件名（filename）和 pg_dump 输出的内容不必与数据库名一样。同样，重载的数据库可以有任意新的名称，所以这个机制还适用于给数据库改名。

PostgreSQL已经是不利新闻的一个牺牲品，被列为一个学术的玩物，为真实世界使用太复杂。在过去这可能是对的，然而不再是。它有一个远远超过其他进展缓慢的数据库甚至一些企业数据库的功能集，然而，它缺乏用来衡量达到高价产品的很多管理特征和调节能力，这些特征是数据库决不会一般水平的数据库所要求的，并且甚至他们不会注意到这很少的缺点。PostgreSQL在Unix数据库竞技场成为了一个真正的竞争者。