Skip to content

Database Backup and Restore

Jump to bottom
MCatherine edited this page Jun 27, 2022 · 4 revisions

Database backup

Setup

Use the backup container provided by the openshift team and follow the example for postgres in their Readme. The example is a little bit out of date, but shows the basic steps. The following steps show an example to setup backup container for a postgres database in a dev namespace.

  1. Git clone this backup container repo

    git clone https://github.com/BCDevOps/backup-container.git && cd backup-container

  2. Login to the namespace

  3. Create the backup image, specify which namespace is used to stored backup image, and the name of the backup

    oc -n [namespace_name] process -f ./openshift/templates/backup/backup-build.yaml -p NAME=[backup_name] OUTPUT_IMAGE_TAG=v1 | oc -n [namespace_name] create -f -

// For example:
oc -n d736c3-dev process -f ./openshift/templates/backup/backup-build.yaml -p NAME=nrog-testbkup OUTPUT_IMAGE_TAG=v1 | oc -n d736c3-dev create -f -

  4. Update configuration in the ./config/backup.conf file to specify the database that needs backup, and the cron job schedule, postgres=[database_deploy_config_name]:[database_port]/[database_name]. For example:

    postgres=nrog-47-database:5432/nrog

// run a daily backup at 4am, verify the most recent backup at 6am
0 4 * * * default ./backup.sh -s
0 6 * * * default ./backup.sh -s -v all

  5. Check the configuration in the openshift/templates/backup/backup-deploy.yaml file, update the following two inline

    • DATABASE_USER_KEY_NAME: check the key to get the database user in the database secret
    • DATABASE_PASSWORD_KEY_NAME: check the key to get the database password in the database secret

  6. Create the config map and label it

    oc -n d736c3-dev create configmap backup-conf --from-file=./config/backup.conf
oc -n d736c3-dev label configmap backup-conf app=nrog-testbkup

  7. Deploy the backup container app, run the script with these parameters, some of the parameters have default value in the backup-deploy.yaml file, could use the default values if meets needs. More detail explaination of these parameters could check the backup-deploy.yaml file

    • NAME: backup name, same in the step 1
    • IMAGE_NAMESPACE: namespace where the image is stored in step 1
    • SOURCE_IMAGE_NAME: use the backup name
    • BACKUP_VOLUME_NAME: name for the presistant volume claim for the backup
    • BACKUP_VOLUME_SIZE and VERIFICATION_VOLUME_SIZE: storge size, for new namepsace might need to apply more resource quota through the Platform Registry
    • DATABASE_DEPLOYMENT_NAME: the deployment config name for the database
    • NAMESPACE_NAME: the namepace where the database lives
    oc -n d736c3-dev process -f ./openshift/templates/backup/backup-deploy.yaml -p NAME=nrog-testbkup -p IMAGE_NAMESPACE=d736c3-dev -p SOURCE_IMAGE_NAME=nrog-testbkup -p TAG_NAME=v1 -p BACKUP_VOLUME_NAME=nrog-testbkup-pvc -p ENVIRONMENT_FRIENDLY_NAME='NROG Dev DB Backups' -p ENVIRONMENT_NAME=d736c3-dev -p BACKUP_VOLUME_SIZE=12Gi -p VERIFICATION_VOLUME_SIZE=4Gi -p DATABASE_DEPLOYMENT_NAME=nrog-47-database -p NAMESPACE_NAME=d736c3-dev | oc -n d736c3-dev create -f -

Update

If want to update any config parameters inside the backup-deploy.yaml file after creating the config deployment, those parameters most likely are used as environment variables. Go to the namespace, administrator view -> workloads -> replicationControllers -> find the one for db backup -> environment, and then change it from there, then restart the deployment config
Or click the db backup deployment config -> click the pod -> select environment, the environment screen will show a note says that environment variables set from parent replication controller and a link to that RC. Click on that RC link and change the env from there, then restart the deployment config

Cleanup

To clean up the backup container and configuration:

oc -n d736c3-dev delete pvc/nrog-testbkup-pvc pvc/backup-verification secret/nrog-testbkup secret/ftp-secret dc/nrog-testbkup

Addtionally to remove everything for the backup, need to remove the imagestream, image build, configMap and network policy as well

Check backup file

The daily backup is stored in the backups/daily/ directory as a gzip file (for example nrog-47-database-nrog_2022-06-03_04-00-00.sql.gz), to unzip the file, could run gunzip -d nrog-47-database-nrog_2022-06-03_04-00-00.sql.gz, and will see the backup sql file. To zip the file back, run gzip nrog-47-database-nrog_2022-06-03_04-00-00.sql

Discussion

  • Could store the backup build image into the tools namespace, but when trying the pull the image, get an authentication error, so right now just store the image in the dev or prod namespace
  • See an error when run backup in a cron job, but it doesn't affect any functionality, the backup still runs successfully

Code example

image

Database restore

Follow the steps in the restore session in the backup container readme. The following steps show an example to restore a database in a dev namespace

  1. Login to the openshift namespace

  2. Scale to 0 all Apps that use the database connection. Could do this using oc command or through the namespace web page

    oc scale dc nrog-47-backup --replicas=0
oc scale dc nrog-47-frontend --replicas=0

  3. Restart the database pod as a quick way of closing any other database connections

    oc rollout latest dc/nrog-47-database

  4. Open an rsh into the backup container pod

    oc get pods
oc rsh <Backup Pod Name>

  5. In the rsh run the backup script in restore mode for the database defined in the ./config/backup.conf file, for example ./backup.sh -r nrog-47-database:5432/nrog, to restore the desired backup file. By default it will use the latest backup file, to specify a backup file to use, could use the '-f' option. To see the parameter options and explaination, could run ./backup.sh -h. During the restore process, it will ask the admin password for the database. In the case don't know what the admin password is, or didn't have one, follow the following step to creat/reset one:

    // go to the database pod terminal, login to the database with user postgres
psql -U postgres
// set a password for this admin user
ALTER USER postgres PASSWORD [new_password];

  6. Verify that the database restore worked. Check if get desired data is shown in the database

  7. Scale up any pods you scaled down in the previous steps

Clone this wiki locally