Automated Backup on Windows
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.
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.