Mid

用命令列優雅地轉移/備份你的 PostgreSQL 資料 - pg_dump Tips

by 慕凡 2018-12-14 04:00 UTC

前言

我們在操作使用 PostgreSQL 的應用程式時時常會有使用 PostgreSQL 提供的命令列工具(Command-Line Tool)搬移或備份資料的需求,這篇文章的目的是列出一些常見的選項,以及如何在一行指令中達成壓縮資料或是在不同主機間備份或轉移資料的方式。

以下會用到的兩種工具

  • pg_dump

    用來備份單一資料庫到各種格式的工具,基本指令如下:

    pg_dump $db_name -h $host -p $port -U $user ...其它 options
    

    這裡面只有 $db_name 是必備,$host是主機 host name,預設值是 localhost,$port是資料庫連線的 port,如果主機是 localhost 則不需指定,以外預設值為 5432;預設狀況會將 dump 的內容輸出到 STDOUT(標準輸出),也就是你的畫面上;$user 為 DB 帳號,預設值為你現在登入主機的帳號。

  • psql

    用來執行 SQL 命令的互動工具,可以用來還原 pg_dump 備份的 SQL 格式檔案;基本指令如下:

    psql $db_name -h $host -p $port -U $user ...其它 options
    

    進入後可以再輸入指令。

問題

  1. Q:如何產生只有資料沒有建立資料表的備份檔

    A:用 -a 選項

    pg_dump $db_name -a
    
  2. 跟上面相反,產生只有 DDL(Data Definition Language)的備份檔

    A:用 -s 選項

    pg_dump $db_name -s
    
  3. Q:備份選定的,或是排除備份某些資料表

    A:選定是 -t,排除是 -T,可以多次使用表示多張表,或是用 wild card(*號)或正規表示式匹配符合的資料表,例如:

    pg_dump -t 'wildcard_name*' 
    
    pg_dump -t '(foo|bar)gsm'
    
  4. Q:將 dump 內容輸出到檔案

    A:有兩種解法

    # 使用 -f 選項輸出到檔案
    pg_dump $db_name -f db.sql
    # 用 pipeline 導入
    pg_dump $db_name > db.sql
    
  5. Q:將備份檔還原到資料庫

    A:和前面一樣,有 -f 讀取檔案與使用管線兩種方法

    psql $db_name -f db.sql 
    cat db.sql > psql $dn_name
    
  6. Q:一步完成輸出壓縮過的備份檔案

    A:用管線將 dump 內容導向到壓縮程式後再直接產生壓縮過後的檔案,例如:

    pg_dump $db_name | bzip2 > db.sql.bz2
    

    解壓縮時一樣使用資料流的方式將解壓的內容用管線把解壓縮後的內容傳給psql

    bzcat db.sql.bz2 | psql $dn_name
    
  7. Q:產生使用 INSERT INTO 指令句的備份內容

    A:預設情況下,pg_dump 產生的備份內容中會產生專用的 COPY 指令為主的資料備份內容,這是因為用 COPY 指令回復資料的速度非常快的原故;有時我們會為了相容性因素,例如為了產生可以還原到其它異種 RDBMS 上的備份而想要取得用標準 INSERT 語句呈現的資料備份內容,這時我們可以用 --inserts 選項產生此類內容,不過如果使用這種備份內容還原的話,速度和 COPY 語句相比將會非常緩慢,請務必注意。

    pg_dump $db_name --inserts
    
  8. Q:在備份內容中,先移除資料表再開始還原

    A:使用 -c (clean) 選項,會先 DROPCREATE,如果想要在 DROP 語句前加入 IF EXISTS 的話再加上 --if-exists 選項即可。

    pg_dump $db_name -c --if-exists
    
  9. Q:在備份檔中排除掉資料表的 Owner 設定(常用選項)

    A:預設情況下在建立資料表後會有ALTER TABLE $schema.$tablename OWNER TO $username; 的語句出現,在使用備份內容還原時,如果目的地的資料庫中沒有對應的使用者將會出現警告訊息;使用 -O 選項將可以排除這個語句的產生,在此情況下預設的擁有者就是資料庫的擁有者。

    一般來說,還原目的端和來源端通常不會有一樣的資料庫使用者,所以會建議預設要加入這個選項。

    pg_dump $db_name -O
    
  10. Q:透過 SSH 一次把遠端的資料庫備份到本地端

    A:透過 SSH 指令可以取得將遠端的 STDOUT 的特性,將備份內容直接拿到本地端

    ssh $remote_host pg_dump $db_name > $local_file.sql
    

    如果覺得資料量太大,也可以用同樣的方式在遠端壓縮再傳過來,但是遠端指令的部份請用單引號包起來做區分:

    ssh $remote_host 'pg_dump $db_name | bzip2' > $local_file.sql.bz2
    
  11. Q:將本地端發起的備份資料流用 SSH 導向還原到遠端

    pg_dump $source_db_name | ssh $remote_host 'psql $remote_db_name'
    
«回文章列表