ChiaHu's Blog

如果 PostgreSQL 的連線設定需要使用密碼，那麼在利用 crontab 排程自動執行備份指令時，也會遇到需要輸入密碼而無法自動執行的情況。

如果你需要多台資料庫並行運作，共用負擔資料的操作動作，而且資料內容異動頻繁。那麼你需要採用 WAL 的方式。另一種採用 WAL 的場合在大型資料庫系統。當你的資料庫空間成長到數百MB或GB的單位時，你也需要考慮改用 WAL 。因為 WAL 採用 log 同步機制，每次備份時只需要傳輸上次備份時間到目前時間的異動資訊，而不會傳輸整個資料庫的內容。關於 WAL 的操作，不在本文的說明中。

PostgreSQL 在 8 版之後可以使用「.pgpass」來為使用者設定自動認證。

以下介紹詳細步驟

1. 假如我們要連線的資料庫位置為「localhost」(表示本機端)、連接埠為「5432」(PostgreSQL預設連接埠)、資料庫名稱為「database」、資料庫帳號為「username」、密碼為「password」。注意，username 不一定要跟你執行 crontab 的帳號相同。

2. 切換到執行 crontab 的實體帳號，假如是「postgres」，則利用su指令切換：

 [postgres@pgdb:/root/]# su postgres

3. 在該帳號的家目錄新增檔案「.pgpass」

 [postgres@pgdb:/home/postgres/]# vi ~/.pgpass

4. 利用vi編輯器輸入以下資料：(格式為: hostname:port:database:username:password )

localhost:5432:database:username:password

完成之後儲存離開。

5. 修改 .pgpass 的權限為 600

[postgres@pgdb:/home/postgres/]# chmod 600 ~/.pgpass

6. 測試看看，利用 pg_dump 匯出資料庫到「/backups/dbbak.sql」

[postgres@pgdb:/]# pg_dump -U username database > /backups/dbbak.sql

7. 如果「/backups/dbbak.sql」有檔案，並且內文為正確的 SQL 檔，則表示認證成功。反之，如果系統要求你輸入密碼，那可能是哪個步驟做錯了，請回頭一一確認。

8.當確認可以自動通過認證，以指令執行 PostgreSQL 匯出的之後，就可以把這些指令撰寫於 backupdb.sh，將它放置在 /home/postgres 底下，backupdb.sh 參考內容如下:

#!/bin/sh

day=`date +%Y-%m-%d`
pg_dump -U username database > /backups/dbbak_$day.sql

說明：也就是將 database 資料庫備份到 /backups/ 底下，檔名則依 dbbak_年月日.sql 格式命名

9. 加入 crontab 排程內，每天凌晨3:30做備份。

30 3 * * * postgres /home/postgres/backupdb.sh