PostgreSQL database maintenance

I. Create a scheduled file, including the following: // Description: The vacuumdb.exe file is a scheduled cleanup program, and pg_dump.exe is a backup.

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

II. For recovery, use Psql to clear the original data;

Psql-H localhost-P 5432-u Postgres-D db_storelib-F "E: \ storelib_deleteall. SQL"

The command in storelib_deleteall. SQL is as follows:

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;

Restore with pg_restore:
Pg_restore-H localhost-P 5432-u Postgres -- Data-only-D db_storelib-V "E: \ 2012_02_14_abce.backup"