A common DBA task is to ensure that a development-type environment is refreshed. In a typical on-premises case a “dev refresh” involves quite a bit of scripting in various programming languages. Whilst that’s a perfectly fine approach, it can be done a lot simpler when you consider the use of the cloud. My example uses Oracle’s Database Cloud Service (DBCS).
I prefix all my cloud posts with a similar warning, and this is no exception. Using cloud services costs money, so please make sure you are authorised to make use of these services. You also need to ensure you are licensed appropriately
The Scenario
I am recreating a typical scenario: a database backup acts as the source for the “DEV” environment. To keep this post simple-ish, let’s assume I can use the backup as it is. The database backup is located in Oracle Cloud Infrastructure (OCI) Object Storage.
Implementation
Writing a piece of Terraform code with the intention of storing it in version control requires the use of variables, at least in my opinion. Otherwise, any change to the input parameters will result in git marking the file’s status as untracked. And you certrainly don’t want to store passwords in code, ever.
You’ll see variables used throughout in my example code.
As I tend to forget how I did things I now pushed my code to my Github repository.
Getting backup details
Backup details for my source database are provided by a database backups data source. My requirement is quite simple: just take the latest backup and use it for the restore operation.
# # get the database backups for src_db_ocid # data "oci_database_backups" "src_bkp" { database_id = var.src_db_ocid }
The database backup to grab is element 0 in the resulting list of backups provided by the data source.
Thinking about passwords
Passwords are a tricky affair in OCI. It would be great if we could lift them from (OCI) Vault, but this wasn’t possible at the time of writing. A Github issue has been raised but didn’t seem to gain much momentum. There are workarounds though, please refer to this excellent post by Yevgeniy Brikman on the topic. I’ll leave it as an exercise to the reader to work out the best strategy.
Since Terraform v0.14 it is possible to declare a variable to be “sensitive”. That sounds great:
variable "new_admin_pwd" { type = string sensitive = true } variable "backup_tde_password" { type = string sensitive = true }
Except they aren’t quite there yet: all sensitive information still appears in the state file in plain text :(
Creating the DB System
The final step is to create the database system. In my case, I only need a single resource:
resource "oci_database_db_system" "dev_system" { # the AD of the new environment has to match the AD where # the backup is stored (a property exported by the data source) availability_domain = data.oci_database_backups.src_bkp.backups.0.availability_domain # instruction to create the database from a backup source = "DB_BACKUP" # Some of these properties are hard-coded to suit my use case. # Your requirement is almost certainly different. Make sure you # change paramaters as required compartment_id = var.compartment_ocid database_edition = "ENTERPRISE_EDITION" data_storage_size_in_gb = 256 hostname = "dev" shape = "VM.Standard2.1" node_count = 1 ssh_public_keys = [var.ssh_public_key] subnet_id = var.subnet_id nsg_ids = [var.nsg_id] license_model = "LICENSE_INCLUDED" display_name = "development DB system" db_home { database { # the admin password for the _new_ database admin_password = var.new_admin_pwd # this is from the source backup! backup_tde_password = var.backup_tde_password backup_id = data.oci_database_backups.src_bkp.backups.0.id db_name = "DEV" } } db_system_options { storage_management = "ASM" } }
This is all it takes. The majority of input parameters are provided as variables to make the script a little more portable between environments and easier to check in with version control.
A short terraform apply
later a new database system is created. Happy Automating!