将你的网站从MySQL改为PostgreSQL

2024-07-24 12:56:50

字体：大中小

来源：转载

供稿：网友

这个教程分为两部分，第一部分讲述了我进行这种转换的动机，并一步步地解释了如何将已存在有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了，它会提示你输入用户名并登录，通过这个程序你管理你的数据库将变得非常简单。

在2001年六月，我将我的站点的后端数据库从mysql改为postgresql。这仅仅花了我一天的时间。从那时起，postgres一直工作得很正常，这证明我作出了正确的选择。

在这篇文章的上半部分，我们讲解了如何将数据从mysql转换到postgres。我们还说明了我改用postgres的理由。而在下半部分，我们将指导你完成另一半的转换工作并讲解如果将已编制好的用于mysql的php代码改用于postgres。

准备

在你做这项工作之前，你需要一些准备工作。你的编程技巧和网站的复杂性将对程序的转换影响很大。为了防止在工作中发生错误，在你的web服务器上为你的站点作一份备份肯定是必要的。我当时的解决方法是设置了一个指定的子域来测试我正在修改的代码。因为站点会根据一个配置文件中的一些基本配置自动处理url，所以这件工作很简单。你也许不能这样做，但是不管怎样，你都需要一个另外的空间来放置你正在改动的程序，这可以是一个子域、你的站点的一个子目录、另一个web主机也可以是一个本地的开发机器。

注意：如果你在另一台机器修改你的程序，你必须确保这个web服务器的配置和你正在使用的站点一样。如果你的web主机只允许从本地连接postgres，你也许将不得不将你的postgres数据拷贝到开发机器上。要得到更多有关拷贝一个postgres数据库的信息，可以参看http://www.postgresql.org/上的pg_dump和pg_restore。

做好了拷贝并且经过测试它可以正常工作之后，你可以着手对程序进行解剖了。

php手术：代码解剖

如何你之前没有一个中心的配置文件，那么首先建立它。这将减轻我们的很多工作量，而且也使得我们的改动可以即时对整个站点发生作用。这个配置文件也该是不允许其它人通过网站访问的，否则这将是一个安全隐患。php的默认包含目录是/usr/local/lib/php/。你需要在你的web主机上建立这样一个目录并不允许通过网站访问。你还得确保这对于使用同一web主机的其他人也无法读取这个目录。如果你的站点和我的一样，包含了一个标准的头文件，你可以将你的postgres设置文件放在那儿，这个配置文件将会是这样的：！<?php
// /usr/local/lib/php/mysite/configfile.php
$hostname = "localhost";
$username = "username";
$database = "mydb";
$password = "mypasswd";
?>
<?php
//标准的html头
include("mysite/configfile.php");
?>
<html>
<head>
<title>bill's kazoos</title>
...
</head>
<body>
...

有了这样一个前面这样的中心配置文件，改变数据库系统将变得非常简单。现在可以着手改程序了。

连接和查询

php有关mysql的函数和postgres很相似，所以转换代码的工作并不是太复杂。事实上，你可以自己写一些函数来完成这种转换。在做这项工作之前，让我们来看看两者的不同：

要连接到mysql数据库需要用到两个命令：

$connection_id=mysql_connect($hostname, $username, $password);

mysql_select_db($database, $connection_id);

如果你使用一个持久的连接：

$connection_id=mysql_pconnect($hostname, $username, $password);

mysql_select_db($database, $connection_id);

然而，php连接postgres的函数只需要一个字符串参数，与mysql函数不同，这是一个复合的字符串参数。postgres函数也需要你指定使用的数据库。下面是一个示例：

