Class: Heroku::Command::Pg

Inherits:
Base
  • Object
show all
Includes:
Helpers::HerokuPostgresql, Helpers::PgDiagnose
Defined in:
lib/heroku/command/pg.rb,
lib/heroku/command/pg_backups.rb

Overview

manage heroku-postgresql databases

Defined Under Namespace

Modules: Hooks Classes: MaybeAttachment

Constant Summary

Constants included from Helpers::PgDiagnose

Helpers::PgDiagnose::DIAGNOSE_URL

Instance Attribute Summary

Attributes inherited from Base

#args, #options

Instance Method Summary collapse

Methods included from Helpers::HerokuPostgresql

#hpg_resolve, #hpg_translate_db_opts_to_urls, #hpg_translate_fork_and_follow

Methods included from Helpers

#action, #app_owner, #ask, #confirm, #confirm_command, #create_git_remote, #debug, #debugging?, #default_org_host, #deprecate, #display, #display_header, #display_object, #display_row, #display_table, #error, #error_log, #error_log_path, error_with_failure, error_with_failure=, extended, extended_into, #fail, #format_bytes, #format_date, #format_error, #format_with_bang, #get_terminal_environment, #git, #has_git?, #has_git_remote?, #has_http_git_entry_in_netrc, #home_directory, #hprint, #hputs, included, included_into, #json_decode, #json_encode, #launchy, #line_formatter, #longest, #org?, #org_host, #output_with_bang, #quantify, #redisplay, #retry_on_exception, #run_command, #running_on_a_mac?, #running_on_windows?, #set_buffer, #shell, #spinner, #status, #stderr_print, #stderr_puts, #string_distance, #styled_array, #styled_error, #styled_hash, #styled_header, #suggestion, #time_ago, #truncate, #update_git_remote, #warn_if_using_jruby, #with_tty

Methods inherited from Base

#api, #app, #heroku, #initialize, namespace, #org, #org_api

Constructor Details

This class inherits a constructor from Heroku::Command::Base

Instance Method Details

#backupsObject

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
restore [[BACKUP_ID] DATABASE] # restore a backup (default latest) to a database (default DATABASE_URL)
public-url BACKUP_ID           # get secret but publicly accessible URL for BACKUP_ID to download it
cancel                         # cancel an in-progress backup
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 DATABASE            # stop nightly backup for database
schedules                      # list backup schedule

48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
# File 'lib/heroku/command/pg_backups.rb', line 48

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

#copyObject

pg:copy SOURCE TARGET

Copy all data from source database to target. At least one of these must be a Heroku Postgres database.


11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# File 'lib/heroku/command/pg_backups.rb', line 11

def copy
  source_db = shift_argument
  target_db = shift_argument

  validate_arguments!

  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 confirm_command
    xfer = hpg_client(attachment).pg_copy(source.name, source.url,
                                          target.name, target.url)
    poll_transfer('copy', xfer[:uuid])
  end
end

#credentialsObject

pg:credentials DATABASE

display the DATABASE credentials.

--reset  # Reset credentials on the specified database.

211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
# File 'lib/heroku/command/pg.rb', line 211

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

#diagnoseObject

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


68
69
70
71
72
# File 'lib/heroku/command/pg.rb', line 68

def diagnose
  requires_preauth
  db_id = shift_argument
  run_diagnose(db_id)
end

#indexObject

pg

list databases for an app


27
28
29
30
31
32
33
34
35
36
37
38
# File 'lib/heroku/command/pg.rb', line 27

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

#infoObject

pg:info [DATABASE]

-x, --extended  # Show extended information

display database information

If DATABASE is not specified, displays all databases


48
49
50
51
52
53
54
55
56
57
58
59
60
# File 'lib/heroku/command/pg.rb', line 48

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

#killObject

pg:kill procpid [DATABASE]

kill a query

-f,–force # terminates the connection in addition to cancelling the query


277
278
279
280
281
282
283
284
285
286
287
# File 'lib/heroku/command/pg.rb', line 277

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

#killallObject

pg:killall [DATABASE]

terminates ALL connections


293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
# File 'lib/heroku/command/pg.rb', line 293

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

#maintenanceObject

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"`

376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
# File 'lib/heroku/command/pg.rb', line 376

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

#promoteObject

pg:promote DATABASE

sets DATABASE as your DATABASE_URL


78
79
80
81
82
83
84
85
86
87
88
89
90
# File 'lib/heroku/command/pg.rb', line 78

def promote
  requires_preauth
  unless db = shift_argument
    error("Usage: heroku pg:promote DATABASE\nMust specify DATABASE to promote.")
  end
  validate_arguments!

  attachment = generate_resolver.resolve(db)

  action "Promoting #{attachment.display_name} to DATABASE_URL" do
    hpg_promote(attachment.url)
  end
end

#psObject

pg:ps [DATABASE]

view active queries with execution time


244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
# File 'lib/heroku/command/pg.rb', line 244

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>'
     #{
        if 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

#psqlObject

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


100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
# File 'lib/heroku/command/pg.rb', line 100

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}"
    exec "psql -U #{uri.user} -h #{uri.host} -p #{uri.port || 5432} #{set_commands} #{prompt_flags} #{command} #{uri.path[1..-1]}"
  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

#pullObject

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.


348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
# File 'lib/heroku/command/pg.rb', line 348

def pull
  requires_preauth
  remote, local = shift_argument, shift_argument
  unless [remote, local].all?
    Heroku::Command.run(current_command, ['--help'])
    exit(1)
  end

  source_uri = resolve_heroku_url(remote)
  target_uri = parse_db_url(local)

  pgdr = PgDumpRestore.new(
    source_uri,
    target_uri,
    self)

  pgdr.execute
end

#pushObject

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.


322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
# File 'lib/heroku/command/pg.rb', line 322

def push
  requires_preauth
  local, remote = shift_argument, shift_argument
  unless [remote, local].all?
    Heroku::Command.run(current_command, ['--help'])
    exit(1)
  end

  target_uri = resolve_heroku_url(remote)
  source_uri = parse_db_url(local)

  pgdr = PgDumpRestore.new(
    source_uri,
    target_uri,
    self)

  pgdr.execute
end

#resetObject

pg:reset DATABASE

delete all data in DATABASE


131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
# File 'lib/heroku/command/pg.rb', line 131

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

#unfollowObject

pg:unfollow REPLICA

stop a replica from following and make it a read/write database


153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
# File 'lib/heroku/command/pg.rb', line 153

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

#upgradeObject

pg:upgrade REPLICA

unfollow a database and upgrade it to the latest PostgreSQL version


420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
# File 'lib/heroku/command/pg.rb', line 420

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

#waitObject

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)


189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
# File 'lib/heroku/command/pg.rb', line 189

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