Skip to content

Latest commit

 

History

History
1060 lines (719 loc) · 27.6 KB

pg.md

File metadata and controls

1060 lines (719 loc) · 27.6 KB
Raw

heroku pg

manage postgresql databases

heroku pg [DATABASE]

show database information

show database information


USAGE
  $ heroku pg [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:backups

list database backups

list database backups


USAGE
  $ heroku pg:backups

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:backups:cancel [BACKUP_ID]

cancel an in-progress backup or restore (default newest)

cancel an in-progress backup or restore (default newest)


USAGE
  $ heroku pg:backups:cancel [BACKUP_ID]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:backups:capture [DATABASE]

capture a new backup

capture a new backup


USAGE
  $ heroku pg:backups:capture [DATABASE]

OPTIONS
  -a, --app=app                  (required) app to run command against
  -r, --remote=remote            git remote of app to use
  -v, --verbose
  --wait-interval=wait-interval

heroku pg:backups:delete BACKUP_ID

delete a backup

delete a backup


USAGE
  $ heroku pg:backups:delete BACKUP_ID

OPTIONS
  -a, --app=app          (required) app to run command against
  -c, --confirm=confirm
  -r, --remote=remote    git remote of app to use

heroku pg:backups:download [BACKUP_ID]

downloads database backup

downloads database backup


USAGE
  $ heroku pg:backups:download [BACKUP_ID]

OPTIONS
  -a, --app=app        (required) app to run command against
  -o, --output=output  location to download to. Defaults to latest.dump
  -r, --remote=remote  git remote of app to use

heroku pg:backups:info [BACKUP_ID]

get information about a specific backup

get information about a specific backup


USAGE
  $ heroku pg:backups:info [BACKUP_ID]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:backups:restore [BACKUP] [DATABASE]

restore a backup (default latest) to a database

restore a backup (default latest) to a database
defaults to saving the latest database to DATABASE_URL

USAGE
  $ heroku pg:backups:restore [BACKUP] [DATABASE]

OPTIONS
  -a, --app=app                  (required) app to run command against
  -c, --confirm=confirm
  -e, --extensions=extensions    comma-separated list of extensions to pre-install in the public schema
  -r, --remote=remote            git remote of app to use
  -v, --verbose
  --wait-interval=wait-interval

DESCRIPTION
  defaults to saving the latest database to DATABASE_URL

heroku pg:backups:schedule [DATABASE]

schedule daily backups for given database

schedule daily backups for given database


USAGE
  $ heroku pg:backups:schedule [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

  --at=at              (required) at a specific (24h) hour in the given timezone. Defaults to UTC. --at '[HOUR]:00
                       [TIMEZONE]'

heroku pg:backups:schedules

list backup schedule

list backup schedule


USAGE
  $ heroku pg:backups:schedules

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:backups:unschedule [DATABASE]

stop daily backups

stop daily backups


USAGE
  $ heroku pg:backups:unschedule [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:backups:url [BACKUP_ID]

get secret but publicly accessible URL of a backup

get secret but publicly accessible URL of a backup


USAGE
  $ heroku pg:backups:url [BACKUP_ID]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:bloat [DATABASE]

show table and index bloat in your database ordered by most wasteful

show table and index bloat in your database ordered by most wasteful


USAGE
  $ heroku pg:bloat [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:blocking [DATABASE]

display queries holding locks other queries are waiting to be released

display queries holding locks other queries are waiting to be released


USAGE
  $ heroku pg:blocking [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:connection-pooling:attach [DATABASE]

add an attachment to a database using connection pooling

add an attachment to a database using connection pooling
Example:

  heroku pg:connection-pooling:attach postgresql-something-12345


USAGE
  $ heroku pg:connection-pooling:attach [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use
  --as=as              name for add-on attachment

DESCRIPTION
  Example:

     heroku pg:connection-pooling:attach postgresql-something-12345

heroku pg:copy SOURCE TARGET

copy all data from source db to target

copy all data from source db to target
at least one of the databases must be a Heroku PostgreSQL DB

USAGE
  $ heroku pg:copy SOURCE TARGET

OPTIONS
  -a, --app=app                  (required) app to run command against
  -r, --remote=remote            git remote of app to use
  --confirm=confirm
  --verbose
  --wait-interval=wait-interval

DESCRIPTION
  at least one of the databases must be a Heroku PostgreSQL DB

heroku pg:credentials [DATABASE]

show information on credentials in the database

show information on credentials in the database


USAGE
  $ heroku pg:credentials [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:credentials:create [DATABASE]

create credential within database

create credential within database
Example:

    heroku pg:credentials:create postgresql-something-12345 --name new-cred-name


USAGE
  $ heroku pg:credentials:create [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -n, --name=name      (required) name of the new credential within the database
  -r, --remote=remote  git remote of app to use

DESCRIPTION
  Example:

       heroku pg:credentials:create postgresql-something-12345 --name new-cred-name

heroku pg:credentials:destroy [DATABASE]

destroy credential within database

destroy credential within database
Example:

    heroku pg:credentials:destroy postgresql-transparent-56874 --name cred-name -a woodstock-production


USAGE
  $ heroku pg:credentials:destroy [DATABASE]

OPTIONS
  -a, --app=app          (required) app to run command against
  -c, --confirm=confirm
  -n, --name=name        (required) unique identifier for the credential
  -r, --remote=remote    git remote of app to use

DESCRIPTION
  Example:

       heroku pg:credentials:destroy postgresql-transparent-56874 --name cred-name -a woodstock-production

heroku pg:credentials:repair-default [DATABASE]

repair the permissions of the default credential within database

repair the permissions of the default credential within database
Example:

    heroku pg:credentials:repair-default postgresql-something-12345


USAGE
  $ heroku pg:credentials:repair-default [DATABASE]

OPTIONS
  -a, --app=app          (required) app to run command against
  -c, --confirm=confirm
  -r, --remote=remote    git remote of app to use

DESCRIPTION
  Example:

       heroku pg:credentials:repair-default postgresql-something-12345

heroku pg:credentials:rotate [DATABASE]

rotate the database credentials

rotate the database credentials


USAGE
  $ heroku pg:credentials:rotate [DATABASE]

OPTIONS
  -a, --app=app          (required) app to run command against
  -c, --confirm=confirm
  -n, --name=name        which credential to rotate (default credentials if not specified)
  -r, --remote=remote    git remote of app to use
  --all                  rotate all credentials
  --force                forces rotating the targeted credentials

heroku pg:credentials:url [DATABASE]

show information on a database credential

show information on a database credential


USAGE
  $ heroku pg:credentials:url [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -n, --name=name      which credential to show (default credentials if not specified)
  -r, --remote=remote  git remote of app to use

heroku pg:diagnose [DATABASE|REPORT_ID]

run or view diagnostics report

run or view diagnostics report

defaults to DATABASE_URL database if no DATABASE is specified
if REPORT_ID is specified instead, a previous report is displayed


USAGE
  $ heroku pg:diagnose [DATABASE|REPORT_ID]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use
  --json               format output as JSON

DESCRIPTION
  defaults to DATABASE_URL database if no DATABASE is specified
  if REPORT_ID is specified instead, a previous report is displayed

heroku pg:info [DATABASE]

show database information

show database information


USAGE
  $ heroku pg:info [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:kill PID [DATABASE]

kill a query

kill a query


USAGE
  $ heroku pg:kill PID [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -f, --force
  -r, --remote=remote  git remote of app to use

heroku pg:killall [DATABASE]

terminates all connections for all credentials

terminates all connections for all credentials


USAGE
  $ heroku pg:killall [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:links [DATABASE]

lists all databases and information on link

lists all databases and information on link


USAGE
  $ heroku pg:links [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:links:create REMOTE DATABASE

create a link between data stores

create a link between data stores
Example:

    heroku pg:links:create HEROKU_REDIS_RED HEROKU_POSTGRESQL_CERULEAN

USAGE
  $ heroku pg:links:create REMOTE DATABASE

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use
  --as=as              name of link to create

DESCRIPTION
  Example:

       heroku pg:links:create HEROKU_REDIS_RED HEROKU_POSTGRESQL_CERULEAN

heroku pg:links:destroy DATABASE LINK

destroys a link between data stores

destroys a link between data stores
Example:

    heroku pg:links:destroy HEROKU_POSTGRESQL_CERULEAN redis-symmetrical-100

USAGE
  $ heroku pg:links:destroy DATABASE LINK

OPTIONS
  -a, --app=app          (required) app to run command against
  -c, --confirm=confirm
  -r, --remote=remote    git remote of app to use

DESCRIPTION
  Example:

       heroku pg:links:destroy HEROKU_POSTGRESQL_CERULEAN redis-symmetrical-100

heroku pg:locks [DATABASE]

display queries with active locks

display queries with active locks


USAGE
  $ heroku pg:locks [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use
  -t, --truncate       truncates queries to 40 charaters

heroku pg:maintenance [DATABASE]

show current maintenance information

show current maintenance information


USAGE
  $ heroku pg:maintenance [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:maintenance:run [DATABASE]

start maintenance

start maintenance


USAGE
  $ heroku pg:maintenance:run [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -f, --force
  -r, --remote=remote  git remote of app to use

heroku pg:maintenance:window DATABASE WINDOW

set weekly maintenance window

set weekly maintenance window
All times are in UTC.

Example:

    heroku pg:maintenance:window postgres-slippery-100 "Sunday 06:00"

USAGE
  $ heroku pg:maintenance:window DATABASE WINDOW

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

DESCRIPTION
  All times are in UTC.

  Example:

       heroku pg:maintenance:window postgres-slippery-100 "Sunday 06:00"

heroku pg:outliers [DATABASE]

show 10 queries that have longest execution time in aggregate

show 10 queries that have longest execution time in aggregate


USAGE
  $ heroku pg:outliers [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -n, --num=num        the number of queries to display (default: 10)
  -r, --remote=remote  git remote of app to use
  -t, --truncate       truncate queries to 40 characters
  --reset              resets statistics gathered by pg_stat_statements

heroku pg:promote DATABASE

sets DATABASE as your DATABASE_URL

sets DATABASE as your DATABASE_URL


USAGE
  $ heroku pg:promote DATABASE

OPTIONS
  -a, --app=app        (required) app to run command against
  -f, --force
  -r, --remote=remote  git remote of app to use

heroku pg:ps [DATABASE]

view active queries with execution time

view active queries with execution time


USAGE
  $ heroku pg:ps [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use
  -v, --verbose

heroku pg:psql [DATABASE]

open a psql shell to the database

open a psql shell to the database


USAGE
  $ heroku pg:psql [DATABASE]

OPTIONS
  -a, --app=app            (required) app to run command against
  -c, --command=command    SQL command to run
  -f, --file=file          SQL file to run
  -r, --remote=remote      git remote of app to use
  --credential=credential  credential to use

heroku pg:pull SOURCE TARGET

pull Heroku database into local or remote database

pull Heroku database into local or remote database
Pull from SOURCE into TARGET.

TARGET must be one of:
  * a database name (i.e. on a local PostgreSQL server)  => TARGET must not exist and will be created
  * a fully qualified URL to a local PostgreSQL server   => TARGET must not exist and will be created
  * a fully qualified URL to a remote PostgreSQL server  => TARGET must exist and be empty

To delete a local database run `dropdb TARGET`
To create an empty remote database, run `createdb` with connection command-line options (run `createdb --help` for details).

Examples:

    # pull Heroku DB named postgresql-swimmingly-100 into local DB mylocaldb that must not exist
    $ heroku pg:pull postgresql-swimmingly-100 mylocaldb --app sushi

    # pull Heroku DB named postgresql-swimmingly-100 into empty remote DB at postgres://myhost/mydb
    $ heroku pg:pull postgresql-swimmingly-100 postgres://myhost/mydb --app sushi


USAGE
  $ heroku pg:pull SOURCE TARGET

OPTIONS
  -a, --app=app                            (required) app to run command against
  -r, --remote=remote                      git remote of app to use
  --exclude-table-data=exclude-table-data  tables for which data should be excluded (use ';' to split multiple names)

DESCRIPTION
  Pull from SOURCE into TARGET.

  TARGET must be one of:
     * a database name (i.e. on a local PostgreSQL server)  => TARGET must not exist and will be created
     * a fully qualified URL to a local PostgreSQL server   => TARGET must not exist and will be created
     * a fully qualified URL to a remote PostgreSQL server  => TARGET must exist and be empty

  To delete a local database run `dropdb TARGET`
  To create an empty remote database, run `createdb` with connection command-line options (run `createdb --help` for 
  details).

  Examples:

       # pull Heroku DB named postgresql-swimmingly-100 into local DB mylocaldb that must not exist
       $ heroku pg:pull postgresql-swimmingly-100 mylocaldb --app sushi

       # pull Heroku DB named postgresql-swimmingly-100 into empty remote DB at postgres://myhost/mydb
       $ heroku pg:pull postgresql-swimmingly-100 postgres://myhost/mydb --app sushi

heroku pg:push SOURCE TARGET

push local or remote into Heroku database

push local or remote into Heroku database
Push from SOURCE into TARGET. TARGET must be empty.

To empty a Heroku database for push run `heroku pg:reset`

SOURCE must be either the name of a database existing on your localhost or the
fully qualified URL of a remote database.

Examples:

    # push mylocaldb into a Heroku DB named postgresql-swimmingly-100
    $ heroku pg:push mylocaldb postgresql-swimmingly-100

    # push remote DB at postgres://myhost/mydb into a Heroku DB named postgresql-swimmingly-100
    $ heroku pg:push postgres://myhost/mydb postgresql-swimmingly-100


USAGE
  $ heroku pg:push SOURCE TARGET

OPTIONS
  -a, --app=app                            (required) app to run command against
  -r, --remote=remote                      git remote of app to use
  --exclude-table-data=exclude-table-data  tables for which data should be excluded (use ';' to split multiple names)

DESCRIPTION
  Push from SOURCE into TARGET. TARGET must be empty.

  To empty a Heroku database for push run `heroku pg:reset`

  SOURCE must be either the name of a database existing on your localhost or the
  fully qualified URL of a remote database.

  Examples:

       # push mylocaldb into a Heroku DB named postgresql-swimmingly-100
       $ heroku pg:push mylocaldb postgresql-swimmingly-100

       # push remote DB at postgres://myhost/mydb into a Heroku DB named postgresql-swimmingly-100
       $ heroku pg:push postgres://myhost/mydb postgresql-swimmingly-100

heroku pg:reset [DATABASE]

delete all data in DATABASE

delete all data in DATABASE


USAGE
  $ heroku pg:reset [DATABASE]

OPTIONS
  -a, --app=app                (required) app to run command against
  -c, --confirm=confirm
  -e, --extensions=extensions  comma-separated list of extensions to pre-install in the public schema
  -r, --remote=remote          git remote of app to use

heroku pg:settings [DATABASE]

show your current database settings

show your current database settings


USAGE
  $ heroku pg:settings [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:settings:log-lock-waits [VALUE] [DATABASE]

Controls whether a log message is produced when a session waits longer than the deadlock_timeout to acquire a lock. deadlock_timeout is set to 1 second

Controls whether a log message is produced when a session waits longer than the deadlock_timeout to acquire a lock. deadlock_timeout is set to 1 second
Delays due to lock contention occur when multiple transactions are trying to access the same resource at the same time.
Applications and their query patterns should try to avoid changes to many different tables within the same transaction.

USAGE
  $ heroku pg:settings:log-lock-waits [VALUE] [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

DESCRIPTION
  Delays due to lock contention occur when multiple transactions are trying to access the same resource at the same 
  time.
  Applications and their query patterns should try to avoid changes to many different tables within the same 
  transaction.

heroku pg:settings:log-min-duration-statement [VALUE] [DATABASE]

The duration of each completed statement will be logged if the statement completes after the time specified by VALUE.

The duration of each completed statement will be logged if the statement completes after the time specified by VALUE.
VALUE needs to specified as a whole number, in milliseconds.
Setting log_min_duration_statement to zero prints all statement durations and -1 will disable logging statement durations.

USAGE
  $ heroku pg:settings:log-min-duration-statement [VALUE] [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

DESCRIPTION
  VALUE needs to specified as a whole number, in milliseconds.
  Setting log_min_duration_statement to zero prints all statement durations and -1 will disable logging statement 
  durations.

heroku pg:settings:log-statement [VALUE] [DATABASE]

log_statement controls which SQL statements are logged.

log_statement controls which SQL statements are logged.
Valid values for VALUE:
none - No statements are logged
ddl  - All data definition statements, such as CREATE, ALTER and DROP will be logged
mod  - Includes all statements from ddl as well as data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, COPY
all  - All statements are logged

USAGE
  $ heroku pg:settings:log-statement [VALUE] [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

DESCRIPTION
  Valid values for VALUE:
  none - No statements are logged
  ddl  - All data definition statements, such as CREATE, ALTER and DROP will be logged
  mod  - Includes all statements from ddl as well as data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, 
  COPY
  all  - All statements are logged

heroku pg:settings:track-functions [VALUE] [DATABASE]

track_functions controls tracking of function call counts and time used. Default is none.

track_functions controls tracking of function call counts and time used. Default is none.
Valid values for VALUE:
none - No functions are tracked
pl   - Only procedural language functions are tracked
all  - All functions, including SQL and C language functions, are tracked. Simple SQL-language that are inlined are not tracked

USAGE
  $ heroku pg:settings:track-functions [VALUE] [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

DESCRIPTION
  Valid values for VALUE:
  none - No functions are tracked
  pl   - Only procedural language functions are tracked
  all  - All functions, including SQL and C language functions, are tracked. Simple SQL-language that are inlined are 
  not tracked

heroku pg:unfollow DATABASE

stop a replica from following and make it a writeable database

stop a replica from following and make it a writeable database


USAGE
  $ heroku pg:unfollow DATABASE

OPTIONS
  -a, --app=app          (required) app to run command against
  -c, --confirm=confirm
  -r, --remote=remote    git remote of app to use

heroku pg:upgrade [DATABASE]

unfollow a database and upgrade it to the latest stable PostgreSQL version

unfollow a database and upgrade it to the latest stable PostgreSQL version
to upgrade to another PostgreSQL version, use pg:copy instead

USAGE
  $ heroku pg:upgrade [DATABASE]

OPTIONS
  -a, --app=app          (required) app to run command against
  -c, --confirm=confirm
  -r, --remote=remote    git remote of app to use
  -v, --version=version  PostgreSQL version to upgrade to

DESCRIPTION
  to upgrade to another PostgreSQL version, use pg:copy instead

heroku pg:vacuum-stats [DATABASE]

show dead rows and whether an automatic vacuum is expected to be triggered

show dead rows and whether an automatic vacuum is expected to be triggered


USAGE
  $ heroku pg:vacuum-stats [DATABASE]

OPTIONS
  -a, --app=app        (required) app to run command against
  -r, --remote=remote  git remote of app to use

heroku pg:wait [DATABASE]

blocks until database is available

blocks until database is available


USAGE
  $ heroku pg:wait [DATABASE]

OPTIONS
  -a, --app=app                  (required) app to run command against
  -r, --remote=remote            git remote of app to use
  --no-notify                    do not show OS notification
  --wait-interval=wait-interval  how frequently to poll in seconds (to avoid rate limiting)