How to Backup the Server Database

Owned by Rob Burkindine
May 13, 2019

PostgreSQL

  1. Create a new directory named "backups" in C:\Program Files\PostgreSQL\9.?
  2. Copy 7za.exe to C:\Program Files\PostgreSQL\9.?\bin
  3. Edit backupForayAdams.bat – Replace <PassWord> with the postgres db user password.

    NOTE 1: Do not put quotes around the password

    NOTE 2: Some password characters may need to be escaped (http://www.robvanderwoude.com/escapechars.php)

  4. Copy backupForayAdams.bat to C:\Program Files\PostgreSQL\9.?\bin
  5. Copy rotatefiles.bat to C:\Program Files\PostgreSQL\9.?\bin
  6. Create a scheduled task
    1. Server Manager -> Task Scheduler -> Create Task
    2. Name task.
    3. Select option "Run whether user is logged on or not." (assuming one is logged on as the local admin)
    4. Check Run with the highest privileges
    5. Configure for: Windows 7, Windows 2008 R2
    6. Go to Triggers tab.  Click new.  Choose Daily.  Start Time: 10:50 AM

      NOTE: Time can be adjusted based on agency’s hours of operation.
    7. Select Actions tab.  Click new.  Browse to .bat file (C:\Program Files\PostgreSQL\9.?\bin\backupForayAdams.bat)
    8. Go to "Start In" Field.  Enter the following path: C:\Program Files\PostgreSQL\9.?\bin\

      NOTE: Do not put quotes around the value in this field.
    9. Hit "OK" -> enter local administrator password

      NOTE: If the task is not run as the local Administrator then the user that the task is run as must have Full Control over the “backups” directory.
    10. Go to Server Manager -> Task Scheduler
    11. Right-click backup task -> Select "Run"
    12. Go to C:\Program Files\PostgreSQL\9.?\backups to verify a backup has been taken (there should now be a .zip.1 file in this folder)
    13. Verify that the backups successfully runs automatically at the scheduled time.

Make sure that the C:\Program Files\PostgreSQL\9.?\data directory is excluded from machine backups (ex: Backup Exec).



© 2023 Foray, LLC - All Rights Reserved