rds-backup-restore-worker

Motivation

It is desirable to copy production databases to another database both for backup and testing purposes. This script facilitates database dumps and restores between rds postgresql or mysql databases in the same AWS account.

How to Use

All that is needed to use this script is a config file stored in the AWS parameter store named <app_name>.<env_name>.db-configs with the following contents:

{
  "parameter_store_prefix": "<app_name>.<env_name>", // Required. 
  "restore_type": "truncate", // Optional. Options include "drop" and "truncate". Default is "drop"
  "database_configs": [
    {
      "app_name": "my-app",
      "service_name": "db",
      "database_type": "mysql", // Required. Options include "mysql" and "postgresql"
      "source": {
        "env_name": "prd",
        "tunnel_port": 4000, // the port used to test locally
        "password": "myPassword",
        "username": "myUsername",
        "host": "my-app-prd-db-mysql.coqerxsme9yp.us-west-2.rds.amazonaws.com",
        "port": 3306,
        "database": "myDatabase"
      },
      "target": {
        "env_name": "dev",
        "tunnel_port": 4001, // the port used to test locally
        "password": "myPassword",
        "username": "myUsername",
        "host": "my-app-dev-db-mysql.coqerxsme9yp.us-west-2.rds.amazonaws.com",
        "port": 3306,
        "database": "myDatabase"
      }
    }
  ]
}

If drop is specified as the restore_type, each table in the target database will be dropped. Note: dropping and recreating tables takes significantly longer than just truncating.

To easily create this config file you can pass a simpler version with the following contents to the generateConfigs function.

{
  "parameter_store_prefix": "<app_name>.<env_name>", // Required. 
  "restore_type": "truncate", // Optional. Options include "drop" and "truncate". Default is "drop"
  "database_configs": [
    {
      "app_name": "my-app",
      "service_name": "db",
      "database_type": "mysql", // Required. Options include "mysql" and "postgresql"
      "source": {
        "env_name": "prd",
        "tunnel_port": 4000 // the port used to test locally
      },
      "target": {
        "env_name": "dev",
        "tunnel_port": 4001 // the port used to test locally
      }
    }
  ]
}

generateConfigs will get the username, password, host, port, and database, and will create the ssm parameter for you (Note: you must be signed in to AWS for this to work).