PG資料庫的維護 postgresql

一。建立定時檔案，包括以下部分：//說明：vacuumdb.exe 檔為定時清理程式, pg_dump.exe為備份

set YEAR=%Date:~-14,4%
set MONTH=%Date:~-9,2%
set DATE=%Date:~-6,2%
set DATE_STR=%YEAR%_%MONTH%_%DATE%
echo "e:/%DATE_STR%_storelib.bak"

e:
cd E:\pg_84_10\bin\
vacuumdb.exe -h localhost -p 5432 -U postgres db_storelib
pg_dump.exe -h localhost -p 5432 -U postgres -F c -v -f "e:\%DATE_STR%_storelib.backup"  db_storelib

二。以下為恢複，首先用psql清空原來的資料；

psql -h localhost -p 5432 -U postgres -d db_storelib -f "e:\storelib_deleteall.sql"

storelib_deleteall.sql 中的命令如下：

delete from tbl_baseinfo;
delete from tbl_catalogs;
delete from tbl_customsinfo;
delete from tbl_deposit;
delete from tbl_inputlog;
delete from tbl_items;
delete from tbl_oprinfo;
delete from tbl_oprlog;
delete from tbl_outputlog;
delete from tbl_proditem;
delete from tbl_putlogbak;
delete from tbl_saleman;
delete from tbl_sysconfig;

再用pg_restore恢複:
pg_restore -h localhost -p 5432 -U postgres --data-only -d db_storelib -v "e:\2012_02_14_abce.backup"