class Heroku::Command::Pg
manage heroku-postgresql databases
Constants
- MaybeAttachment
Public Instance Methods
pg:backups [subcommand]
Interact with built-in backups. Without a subcommand, it lists all available backups. The subcommands available are:
info BACKUP_ID # get information about a specific backup capture DATABASE # capture a new backup --wait-interval SECONDS # how frequently to poll (to avoid rate-limiting) restore [[BACKUP_ID] DATABASE] # restore a backup (default latest) to a database (default DATABASE_URL) --wait-interval SECONDS # how frequently to poll (to avoid rate-limiting) public-url BACKUP_ID # get secret but publicly accessible URL for BACKUP_ID to download it -q, --quiet # Hide expiration message (for use in scripts) cancel [BACKUP_ID] # cancel an in-progress backup or restore (default newest) delete BACKUP_ID # delete an existing backup schedule DATABASE # schedule nightly backups for given database --at '<hour>:00 <timezone>' # at a specific (24h clock) hour in the given timezone unschedule SCHEDULE # stop nightly backups on this schedule schedules # list backup schedule
# File lib/heroku/command/pg_backups.rb, line 72 def backups if args.count == 0 list_backups else command = shift_argument case command when 'list' then list_backups when 'info' then backup_status when 'capture' then capture_backup when 'restore' then restore_backup when 'public-url' then public_url when 'cancel' then cancel_backup when 'delete' then delete_backup when 'schedule' then schedule_backups when 'unschedule' then unschedule_backups when 'schedules' then list_schedules else abort "Unknown pg:backups command: #{command}" end end end
pg:copy SOURCE TARGET
--wait-interval SECONDS # how frequently to poll (to avoid rate-limiting)
Copy all data from source database to target. At least one of these must be a Heroku Postgres database.
# File lib/heroku/command/pg_backups.rb, line 14 def copy source_db = shift_argument target_db = shift_argument validate_arguments! interval = options[:wait_interval].to_i || 3 interval = [3, interval].max source = resolve_db_or_url(source_db) target = resolve_db_or_url(target_db) if source.url == target.url abort("Cannot copy database to itself") end attachment = target.attachment || source.attachment if target.attachment.nil? target_url = URI.parse(target.url) confirm_with = target_url.path[1..-1] confirm_with = target_url.host if confirm_with.empty? affected = target.name.downcase else confirm_with = target.attachment.app affected = "the app: #{target.attachment.app}" end message = "WARNING: Destructive Action" message << "\nThis command will remove all data from #{target.name}" message << "\nData from #{source.name} will then be transferred to #{target.name}" message << "\nThis command will affect #{affected}" if confirm_command(confirm_with, message) xfer = hpg_client(attachment).pg_copy(source.name, source.url, target.name, target.url) poll_transfer('copy', xfer[:uuid], interval) end end
pg:credentials DATABASE
display the DATABASE credentials.
--reset # Reset credentials on the specified database.
# File lib/heroku/command/pg.rb, line 247 def credentials requires_preauth unless db = shift_argument error("Usage: heroku pg:credentials DATABASE\nMust specify DATABASE to display credentials.") end validate_arguments! attachment = generate_resolver.resolve(db) if options[:reset] action "Resetting credentials for #{attachment.display_name}" do hpg_client(attachment).rotate_credentials end if attachment.primary_attachment? attachment = generate_resolver.resolve(db) action "Promoting #{attachment.display_name}" do hpg_promote(attachment.url) end end else uri = URI.parse( attachment.url ) display "Connection info string:" display " \"dbname=#{uri.path[1..-1]} host=#{uri.host} port=#{uri.port || 5432} user=#{uri.user} password=#{uri.password} sslmode=require\"" display "Connection URL:" display " " + attachment.url end end
pg:diagnose [DATABASE|REPORT_ID]
run diagnostics report on DATABASE
defaults to DATABASE_URL databases if no DATABASE is specified if REPORT_ID is specified instead, a previous report is displayed
# File lib/heroku/command/pg.rb, line 71 def diagnose requires_preauth db_id = shift_argument run_diagnose(db_id) end
pg
list databases for an app
# File lib/heroku/command/pg.rb, line 30 def index requires_preauth validate_arguments! if hpg_databases_with_info.empty? display("#{app} has no heroku-postgresql databases.") else hpg_databases_with_info.keys.sort.each do |name| display_db name, hpg_databases_with_info[name] end end end
pg:info [DATABASE]
-x, --extended # Show extended information
display database information
If DATABASE is not specified, displays all databases
# File lib/heroku/command/pg.rb, line 51 def info db = shift_argument validate_arguments! requires_preauth if db @resolver = generate_resolver attachment = @resolver.resolve(db) display_db attachment.display_name, hpg_info(attachment, options[:extended]) else index end end
pg:kill procpid [DATABASE]
kill a query
-f,–force # terminates the connection in addition to cancelling the query
# File lib/heroku/command/pg.rb, line 319 def kill requires_preauth procpid = shift_argument output_with_bang "procpid to kill is required" unless procpid && procpid.to_i != 0 procpid = procpid.to_i cmd = options[:force] ? 'pg_terminate_backend' : 'pg_cancel_backend' sql = %Q(SELECT #{cmd}(#{procpid});) puts exec_sql(sql) end
pg:killall [DATABASE]
terminates ALL connections
# File lib/heroku/command/pg.rb, line 335 def killall requires_preauth db = args.first attachment = generate_resolver.resolve(db, "DATABASE_URL") client = hpg_client(attachment) client.connection_reset display "Connections terminated" rescue StandardError # fall back to original mechanism if calling the reset endpoint # fails sql = %Q( SELECT pg_terminate_backend(#{pid_column}) FROM pg_stat_activity WHERE #{pid_column} <> pg_backend_pid() AND #{query_column} <> '<insufficient privilege>' ) puts exec_sql(sql) end
pg:links <create|destroy>
Create links between data stores. Without a subcommand, it lists all databases and information on the link. create <REMOTE> <LOCAL> # Create a data link --as <LINK> # override the default link name destroy <LOCAL> <LINK> # Destroy a data link between a local and remote database
# File lib/heroku/command/pg.rb, line 511 def links mode = shift_argument || 'list' if !(%w(list create destroy).include?(mode)) Heroku::Command.run(current_command, ["--help"]) exit(1) end case mode when 'list' db = shift_argument resolver = generate_resolver if db dbs = [resolver.resolve(db, "DATABASE_URL")] else dbs = resolver.all_databases.values end error("No database attached to this app.") if dbs.compact.empty? dbs.each_with_index do |attachment, index| response = hpg_client(attachment).link_list display "\n" if index.nonzero? styled_header("#{attachment.display_name} (#{attachment.resource_name})") next display response[:message] if response.kind_of?(Hash) next display "No data sources are linked into this database." if response.empty? response.each do |link| display "==== #{link[:name]}" link[:created] = time_format(link[:created_at]) link[:remote] = "#{link[:remote]['attachment_name']} (#{link[:remote]['name']})" link.reject! { |k,_| [:id, :created_at, :name].include?(k) } styled_hash(Hash[link.map {|k, v| [humanize(k), v] }]) end end when 'create' remote = shift_argument local = shift_argument error("Usage links <LOCAL> <REMOTE>") unless [local, remote].all? local_attachment = generate_resolver.resolve(local, "DATABASE_URL") remote_attachment = resolve_service(remote) output_with_bang("No source database specified.") unless local_attachment output_with_bang("No remote database specified.") unless remote_attachment response = hpg_client(local_attachment).link_set(remote_attachment.name, options[:as]) display("New link '#{response[:name]}' successfully created.") when 'destroy' local = shift_argument link = shift_argument error("No local database specified.") unless local error("No link name specified.") unless link local_attachment = generate_resolver.resolve(local, "DATABASE_URL") message = [ "WARNING: Destructive Action", "This command will affect the database: #{local}", "This will delete #{link} along with the tables and views created within it.", "This may have adverse effects for software written against the #{link} schema." ].join("\n") if confirm_command(app, message) action("Deleting link #{link} in #{local}") do hpg_client(local_attachment).link_delete(link) end end end end
pg:maintenance <info|run|set-window> <DATABASE>
manage maintenance for <DATABASE> info # show current maintenance information run # start maintenance -f, --force # run pg:maintenance without entering application maintenance mode window="<window>" # set weekly UTC maintenance window for DATABASE # eg: `heroku pg:maintenance window="Sunday 14:30"`
# File lib/heroku/command/pg.rb, line 420 def maintenance requires_preauth mode_with_argument = shift_argument || '' mode, mode_argument = mode_with_argument.split('=') db = shift_argument no_maintenance = options[:force] if mode.nil? || db.nil? || !(%w[info run window].include? mode) Heroku::Command.run(current_command, ["--help"]) exit(1) end resolver = generate_resolver attachment = resolver.resolve(db) if attachment.starter_plan? error("pg:maintenance is not available for hobby-tier databases") end case mode when 'info' response = hpg_client(attachment).maintenance_info display response[:message] when 'run' if in_maintenance?(resolver.app_name) || no_maintenance response = hpg_client(attachment).maintenance_run display response[:message] else error("Application must be in maintenance mode or --force flag must be used") end when 'window' unless mode_argument =~ /\A[A-Za-z]{3,10} \d\d?:[03]0\z/ error('Maintenance windows must be "Day HH:MM", where MM is 00 or 30.') end response = hpg_client(attachment).maintenance_window_set(mode_argument) display "Maintenance window for #{attachment.display_name} set for #{response[:window]}." end end
pg:promote DATABASE
sets DATABASE as your DATABASE_URL
# File lib/heroku/command/pg.rb, line 81 def promote requires_preauth unless db = shift_argument error("Usage: heroku pg:promote DATABASE\nMust specify DATABASE to promote.") end validate_arguments! db = db.sub(/_URL$/, '') # allow promoting with a var name addon = resolve_addon!(db) { |addon| addon['addon_service']['name'] == 'heroku-postgresql' } promoted_name = 'DATABASE' action "Ensuring an alternate alias for existing #{promoted_name}" do backup = find_or_create_non_database_attachment(app) if backup @status = backup['name'] else @status = "not needed" end end action "Promoting #{addon['name']} to #{promoted_name}_URL on #{app}" do request( :body => json_encode({ "app" => {"name" => app}, "addon" => {"name" => addon['name']}, "confirm" => app, "name" => promoted_name }), :expects => 201, :method => :post, :path => "/addon-attachments" ) end end
pg:ps [DATABASE]
view active queries with execution time
-v,--verbose # also show idle connections
# File lib/heroku/command/pg.rb, line 282 def ps requires_preauth sql = %Q( SELECT #{pid_column}, #{"state," if nine_two?} application_name AS source, age(now(),xact_start) AS running_for, waiting, #{query_column} AS query FROM pg_stat_activity WHERE #{query_column} <> '<insufficient privilege>' #{ # Apply idle-backend filter appropriate to versions and options. case when options[:verbose] '' when nine_two? "AND state <> 'idle'" else "AND current_query <> '<IDLE>'" end } AND #{pid_column} <> pg_backend_pid() ORDER BY query_start DESC ) puts exec_sql(sql) end
pg:psql [DATABASE]
-c, --command COMMAND # optional SQL command to run
open a psql shell to the database
defaults to DATABASE_URL databases if no DATABASE is specified
# File lib/heroku/command/pg.rb, line 127 def psql requires_preauth attachment = generate_resolver.resolve(shift_argument, "DATABASE_URL") validate_arguments! uri = URI.parse( attachment.url ) begin ENV["PGPASSWORD"] = uri.password ENV["PGSSLMODE"] = 'require' ENV["PGAPPNAME"] = "#{pgappname} interactive" if command = options[:command] command = %Q(-c "#{command}") end shorthand = "#{attachment.app}::#{attachment.name.sub(/^HEROKU_POSTGRESQL_/,'').gsub(/\W+/, '-')}" set_commands = Hooks.set_commands(shorthand) prompt_expr = "#{shorthand}%R%# " prompt_flags = %Q(--set "PROMPT1=#{prompt_expr}" --set "PROMPT2=#{prompt_expr}") puts "---> Connecting to #{attachment.display_name}" attachment.maybe_tunnel do |uri| command = "psql -U #{uri.user} -h #{uri.host} -p #{uri.port || 5432} #{set_commands} #{prompt_flags} #{command} #{uri.path[1..-1]}" if attachment.uses_bastion? spawn(command) Process.wait exit($?.exitstatus) else exec(command) end end rescue Errno::ENOENT output_with_bang "The local psql command could not be located" output_with_bang "For help installing psql, see http://devcenter.heroku.com/articles/local-postgresql" abort end end
pg:pull <REMOTE_SOURCE_DATABASE> <TARGET_DATABASE>
pull from REMOTE_SOURCE_DATABASE to TARGET_DATABASE TARGET_DATABASE must not already exist.
TARGET_DATABASE will be created locally if it's a database name or remotely if it's a fully qualified URL.
# File lib/heroku/command/pg.rb, line 391 def pull requires_preauth remote, local = shift_argument, shift_argument unless [remote, local].all? Heroku::Command.run(current_command, ['--help']) exit(1) end source_attachment = resolve_heroku_attachment(remote) target_uri = parse_db_url(local) source_attachment.maybe_tunnel do |uri| pgdr = PgDumpRestore.new( uri.to_s, target_uri, self) pgdr.execute end end
pg:push <SOURCE_DATABASE> <REMOTE_TARGET_DATABASE>
push from SOURCE_DATABASE to REMOTE_TARGET_DATABASE REMOTE_TARGET_DATABASE must be empty.
SOURCE_DATABASE must be either the name of a database existing on your localhost or the fully qualified URL of a remote database.
# File lib/heroku/command/pg.rb, line 364 def push requires_preauth local, remote = shift_argument, shift_argument unless [remote, local].all? Heroku::Command.run(current_command, ['--help']) exit(1) end target_attachment = resolve_heroku_attachment(remote) source_uri = parse_db_url(local) target_attachment.maybe_tunnel do |uri| pgdr = PgDumpRestore.new( source_uri, uri.to_s, self) pgdr.execute end end
pg:reset DATABASE
delete all data in DATABASE
# File lib/heroku/command/pg.rb, line 167 def reset requires_preauth unless db = shift_argument error("Usage: heroku pg:reset DATABASE\nMust specify DATABASE to reset.") end validate_arguments! resolver = generate_resolver attachment = resolver.resolve(db) @app = resolver.app_name if @app.nil? return unless confirm_command action("Resetting #{attachment.display_name}") do hpg_client(attachment).reset end end
pg:unfollow REPLICA
stop a replica from following and make it a read/write database
# File lib/heroku/command/pg.rb, line 189 def unfollow requires_preauth unless db = shift_argument error("Usage: heroku pg:unfollow REPLICA\nMust specify REPLICA to unfollow.") end validate_arguments! resolver = generate_resolver replica = resolver.resolve(db) @app = resolver.app_name if @app.nil? replica_info = hpg_info(replica) unless replica_info[:following] error("#{replica.display_name} is not following another database.") end origin_url = replica_info[:following] origin_name = resolver.database_name_from_url(origin_url) output_with_bang "#{replica.display_name} will become writable and no longer" output_with_bang "follow #{origin_name}. This cannot be undone." return unless confirm_command action "Unfollowing #{replica.display_name}" do hpg_client(replica).unfollow end end
pg:upgrade REPLICA
unfollow a database and upgrade it to the latest PostgreSQL version
# File lib/heroku/command/pg.rb, line 464 def upgrade requires_preauth unless db = shift_argument error("Usage: heroku pg:upgrade REPLICA\nMust specify REPLICA to upgrade.") end validate_arguments! resolver = generate_resolver replica = resolver.resolve(db) @app = resolver.app_name if @app.nil? replica_info = hpg_info(replica) if replica.starter_plan? error("pg:upgrade is only available for follower production databases.") end upgrade_status = hpg_client(replica).upgrade_status if upgrade_status[:error] output_with_bang "There were problems upgrading #{replica.resource_name}" output_with_bang upgrade_status[:error] else origin_url = replica_info[:following] origin_name = resolver.database_name_from_url(origin_url) output_with_bang "#{replica.resource_name} will be upgraded to a newer PostgreSQL version," output_with_bang "stop following #{origin_name}, and become writable." output_with_bang "Use `heroku pg:wait` to track status" output_with_bang "\nThis cannot be undone." return unless confirm_command action "Requesting upgrade" do hpg_client(replica).upgrade end end end
pg:wait [DATABASE]
monitor database creation, exit when complete
defaults to all databases if no DATABASE is specified
–wait-interval SECONDS # how frequently to poll (to avoid rate-limiting)
# File lib/heroku/command/pg.rb, line 225 def wait requires_preauth db = shift_argument validate_arguments! interval = options[:wait_interval].to_i interval = 1 if interval < 1 if db wait_for(generate_resolver.resolve(db), interval) else generate_resolver.all_databases.values.each do |attach| wait_for(attach, interval) end end end
Private Instance Methods
# File lib/heroku/command/pg_backups.rb, line 114 def arbitrary_app_db generate_resolver.all_databases.values.first end
# File lib/heroku/command/pg_backups.rb, line 264 def backup_status backup_name = shift_argument validate_arguments! verbose = true client = hpg_app_client(app) backup = if backup_name.nil? backups = client.transfers last_backup = backups.select do |b| b[:from_type] == 'pg_dump' && b[:to_type] == 'gof3r' end.sort_by { |b| b[:created_at] }.last if last_backup.nil? error("No backups. Capture one with `heroku pg:backups capture`.") else if verbose client.transfers_get(last_backup[:num], verbose) else last_backup end end else backup_num = transfer_num(backup_name) if backup_num.nil? error("No such backup: #{backup_num}") end client.transfers_get(backup_num, verbose) end status = if backup[:succeeded] warnings = backup[:warnings] if warnings && warnings > 0 "Finished with #{warnings} warnings" else "Completed" end elsif backup[:canceled_at] "Canceled" elsif backup[:finished_at] "Failed" elsif backup[:started_at] "Running" else "Pending" end type = if backup[:schedule] "Scheduled" else "Manual" end backup_name = transfer_name(backup) display <<-EOF === Backup info: #{backup_name} Database: #{backup[:from_name]} EOF if backup[:started_at] display <<-EOF Started: #{backup[:started_at]} EOF end if backup[:finished_at] display <<-EOF Finished: #{backup[:finished_at]} EOF end display <<-EOF Status: #{status} Type: #{type} EOF backup_size = backup[:processed_bytes] orig_size = backup[:source_bytes] || 0 if orig_size > 0 compress_str = "" unless backup[:finished_at].nil? compression_pct = if backup_size > 0 [((orig_size - backup_size).to_f / orig_size * 100) .round, 0].max else 0 end compress_str = " (#{compression_pct}% compression)" end display <<-EOF Original DB Size: #{size_pretty(orig_size)} Backup Size: #{size_pretty(backup_size)}#{compress_str} EOF else display <<-EOF Backup Size: #{size_pretty(backup_size)} EOF end if verbose display "=== Backup Logs" backup[:logs].each do |item| display "#{item['created_at']}: #{item['message']}" end end end
# File lib/heroku/command/pg_backups.rb, line 530 def cancel_backup backup_name = shift_argument validate_arguments! client = hpg_app_client(app) transfer = if backup_name backup_num = transfer_num(backup_name) if backup_num.nil? error("No such backup/restore: #{backup_name}") else client.transfers_get(backup_num) end else last_transfer = client.transfers.sort_by { |b| b[:created_at] }.reverse.find { |b| b[:finished_at].nil? } if last_transfer.nil? error("No active backups/restores") else last_transfer end end client.transfers_cancel(transfer[:uuid]) display "Canceled #{transfer_name(transfer)}" end
# File lib/heroku/command/pg_backups.rb, line 362 def capture_backup db = shift_argument attachment = generate_resolver.resolve(db, "DATABASE_URL") validate_arguments! interval = options[:wait_interval].to_i || 3 interval = [3, interval].max backup = hpg_client(attachment).backups_capture display <<-EOF Use Ctrl-C at any time to stop monitoring progress; the backup will continue running. Use heroku pg:backups info to check progress. Stop a running backup with heroku pg:backups cancel. #{attachment.name} ---backup---> #{transfer_name(backup)} EOF poll_transfer('backup', backup[:uuid], interval) end
# File lib/heroku/command/pg_backups.rb, line 483 def delete_backup backup_name = shift_argument validate_arguments! if confirm_command backup_num = transfer_num(backup_name) if backup_num.nil? error("No such backup: #{backup_num}") end hpg_app_client(app).transfers_delete(backup_num) display "Deleted #{backup_name}" end end
# File lib/heroku/command/pg.rb, line 624 def display_db(name, db) styled_header(name) if db dsphash = db[:info].inject({}) do |hash, item| hash.update(item["name"] => hpg_info_display(item)) end dspkeys = db[:info].map {|item| item['name']} styled_hash(dsphash, dspkeys) else styled_hash("Error" => "Not Found") end display end
# File lib/heroku/command/pg.rb, line 760 def exec_sql(sql) attachment = generate_resolver.resolve(shift_argument, "DATABASE_URL") attachment.maybe_tunnel do |uri| exec_sql_on_uri(sql, uri) end end
# File lib/heroku/command/pg.rb, line 767 def exec_sql_on_uri(sql,uri) begin ENV["PGPASSWORD"] = uri.password ENV["PGSSLMODE"] = (uri.host == 'localhost' ? 'prefer' : 'require' ) ENV["PGAPPNAME"] = "#{pgappname} non-interactive" user_part = uri.user ? "-U #{uri.user}" : "" output = %x`#{psql_cmd} -c "#{sql}" #{user_part} -h #{uri.host} -p #{uri.port || 5432} #{uri.path[1..-1]}` if (! $?.success?) || output.nil? || output.empty? raise "psql failed. exit status #{$?.to_i}, output: #{output.inspect}" end output rescue Errno::ENOENT output_with_bang "The local psql command could not be located" output_with_bang "For help installing psql, see https://devcenter.heroku.com/articles/heroku-postgresql#local-setup" abort end end
Finds or creates a non-DATABASE attachment for the DB currently attached as DATABASE.
If current DATABASE is attached by other names, return one of them. If current DATABASE is only attachment, create a new one and return it. If no current DATABASE, return nil.
# File lib/heroku/command/pg.rb, line 805 def find_or_create_non_database_attachment(app) attachments = get_attachments(:app => app) current_attachment = attachments.detect { |att| att['name'] == 'DATABASE' } current_addon = current_attachment && current_attachment['addon'] if current_addon existing = attachments. select { |att| att['addon']['id'] == current_addon['id'] }. detect { |att| att['name'] != 'DATABASE' } return existing if existing # The current add-on occupying the DATABASE attachment has no # other attachments. In order to promote this database without # error, we can create a secondary attachment, just-in-time. request( # Note: no attachment name provided; let the API choose one :body => json_encode({ "app" => {"name" => app}, "addon" => {"name" => current_addon['name']}, "confirm" => app }), :expects => 201, :method => :post, :path => "/addon-attachments" ) end end
# File lib/heroku/command/pg.rb, line 609 def generate_resolver app_name = app rescue nil # will raise if no app, but calling app reads in arguments Resolver.new(app_name, api) end
# File lib/heroku/command/pg.rb, line 600 def get_config_var(name) res = api.get_config_vars(app) res.data[:body][name] end
# File lib/heroku/command/pg_backups.rb, line 638 def hpg_app_client(app_name) Heroku::Client::HerokuPostgresqlApp.new(app_name) end
# File lib/heroku/command/pg.rb, line 649 def hpg_client(attachment) Heroku::Client::HerokuPostgresql.new(attachment) end
# File lib/heroku/command/pg.rb, line 653 def hpg_databases_with_info return @hpg_databases_with_info if @hpg_databases_with_info @resolver = generate_resolver attachments = @resolver.all_databases attachments_by_db = attachments.values.group_by(&:resource_name) db_infos = {} mutex = Mutex.new threads = attachments_by_db.map do |resource, attachments| Thread.new do begin info = hpg_info(attachments.first, options[:extended]) rescue info = nil end # Make headers as per heroku/heroku#1605 names = attachments.map(&:config_var) names << 'DATABASE_URL' if attachments.any? { |att| att.primary_attachment? } name = names. uniq. sort_by { |n| n=='DATABASE_URL' ? '{' : n }. # Weight DATABASE_URL last join(', ') mutex.synchronize do db_infos[name] = info end end end threads.map(&:join) @hpg_databases_with_info = db_infos return @hpg_databases_with_info end
# File lib/heroku/command/pg.rb, line 690 def hpg_info(attachment, extended=false) info = hpg_client(attachment).get_database(extended) # TODO: Make this the section title and list the current `name` as an # "Attachments" item here: info.merge(:info => info[:info] + [{"name" => "Add-on", "values" => [attachment.resource_name]}]) end
# File lib/heroku/command/pg.rb, line 698 def hpg_info_display(item) item["values"] = [item["value"]] if item["value"] item["values"].map do |value| if item["resolve_db_name"] @resolver.database_name_from_url(value) else value end end end
# File lib/heroku/command/pg.rb, line 641 def in_maintenance?(app) api.get_app_maintenance(app).body['maintenance'] end
# File lib/heroku/command/pg_backups.rb, line 192 def list_backups validate_arguments! transfers = hpg_app_client(app).transfers display "=== Backups" display_backups = transfers.select do |b| b[:from_type] == 'pg_dump' && b[:to_type] == 'gof3r' end.sort_by { |b| b[:created_at] }.reverse.map do |b| { "id" => transfer_name(b), "created_at" => b[:created_at], "status" => transfer_status(b), "size" => size_pretty(b[:processed_bytes]), "database" => b[:from_name] || 'UNKNOWN' } end if display_backups.empty? display("No backups. Capture one with `heroku pg:backups capture`.") else display_table( display_backups, %w(id created_at status size database), ["ID", "Backup Time", "Status", "Size", "Database"] ) end display "\n=== Restores" display_restores = transfers.select do |r| r[:from_type] != 'pg_dump' && r[:to_type] == 'pg_restore' end.sort_by { |r| r[:created_at] }.reverse.first(10).map do |r| { "id" => transfer_name(r), "created_at" => r[:created_at], "status" => transfer_status(r), "size" => size_pretty(r[:processed_bytes]), "database" => r[:to_name] || 'UNKNOWN' } end if display_restores.empty? display("No restores found. Use `heroku pg:backups restore` to restore a backup") else display_table( display_restores, %w(id created_at status size database), ["ID", "Restore Time", "Status", "Size", "Database"] ) end display "\n=== Copies" display_restores = transfers.select do |r| r[:from_type] == 'pg_dump' && r[:to_type] == 'pg_restore' end.sort_by { |r| r[:created_at] }.reverse.first(10).map do |r| { "id" => transfer_name(r), "created_at" => r[:created_at], "status" => transfer_status(r), "size" => size_pretty(r[:processed_bytes]), "to_database" => r[:to_name] || 'UNKNOWN', "from_database" => r[:from_name] || 'UNKNOWN' } end if display_restores.empty? display("No copies found. Use `heroku pg:copy` to copy a database to another") else display_table( display_restores, %w(id created_at status size from_database to_database), ["ID", "Restore Time", "Status", "Size", "From Database", "To Database"] ) end end
# File lib/heroku/command/pg_backups.rb, line 620 def list_schedules validate_arguments! attachment = arbitrary_app_db if attachment.nil? abort("#{app} has no heroku-postgresql databases.") end schedules = hpg_client(attachment).schedules if schedules.empty? display "No backup schedules found. Use `heroku pg:backups schedule` to set one up." else display "=== Backup Schedules" schedules.each do |s| display "#{s[:name]}: daily at #{s[:hour]}:00 (#{s[:timezone]})" end end end
# File lib/heroku/command/pg.rb, line 739 def nine_two? return @nine_two if defined? @nine_two @nine_two = version.to_f >= 9.2 end
Parse string database parameter and return string database URL.
@param db_string [String] The local database name or a full connection URL, e.g. `my_db` or `postgres://user:pass@host:5432/my_db` @return [String] A full database connection URL.
# File lib/heroku/command/pg.rb, line 618 def parse_db_url(db_string) return db_string if db_string =~ %r(://) "postgres:///#{db_string}" end
# File lib/heroku/command/pg_backups.rb, line 642 def parse_schedule_time(time_str) hour, tz = time_str.match(/([0-2][0-9]):00 ?(.*)/) && [ $1, $2 ] if hour.nil? || tz.nil? abort("Invalid schedule format: expected '<hour>:00 <timezone>'") end # do-what-i-mean remapping, since transferatu is (rightfully) picky remap_tzs = { 'PST' => 'America/Los_Angeles', 'PDT' => 'America/Los_Angeles', 'MST' => 'America/Boise', 'MDT' => 'America/Boise', 'CST' => 'America/Chicago', 'CDT' => 'America/Chicago', 'EST' => 'America/New_York', 'EDT' => 'America/New_York', 'Z' => 'UTC', 'GMT' => 'Europe/London', 'BST' => 'Europe/London', } if remap_tzs.has_key? tz.upcase tz = remap_tzs[tz.upcase] end { :hour => hour, :timezone => tz } end
# File lib/heroku/command/pg.rb, line 785 def pgappname if running_on_windows? 'psql (windows)' else "psql #{`whoami`.chomp.gsub(/\W/,'')}" end end
# File lib/heroku/command/pg.rb, line 744 def pid_column if nine_two? 'pid' else 'procpid' end end
# File lib/heroku/command/pg_backups.rb, line 443 def poll_transfer(action, transfer_id, interval) # pending, running, complete--poll endpoint to get backup = nil ticks = 0 failed_count = 0 begin begin backup = hpg_app_client(app).transfers_get(transfer_id) failed_count = 0 status = if backup[:started_at] "Running... #{size_pretty(backup[:processed_bytes])}" else "Pending... #{spinner(ticks)}" end redisplay status ticks += 1 rescue RestClient::Exception backup = {} failed_count += 1 if failed_count > 120 raise end end sleep interval end until backup[:finished_at] if backup[:succeeded] redisplay "#{action.capitalize} completed\n" else # TODO: better errors for # - db not online (/name or service not known/) # - bad creds (/psql: FATAL:/???) redisplay <<-EOF An error occurred and your backup did not finish. Please run `heroku pg:backups info #{transfer_name(backup)}` for details. EOF end end
# File lib/heroku/command/pg.rb, line 793 def psql_cmd # some people alais psql, so we need to find the real psql # but windows doesn't have the command command running_on_windows? ? 'psql' : 'command psql' end
# File lib/heroku/command/pg_backups.rb, line 497 def public_url backup_name = shift_argument validate_arguments! backup_num = nil client = hpg_app_client(app) if backup_name backup_num = transfer_num(backup_name) if backup_num.nil? error("No such backup: #{backup_num}") end else last_successful_backup = client.transfers.select do |xfer| xfer[:succeeded] && xfer[:to_type] == 'gof3r' end.sort_by { |b| b[:created_at] }.last if last_successful_backup.nil? error("No backups. Capture one with `heroku pg:backups capture`.") else backup_num = last_successful_backup[:num] end end url_info = client.transfers_public_url(backup_num) if $stdout.tty? && !options[:quiet] display <<-EOF The following URL will expire at #{url_info[:expires_at]}: "#{url_info[:url]}" EOF else display url_info[:url] end end
# File lib/heroku/command/pg.rb, line 752 def query_column if nine_two? 'query' else 'current_query' end end
# File lib/heroku/command/pg_backups.rb, line 101 def resolve_db_or_url(name_or_url, default=nil) if name_or_url =~ %r{postgres://} url = name_or_url uri = URI.parse(url) name = url_name(uri) MaybeAttachment.new(name, url, nil) else attachment = generate_resolver.resolve(name_or_url, default) name = attachment.config_var.sub(/^HEROKU_POSTGRESQL_/, '').sub(/_URL$/, '') MaybeAttachment.new(name, attachment.url, attachment) end end
# File lib/heroku/command/pg.rb, line 605 def resolve_heroku_attachment(remote) generate_resolver.resolve(remote) end
# File lib/heroku/command/pg.rb, line 591 def resolve_service(name) attachment = (resolve_addon(name) || []).first error("Remote database could not be found.") unless attachment error("Remote database is invalid.") unless attachment['addon_service']['name'] =~ /heroku-(redis|postgresql)/ MaybeAttachment.new(attachment['name'], nil, attachment) end
# File lib/heroku/command/pg_backups.rb, line 383 def restore_backup # heroku pg:backups restore [[backup_id] database] db = nil restore_from = :latest # N.B.: we have to account for the command argument here if args.count == 2 db = shift_argument elsif args.count == 3 restore_from = shift_argument db = shift_argument end attachment = generate_resolver.resolve(db, "DATABASE_URL") validate_arguments! interval = options[:wait_interval].to_i || 3 interval = [3, interval].max restore_url = nil if restore_from =~ %r{\Ahttps?://} restore_url = restore_from else # assume we're restoring from a backup if restore_from =~ /::/ backup_app, backup_name = restore_from.split('::') else backup_app, backup_name = [app, restore_from] end backups = hpg_app_client(backup_app).transfers.select do |b| b[:from_type] == 'pg_dump' && b[:to_type] == 'gof3r' end backup = if backup_name == :latest backups.select { |b| b[:succeeded] } .sort_by { |b| b[:finished_at] }.last else backups.find { |b| transfer_name(b) == backup_name } end if backups.empty? abort("No backups for #{backup_app}. Capture one with `heroku pg:backups capture`.") elsif backup.nil? abort("Backup #{backup_name} not found for #{backup_app}.") elsif !backup[:succeeded] abort("Backup #{backup_name} for #{backup_app} did not complete successfully; cannot restore it.") end restore_url = backup[:to_url] end if confirm_command(attachment.app) restore = hpg_client(attachment).backups_restore(restore_url) display <<-EOF Use Ctrl-C at any time to stop monitoring progress; the backup will continue restoring. Use heroku pg:backups to check progress. Stop a running restore with heroku pg:backups cancel. #{transfer_name(restore)} ---restore---> #{attachment.name} EOF poll_transfer('restore', restore[:uuid], interval) end end
# File lib/heroku/command/pg_backups.rb, line 556 def schedule_backups db = shift_argument validate_arguments! at = options[:at] if !at error("You must specifiy a time to schedule backups, i.e --at '04:00 UTC'") end schedule_opts = parse_schedule_time(at) resolver = generate_resolver attachment = resolver.resolve(db, "DATABASE_URL") # N.B.: we need to resolve the name to find the right database, # but we don't want to resolve it to the canonical name, so that, # e.g., names like FOLLOWER_URL work. To do this, we look up the # app config vars and re-find one that looks like the user's # requested name. db_name, alias_url = resolver.app_config_vars.find do |k,v| k =~ /#{db}/i && v == attachment.url end if alias_url.nil? error("Could not find database to schedule for backups. Try using its full name.") end schedule_opts[:schedule_name] = db_name hpg_client(attachment).schedule(schedule_opts) display "Scheduled automatic daily backups at #{at} for #{attachment.name}" end
# File lib/heroku/command/pg_backups.rb, line 167 def size_pretty(bytes) suffixes = [ ['B', 1], ['kB', 1_000], ['MB', 1_000_000], ['GB', 1_000_000_000], ['TB', 1_000_000_000_000] # (ohdear) ] suffix, multiplier = suffixes.find do |k,v| normalized = bytes / v.to_f normalized >= 0 && normalized < 1_000 end if suffix.nil? return bytes end normalized = bytes / multiplier.to_f num_digits = case when normalized >= 100 then '0' when normalized >= 10 then '1' else '2' end fmt_str = "%.#{num_digits}f#{suffix}" format(fmt_str, normalized) end
# File lib/heroku/command/pg.rb, line 709 def ticking(interval) ticks = 0 loop do yield(ticks) ticks +=1 sleep interval end end
# File lib/heroku/command/pg.rb, line 645 def time_format(time) Time.parse(time).getutc.strftime("%Y-%m-%d %H:%M %Z") end
# File lib/heroku/command/pg_backups.rb, line 118 def transfer_name(transfer) old_pgb_name = transfer.has_key?(:options) && transfer[:options]["pgbackups_name"] if old_pgb_name "o#{old_pgb_name}" else transfer_num = transfer[:num] from_type, to_type = transfer[:from_type], transfer[:to_type] prefix = if from_type == 'pg_dump' && to_type != 'pg_restore' transfer.has_key?(:schedule) ? 'a' : 'b' elsif from_type != 'pg_dump' && to_type == 'pg_restore' 'r' elsif from_type == 'pg_dump' && to_type == 'pg_restore' 'c' else 'b' end "#{prefix}#{format("%03d", transfer_num)}" end end
# File lib/heroku/command/pg_backups.rb, line 139 def transfer_num(transfer_name) if /\A[abcr](\d+)\z/.match(transfer_name) $1.to_i elsif /\Ao[ab]\d+\z/.match(transfer_name) xfer = hpg_app_client(app).transfers.find do |t| transfer_name(t) == transfer_name end xfer[:num] unless xfer.nil? end end
# File lib/heroku/command/pg_backups.rb, line 150 def transfer_status(t) if t[:finished_at] && t[:succeeded] warnings = t[:warnings] if warnings && warnings > 0 "Finished with #{warnings} warnings" #{t[:finished_at]}" else "Completed #{t[:finished_at]}" end elsif t[:finished_at] && !t[:succeeded] "Failed #{t[:finished_at]}" elsif t[:started_at] "Running (processed #{size_pretty(t[:processed_bytes])})" else "Pending" end end
# File lib/heroku/command/pg_backups.rb, line 588 def unschedule_backups db = shift_argument validate_arguments! if db.nil? # try to provide a more informative error message, but rescue to # a generic error message in case things go poorly begin attachment = arbitrary_app_db schedules = hpg_client(attachment).schedules schedule_names = schedules.map { |s| s[:name] }.join(", ") abort("Must specify schedule to cancel: existing schedules are #{schedule_names}") rescue StandardError abort("Must specify schedule to cancel. Run `heroku help pg:backups` for usage information.") end end attachment = generate_resolver.resolve(db, "DATABASE_URL") schedule = hpg_client(attachment).schedules.find do |s| # s[:name] is HEROKU_POSTGRESQL_COLOR_URL s[:name] =~ /#{db}/i end if schedule.nil? display "No automatic daily backups for #{attachment.name} found" else hpg_client(attachment).unschedule(schedule[:uuid]) display "Stopped automatic daily backups for #{attachment.name}" end end
# File lib/heroku/command/pg_backups.rb, line 97 def url_name(uri) "Database #{uri.path[1..-1]} on #{uri.host}:#{uri.port || 5432}" end
# File lib/heroku/command/pg.rb, line 732 def version return @version if defined? @version result = exec_sql("select version();").match(/PostgreSQL (\d+\.\d+\.\d+) on/) fail("Unable to determine Postgres version") unless result @version = result[1] end
# File lib/heroku/command/pg.rb, line 718 def wait_for(attach, interval) ticking(interval) do |ticks| status = hpg_client(attach).get_wait_status error status[:message] if status[:error?] break if !status[:waiting?] && ticks.zero? redisplay("Waiting for database %s... %s%s" % [ attach.display_name, status[:waiting?] ? "#{spinner(ticks)} " : "", status[:message]], !status[:waiting?]) # only display a newline on the last tick break unless status[:waiting?] end end