:::

PostgreSQL的備份與復原

12月 18, 2012 4 Comments Edit Post

120_120823175057_1

最近又回來做標註系統了,這次一樣要先建構環境。當初我寫KALS標註系統的時候使用的資料庫是PostgreSQL,它沒辦法透過檔案系統來備份跟復原,必須仰賴它的工具。這篇稍微記錄怎麼備份與還原PostgreSQL資料庫。其實不是很複雜,只是備忘而已。


備份:pg_dump

pg_dump是PostgreSQL的標準備份工具,其主要用法為:

pg_dump [資料庫名稱] -U [帳號] -f [備份檔案名稱.backup.sql]

備份檔案名稱後面的附檔名.backup.sql只是我自己的習慣而已。

舉例來說,我現在要輸出「kals」資料庫,帳號為「kals」,輸出到檔名「kals-db-20121218.backup.sql」,那麼指令如下:

pg_dump kals -U kals -f kals-db-20121218.backup.sql

你會獲得一個「kals-db-20121218.backup.sql」檔案,這個就是你整個資料庫的備份檔了。

復原(備份回存):psql

因為上面我們匯出的是sql檔案,所以這邊用psql來復原備份的資料。

psql是PostgreSQL主要的管理工具,可以執行各種工作。在這裡我們要復原SQL檔案,其用法是:

psql -f [備份檔案名稱.backup.sql] [資料庫] [帳號]

現在我們要將備份檔「kals-db-20121218.backup.sql」復原到「kals」,使用「kals」帳號進行,那麼指令如下:

psql -f kals-db-20121218.backup.sql kals kals

Windows注意路徑問題

在Windows操作的時候,要特別注意到路徑的問題。直接在命令提示字元執行「pg_dump」或「psql」都會發生錯誤,因為Windows不知道這兩個工具的位置。

一般來說,PostgreSQL工具的位置會擺在以下位置:

C:\Program Files\PostgreSQL\[PostgreSQL版本]\bin\

以9.2版的pg_dump工具來說,它的路徑就是:

C:\Program Files\PostgreSQL\9.2\bin\pg_dump.exe

指定備份檔案的時候,也要注意你的路徑是否正確喔。

備份跟還原時放棄使用pgAdmin跟phpPgAdmin吧

image

一開始我是想要用phpPgAdmin來備份,可是卻發生以下錯誤訊息:

Export error: Failed to execute pg_dump (given path in your conf/config.inc.php : /usr/bin/pg_dump). Please, fix this path in your configuration and relog.

奇怪的是,設定檔本身並沒有錯:/usr/bin/pg_dump的確是在那個位置沒錯。這個問題也有人提出來,似乎尚未解決。

然後我想用pgAdmin來連線,但PostgreSQL要從外面連進去必須做許多設定,進行的不是很順利。後來直接放棄pgAdmin,改用指令比較快。


結語:未來考慮使用SQLite

雖然大型系統需要PostgreSQL這種重量級資料庫來支援,但是這大神實在是我們小小研究者難以搬動的對象。

sqlite370_banner

比起控管嚴格以致於難以駕馭的PostgreSQL,未來我考慮使用SQLite作為主要開發的資料庫,可能會比較適合不斷調整、修改、搬移的小型研究環境吧。

總共4 則留言, (我要發問)

  1. 如果只要輸出結構的語法:

    pg_dump kals -U kals -s -f kals-db-20131029-schema.backup.sql

    回覆刪除
  2. 如果是在Windows環境底下使用PostgreSQL 9.2

    匯出指令要把資料庫參數擺在最後一個:

    pg_dump -U kals -s -f kals-db-20141107-schema.backup.sql kals

    最後的kals才是資料庫名稱。

    回覆刪除
  3. 由於phpPgAdmin備份pgdump一直失敗,我後來發現php的錯誤訊息裡面多了這一行:

    /usr/bin/pg_dump: error while loading shared libraries: libssl.so.0.9.8: cannot open shared object file: No such file or directory

    這個問題是因為OpenSLL的版本過舊,導致無法使用pg_dump。
    然而因為我的作業系統是CentOS 5,已經無法升級OpenSLL了,所以這個問題除了更換作業系統之外沒有解決方法。

    回覆刪除
  4. 最近想要做自動匯出資料庫備份的腳本。
    一般來說,在使用pg_dump指令的時候,都必須在指令之後輸入密碼。但是這導致程式自動運作中卡住。

    PostgreSQL非常好心地讓我們可以使用PGPASSWORD這個環境變數來輸入密碼。
    以Windows的bat批次執行檔為例子,當我們要輸出資料庫時,可以在bat裡面寫下以下指令(假設資料庫密碼為database_password):

    SET PGPASSWORD=database_password
    pg_dump -U kals -s -f kals-db-20141107-schema.backup.sql kals

    這樣就可以略過輸入密碼的詢問了!

    回覆刪除

留言工具: