# 使用 pg_dump 與 pg_restore 來備份 PostgreSQL 資料庫

PostgreSQL 的備份與還原可以透過 pg_dump 跟 pg_restore 這兩個程式來完成

# pg_dump - Dump 資料庫

pg_dump -h $host -U $user -F c -f $dump_file $db

這樣會把 $db dump 成 $dump_file

這邊參數 -F c 指定 dump 出來的格式是 custom 格式,這是 PostgreSQL 自定義的格式,也是壓縮過的格式,比較節省空間

# pg_restore - Restore 資料庫

pg_restore -h $host -U $user -v -e -c -C -d template1 $dump_file

-U 是要用哪個的帳號來 restore ,這邊要注意這個帳號要有 create db 的權限,簡單一點可以直接用 superuser 的帳號來用

-v 是輸出 restore 的過程,不加會快一點

-e 是有錯誤的時候就停止 restore ,這個參數可以確保 restore 中有錯可以停下來,這樣就可以針對這個錯誤修正

-c 是在 restore 前先 clean database , clean 的方式其實就是 drop db

如果你的 PosggreSQL 是全新安裝裡面沒有任何資料庫的話,會發生錯誤,像下面這樣。

解決方式可以不加這個參數,或是手動建立要恢復的資料庫

pg_restore: dropping DATABASE PROPERTIES mydb
pg_restore: dropping DATABASE mydb
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3963; 1262 16596 DATABASE mydb
pg_restore: error: could not execute query: ERROR:  database "mydb" does not exist
Command was: DROP DATABASE mydb;

-C 會 create 資料庫,搭配 -c 使用,因為 -c 只有 drop db 還要 create db 才能 restore

如果 PostgreSQL 裡面已經有資料庫,只使用 -C 會發生以下的錯誤,可以先手動 drop

pg_restore: creating DATABASE "mydb"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3963; 1262 16596 DATABASE mydb
pg_restore: error: could not execute query: ERROR:  database "mydb" already exists
Command was: CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'zh_TW.UTF-8' LC_CTYPE = 'zh_TW.UTF-8';

那基本上 -c -C 都會搭配一起使用就比較方便

-d 是指 database name 這個參數比較特殊

如果搭配 -C 使用,那 -d 就不能使用 mydb 的名稱,假設你要恢復的 db name 是 mydb 然後指令這樣下

# pg_restore -h localhost -U user -v -e -c -C -d mydb mydb.dbf
pg_restore: dropping DATABASE PROPERTIES mydb
pg_restore: dropping DATABASE mydb
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3963; 1262 16596 DATABASE mydb
pg_restore: error: could not execute query: ERROR:  cannot drop the currently open database
Command was: DROP DATABASE mydb;

這邊是因為 pg_restore 會先連線到 mydb 去執行 drop mydb 所以就會出現 cannot drop the currently open database 的錯誤

所以說搭配 -c -C 時,-d 必須使用 PostgreSQL 內的其他 db ,一般來說會使用 template1 這個 db 。

然後搭配 -c -C 就只能恢復到同樣的 db name 假設 dump 的 db name 叫做 mydb 那恢復也只能恢復到 mydb

如果要恢復到不同的 db 假設 mydb 要恢復到 youdb 的話,那要這樣下

# createdb -T template0 youdb
# pg_restore -h localhost -U onlinemad -v -e -d youdb mydb.dbf

等於先手動建立一個 youdb 之後再 restore 到 youdb

# Restore 特定的 table

如果有時候只需要恢復某一個 table 內的資料,也可以透過 -a -t 這兩個參數來恢復特定 table 的資料,這樣可以節省恢復所需要的時間

pg_restore -h $host -U $user -v -a -t $table -d $database $dump_file

-a 指定只恢復 data

-t 指定恢復的 table

# 參考資料

# 讓 Backup 與 Restore 顯示進度條

pg_dump 與 pg_restore 是沒有辦法顯示進度條,但是可以透過 pv - Pipe Viewer (opens new window) 來顯示進度條。

安裝 pv , Mac 可以用 brew install pv , Debian 可以用 apt-get install pv

# Backup

pg_dump -h $host -U $user $db | pv -s $(psql -h $host -U $user -d $db -tc "SELECT SUM(pg_table_size(oid)) FROM pg_class WHERE relkind='r'") | gzip > $db.gz

