(本文章原始記錄於 2010 年 5 月 24 日)

******* 還原資料庫 *******

  1. 由 SQL 指令所組成的純文字檔案還原

    以 SQL 指令所組成的純文字檔還原,就如同直接在 Client 端程式連續下 SQL 指令來還原。以下兩種方式可以讀入 SQL 指令檔至 psql:
    (psql 指令語法請參考 psql 部份的說明)

    1. 使用導入檔案「<」的方式:

      使用方法如下:
      C:\> psql DBNAME USER < FILENAME
      或者用參數指定帳號及資料庫
      C:\> psql -U USER -d DBNAME < FILENAME

      範例:
      C:\> psql tester < C:/postgresql8.4.31/backups/tester.sql 以檔案系統的使用者同名之帳號登入 tester 資料庫, 並執行 C:/postgresql8.4.31/backups/tester.sql 指令檔還原。 C:\> psql norm admin < C:/postgresql8.4.31/backups/norm.sql 以 admin 帳號登入 norm 資料庫並執行 C:/postgresql8.4.31/backups/norm.sql 指令檔還原。

    2. 使用 -f 選項指定 SQL 指令檔:

      使用方法如下:
      C:\> psql -f FILENAME DBNAME USER
      或者用參數指定帳號及資料庫
      C:\> psql -U USER -d DBNAME -f FILENAME

      範例:
      C:\> psql -f C:/postgresql8.4.31/tester.sql tester
      以檔案系統的使用者同名之帳號登入 tester 資料庫,
      並執行 C:/postgresql8.4.31/backups/tester.sql 指令檔還原。

      C:\> psql -d norm -U admin -f C:/postgresql8.4.31/backups/norm.sql
      以 admin 帳號登入 norm 資料庫並執行 C:/postgresql8.4.31/backups/norm.sql 指令檔還原。

    以上兩種方法的差異只在於一個是用「<」導入檔案,一個是用 -f 選項指定檔案,
    其他的選項可以自由使用,例如都可以使用 -U 選項指定使用者以及用 -d 選項指定資料庫。

    如果是還原全部資料庫(由 pg_dumpall 備份的 SQL 指令檔還原),則登入哪一個資料庫都可以,最保險的方式是登入 template1 資料庫,而且通常需要以 superuser 的帳號登入來還原;如果是還原單一資料庫(由 pg_dump 備份的 SQL 指令檔還原),建議進行兩項額外的動作:

    1. 還原前先建立資料庫:

      由於 pg_dump 建立的 SQL 指令檔不會先 CREATE DATABASE,只會 CREATE TABLE,所以若是還原至一個全新的資料庫,則在還原前必須先建立一個資料庫才行。
      可以下類似 createdb -T template0 DBNAME 的指令,依 template0 建立一個新的資料庫。若是還原至已存在的資料庫(原本的資料庫裡可能已經有其他人所建立的資料表),則不需要先建立一個新的資料庫也可以正常還原成功。

    2. 檢查分析還原的資料:

      vacuum 為 garbage collection 的動作。當 DELETE 或 UPDATE 資料之後,
      在執行 vacuum 之前,資料表內的資料都還不會變動,因此對於會經常更新的資料表,必須週期性地執行 vacuum 以取得最新最正確的資料;vacuum 可以配合 analyze 指令。analyze 指令用來統計資料表的內容並作出最佳化的規劃,使查詢資料時可以更快速更有效率。還原資料庫後,可以透過 vacuumdb 指令來整理內容。

      vacuumdb 的語法如下:
      C:\> vacuumdb [OPTION] … [DBNAME]

      其中常用的 option 有以下幾個:

      -v verbose 冗長模式。可以觀看詳細的運作輸出訊息。
      -f 進行完整的 vacuum 動作。所花費的時間會較久。
      -z vacuum 之後再加上 analyze 來分析資料表內容以最佳化查詢的動作。
      [-d DBNAME|-a] 指定要作 vacuum 的資料庫。若要指定複數的話則重複加上 -d 選項,例如 -d db1 -d db2。若要對所有的資料庫作 vacuum 的動作,則使用 -a 選項。-d 和 -a 擇一使用。
      -t TBLNAME 指定要作 vacuum 的資料表。若要指定複數的話則重複加上 -t 選項,例如 -t table1 -t table2。
      === 以下為連線選項,表示要指定到哪一個 PostgreSQL Cluster 上 ===
      -h HOST 指定主機名稱
      -p PORT 指定主機 port
      -U USER 指定由哪位使用者整理資料庫(必須為資料庫擁有者)
      若整理全部資料庫,則必須為 superuser 帳號
      -W 強制使用者輸入密碼作確認,若 initdb 時指定 -A password 的話,則不管是否有此選項都會要求使用者輸入密碼

      範例:
      C:\> vacuumdb -a -f -z
      以檔案系統的使用者同名之帳號對 Cluster 中的所有資料庫作完整的 vacuum 動作,
      並加以 analyze 以最佳化查詢。

      C:\> vacuumdb -U admin -z -d tester -d norm
      以 admin 的帳號對 Cluster 中的 tester 及 norm 資料庫作 vacuum 動作,
      並加以 analyze 以最佳化查詢。

      C:\> vacuumdb -U tester -t comm -t room tester
      以 tester 帳號登入 tester 資料庫,對 comm 及 room 資料表作 vacuum 動作。

      若使用 -a 選項對 Cluster 中的所有資料庫作 vacuum 動作的話,
      則在需要輸入密碼登入時,和 pg_dumpall 一樣會重複詢問密碼,
      因此若有 pgpass.conf 檔則可以自動執行而不需要重複手動輸入密碼。

      關於 vacuumdb 的用法可以參考 C:\> vacuumdb --help

    PostgreSQL Client 端程式 psql 有對應的 VACUUM 以及 ANALYZE 的 SQL 指令可以使用,使用方法可以用 \h VACUUM\h ANALYZE 查詢。
    關於 VACUUM 及 ANALYZE 可以參考
    http://twpug.net/docs/postgresql-doc-8.0-zh_TW/sql-vacuum.html
    以及
    http://twpug.net/docs/postgresql-doc-8.0-zh_TW/sql-analyze.html

  2. 由 pg_restore 指令還原

    使用 pg_dump 備份單一資料庫時可以加上 -F 選項指定輸出的檔案格式,
    共有 custom、tar 及 Plain Text 三種格式。除了純文字格式的 Plain Text 外,
    輸出成 custom 格式及 tar 格式的備份檔都必須使用 pg_restore 指令來還原。
    使用 pg_dumpall 備份的 Cluster 所有資料庫資料由於一定為 SQL 純文字檔案,
    因此一定要透過導入 SQL 指令檔的方式還原,無法使用 pg_restore 還原。

    pg_restore 的語法如下:
    C:\> pg_restore [OPTION] … [FILENAME]

    其中常用的 option 有以下幾個:

    -v verbose 冗長模式。可以觀看詳細的運作輸出訊息
    -d DBNAME 指定要連線來還原資料的資料庫名稱
    -t TBLNAME 指定要還原的資料表。若要指定複數的話則重複加上 -t 選項,
    例如 -t table1 -t table2
    -c 設定在建立資料前先刪除原有的資料,以免名稱重複而產生錯誤,
    例如先 DROP TABLE <table> 再 CREATE TABLE <table>
    -O 設定不還原各物件的擁有者,如同資料易主
    -F [c|t] 指定還原檔案的格式為 custom 或 tar。就算不指定也會自動判斷
    -f FILENAME 指定要還原的檔案(原 pg_dump 時所輸出的檔案)
    === 以下為連線選項,表示要指定到哪一個 PostgreSQL Cluster 上 ===
    -h HOST 指定主機名稱
    -p PORT 指定主機 port
    -U USER 指定由哪位使用者還原資料庫(必須為資料庫擁有者或 superuser)
    -W 強制使用者輸入密碼作確認,若 initdb 時指定 -A password 的話,則不管是否有此選項都會要求使用者輸入密碼

    範例:
    C:\> pg_restore -d tester C:/postgresql8.4.31/backups/tester.tar
    以檔案系統的使用者同名之帳號登入 tester 資料庫,
    並透過 C:/postgresql8.4.31/backups/tester.tar 檔還原資料庫資料

    C:\> pg_restore -v -c -O -U norm -d norm C:/postgresql8.4.31/backups/norm.bak
    以 norm 帳號登入 norm 資料庫,並依據 C:/postgresql8.4.31/backups/norm.bak 檔案還原,設定在建立資料前先刪除資料,而且不還原資料的擁有者記錄,並觀看還原時的詳細資訊。

    C:\> pg_restore -F c -f C:/postgresql8.4.31/backups/tester.bak -d tester
    -t comm -t room -U tester
    以 tester 帳號登入 tester 資料庫,並指定還原檔為 custom 格式,然後依據 C:/postgresql8.4.31/backups/tester.bak 備份檔,只還原 comm 及 room 資料表。

    關於 pg_restore 的用法可以參考 C:\> pg_restore --help
    更詳細說明可以參考
    http://twpug.net/docs/postgresql-doc-8.0-zh_TW/app-pgrestore.html

  3. 檔案系統整個還原

    將備份的包裝檔案解開後放置於想放置的地方,然後啟動 Cluster 時指定該目錄即可。
    例如將備份檔解開放置於 C:\postgres\data\
    然後用 pg_ctl -w -D C:/postgres/data start 指令即可啟動該 Cluster。