# 使用 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

# 不同版本的 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)
;

# 參考資料

贊助商連結
(adsbygoogle = window.adsbygoogle || []).push({});