使用 PowerShell 備份與還原 PostgreSQL：pg_dump、psql、pg_restore 命令完整教學

| SQL | 5 Reads

Copyright Notice: This article is an original work licensed under the CC 4.0 BY-NC-ND license. If you wish to repost this article, please include the original source link and this copyright notice.
Source link: https://v2know.com/article/1256

以下是不用 GUI、只利用 PowerShell 搭配 PostgreSQL 官方工具 (pg_dump、psql、pg_restore) 進行備份與還原的完整命令範例與說明。請先確認 PostgreSQL 的 bin 目錄已加入系統 PATH，並以實際資料庫連線資訊（主機、埠號、帳號、資料庫名稱等）替換示範值。

一、Plain SQL 格式（純文字 SQL）

1. 備份（Plain SQL）

# 將資料庫 "mydb" 備份為純文字 SQL 檔案
pg_dump `
  --host=localhost `
  --port=5432 `
  --username=myuser `
  --dbname=mydb `
  --file="C:\backup\mydb.sql"

pg_dump 預設輸出即為 Plain SQL，不需額外加 -F 參數。
備份檔為普通 SQL 腳本，可用文字編輯器檢視或修改。

2. 還原（Plain SQL）

# 將純文字 SQL 檔案還原到資料庫 "mydb"
psql `
  --host=localhost `
  --port=5432 `
  --username=myuser `
  --dbname=mydb `
  --file="C:\backup\mydb.sql"

可選：在還原前先清除既有物件
如果想在還原前清除相同名稱的資料表、函式等，可在指令中加入 DROP 語句或先執行 dropdb / createdb：

# 先刪除，再重新建立資料庫
dropdb  --host=localhost --port=5432 --username=myuser mydb
createdb --host=localhost --port=5432 --username=myuser mydb

# 然後再執行 psql 還原
psql --host=localhost --port=5432 --username=myuser --dbname=mydb --file="C:\backup\mydb.sql"

二、Custom 格式（pg_dump/pg_restore 專用二進位）

1. 備份（Custom Format）

# 使用自訂格式 (-F c) 備份
pg_dump `
  --host=localhost `
  --port=5432 `
  --username=myuser `
  --dbname=mydb `
  --format=custom `          # 或 -F c
  --file="C:\backup\mydb.backup"

產出的 .backup 檔為壓縮的二進位格式，無法直接以文字瀏覽。

2. 還原（Custom Format）

# 使用 pg_restore 從自訂格式檔案還原到資料庫 "mydb"
pg_restore `
  --host=localhost `
  --port=5432 `
  --username=myuser `
  --dbname=mydb `
  --verbose `
  "C:\backup\mydb.backup"

使用例：

$env:PGPASSWORD = "tstt"

pg_restore `
  --host=localhost `
  --port=5432 `
  --username=tstt `
  --dbname=tsttpg `
  --verbose `
"tstt_exp_20250804151932.dmp"

pg_restore 會解析自訂格式檔案，再透過 psql 將資料與結構建立回資料庫。
可搭配 --clean 參數於還原前清除現有物件：

pg_restore --clean --host=... --dbname=... C:\backup\mydb.backup

若要還原至尚未存在的新資料庫，可先以 createdb 建立空資料庫，再執行上述指令。

三、額外建議

  • 密碼輸入：若不想交互式輸入密碼，可在 PowerShell 中先設定環境變數：

    $env:PGPASSWORD = "mypassword"

  • 遠端連線：只需將 --host 換成對方主機的 IP 或 DNS，並確保防火牆與使用者權限允許。

  • 編碼設定：若遇到編碼問題，可加入 --encoding=UTF8 或調整 PGCLIENTENCODING 環境變數。

要是不小心restore到postgres的基本數據庫裏的話，這是還原語句：

psql -U postgres -h localhost -p 5432 -d postgres -c "DROP SCHEMA public CASCADE;"
psql -U postgres -h localhost -p 5432 -d postgres -c "CREATE SCHEMA public AUTHORIZATION postgres;"
psql -U postgres -h localhost -p 5432 -d postgres -c "GRANT ALL ON SCHEMA public TO postgres;"
psql -U postgres -h localhost -p 5432 -d postgres -c "GRANT ALL ON SCHEMA public TO PUBLIC;"

以上即是不使用 GUI、直接透過 PowerShell 執行的 Plain SQL 與 Custom Format 之備份/還原指令範本。

依需求替換參數即可完成 PostgreSQL 的備份與復原。

祝您操作順利！ 

This article was last edited at