Start rails command line
rails c
From rails c
Rails.configuration.database_configuration[Rails.env]
As a method
namespace :db_utils do
desc "db_utils"
# POSTGRES - options - notes
# -C Begin the output with a command to create the database itself and reconnect to the created database.
# If --clean is also specified, the script drops and recreates the target database before reconnecting to it.
# -h hostname
# -U username
# file_provided? is not in use but may be helpful moving forward
def file_provided?(file)
unless file
puts "Please specify an export file name."
return false
end
return true
end
def db_provided?(db)
unless db
puts "Please specify a db to export."
return false
end
return true
end
def db_exists?(db)
# system and backticks issue sh commands not bash commands
# add "bash", "-c" before cmd to use bash
return system "bash", "-c", "psql -lqt | cut -f 1 -d \\| | grep -w #{db} | wc -l"
puts "db_exists? #{exists}"
end
def create_time_stamp()
return Time.now.strftime("%Y%m%d%H%M%S")
end
def set_file(file)
if (file.nil?)
file = create_time_stamp()
end
if ("#{file}".include? ".sql")
return file
else
return "#{file}.sql"
end
end
def default_file()
app = Rails.application.class.parent_name.underscore
return "#{Rails.root}/db/#{app}.sql"
end
# returns -h host
def set_host(host)
if (host.nil?)
host = "localhost"
end
return "-h #{host}"
end
# confirms db provided and db exists
# returns -d dbname
def set_db(db)
if (db_provided?(db) && db_exists?(db))
return "-d #{db}"
end
end
# returns -U with env username or current user from whoami
def set_user(user)
if (user.nil?)
# Strip trailing whitespace
user = `whoami`.strip
end
return "-U #{user}"
end
def create_db(db)
system "createdb '#{db}'"
end
def backup_to_file(host, user, db, file)
if db_provided?(db) && db_exists?(db)
host = set_host(host)
user = set_user(user)
db = set_db(db)
file = set_file(file)
# --clean Output commands to clean (drop) database objects prior to (the commands for) creating them.
# --verbose output detailed object comments
# --no-owner Do not output commands to set ownership of objects to match the original database.
# --no-acl Prevent dumping of access privileges (grant/revoke commands).
# -Fc The most flexible output file format is the "custom" format (-Fc)
# --format=t Output a tar-format archive suitable for input into pg_restore!
cmd = "pg_dump #{host} #{user} --clean --verbose --no-owner --no-acl -Fc --format=t #{db} > #{file}"
puts cmd
system cmd
end
end
def restore_from_file(host, user, db, file)
if db_provided?(db)
host = set_host(host)
user = set_user(user)
db = set_db(db)
file = set_file(file)
# --verbose output detailed object comments
# --no-owner Do not output commands to set ownership of objects to match the original database.
# --no-acl Prevent dumping of access privileges (grant/revoke commands).
cmd = "pg_restore --verbose #{host} #{user} --no-owner --no-acl #{db} < #{file}"
puts cmd
system cmd
end
end
def killDB(db)
# old attempt
#cmd = "psql #{user} postgres; REVOKE CONNECT ON DATABASE #{db} FROM public; ALTER DATABASE #{db} CONNECTION LIMIT 0; SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname='#{db}'; DROP DATABASE #{db};"
# Fix This?
# boot connected db users to drop db
# this is the only way to remove the lock on the db
# I've found that works
killDB = "ps xa | grep postgres: | grep #{db} | grep -v grep | awk '{print $1}' | sudo xargs kill"
system killDB
end
# pg uses current user as default
# to do - add host
def drop_db(user, db)
user = set_user(user)
if (!db.nil? && db_exists?(db))
killDB(db)
cmd = "dropdb #{db}"
puts cmd
end
end
def backup_env(originStr)
origin = get_environment_config(originStr)
puts "backup_env"
backup_to_file(origin["host"], origin["username"], origin["database"], nil)
end
def get_environment_config(env = Rails.env)
return Rails.configuration.database_configuration[env]
end
def migrate_db_env(originStr, destStr)
# environment vars
origin = get_environment_config(originStr)
dest = get_environment_config(destStr)
# database.yml doesn't always have username for development
# current user is the likely owner of the pg db
dest_username = (dest["username"].nil?) ? `whoami`.strip : '#{dest["username"]}'
killDB(dest["database"])
drop_db(dest_username, dest["database"])
# FIX THIS - NOT WORKING / Too many vars
migrateDb(origin["host"], origin["username"], origin["database"], dest["host"], dest_username, dest["database"])
end
# FIX THIS - Too many vars
# complex manual setup
# set up to use set_user etc..
def migrateDb(originHost, originUser, originDB, destHost, destUser, destDB)
system "migrateDb"
# should be split into syntax builders and use |
cmd = "pg_dump -h #{originHost} -U #{originUser} --clean --verbose --no-owner --no-acl -Fc --format=t -d #{originDB} | pg_restore -h #{destHost} -U #{destUser} -d #{destDB} --clean --create --verbose"
puts cmd
system cmd
end
# TASKS
task :backup, [:db, :file] => :environment do |task, args|
puts "backup db #{args.host} #{args.user} #{args.db}"
backup_to_file(nil, nil, args.db, args.file)
end
task :drop, [:db] => :environment do |task, args|
puts "drop db"
drop_db(nil, args.db)
end
task :restore, [:db, :file] => :environment do |task, args|
puts "restore db #{args.db} #{args.file}"
restore_from_file(nil, nil, args.db, args.file)
end
task :backup_drop_restore, [:fromdb, :todb, :file] => :environment do |task, args|
puts "backup_drop_restore db"
backup_to_file(args.fromdb, args.file)
drop_db(nil, args.todb)
restore_from_file(nil, nil, args.todb, args.file)
end
task :migrate_db_env, [:fromdb, :todb] => :environment do |task, args|
puts "migrate_db_env db"
migrate_db_env(args.fromdb, args.todb)
end
task :backup_env, [:db] => :environment do |task, args|
puts "backup_env db"
backup_env(args.db)
end
task :dumpDB, [:env] =>:environment do |task, args|
cmd = nil
dbEnv = get_environment_config(args.env)
backup_to_file(dbEnv["host"], dbEnv["username"], dbEnv["database"], default_file())
end
task :restoreDB, [:env] =>:environment do |task, args|
cmd = nil
dbEnv = get_environment_config(args.env)
# Fix This?
# disconnect connected db users to drop db
# this is the only way to remove the lock on the db I've found that works
killDB(dbEnv["database"])
drop_db(nil, dbEnv["database"])
Rake::Task["db:drop"].invoke
create_db(dbEnv["database"])
Rake::Task["db:create"].invoke
Rake::Task["db:migrate"].invoke
restore_from_file(dbEnv["host"], dbEnv["username"], dbEnv["database"], default_file())
end
# http://www.tutorialspoint.com/ruby-on-rails/rails-and-rake.htm
# rake db:schema:dump
# rake db:schema:load
# Order of events
# rake db:drop
# createdb ats_development
# rake ats_api:db:v2:migrate
# rake db_utils:backup_env[staging]
# rake db_utils:restore[db_name,20150512151540.sql]
# OR
# rake db_utils:migrate_db_env[staging,development]
# API Server must be down during rake
#
# Examples
# output db with structure and create commands using datetimestamp.sql as file name
# rake db_utils:backup[db_name]
# output db with structure and create commands using
# rake db_utils:backup[db_name, 'backup.sql']
# drop a db
# rake db_utils:drop[db_name]
# restore db from file
# rake db_utils:restore[db_name,20150506153637.sql]
# migrate directly from one db to another db as described in database.yml
# rake db_utils:migrate_db_env[staging,development]
end