Deployment

Running database migrations on the deployed CloudSQL instance

One-time setup

• Install Cloud SDK
• gcloud auth login to login
• Set environment variables
  • cd bin/
  • cp .env.template .env
  • Replace INSTANCE_CONNECTION_NAME, PGUSER, and PGPASSWORD with the appropriate values for the deployed instance.

Run the migration on the deployed instance

• Ensure you do not have a postgres instance running locally. psql should fail, or try running one of these commands:

  # linux:
  sudo service postgresql stop
  # mac:
  brew services stop postgresql
  

• Ensure you are on the main branch:

  git checkout main
  git pull
  

• cd bin/
• ./run_prod_database_migration.sh

Breakglass access to the deployed CloudSQL instance

TODO: this shouldn't typically be used, make that more clear

• Using https://cloud.google.com/sql/docs/postgres/connect-admin-proxy
• Download and install Cloud SQL Auth proxy and make it executable.

  wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
  chmod +x cloud_sql_proxy
  

• Start it and use the TCP sockets.

  ./cloud_sql_proxy -instances=INSTANCE_CONNECTION_NAME=tcp:5432
  

• Change env variables to production values.

  export PGUSER=<PRODUCTION_USER>
  export PGPASSWORD=<PRODUCTION_PW>
  

• To reseed the production database, you will need to manually run psql commands to create the database.
  • This is due to the fact that psql by default dos not use the TCP socket that the proxy is connected on.

  psql -h 127.0.0.1 -c 'DROP DATABASE IF EXISTS bloom;'
  psql -h 127.0.0.1 -c 'CREATE DATABASE bloom;'
  psql -h 127.0.0.1 -d bloom -c 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";'
  

• Ensure that you're in the backend/core directory.

  cd backend/core
  

• Then run the migration scripts and seed the database.

  yarn db:migration:run
  yarn db:seed:detroit