将你的网站从MySQL改为PostgreSQL(上)(转)[@more@]原著:Nathan Matias 翻译:处处
我的站点最初是采用PHP驱动,由MySQL数据库支持的方案,这在当时是一个明智的方案。在2001年夏天,我将我的数据库换成了PostgreSQL(有时也简称为Postgres)。
这个教程分为两部分,第一部分讲述了我进行这种转换的动机,并一步步地解释了如何将已存在有MySQL的数据转换到Postgres中。第二部分将会解释如何根据新的数据库系统对PHP进行相应的调整。
转换的动机
我第一次了解Postgres是在PHPBuilder网站的一篇文章中。这篇文章将Postgres和MySQL进行了比较,当时我正在使用MySQL。但是,当我阅读了这篇文章后,我对Postgres着了迷 -- 但是当时我还没有想到对我的网站进行重新的设计。
我继续使用MySQL,因为我的主机提供商只能提供MySQL的支持,这是我所无法改变的。直到有一天,主机提供商的主机崩溃了。我立即换了一个主机提供商,与原来的那个相比,新的主机提供商有很多不同,他们在安全性和稳定性方面对我作出了更多的承诺。新公司试图说服我使用Postgres,因为Postgres要比MySQL来得更稳定,但是我当时没有接受这个建议,因为我的网站已经根据MySQL完成了全部的编码工作。他们只好专门为我的站点安装了MySQL。于是问题开始了。
我的第一个工作是将旧服务器上的MySQL的数据拷贝到新的主机上。首先,我将已有的数据dump到一个SQL文件中,然后在新的主机上导入这个SQL文件。在处理这个数千行的文件时,MySQL迅速地崩溃了。重启MySQL后,其中大概只有一半数据成功地导入了,而且MySQL只能间歇性地工作。最后,他们不得不删除了已经导入的信息让我再试一次。MySQL再次崩溃。这种情况重复了好几次,直到最终我决定将我的SQL文件分割成几块。我不得不又试了几次,最后终于将绝大多数的数据都成功地导入到新的MySQL服务器中。一切都好了,我总算松了一口气。
在下面的几个月中,MySQL几乎每两周都要崩溃一次,其中最惨痛的一次是在2001年6月底。这一次,存储在MySQL中的数据完全被毁坏了。我有一个SQL的备份文件,但是因为上次向MySQL中导入大量数据的痛苦的经历,这一次我再也不想通过这个备份恢复数据了。这时,公司再次建议我对我的网站进行转向,使用Postgres。由于MySQL的失败,最终我接受了这个建议。
将数据从MySQL转移到Postgres中
将数据从MySQL转移到Postgres是一个不大的挑战,因为Postgres比MySQL支持了更多的SQL的标准格式,在POstgres中直接使用SQL的dump结果是不可能的。但是,SQL语法相当相似,因此对于我来说,这并没有花费太多的时间。
对MySQL的Dump结果进行转换
首先,要求你的主机提供商为你的帐号建立一个数据库。和MySQL数据库一样,Postgres的数据库也由一系列包含实际数据的数据表组成。然后,使用mysqldump命令为你的MySQL数据库做一个dump文件。
mysqldump -u username -p databasename > sqldump.txt
使用FTP将整个dump文件下载下来。现在在你的计算机上有了这个SQL文件,你可以将其转换成Postgres可以导入的文件。
首先,从dump文件中剪切所有的MySQL的CREATE TABLE查询,并将其粘贴到一个单独的文本文件中。下一步是使用Postgres可以理解的语言重新对数据表进行定义。
Postgres建立表的SQL和MySQL非常类似,但不完全一样。下面是一个例子:
CREATE TABLE practicetable
{
someID SERIAL,
time TIMESTAMP DEFAULT now(),
name VARCHAR(50),
address VARCHAR(50),
city VARCHAR(50),
state VARCHAR(2),
country VARCHAR(3) DEFAULT 'USA',
postlcode VARCHAR(15),
age smallint,
lattitude real,
longitude real,
somebool boolean,
message textitem
};
在一个Postgres的表定义中,字段名后面必须跟着字段类型。在上面的例子中我们给出了一些最普通的字段类型,你还可以在有关Postgres数据类型的文档中找到全部的字段类型的列表。对于不同的任务,Postgres在字段类型方面有多种选择,并可以存储各种类型的数据,从Internet地址到货币信息到几何对象的定义。
这儿简要地介绍最常用的几种数据类型。
SERIAL类型的字段和MySQL中的自增唯一ID等价。当你在你的数据表中定义了一个SERIAL类型的列后,SERIAL的自增功能会被自动添加到数据库。当自增功能不能适应实际需求时,我们可以自定义唯一ID的逻辑。从MySQL向Postgres转输数据时,默认的功能已经足够了。
和字面上的意义一样VARCHAR类型是一个可变长度的文本字段。字段的长度由括号中的数值定义。例如,VARCHAR(5)定义了一个最多可包含5个字符的文本字段。
SMALLINT、INT和BIGINT用来定义整型字段。SMALLINT字段可存储数值范围为-32768到+32767(实际的范围可能会稍微受到你的计算机类型的影响,上面的范围适用于最普通的系统)。INT字段可存储数值范围为-2147483648到+2147483647。而BIGIN字段类型可存储任何更大的整数,它没有范围的限制。
REAL字符类型是一个包含十进制小数的实数。它可以精确到小数点后六位。DOUBLE PRECISION字段与此相类似,但是它可以精确到小数点后15位。
BOOLEAN字段是真或假、1或0。这和MySQL中相似。
TIMESTAMP字段和MySQL中的情况类型。每次记录更新时,timestamp被更新为当前的日期和时间。Postgres的时间字段还可以包含时区信息。有关Postgres时间数据的更复杂的应用,请参看PostgreSQL文档的日期和时间。
建立数据表
当你使用SQL文件在Postgres中建立数据表时,请检查在每一个CREATE TABLE查询的最后是不是都以分号结束 - 这对于Postgres是不可省略的。使用telnet这样的工具连接到你的Web主机,然后用下面的方法建立数据表。
首先,用一个文本编辑器打开你的表定义文件。然后登录到你的主机,并输入psql运行Postgres交互终端。默认的用户论证方式是使用你的telnet/FTP用户名作为你的Postgres帐号。这使得不需要你输入用户名和口令,Postgres就能自动鉴别你的身份。你的Web主机也许不是采用的这种方式,在这种情况下,你需要为psql程序带入参数:psql -d databasename -U username -W。-d用来指定数据库,-U指定用户名,而-W要求psql提示你输入一个口令。
当你成功地运行了psql以后,将每个CREATE TABLE查询单独地粘贴到psql中并按回车键。如果在你的SQL语句中有错误,psql会给出相应提示。通过逐一地加入每一个表,你会得到每一个表的调试信息,这样做起来相当简单。
如果,在你输入了表的定义之后,你发现遗漏了一两个字段,有两种方法能解决这个问题。你可以使用ALTER TABLE命令,或者是使用DROP TABLE删除这张表,然后重新生成。如果你使用第二种方法,你会看到一个警告以验证你是不是真的想要删除表。
要使用DROP TABLE命令,只需要输入DROP TABLE practicetable;。这会删除我们刚才定义的表。但是当你对这个表重新进行定义时,你会发现一个错误。这是因为在删除一个表时并不相应地删除这个表中SERIAL类型字段的序列。这些遗留下来的序列会在你重建表时引起错误。要解决这个问题,你必须在删除表之前使用DROP SEQUENCE sequencename;删除相应的序列。而且有件很讨厌的事,那就是序列名并不就是SERIAL列的名字。当你定义一个SERIAL类型的字段时,Postgres会自动生成这样的序列名:tablename_colname_seq。在现在的这种情况下,DROP SEQUENCE 语句将会是这样的:DROP SEQUENCE practicetable_someID_seq;。现在你就可以删除这张表并重新生成它了。
在添加完这些表之后,你可以输入z对这些表进行复查。而输入q将会退出psql。现在剩下来的就是准备输入到Postgres中的数据了。
处理Dump文件
因为MySQL保留了绝大多数的SQL语言的标准,从一个SQL的dump文件中导出实际数据并不是太困难的。然而,在我们使用Postgres对这个文件进行处理前,我们还是需要作一些编辑工作。
对于数据记录,在MySQL和Postgres之间的主要区别是对引号的处理。在Postgres中,字符串变量(包含文本的变量)必须由两个单引号引出。而在MySQL中,你还可以使用双引号,但是幸运的是,在mysqldump程序中程序中使用的是单引号,这刚好与Postgres一致。然而,MySQL和Postgres还有一个地方不同,那就是对字符串中出现的引号的处理。在MySQL中使用"",而在Postgres中使用"。使用你的文本编辑器并通过替换功能将其中所有的""替换为"。有趣的是,Postgres和MySQL都使用''来表示单引号,这使得我们免去了一个麻烦。
导入到Postgres中
当你整理好SQL dump文件后,将这个文件上载到你的Web主机中,就如同你当初建表那样登录到主机,转到SQL dump文件存放的目录。启动psql,不过这次你必须使用另一个命令行参数:psql -f sqldump.txt,这儿的sqldump.txt就改为你的SQL dump文件的文件名。这个命令会将全部的SQL文件导入到适当的Postgres数据表中。在此之前,你也许还需要其它的一些命令行参数以使得psql可以对你的身份进行验证。如果发生了错误,psql会告诉这是由什么引起的。找到文件中的这一部分,找到问题并手工解决它。我当初是没有遇到任何问题,我差不多准备结束工作了。但是,很快我注意到另一个问题。
在我开始使用我的新的Postgres驱动的站点时,我偶然地发现MySQL和Postgres之间另一个不兼容的地方。SERIAL类型的自增字段所使用的Postgres的序列,它从1开始,并在每次有一个SERIAL类型字段的记录插入时加一。然而,在我导入MySQL的dump文件时,这个dump文件中的SQL将这个值定义为整型主键。我当时的情况是,我有一个到唯一主键已经到了60,而序列仍然是1。于是我的每一个插入命令都没法成功,因为根据序列产生的不是唯一ID。我当时用了一个很笨的方法解决这个问题,那就是运行了60次INSERT语句以将序列调整为适当的值,但是后来有一个熟悉Postgres的朋友教给我一个好方法。下面就是他所讲的方法:
使用telnet这样的终端程序连接到你的主机。然后启动psql程序。首先,确定表中ID的最大值。这可以用SELECT fieldname FROM tablename WHERE fieldname=MAX(fieldname);。然后使用DROP SEQUENCE table_colname_seq;删除有问题的序列,这儿table是表名,而colname是SERIAL字段的列名。然后使用CREATE SEQUENCE table_colname_seq START 61;重建序列,当然这儿的61应根据你的实际情况进行修改。
安装一个图形界面的工具
当我成功地将数据导入到Postgres后,我还需要让我的不懂Unix的伙伴能够操作数据库中的数据。当初的MySQL我是使用的phpMyAdmin,这是一个很好用的工具,它能够在线的显示和编辑数据库。幸运的是,已经有了“Postgres版本”的phpMyAdmin,那就是phpPgAdmin。
phpPgAdmin的安装非常简单。首先,从phpPgAdmin网站下载最新的版本,然后将其放到你的Web主机上的你所能访问的地方。使用telnet这样的工具登陆到主机。到phpPgAdmin.tar.gz文件所在的目录,输入tar -xzvf phpPgAdmin.tar.gz对程序进行解压(这里只是举个例子,你的文件名可能不一样)。下面一件事就是将解压生成的新的子目录移到合适的地方,并阅读README文件。
最后,用你的Web主机上的文本编辑器打开config.inc.php。这个文件中包含了对phpPgAdmin的配置。将这个文件配置好你就可以通过浏览器使用PhpPgAdmin了,它会提示你输入用户名并登录,通过这个程序你管理你的数据库将变得非常简单。
结语
与MySQL相比,Postgres更加稳定,更加可靠,可以应付更大的数据。按照上面的提示,你可以使用SQL的dump文件将数据从MySQL转到Postgres。如果在这个过程中你遇到什么困难,从Postgres网站你可以找到一些非常有用的文档资源。
在本文的下半部分,我们将讨论PHP访问Postgres与访问MySQL的不同点。
我的站点最初是采用PHP驱动,由MySQL数据库支持的方案,这在当时是一个明智的方案。在2001年夏天,我将我的数据库换成了PostgreSQL(有时也简称为Postgres)。
这个教程分为两部分,第一部分讲述了我进行这种转换的动机,并一步步地解释了如何将已存在有MySQL的数据转换到Postgres中。第二部分将会解释如何根据新的数据库系统对PHP进行相应的调整。
转换的动机
我第一次了解Postgres是在PHPBuilder网站的一篇文章中。这篇文章将Postgres和MySQL进行了比较,当时我正在使用MySQL。但是,当我阅读了这篇文章后,我对Postgres着了迷 -- 但是当时我还没有想到对我的网站进行重新的设计。
我继续使用MySQL,因为我的主机提供商只能提供MySQL的支持,这是我所无法改变的。直到有一天,主机提供商的主机崩溃了。我立即换了一个主机提供商,与原来的那个相比,新的主机提供商有很多不同,他们在安全性和稳定性方面对我作出了更多的承诺。新公司试图说服我使用Postgres,因为Postgres要比MySQL来得更稳定,但是我当时没有接受这个建议,因为我的网站已经根据MySQL完成了全部的编码工作。他们只好专门为我的站点安装了MySQL。于是问题开始了。
我的第一个工作是将旧服务器上的MySQL的数据拷贝到新的主机上。首先,我将已有的数据dump到一个SQL文件中,然后在新的主机上导入这个SQL文件。在处理这个数千行的文件时,MySQL迅速地崩溃了。重启MySQL后,其中大概只有一半数据成功地导入了,而且MySQL只能间歇性地工作。最后,他们不得不删除了已经导入的信息让我再试一次。MySQL再次崩溃。这种情况重复了好几次,直到最终我决定将我的SQL文件分割成几块。我不得不又试了几次,最后终于将绝大多数的数据都成功地导入到新的MySQL服务器中。一切都好了,我总算松了一口气。
在下面的几个月中,MySQL几乎每两周都要崩溃一次,其中最惨痛的一次是在2001年6月底。这一次,存储在MySQL中的数据完全被毁坏了。我有一个SQL的备份文件,但是因为上次向MySQL中导入大量数据的痛苦的经历,这一次我再也不想通过这个备份恢复数据了。这时,公司再次建议我对我的网站进行转向,使用Postgres。由于MySQL的失败,最终我接受了这个建议。
将数据从MySQL转移到Postgres中
将数据从MySQL转移到Postgres是一个不大的挑战,因为Postgres比MySQL支持了更多的SQL的标准格式,在POstgres中直接使用SQL的dump结果是不可能的。但是,SQL语法相当相似,因此对于我来说,这并没有花费太多的时间。
对MySQL的Dump结果进行转换
首先,要求你的主机提供商为你的帐号建立一个数据库。和MySQL数据库一样,Postgres的数据库也由一系列包含实际数据的数据表组成。然后,使用mysqldump命令为你的MySQL数据库做一个dump文件。
mysqldump -u username -p databasename > sqldump.txt
使用FTP将整个dump文件下载下来。现在在你的计算机上有了这个SQL文件,你可以将其转换成Postgres可以导入的文件。
首先,从dump文件中剪切所有的MySQL的CREATE TABLE查询,并将其粘贴到一个单独的文本文件中。下一步是使用Postgres可以理解的语言重新对数据表进行定义。
Postgres建立表的SQL和MySQL非常类似,但不完全一样。下面是一个例子:
CREATE TABLE practicetable
{
someID SERIAL,
time TIMESTAMP DEFAULT now(),
name VARCHAR(50),
address VARCHAR(50),
city VARCHAR(50),
state VARCHAR(2),
country VARCHAR(3) DEFAULT 'USA',
postlcode VARCHAR(15),
age smallint,
lattitude real,
longitude real,
somebool boolean,
message textitem
};
在一个Postgres的表定义中,字段名后面必须跟着字段类型。在上面的例子中我们给出了一些最普通的字段类型,你还可以在有关Postgres数据类型的文档中找到全部的字段类型的列表。对于不同的任务,Postgres在字段类型方面有多种选择,并可以存储各种类型的数据,从Internet地址到货币信息到几何对象的定义。
这儿简要地介绍最常用的几种数据类型。
SERIAL类型的字段和MySQL中的自增唯一ID等价。当你在你的数据表中定义了一个SERIAL类型的列后,SERIAL的自增功能会被自动添加到数据库。当自增功能不能适应实际需求时,我们可以自定义唯一ID的逻辑。从MySQL向Postgres转输数据时,默认的功能已经足够了。
和字面上的意义一样VARCHAR类型是一个可变长度的文本字段。字段的长度由括号中的数值定义。例如,VARCHAR(5)定义了一个最多可包含5个字符的文本字段。
SMALLINT、INT和BIGINT用来定义整型字段。SMALLINT字段可存储数值范围为-32768到+32767(实际的范围可能会稍微受到你的计算机类型的影响,上面的范围适用于最普通的系统)。INT字段可存储数值范围为-2147483648到+2147483647。而BIGIN字段类型可存储任何更大的整数,它没有范围的限制。
REAL字符类型是一个包含十进制小数的实数。它可以精确到小数点后六位。DOUBLE PRECISION字段与此相类似,但是它可以精确到小数点后15位。
BOOLEAN字段是真或假、1或0。这和MySQL中相似。
TIMESTAMP字段和MySQL中的情况类型。每次记录更新时,timestamp被更新为当前的日期和时间。Postgres的时间字段还可以包含时区信息。有关Postgres时间数据的更复杂的应用,请参看PostgreSQL文档的日期和时间。
建立数据表
当你使用SQL文件在Postgres中建立数据表时,请检查在每一个CREATE TABLE查询的最后是不是都以分号结束 - 这对于Postgres是不可省略的。使用telnet这样的工具连接到你的Web主机,然后用下面的方法建立数据表。
首先,用一个文本编辑器打开你的表定义文件。然后登录到你的主机,并输入psql运行Postgres交互终端。默认的用户论证方式是使用你的telnet/FTP用户名作为你的Postgres帐号。这使得不需要你输入用户名和口令,Postgres就能自动鉴别你的身份。你的Web主机也许不是采用的这种方式,在这种情况下,你需要为psql程序带入参数:psql -d databasename -U username -W。-d用来指定数据库,-U指定用户名,而-W要求psql提示你输入一个口令。
当你成功地运行了psql以后,将每个CREATE TABLE查询单独地粘贴到psql中并按回车键。如果在你的SQL语句中有错误,psql会给出相应提示。通过逐一地加入每一个表,你会得到每一个表的调试信息,这样做起来相当简单。
如果,在你输入了表的定义之后,你发现遗漏了一两个字段,有两种方法能解决这个问题。你可以使用ALTER TABLE命令,或者是使用DROP TABLE删除这张表,然后重新生成。如果你使用第二种方法,你会看到一个警告以验证你是不是真的想要删除表。
要使用DROP TABLE命令,只需要输入DROP TABLE practicetable;。这会删除我们刚才定义的表。但是当你对这个表重新进行定义时,你会发现一个错误。这是因为在删除一个表时并不相应地删除这个表中SERIAL类型字段的序列。这些遗留下来的序列会在你重建表时引起错误。要解决这个问题,你必须在删除表之前使用DROP SEQUENCE sequencename;删除相应的序列。而且有件很讨厌的事,那就是序列名并不就是SERIAL列的名字。当你定义一个SERIAL类型的字段时,Postgres会自动生成这样的序列名:tablename_colname_seq。在现在的这种情况下,DROP SEQUENCE 语句将会是这样的:DROP SEQUENCE practicetable_someID_seq;。现在你就可以删除这张表并重新生成它了。
在添加完这些表之后,你可以输入z对这些表进行复查。而输入q将会退出psql。现在剩下来的就是准备输入到Postgres中的数据了。
处理Dump文件
因为MySQL保留了绝大多数的SQL语言的标准,从一个SQL的dump文件中导出实际数据并不是太困难的。然而,在我们使用Postgres对这个文件进行处理前,我们还是需要作一些编辑工作。
对于数据记录,在MySQL和Postgres之间的主要区别是对引号的处理。在Postgres中,字符串变量(包含文本的变量)必须由两个单引号引出。而在MySQL中,你还可以使用双引号,但是幸运的是,在mysqldump程序中程序中使用的是单引号,这刚好与Postgres一致。然而,MySQL和Postgres还有一个地方不同,那就是对字符串中出现的引号的处理。在MySQL中使用"",而在Postgres中使用"。使用你的文本编辑器并通过替换功能将其中所有的""替换为"。有趣的是,Postgres和MySQL都使用''来表示单引号,这使得我们免去了一个麻烦。
导入到Postgres中
当你整理好SQL dump文件后,将这个文件上载到你的Web主机中,就如同你当初建表那样登录到主机,转到SQL dump文件存放的目录。启动psql,不过这次你必须使用另一个命令行参数:psql -f sqldump.txt,这儿的sqldump.txt就改为你的SQL dump文件的文件名。这个命令会将全部的SQL文件导入到适当的Postgres数据表中。在此之前,你也许还需要其它的一些命令行参数以使得psql可以对你的身份进行验证。如果发生了错误,psql会告诉这是由什么引起的。找到文件中的这一部分,找到问题并手工解决它。我当初是没有遇到任何问题,我差不多准备结束工作了。但是,很快我注意到另一个问题。
在我开始使用我的新的Postgres驱动的站点时,我偶然地发现MySQL和Postgres之间另一个不兼容的地方。SERIAL类型的自增字段所使用的Postgres的序列,它从1开始,并在每次有一个SERIAL类型字段的记录插入时加一。然而,在我导入MySQL的dump文件时,这个dump文件中的SQL将这个值定义为整型主键。我当时的情况是,我有一个到唯一主键已经到了60,而序列仍然是1。于是我的每一个插入命令都没法成功,因为根据序列产生的不是唯一ID。我当时用了一个很笨的方法解决这个问题,那就是运行了60次INSERT语句以将序列调整为适当的值,但是后来有一个熟悉Postgres的朋友教给我一个好方法。下面就是他所讲的方法:
使用telnet这样的终端程序连接到你的主机。然后启动psql程序。首先,确定表中ID的最大值。这可以用SELECT fieldname FROM tablename WHERE fieldname=MAX(fieldname);。然后使用DROP SEQUENCE table_colname_seq;删除有问题的序列,这儿table是表名,而colname是SERIAL字段的列名。然后使用CREATE SEQUENCE table_colname_seq START 61;重建序列,当然这儿的61应根据你的实际情况进行修改。
安装一个图形界面的工具
当我成功地将数据导入到Postgres后,我还需要让我的不懂Unix的伙伴能够操作数据库中的数据。当初的MySQL我是使用的phpMyAdmin,这是一个很好用的工具,它能够在线的显示和编辑数据库。幸运的是,已经有了“Postgres版本”的phpMyAdmin,那就是phpPgAdmin。
phpPgAdmin的安装非常简单。首先,从phpPgAdmin网站下载最新的版本,然后将其放到你的Web主机上的你所能访问的地方。使用telnet这样的工具登陆到主机。到phpPgAdmin.tar.gz文件所在的目录,输入tar -xzvf phpPgAdmin.tar.gz对程序进行解压(这里只是举个例子,你的文件名可能不一样)。下面一件事就是将解压生成的新的子目录移到合适的地方,并阅读README文件。
最后,用你的Web主机上的文本编辑器打开config.inc.php。这个文件中包含了对phpPgAdmin的配置。将这个文件配置好你就可以通过浏览器使用PhpPgAdmin了,它会提示你输入用户名并登录,通过这个程序你管理你的数据库将变得非常简单。
结语
与MySQL相比,Postgres更加稳定,更加可靠,可以应付更大的数据。按照上面的提示,你可以使用SQL的dump文件将数据从MySQL转到Postgres。如果在这个过程中你遇到什么困难,从Postgres网站你可以找到一些非常有用的文档资源。
在本文的下半部分,我们将讨论PHP访问Postgres与访问MySQL的不同点。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10617731/viewspace-950678/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10617731/viewspace-950678/