將你的網站從MySQL改為PostgreSQL(上)

火星人 @ 2014-03-12 , reply:0
←手機掃碼閱讀

  原著: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的不同點。





[火星人 via ] 將你的網站從MySQL改為PostgreSQL(上)已經有371次圍觀

http://www.coctec.com/docs/linux/show-post-74169.html