pv 的原理就是透過 pipe 將資料導入 pv 透過計算流過的資料量來計算進度,所以在備份時要將 pg_dump dump 出來的資料 pipe 到 pv ,同時要透過 -s 給 pv 一個資料量的大小這樣才有辦法計算進度,這裡單位是 bytes 。然後資料庫的大小可以用 SELECT SUM(pg_table_size(oid)) FROM pg_class WHERE relkind='r' 這條 SQL 去計算。那這邊因為 pv 需要有資料流流入流出才能計算進度,所以 pg_dump 就必須使用 plain text 格式才能被 pipe 到 pv ,當然 plain text 格式檔案大小會比較大,所以最後再加上 gizip 壓縮產生資料庫的備份壓縮檔。

# Restore

pv $db.gz | zcat | psql -h $host -U $user -q -d $db

還原的時候,因為備份是 plain text 格式,所以必須使用 psql 來還原,然後被備份時又有透過 gzip 壓縮,所以要透過 zcat 把壓縮的資料解壓縮。所以透過 pv 顯示進度條,就要先用 pv 將壓縮檔 pipe 到 zcat 再 pipe 到 psql 就可以了。

# 參考資料

Pipe Viewer views pipes - and long running restores (Example) (opens new window)

# Pipe 備份到 Google Cloud Storage 或是 AWS S3

# Backup

除了將 PostgreSQL 備份檔存在 server 裡面之外,一般來說也會再備份一份到異地,像是 Google Cloud Storage 或是 AWS S3

以 Google Cloud Storage 為例,要先安裝 gsutil (opens new window)

pg_dump -h $host -U $user $db | gzip | gsutil cp - gs://$bucket/$db.gz

gsutil 有支援接收 pipe 並 streaming 到 Google Cloud Storage 上,所以使用就相當簡單,直接把 pg_dump pipe 到 gizp 壓縮再 pipe 上傳到 Google Cloud Storage 就好了。

# Restore

另外也可以直接從 Google Cloud Storage 上 streaming pipe restore ,一樣 pipe 到 zcat 解壓縮再 pipe 到 psql 就可以完成 restore 了

gsutil cp gs://$bucket/$db.gz - | zcat | psql -h $host -U $user -q -d $db

既然都是 pipe 當然也可以搭配上面介紹的 pv 來顯示進度條

# 參考資料

amazon s3 - Streaming PostgreSQL pg_dump to S3 - Server Fault (opens new window) Streaming transfers  |  Cloud Storage  |  Google Cloud (opens new window)

# 不同版本的 dump 與 restore

我自己有發生過用不同版本 dump 會沒辦法 restore

這個案例是這樣

原本的 PostgreSQL 是 9.5,新的 PostgreSQL 是 12,然後我在 9.5 的 server dump 資料下來,但是沒辦法 restore 回去

原因是在作 dump 時在 PostgreSQL 9.5 的環境下 dump ,所以是用 pg_dump 9.5 去 dump ,但是在新的 PostgreSQL 上 pg_restore 版本是 12 ,所以就 restore 不回去

那其實可以用 pg_restore 9.5 恢復 PostgreSQL 的資料庫,不過我覺得這樣可能會有問題,所以就再用 pg_dump 12 去 dump ,再把 dump 檔用 pg_restore 12 恢復

如果你在系統內裝了不同版的 PostgreSQL 那 pg_dump 與 pg_restore 的路徑如下

# /usr/lib/postgresql/12/bin/pg_dump
# /usr/lib/postgresql/12/bin/pg_restore

# 查看 dump 是用哪個版本的 pg_dump dump 的

可以使用 -l 這個參數,搭配 head 指令,印出前面 12 行就可以看到這個 dump 檔的 metadata

# pg_restore -l mydb.dbf | head -12
;
; Archive created at 2020-05-22 00:39:29 CST
;     dbname: mydb
;     TOC Entries: 395
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 9.5.21
;     Dumped by pg_dump version: 12.3 (Debian 12.3-1.pgdg100+1)
;

# 參考資料

Last Updated: 2021-11-26 16:02:26
贊助商連結
    贊助商連結
    (adsbygoogle = window.adsbygoogle || []).push({});