Automated Backup on Windows

Updated to reflect changes from 8.3 to 11

This method uses pg_dump.exe along with a batch file to call it. This batch file will create a file/directory for each day it is run.

Keep in mind pg_dump and pg_dumpall are version specific meaning do not use pg_dump from 9.3 to backup version 11.0. The option -i & --ignore-version are ignored

Files needed to run pg_dump & pg_dumpall

Getting the pg_dump, pg_dumpall binaries one has to extract it from a PostgreSQL Server Install, compile, or download binaries from EDB. There is no package available to get just these files.

Should download and install the Windows C/C++ runtime libraries from Microsoft for the version Postgresql being used, version 11.0 uses VS-2013

Go to backup server/location create a Directory called Drive:\PostgresqlBack then create a sub directory called "bin" in the Drive:\PostgresqlBack and place the following files in this directory.

    libeay32.dll
    libiconv-2.dll
    libintl-8.dll
    libintl-9.dll
    libpg.dll
    libwinpthread-1.dll
    msvr120.dll
    pg_dump.exe
    pg_dumpall.exe
    ssleay32.dll
    zlib1.dll

Using pgdump, creates a new file for each day

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 -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

Create Task for the MS Task Scheduler

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.

Another option is to modify the pg_hba.conf file adding the backup server as a trusted connection

New feature in pg_dump as of 9.1

The option -F d. This changes the backup from creating a single large TAR file, it will now will create a directory and contain individual files for each table.

the pg_dump command looks like this

    bin\pg_dump -h <HostName> -p 5432 -U <UserName> -F d -b -v -f %BACKUP_FILE% <DATABASENAME>

There are a few advantages to the -F d option. One restores can be made significantly faster as pg_restore has the option to run parallel connections instead of one table at a time. Two its faster to extract specific table(s) to restore when using this option vs TAR format. Three when coping/moving the files off-site and it fails in mid-stream it does not have to restart at the beginning unlike one large TAR file, for large databases this is a big plus.

Using pg_dumpall

It is necessary to use pg_dumpall to get the logins/roles, and schema information as pg_dump does not include this information

   set BACKUP_FILE=Globals\Globals_%datestr%.backup.sql
   echo backup file name is %BACKUP_FILE%
   SET PGPASSWORD=<PassWord>
   echo on
   bin\pg_dumpall -g -h <HostName> -p 5432 -U <UserName> -v -f %BACKUP_FILE% <DATABASENAME>

This creates a sql script used to restore roles and schema information. Keep in mind if not restoring to the same server settings such TableSpaces are most likely not valid and will error.

Automated Backup on Windows

Contents