Using a copy of the dev database for local testing

Postgres can serialize the entire database (more or less) to a sql file using pg_dump. This file can then be loaded up into a local copy of the database for testing.

Quickstart

1. Copy dev.db

2. createdb shadowlms_development

3. psql shadowlms_development

4. run a bunch of roles

   create role shadowadmin; 
   create role shadow_lms;
   create role readonly;
   create role rdsadmin;
   

5. load the database (dev.db) with psql shadowlms_development < dev.db

Basic case

This is an example using your local development databaes (useful for backups)

1. Dump a database to sql

   This creates the sql backup file named dev.psql

   pg_dump shadowlms_development > dev.psql

2. Create a new database for testing

   createdb shadowlms_scratch

3. Load the sql dump file into the scratch database

   psql shadowlms_scratch < dev.psql
   

4. Change ther rails database.yml file to use the new database

   NOTE: don't commit this change.

   development:
       adapter: postgresql
       database: shadowlms_scratch

   You will need to reload your rails server and rails console for them to use the new database.

   You can check if rails console is using the correct database by running ActiveRecord::Base.connection.current_database

Dumping / Loading app-dev

NOTE: It probably isn't a good idea to dump app-dev frequently...

1. Start the vpn

2. Get the postgres connection string from the app-dev connection

   k8s # inside k8s
   k8s apps:config --list shadowlms_development # or whatever pod

   copy the value of the key called DATABASE_URL. It should be something like postgres://<longhash>@<long_host>:5432/shadowlms_<something>

3. Dump the database with pg_dump

   pg_dump <your-database-url> > app_dev.psql

4. Load the database as above

   I ran into issues like *no such role "shadow_lms". For each of those, create the users in the psql command prompt. It should be done something like this:

   psql <your-local-database>
   create role shadowadmin; 
   create role shadow_lms;
   create role readonly;
   create role rdsadmin;

   remember to edit your database.yml file.

5. Run migrations

Useful shell commands

I use these functions in my bash config to easily keep different databases on different branches.

# get the current database name
# usage: ,db_current 
# output: shadowlms_scratch
function ,db_current {
  cat ./config/database.yml | grep database: | head -n 1 | awk '{split($0,a," "); print a[2]}'   
}

# create a stash of a database 
# usage: ,db_stash `,db_current` my_stash
function ,db_stash {
  dbname=$1
  stashname=$2
  echo $dbname to $stashname

  pg_dump $dbname > ~/.local/$stashname.db
}

# load a stashed database from the command above
# usage: ,db_load `,db_current` my_stash
# note: this drops the prior database
function ,db_load {
  dbname=$1
  stashname=$2
  echo $dbname from $stashname
  dropdb $dbname
  createdb $dbname
  psql -U saahil.claypool $dbname < ~/.local/$stashname.db
  vim ./config/database.yml
}