Below you will find instructions for using LabKey’s sample database backup script to backup your PostgreSQL databases on Windows. This is a very simple script which backups up the databases to the specified directory. Please feel free to use it as a starting point for backing up the database for your LabKey Server.

What will this script do

This script can be used to perform a nightly backup (using pg_dump) of databases on a PostgreSQL server instance. The script will

  1. Perform a full backup of the postgres and labkey databases using pg_dump
  2. Dump archives will be placed in the %BACKUP_DIR% directory
  3. Dump archive file names will be in the format postgresql-backup-DB_YYYYMMDD.bak where
    • DB is the database name
    • YYYY is the year
    • MM is the month
    • DD is the day of the month
  4. Write status and error messages to STDOUT
  5. Write status and error messages to the logfile (%LOGFILE%)

Install the backup script

Install the sample backup script in a directory, such as c:\labkey\bin, on the server running your PostgreSQL database instance. In many cases, this will be the same server running your LabKey Server.

Customize the backup script for your server

You will need to customize the script for your server and environment.

  1. Change the name of the script to something more meaningful to you, such as labkey-database-backup.bat
  2. Open the script and edit the variables as needed. The important variables are
    • BACKUP_DIR: This is the directory where the backups will be stored
    • POSTGRES_HOME: The directory where your PostgreSQL database is installed
    • POSTGRES_USER: The user account to be used for the backup.
    • PGPASSFILE: Location of the pgpass file which will be used for authenticating to the PostgreSQL server

Create the pgpass file

To authenticate to the PostgreSQL database server, the script will use a pgpass file. This allows us to not have the database password in clear-text in the backup script.

  1. Create a pgpass file and place it in a secure location.
    • For example, you could place it in your HOME directory at C:\Users\ACCOUNTNAME\postgresql\pgpass.conf
    • where ACCOUNTNAME is your username.

  2. Add the login credentials to the pgpass file

    • The format of the pgpass is

        hostname:port:database:username:password

    • For example, if you plan on performing the backup using the postgres database user, the file may contain 

        localhost:5432:postgres:postgres:THEPASSWORD
  localhost:5432:labkey:postgres:THEPASSWORD

  3. Secure the pgpass file

    • Using NTFS permissions, disable access to this file for everybody except for the ACCOUNTNAME user
  4. Edit the backup script and change the PGPASSFILE variable to be path to the file created in step 1.

Running the backup script.

To execute the backup script, you will need to open a command prompt and run 

C:\labkey\bin\labkey-database-backup.bat

NOTE: This example assumes you have installed the backup script at C:\labkey\bin\labkey-database-backup.bat

The script will output status and error messages to the screen. In addition, it will write these messages, plus additional debug messages to a log file located at %BACKUP_DIR%\labkey-database-backup.log

Create a scheduled task [OPTIONAL]:

If you would like to perform a backup on a nightly basis, you can create scheduled task in windows.

The command to use is 

C:\Windows\System32\cmd.exe /c "C:\labkey\bin\labkey-database-backup.bat"

The start directory to use will be the directory where the backup script is located (for example C:\labkey\bin)

NOTE: This example assumes you have installed the backup script at C:\labkey\bin\labkey-database-backup.bat