Automated Backup on Windows

From PostgreSQL wiki

Revision as of 05:03, 26 November 2009 by Ringerc (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Automated Backup on Windows

Below are 2 set of instructions to automate backing up of PostgreSQL Server in Windows Environment.

  • The First method uses pg_dump.exe along with batch file to call it. This batch file will create a new file for each day it is run.
  • The Second method backups uses PgPass, pg_dumpall.exe, and a batch file to backup the entire server and over write the file every time it is run.

Using pgdump, new file for each day

  • Go to Server create a Directory called Drive:\PostgresqlBack then create a sub directory called "bin" in the Drive:\PostgresqlBack
  • Instead of compiling pg_dump.exe the pgAdmin III install has the pg_dump ready to go. The following files are required when using pg_dump.exe from pgAdmin III install, these files must be located in the bin folder along side pg_dump.exe
    comerr32.dll
    gssapi32.dll
    k5sprt32.dll
    krb_32.dll
    libeay32.dll
    libiconv2.dll
    libpq.dll
    Microsoft.VC80.CRT.manifest
    msvcm80.dll
    msvcp80.dll
    msvcr80.dll
    pg_dump.dll
    ssleay32.dll
    zlib1.dll
  • Create batch file called something, example is postgresqlBackup.bat. The file must be located in PostgresqlBack directory not the bin folder.
  • Open the File then Copy/Paste the following
    @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 BACKUP_FILE=<NameOfTheFile>_%datestr%.backup
   echo backup file name is %BACKUP_FILE%
   SET PGPASSWORD=<PassWord>
   echo on
   bin\pg_dump -i -h <HostName> -p 5432 -U <UserName> -F c -b -v -f %BACKUP_FILE% <DATABASENAME>


  • Change <NameOfTheFile> to something. One idea is use the name of the database. (make sure there are no spaces after the word BACKUP_FILE any spaces will cause this setting not to work.) Setting is the first part of the file name then followed by the date the file was created with the extension .backup
  • Change the <PassWord > setting above to the correct password for the backup users. (make sure there is no spaces after the word PGPASSWORD any spaces will cause this setting not to work. Description of pgPassword
  • Change <HostName> either to ip address or dns name of the server hosting Postgresql.
  • Change <UserName> to backup user make sure this users has access to database for backup purposes
  • Change <DATABASENAME> to the database name being backed up.
  • Save the File
  • Once you have chosen the security context the Task in going to run in, it is advised to change the directory security where the backup is run and the files are stored, as a high level user name and password are stored in plain text.

Using .pgpass and pgdumpall, same file

To achieve an automated backup in a windows environment, I did the following.

(I called mine pgpass.conf) and put it somewhere secure. I have it in a subdirectory under the script that runs the backup.
  • Lock down the .pgpass file
Using NTFS permissions, disable access to this file for everybody except the user pg is running as
(If you're running pg under the system account, then you should set it to use it's own user credentials)
  • Create a script to call pg_dumpall
Mine looks like this:
SET PGPASSFILE=C:\foo\bar\PG_BACKUP\PGPASSFILE\pgpass.conf
"C:\Program Files\PostgreSQL\8.2\bin\pg_dumpall.exe" -U scfcu_postgres > C:\foo\bar\PG_BACKUP\db.out current
Important to note is the first line, that dynamically sets the PGPASSFILE environment variable. This releases after the script is finished, so that only this process for only the time it's running has automatic login. The second line is a very basic pg_dumpall command, with a username passed (-U) and the output filename (db.out) as well as the database to backup (in this case 'current')
  • Create a scheduled task
I created a scheduled task in windows to run each night at 11:00 PM. The command is
C:\Windows\System32\cmd.exe /c "C:\foo\bar\PG_BACKUP\pg_backup.bat"
and it starts in the directory
C:\foo\bar\PG_BACKUP

Once this is all set up, pg_dumpall will run automatically each night and dumps out a file to this directory. At this point it's best to use whatever backup solution you're currently using to backup the entire system (including that directory) and hopefully get a copy off-site as well. If you ever need to restore, you can use the db.out file you have backed up. As long as you stay within the same version number during dumpall and restore, everything should be gorgeous.

It took me a little while to figure out how to automatically pass the password securely (or as secure as you can get in windows). This isn't bulletproof, but we've been running this for some time now on a db that holds all of the data mine information for a 3 branch credit union. Hopefully this will help you get it going, and cut out that pesky user interaction when backing up on windows.

Personal tools