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