$connection_id=pg_connect("host=$hostname dbname=$database user=$username

password=$password");

一个持久的连接执行同样的工作，只是需要调用pg_pconnect()函数。

php的mysql和postgres的查询函数同样有点不同。mysql的查询函数是$result_data = mysql_query("query goes here",$connection_id);，而postgres的查询函数是这样的：$result_data = pg_exec($connection_id, "query goes here")。

正如你所看到的，php对mysql和postgres和连接和查询的支持区别并不大，但是函数参数的不同还是需要我们慢慢处理。要提高速度，你可以写一些函数使得postgres可以使用和mysql一样的函数来连接。如果你有了包含这样一种函数的中心库，你可以将这些函数也放在那里。你也可以将它们放置在我们前面所提到的配置文件中，因为它会自动地被每个页面包含。

//连接到数据库

function postg_connect($hostname, $username, $password, $database)

{

return pg_connect("host=$hostname dbname=$database

user=$username password=$password");

}

//如果你仅仅使用一个数据库，你最好将这些变量放到你的配置文件中

function postg_autoconnect()

{

global $hostname, $username $password $database;

return pg_connect("host=$hostname dbname=$database

user=$username password=$password");

}

//查询函数

function postg_query($query, $connection_id)

{

return pg_exec($connection_id, $query);

}

不管你是否使用这种函数，代码转换的工作总是相当简单的。postgres几乎可以支持所有的以前在mysql下使用的sql查询，但是你可能还是要整理一下你的查询。因为在不同的地方数据模型和代码会有一些不同，我在这里不想详细解释这个问题。然而，对sql的转换并不困难。首先转换代码，然后看看有哪些查询无法在postgres中正常执行。对mysql语言指南和postgresql用户向导中的相关问题进行比较，你也许不能在postgres中找到所有与mysql同等的功能，但是postgres支持所有的通用的功能。

现在你已经把连接和查询的代码改好了，下面的问题可能要稍微复杂一点。php中mysql和postgres对结果集处理的不同可能需要你对代码作更多的变动。

让我们来看看对结果的处理

php的postgres对结果的处理并不完全和mysql一一对应；它们有一些微小的不同。这些微小的差别可能只需要对代码作微小的改动，但是也可能是一个挺复杂的问题。

首先，让我们看看mysql和postgres有哪些相似的地方。下面这个列表介绍了普通的mysql结果处理函数和它们相对应的postgres函数：

mysql

mysql_num_rows($result) 返回结果集的行数，这仅对select语句有效

mysql_affected_rows($result) 返回在一个insert、update或delete查询中受到影响的行数

mysql_fetch_object($result) 取得一行的数据并将其作为一个对象返回。字段名对应于类的属性名。(即$field1 = $var->field1;)这个函数保存了一个内部变量以保证每次调用时可以返回下一行。

mysql_fetch_row($result) 这个函数以一个数组的形式返回结果集的一行。这个值可以通过一个从0开始的数组值获得。(即$field1 = $var[0];)。同样，这个函数保存了一个内部的计数器以保证每次调用时可以返回下一行。

mysql_fetch_array($result) 这个函数和另外两个fetch函数基本相同，只是它以一个联合数组的形式返回一个行($field1 = $var["field1"];)。

postgres

pg_numrows($result) 与对应的mysql_num_rows($result)完全一样

pg_cmdtuples($result) 与对应的mysql_affected_rows($result)完全一样

pg_fetch_object($result, $row) 获得结果集中的指定行。必须使用$row参数，而且没有一个内部的计数器。除此之外，它与mysql_fetch_object($result)完全相同。

pg_fetch_row($result, $row) 以一个数组的形式返回结果集中的指定行。同样必须使用$row参数，而且没有一个内部的计数器。

pg_fetch_array($result, $row) 与对应的mysql_fetch_array($result)基本一样，只是需要指定行，并且缺少一个内部的计数器。

有关这些函数的更详细的信息，请参看php.net上的php文档。

php对mysql和postgres支持的最本质的不同在于对结果集的阅读。mysql自动决定获取哪一行，而postgres必须指定要阅读哪一行。下面是一些例子，你也可能会遇到这些问题，对于它们有两个解决方案。

//第一个普通的例子：

$rslt=mysql_query("select * from blah", $connection_id);

while($value=mysql_fetch_array($rslt))

{

//完成数据处理工作

}

//对于postgres，这样的代码无法执行，因为他们需要指定行号

//代码将作如下改动(如果你没有使用前面讨论的函数):

$rslt=pg_exec($connection_id, "select * from blah");

$limit=pg_numrows($rslt);

for($rownum=0;$rownum<$limit;$rownum++)

{

$value=pg_fetch_array($rslt, $rownum);

//完成处理工作

}

在上面的例子中，你可以注意到postgres的代码要稍微长一点，这是因为你必须指定行号。然而，如果你使用了你编写的自己的计数函数，问题就变得很简单了。这儿是一个添加了这样一个函数的有用的文件。请注意在postg_query()中使用了三个全局变量。

// /usr/local/lib/php/mysite/configfile.php

$hostname = "localhost";

$username = "username";

$database = "mydb";

$password = "mypasswd";

//内部计数变量

$fetch_array_counter=0;

$fetch_object_counter=0;

$fetch_row_counter=0;

//处理连接到postgresql数据库的函数

function postg_connect($hostname, $username, $password, $database)

{

return pg_connect(host=$hostname, dbname=$database user=$username,

password=$password");

}

//不需要任何参数的连接

function postg_autoconnect()

{

global $hostname, $username, $password, $database;

return pg_connect(host=$hostname, dbname=$database user=$username,

password=$password");

}

//查询函数

function postg_query($query, $connection_id)

{

//将全局变量设置为0

global $fetch_array_counter, $fetch_row_counter, $fetch_object_counter;

$fetch_array_counter=$fetch_row_counter=$fetch_object_counter=0;

return pg_exec($connection_id, $query);

}

//pg_fetch_array()置换

function postg_fetch_array($rslt)

{

global $fetch_array_counter;

$fetch_array_counter++;

//计数器加一

return pg_fetch_array($rslt, $fetch_array_counter);

}

//pg_fetch_row()置换

function postg_fetch_row($rslt)

{

global $fetch_row_counter;

$fetch_row_counter++;

//计数器加一

return pg_fetch_row($rslt, $fetch_row_counter);

}

//pg_fetch_object()置换

function postg_fetch_object($rslt)

{

global $fetch_object_counter;

$fetch_object_counter++;

//计数器加一

return pg_fetch_object($rslt, $fetch_object_counter);

}

当然，如果你在同一个循环中同时对两个结果集进行操作，上面的函数将无法正常地工作，因为它们只使用了一个内部的计数器。如果因为某种原因，你需要同时阅读几个结果集，你将不得不使用传统的postgres方法。

另一个你可能遇到的问题是在postgres中没有与mysql中mysql_insert_id()相应的函数，这个函数反映最后的insert查询的索引值。php文档往往会让读者误以为pg_getlastoid()会完成这项工作，但是实际情况并不是这样。缺少这一样一个函数并不一个无法逾越的障碍，你可以利用postgres的sequence系统来实现这样的功能。