Start rails command line<\/p>\n
rails c<\/pre>\nFrom rails c<\/p>\n
Rails.configuration.database_configuration[Rails.env]\n<\/pre>\nAs a method<\/p>\n
namespace :db_utils do\n \n desc \"db_utils\"\n\n # POSTGRES - options - notes\n # -C Begin the output with a command to create the database itself and reconnect to the created database. \n # If --clean is also specified, the script drops and recreates the target database before reconnecting to it.\n # -h hostname\n # -U username\n\n # file_provided? is not in use but may be helpful moving forward\n def file_provided?(file)\n unless file\n puts \"Please specify an export file name.\"\n return false \n end\n return true\n end\n\n def db_provided?(db)\n unless db\n puts \"Please specify a db to export.\"\n return false \n end\n return true\n end\n\n def db_exists?(db)\n # system and backticks issue sh commands not bash commands\n # add \"bash\", \"-c\" before cmd to use bash\n return system \"bash\", \"-c\", \"psql -lqt | cut -f 1 -d \\\\| | grep -w #{db} | wc -l\"\n puts \"db_exists? #{exists}\"\n end\n\n def create_time_stamp() \n return Time.now.strftime(\"%Y%m%d%H%M%S\")\n end\n\n def set_file(file)\n if (file.nil?)\n file = create_time_stamp()\n end\n if (\"#{file}\".include? \".sql\")\n return file\n else\n return \"#{file}.sql\"\n end\n end\n\n def default_file()\n app = Rails.application.class.parent_name.underscore \n return \"#{Rails.root}\/db\/#{app}.sql\"\n end\n\n # returns -h host\n def set_host(host)\n if (host.nil?)\n host = \"localhost\"\n end\n return \"-h #{host}\"\n end\n\n # confirms db provided and db exists \n # returns -d dbname\n def set_db(db)\n if (db_provided?(db) && db_exists?(db))\n return \"-d #{db}\"\n end\n end\n \n # returns -U with env username or current user from whoami\n def set_user(user)\n if (user.nil?)\n # Strip trailing whitespace\n user = `whoami`.strip\n end \n return \"-U #{user}\"\n end\n\n def create_db(db)\n system \"createdb '#{db}'\"\n end\n\n def backup_to_file(host, user, db, file)\n if db_provided?(db) && db_exists?(db)\n host = set_host(host)\n user = set_user(user)\n db = set_db(db)\n file = set_file(file)\n\n # --clean Output commands to clean (drop) database objects prior to (the commands for) creating them.\n # --verbose output detailed object comments\n # --no-owner Do not output commands to set ownership of objects to match the original database.\n # --no-acl Prevent dumping of access privileges (grant\/revoke commands).\n # -Fc The most flexible output file format is the \"custom\" format (-Fc)\n # --format=t Output a tar-format archive suitable for input into pg_restore!\n cmd = \"pg_dump #{host} #{user} --clean --verbose --no-owner --no-acl -Fc --format=t #{db} > #{file}\"\n puts cmd\n system cmd\n end\n end\n\n def restore_from_file(host, user, db, file)\n if db_provided?(db)\n host = set_host(host)\n user = set_user(user)\n db = set_db(db)\n file = set_file(file)\n\n # --verbose output detailed object comments\n # --no-owner Do not output commands to set ownership of objects to match the original database.\n # --no-acl Prevent dumping of access privileges (grant\/revoke commands).\n\n cmd = \"pg_restore --verbose #{host} #{user} --no-owner --no-acl #{db} < #{file}\"\n puts cmd\n system cmd\n end \n end\n\n def killDB(db)\n # old attempt\n #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};\"\n\n # Fix This?\n # boot connected db users to drop db\n # this is the only way to remove the lock on the db\n # I've found that works\n killDB = \"ps xa | grep postgres: | grep #{db} | grep -v grep | awk '{print $1}' | sudo xargs kill\"\n system killDB\n end\n\n # pg uses current user as default\n # to do - add host\n def drop_db(user, db)\n user = set_user(user)\n if (!db.nil? && db_exists?(db))\n killDB(db)\n cmd = \"dropdb #{db}\"\n puts cmd\n end\n end\n\n def backup_env(originStr)\n origin = get_environment_config(originStr)\n puts \"backup_env\"\n backup_to_file(origin[\"host\"], origin[\"username\"], origin[\"database\"], nil)\n end\n\n def get_environment_config(env = Rails.env)\n return Rails.configuration.database_configuration[env]\n end\n\n\n def migrate_db_env(originStr, destStr)\n\n # environment vars\n origin = get_environment_config(originStr)\n dest = get_environment_config(destStr)\n\n # database.yml doesn't always have username for development\n # current user is the likely owner of the pg db\n dest_username = (dest[\"username\"].nil?) ? `whoami`.strip : '#{dest[\"username\"]}'\n\n killDB(dest[\"database\"])\n drop_db(dest_username, dest[\"database\"])\n\n # FIX THIS - NOT WORKING \/ Too many vars\n migrateDb(origin[\"host\"], origin[\"username\"], origin[\"database\"], dest[\"host\"], dest_username, dest[\"database\"])\n\n end\n\n\n # FIX THIS - Too many vars\n # complex manual setup\n # set up to use set_user etc..\n def migrateDb(originHost, originUser, originDB, destHost, destUser, destDB)\n system \"migrateDb\"\n # should be split into syntax builders and use |\n 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\"\n puts cmd\n system cmd\n end\n\n\n # TASKS\n\n task :backup, [:db, :file] => :environment do |task, args|\n puts \"backup db #{args.host} #{args.user} #{args.db}\"\n backup_to_file(nil, nil, args.db, args.file)\n end\n\n task :drop, [:db] => :environment do |task, args|\n puts \"drop db\"\n drop_db(nil, args.db)\n end\n\n task :restore, [:db, :file] => :environment do |task, args|\n puts \"restore db #{args.db} #{args.file}\"\n restore_from_file(nil, nil, args.db, args.file)\n end\n\n task :backup_drop_restore, [:fromdb, :todb, :file] => :environment do |task, args|\n puts \"backup_drop_restore db\"\n backup_to_file(args.fromdb, args.file)\n drop_db(nil, args.todb)\n restore_from_file(nil, nil, args.todb, args.file)\n end\n\n task :migrate_db_env, [:fromdb, :todb] => :environment do |task, args|\n puts \"migrate_db_env db\"\n migrate_db_env(args.fromdb, args.todb)\n end\n \n task :backup_env, [:db] => :environment do |task, args|\n puts \"backup_env db\"\n backup_env(args.db)\n end\n\n task :dumpDB, [:env] =>:environment do |task, args|\n \n cmd = nil\n dbEnv = get_environment_config(args.env)\n\n backup_to_file(dbEnv[\"host\"], dbEnv[\"username\"], dbEnv[\"database\"], default_file())\n end\n\n task :restoreDB, [:env] =>:environment do |task, args|\n \n cmd = nil\n dbEnv = get_environment_config(args.env)\n\n # Fix This?\n # disconnect connected db users to drop db\n # this is the only way to remove the lock on the db I've found that works\n killDB(dbEnv[\"database\"])\n\n drop_db(nil, dbEnv[\"database\"])\n Rake::Task[\"db:drop\"].invoke\n\n create_db(dbEnv[\"database\"])\n Rake::Task[\"db:create\"].invoke\n\n Rake::Task[\"db:migrate\"].invoke\n\n restore_from_file(dbEnv[\"host\"], dbEnv[\"username\"], dbEnv[\"database\"], default_file())\n end\n\n # http:\/\/www.tutorialspoint.com\/ruby-on-rails\/rails-and-rake.htm\n # rake db:schema:dump\n # rake db:schema:load\n\n # Order of events\n # rake db:drop\n # createdb ats_development\n # rake ats_api:db:v2:migrate\n # rake db_utils:backup_env[staging]\n # rake db_utils:restore[db_name,20150512151540.sql]\n # OR\n # rake db_utils:migrate_db_env[staging,development]\n \n # API Server must be down during rake\n #\n # Examples \n \n # output db with structure and create commands using datetimestamp.sql as file name\n # rake db_utils:backup[db_name]\n\n # output db with structure and create commands using \n # rake db_utils:backup[db_name, 'backup.sql']\n \n # drop a db\n # rake db_utils:drop[db_name]\n\n # restore db from file\n # rake db_utils:restore[db_name,20150506153637.sql]\n\n # migrate directly from one db to another db as described in database.yml\n # rake db_utils:migrate_db_env[staging,development]\n\nend\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-303","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/posts\/303","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=303"}],"version-history":[{"count":4,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/posts\/303\/revisions"}],"predecessor-version":[{"id":307,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/posts\/303\/revisions\/307"}],"wp:attachment":[{"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=303"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=303"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}