Please document initial bootstrap of prodder

[esambo]

In order to use multiple DB schemas, synchronize lookup tables from production across other environments and archive old DB migrations
As a Ruby developer using ActiveRecord Migration
I want to start using prodder before my first DB migration so that I don't have to move all the DB objects out of the public DB schema later on

My current steps so far are (for a rails app named foo):

1. Create new rails app
2. Change schema format from Ruby to SQL
   • Edit config/application.rb by adding:
     • config.active_record.schema_format = :sql
   • Run bundle exec rake db:drop db:create db:migrate
   • git rm db/schema.rb db/seeds.rb # in case you already had it
   • git add db/structure.sql
3. Add initial DB migration
   • This prevents an error with prodder not finding any migration
   • This also prevents an error with db/structure.sql doing an empty INSERT INTO
   • An empty migration with say 'First migration' works great for this: bundle exec rails generate migration init
4. Add gem: prodder
   • Add to Gemfile: gem 'prodder', require: 'prodder/railtie'
   • bundle install
   • Use rake tasks from prodder
     • Edit bin/setup by replacing
       • From: db:setup
       • To: `db:reset db:migrate'
   • Specify DB user
     • Create user in bin/setup:

       system 'createuser --superuser  local_dev_superuser'
       

     • Edit config_database.yml and add that user for the development and test environment:

       username: local_dev_superuser
       

   • Verify it works by running: bin/setup
5. Create & use DB schema unique to the application
   • Run bundle exec rake db:drop
   • Edit db/structure.sql
     • Replace (prefixing foo to it):
       • From: SET search_path = public
       • To: SET search_path = foo, public
     • Add the following before the first SET search_path (for application named foo):
       CREATE SCHEMA IF NOT EXISTS foo;
   • Edit config/database.yml by adding following to the default (skip this and rather do the next step):

     schema_search_path: foo,public
     

   • Add the following to a new file db/settings.sql (better than the previous step):

     ALTER DATABASE :DBNAME SET search_path=foo, "$user", public;
     

   • Run bin/setup
6. Run prodder against local DB
   • Add a config/seeds.yml with a list of all the DB lookup tables that should be dumped:

     - schema_migrations
     

   • Create a new prodder_config repo
   • Configure it to run against the local development DB initially
   • Execute it
   • Make this a private repo before you change it for production
     • with a prodder_read_only DB user & password

Optional

This requires configuring additional rails environments to be configured in config/database.yml:

• staging_migration
• production_migration

1. DB users with finer grained permissions
   • Edit bin/setup by adding:

     system 'createuser --createrole foo__owner'
     

   • Edit config/database.yml by adding (to development and test):

     superuser:      local_dev_superuser
     migration_user: foo__owner
     username:       foo__web            # have your DBA create this in production
     username:       local_dev_superuser # remove once your DBA set up the _foo__web_ user in production and you ran prodder against production
     

   • Run bin/setup

Updated on 1/31/2017