class Heroku::Command::Pg

manage heroku-postgresql databases

Constants

MaybeAttachment

Public Instance Methods

backups() click to toggle source

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
copy() click to toggle source

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
credentials() click to toggle source

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
diagnose() click to toggle source

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
index() click to toggle source

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
info() click to toggle source

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
kill() click to toggle source

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
killall() click to toggle source

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
maintenance() click to toggle source

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
promote() click to toggle source

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
ps() click to toggle source

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
psql() click to toggle source

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
pull() click to toggle source

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
push() click to toggle source

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
reset() click to toggle source

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
unfollow() click to toggle source

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
upgrade() click to toggle source

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
wait() click to toggle source

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

arbitrary_app_db() click to toggle source
# File lib/heroku/command/pg_backups.rb, line 114
def arbitrary_app_db
  generate_resolver.all_databases.values.first
end
backup_status() click to toggle source
# 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
cancel_backup() click to toggle source
# 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
capture_backup() click to toggle source
# 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
delete_backup() click to toggle source
# 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
display_db(name, db) click to toggle source
# 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
exec_sql(sql) click to toggle source
# 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
exec_sql_on_uri(sql,uri) click to toggle source
# 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
find_or_create_non_database_attachment(app) click to toggle source

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
generate_resolver() click to toggle source
# 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
get_config_var(name) click to toggle source
# File lib/heroku/command/pg.rb, line 600
def get_config_var(name)
  res = api.get_config_vars(app)
  res.data[:body][name]
end
hpg_app_client(app_name) click to toggle source
# File lib/heroku/command/pg_backups.rb, line 638
def hpg_app_client(app_name)
  Heroku::Client::HerokuPostgresqlApp.new(app_name)
end
hpg_client(attachment) click to toggle source
# File lib/heroku/command/pg.rb, line 649
def hpg_client(attachment)
  Heroku::Client::HerokuPostgresql.new(attachment)
end
hpg_databases_with_info() click to toggle source
# 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
hpg_info(attachment, extended=false) click to toggle source
# 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
hpg_info_display(item) click to toggle source
# 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
in_maintenance?(app) click to toggle source
# File lib/heroku/command/pg.rb, line 641
def in_maintenance?(app)
  api.get_app_maintenance(app).body['maintenance']
end
list_backups() click to toggle source
# 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
list_schedules() click to toggle source
# 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
nine_two?() click to toggle source
# 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_db_url(db_string) click to toggle source

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
parse_schedule_time(time_str) click to toggle source
# 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
pgappname() click to toggle source
# File lib/heroku/command/pg.rb, line 785
def pgappname
  if running_on_windows?
    'psql (windows)'
  else
    "psql #{`whoami`.chomp.gsub(/\W/,'')}"
  end
end
pid_column() click to toggle source
# File lib/heroku/command/pg.rb, line 744
def pid_column
  if nine_two?
    'pid'
  else
    'procpid'
  end
end
poll_transfer(action, transfer_id, interval) click to toggle source
# 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
psql_cmd() click to toggle source
# 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
public_url() click to toggle source
# 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
query_column() click to toggle source
# File lib/heroku/command/pg.rb, line 752
def query_column
  if nine_two?
    'query'
  else
    'current_query'
  end
end
resolve_db_or_url(name_or_url, default=nil) click to toggle source
# 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
resolve_heroku_attachment(remote) click to toggle source
# File lib/heroku/command/pg.rb, line 605
def resolve_heroku_attachment(remote)
  generate_resolver.resolve(remote)
end
resolve_service(name) click to toggle source
# 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
restore_backup() click to toggle source
# 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
schedule_backups() click to toggle source
# 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
size_pretty(bytes) click to toggle source
# 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
ticking(interval) { |ticks| ... } click to toggle source
# File lib/heroku/command/pg.rb, line 709
def ticking(interval)
  ticks = 0
  loop do
    yield(ticks)
    ticks +=1
    sleep interval
  end
end
time_format(time) click to toggle source
# File lib/heroku/command/pg.rb, line 645
def time_format(time)
  Time.parse(time).getutc.strftime("%Y-%m-%d %H:%M %Z")
end
transfer_name(transfer) click to toggle source
# 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
transfer_num(transfer_name) click to toggle source
# 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
transfer_status(t) click to toggle source
# 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
unschedule_backups() click to toggle source
# 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
url_name(uri) click to toggle source
# 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
version() click to toggle source
# 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
wait_for(attach, interval) click to toggle source
# 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