Copying data to preview deployment via initialDeployHook

[cdm6a]

Hello, as part of my preview deployments, I’m hoping to copy data over from a staging DB to the preview deployment DB. As I understand, pg_dump and psql are both available in the build container but I’m having a hard time figuring out how I can reference the connection strings for the DBs I’m trying to copy between.

Ideally, I could have the following in my render.yaml where the staging_db is defined in the render.yaml file:

initialDeployHook: pg_dump -d {staging_db_connection_string} --data-only | psql -d {preview_staging_db_connection_string}

Is there an easy way I can embed these connection strings in the initialDeployHook command without needing to write a script?

[John_B]

Hey Conner,

So this is something you can do. I’ll walk you through the steps.

First, you’ll need to make the DB you’re using as the source is available to preview environments. Assuming you don’t want to put that in source code you need to create an environment group with the variables you want in, in my case SOURCE_DB_URL with a Heroku Postgres URL, and then link it to your service in your render.yaml with fromGroup attribute. This does mean that this would be available to the primary instance, but to have it available to preview environments, this is what you need to do.

You then need to link the database as you would normally (and may already have) and give it a name. Here’s what my complete render.yaml looks like:

previewsEnabled: trueservices: - type: web initialDeployHook: ./copy_db_from_heroku.sh name: sample-ruby-app plan: starter env: ruby buildCommand: ./render-build.sh startCommand: ./render-start.sh envVars: - key: DB_URL fromDatabase: name: mydb property: connectionString - fromGroup: heroku-credsdatabases: - name: mydb plan: starter

So when this deploys, I get my 2 services, sample-ruby-app and mydb and then the DB connection string is set as DB_URL on the web service and SOURCE_DB_URL comes from the env var group.

copy_db_from_heroku.sh looks like this:

if ["$IS_PULL_REQUEST" = 'true']thenpg_dump -d $SOURCE_DB_URL | psql $DB_URLfi

Here, I’m conditionally testing if this is a pull request so this doesn’t run when I first deploy my primary service and to ensure it ONLY ever runs on a preview environment. I’m dumping the source db and piping it straight into DB_URL, which will be the preview environment Postgres database.

And that should be it. Let me know how it goes

Regards,

John B

[cdm6a]

Thanks John_B!

In my case, the staging DB I’m using is actually defined in the render.yaml and not running somewhere else so I’m wondering if there is a different way to reference it or I’ll still need to statically define the connection string in the env group?

[John_B]

In this case, when that Blueprint spins up a preview environment then both a web service and a database would be provisioned for the preview env and the preview backend-staging webservice POSTGRES_URL would point at the preview environment version of backend_db_staging.

For the preview environment to reference the original backend-db-staging database then use a source via the env var group method I first described is why to accomplish this - not sure why I picked on Heroku here but that could equally be any other database source, Render or external.

Regards,

John B