PostgreSQL自動備份

[mangreen]

自動備份

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

$ sudo vim ~/.pgpass

修改或輸入以下資料

localhost:5432:database:username:password
#「localhost」(表示本機端)，輸入127.0.0.1或IP
#「5432」為連接埠(PostgreSQL預設連接埠)
#「database」為資料庫名稱
#「username」為資料庫帳號，(預設為postgres)
#「password」為密碼

修改 .pgpass 的權限為 600

$ sudo chmod 600 ~/.pgpass

新增備份目錄並測試看看，利用 pg_dump 能否匯出資料庫

$ mkdir dbbackup
$ pg_dump –h 127.0.0.1 –p 5432 –U postgres 資料庫 > test.dump

如果沒問密碼就備份表示設定成功。反之，如果系統要求你輸入密碼，那可能是哪個步驟做錯了，請回頭一一確認。

在該帳號的 ~/bin/ 新增檔案backupdb.sh，檔案內容如下

$ vim ~/bin/backupdb.sh

#!/bin/bash

#設定時間變數
TODAY=`date +%Y%m%d`
YESTERDAY=$(date -d "yesterday" +%Y%m%d)
#設定備份路徑，請修改到正確路徑
BKDIR="/home/username/backup"
#設定專案變數
PROJECT="test"
#備份資料庫，請修改資料庫名稱
pg_dump -h localhost -p 5432 -U $PROJECT $PROJECT | gzip > $BKDIR/$PROJECT.$TODAY.gz

#刪除舊備份檔
rm $BKDIR/$PROJECT.$YESTERDAY.gz

將backupdb.sh設為執行檔

$ chmod +x backupdb.sh

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

$ sudo crontab -e

將下面內容加入檔案內，紅色部分請修改到正確路徑，如要修改備份時間請修改 30(分) 及 3(時)

30 3 * * * /home/username/bin/backupdb.sh

備份匯出

使用 pg_dump 指令。

pg_dump -W -U postgres -h localhost your_db_name > 20150304.sql

其中 -U postgres 是資料庫使用者名稱。

匯入還原

使用 psql 指令。

sudo service nginx stop

dropdb  -W -U postgres -h localhost your_db_name 
createdb -W -U postgres -h localhost your_db_name
psql  -W -U postgres -h localhost your_db_name < 20150304.sql

sudo service nginx start

如果要砍掉重現現有的資料庫，用 dropdb 和 createdb 指令，並且需要關閉正在連線的應用，例如 nginx 網站伺服器。