Skip to content

Database Restore

Jump to bottom
Ian Liu edited this page Aug 28, 2024 · 9 revisions

Instruction

The database restore cron job is configured in the db/openshift.deploy.yml file. The schedule is at midnight on February 31st, so it actually will never run automatically. We will trigger it manually whenever needs.

The cron job will use the same image that our database uses, so it comes with the Postgres command that we need for the restore. And it will use the persistent volume storage that our database backup cronjob uses, so it includes all the backup files (Please note that: do not run the restore at the same time as the backup, the same persistent volume storage can only be used by one container at a time).

FOM is using the Openshift backup-container for the database backup, so the backup files are stored under backups directory, with daily, weekly and monthly three folders. An example can be seen below (this is an example from another old project that is using backup-container):

  • Daily backup will be stored under daily
  • If it's Sunday, then the backup will be stored under weekly
  • Backup files are zipped sql files
Screen Shot 2024-08-14 at 3 38 57 PM

Restore process

Prerequisite:

  • Require Openshift command line tool(CLI). This can be found by go to top-right (question mark) icon and download the tar file: image
  • Unpack the OC CLI tar file and copy the exactable file (oc file) to user's $PATH (usually under user's home bin folder): image
  • Sample command to copy from unpacked directory to bin directory: uadmin@CA-L58347M3:~/.local/bin$ cp /home/uadmin/sw/openshift-cli/oc .

Steps:

  1. Login to the Openshift namespace
  2. Scale to 0 all apps that use the database connection (api, admin, public)
  3. Restart the database pod as a quick way of closing any other database connections
  4. Do a manual backup of the current database (can do that in the database pod, so this temporary backup will be stored in the database volume)
  5. Find the CronJob config in Openshift, update the environment variables in the yaml file
  • BACKUP_FILE_DIR is where the backup file you want to restore is at
image Screen Shot 2024-08-22 at 10 54 13 AM
  1. Login to Openshift from command line
Screen Shot 2024-08-19 at 4 20 09 PM

  1. Create a job from the Cron Job oc create job --from=cronjob/[db_restore_cron_job_name] [job_name_can_be_any]. For example, oc create job --from=cronjob/fom-24-db-restore db-backup-restore-2024-08-20

  2. Cleanup the job when it completed successfully if need

The cron job for restoring database is running very fast. We want to keep an eye watching on it very closely, so we can find the error logs if anything wrong will happen.

Restore script logic

The restore script inside the db/openshift.deploy.yml file will do:

  • Check if there is an existing sql file in the given backup file directory (in case we already unzip the backup file and get the sql file in the past)
  • If not found the sql file, try to find the zipped sql file and unzip it
  • Try to find the sql file again
  • If not found, then print "No backup SQL file is found" and exit with error
  • If found, run the database restore script:
    • Connect to the database remotely
    • Rename the existing "fom" database to <OLD_FOM_DATABASE_NAME> that we defined in the environment variable
    • Create an empty database named "fom" (the backup file will run for database "fom", that's why we must name it "fom")
    • Run the backup file
    • If psql command failed to restore the database, exit with error

Further discussion

  • Make the restore script in to a file and mount it to the cronjob container, instead of having the script directly inside the file
  • Review the database migration script to handle the on conflict situation
Clone this wiki locally