如何将网站从MySQL转移到PostgreSQL数据库

在这篇文章的上半部分，我们讲解了如何将数据从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设置文件放在那儿，这个配置文件将会是这样的：

// /usr/local/lib/php/mysite/configfile.php
$hostname = "localhost";
$username = "username";
$database = "mydb";
$password = "mypasswd";
?>
//标准的HTML头
include("mysite/configfile.php");
?>

...

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

连接和查询

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系统来实现这样的功能。
幸运的是，要获得最后的ID是相当容易的。你可以通过SQL获得Sequence信息，因此你可以用这个语句来实现mysql_insert_id()的功能：
function postg_insert_id($tablename, $fieldname)
{
global connection_id;
$result=pg_exec($connection_id, "SELECT last_value FROM ${tablename}_
${fieldname}_seq");
$seq_array=pg_fetch_row($result, 0);
return $seq_array[0];
}
因为Postgres使用了一个特别的命名系统来命名序列，我上面建立的这个函数需要指定表名和字段名。调用这个函数，会返回你的表中的任意SERIAL字段的最后一个序列值，即使在表中有不止一个这样的字段。
经过上面的这些处理后，你已经可以在你的MySQL站点上成功地运行PostgreSQL了。然而，这仅仅是第一步；如果你想了解更多，继续看下去，你会看到一些有用的PostgreSQL的资源。