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"