Backing up data in a Postgres DB


#1

We would like to manage a Postgres DB for Concourse using BOSH.

We’ve put https://github.com/cloudfoundry/postgres-release/tree/5de4d63d0f7ebdd478228e8bc5c9e7cb8e7c8d7a/jobs/bbr-postgres-db in place thus far.

We have considered now how this will be executed.
First, we know we’ll have a manual step that we can run to do a backup:

        #!/bin/bash
        export BBR_ARTIFACT_DIRECTORY=/var/vcap/store/postgres/backup
        MANUAL_FILENAME="postgres_man_((postgres_database)).sql"
        FILENAME="postgres_((postgres_database)).sql"
        rm -f ${BBR_ARTIFACT_DIRECTORY}/${FILENAME}
        /var/vcap/jobs/bbr-postgres-db/bin/bbr/backup
        BBR_ARTIFACT_FILE_PATH="${BBR_ARTIFACT_DIRECTORY}/${FILENAME}"
        echo "Uploading file $MANUAL_FILENAME"
        curl -v -X PUT -u ((vault-artifact-repo-username)):((vault-artifact-repo-password)) --upload-file $BBR_ARTIFACT_FILE_PATH https://artifactory.fmr.com/.../${MANUAL_FILENAME}
        echo "Cleaning up..."
        rm -f ${BBR_ARTIFACT_DIRECTORY}/${FILENAME}

We will also have a button to restore step we can run manually.

For scheduled backups, we know we can use a looping “janitor” script (https://github.com/cloudfoundry/postgres-release/blob/master/jobs/postgres/spec#L120) to complete backups, but we think it might be ‘safer’ to use an os-conf cron job. Any thoughts?