將你的網站從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系統來實現這樣的功能。