Scheduled PostgreSQL Backups 9.3

An example of how to create simple scheduled PostgreSQL 9.3 backups on Windows

Tested on PostgreSQL 9.3, Windows server 2012 and 2008.

Icon

• In order to backup database automatically, a password need to be stored in pgpass.conf. Please consider a different method of backing up database if this is not suitable for your company’s security policy.
• If your Content Manager version is old such as 6, 10.2, 10.5, please check the number of network errors.
• After upgrading PostgreSQL, pg_dump location would change. You need to edit a batch file. C:\Program Files\PostgreSQL\9.3\bin

1. Open pgAdmin III

1. When you connect to server, tick store password and type a database password>OK>Close pgAdmin

1. The Password will be stored in %APPDATA%\PostgreSQL\pgpass.conf. Default: C:\Users\XXXXX\AppData\Roaming\postgresql
   • Open pgpass.conf to confirm the password

1. Create a batch and backup holder. Example c:batch d:backup

1. Confirm the location where pg_dump.exe is stored. Default: C:\Program Files\PostgreSQL\9.3\bin

1. Confirm Username and Schema. System>Database configuration>

1. Open Command prompt. Type CD C:\Program Files\PostgreSQL\9.3\bin Press Enter on keyboard. Type pg_dump --format=custom --compress=5 -U postgres -w cm > D:\backup\db.backup Press Enter on keyboard.

1. Creating a batch file (This is a basic example, please adjust settings.)
   • The backup file name will be like YYYY_MM_DD.backup
   • Generating log files.
   • Deleting files older than 7 days (Extend to 14 - 30 days if you prefer)
   1. Open a new .txt file

1. Open Task Scheduler

• Check the size of backups (Generally less than 50 MB).
• Check whether old file has been deleted or not.
• Restore the database backup in a test environment.
• Check whether backup files remain after restarting server or not.
• Occationally save it to another storage device.
• After upgrading PostgreSQL, check whether backup files have been created properly.