postgreSQL - backup script

0. The password file

"The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile)

This file should contain lines of the following format:"

hostname:port:database:username:password

-  backup을 위해서 password 파일을 참고해야 하며, 나의 경우: C:\Documents and Settings\Administrator\Application Data\postgresql에 있다.

- 혹은 파일을 복사해서 사용해도 된다. (아래 script에서는 d:\pgpass.conf로 복사해서 사용함)

1. pg_dump.bat

Everyday db dump script

  @echo off
   for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
     set dow=%%i
     set month=%%j
     set day=%%k
     set year=%%l
   )
   set datestr=%month%_%day%_%year%
   echo datestr is %datestr%
   
   set FILE=pgdump_%date%.backup
   echo backup file name is %FILE%
   SET PGPASSFILE=D:\pgpass.conf
   echo on
   pg_dump -i -U username -Fc -b -v -f %FILE% dbname

2.  pg_dumpall.bat

"The above mechanism is cumbersome and inappropriate when backing up an entire database cluster. For this reason the pg_dumpall program is provided. pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as users and groups." 

SET PGPASSFILE=D:\pgpass.conf
"PostgreSQL_PATH\bin\pg_dumpall.exe" -U username > .\db.out postgres"