Postgres
Last updated December 06, 2023
This is a draft article - the text and URL may change in the future. This article is unlisted. Only those with the link can access it.
Table of Contents
Heroku Shared PostgreSQL is a multi-tenant PostgreSQL database service for Heroku applications. The service is provided and maintained by Heroku.
Please note that this is an experimental product, and as such, is subject to unplanned maintenance and downtime. Users should not entrust their production data to this environment.
Installation
Install the Heroku Shared PostgreSQL addon:
:::term
$ heroku addons:create postgres
-----> Adding postgres to sushi... done, v196 (free)
Attached as POSTGRES_COPPER
Database creation complete
Specifying and Promoting Databases
Heroku stores database credentials (URL, database name, username, and password)
in config vars. On Heroku
the DATABASE_URL
config vars will be used as the default database.
Most database commands allow you to specify on which database the command will
operate. To see a list of available databases, use heroku pg:info
. You can
also specify a database with most commands, for example:
:::term
$ heroku pg:info POSTGRES_COPPER
Promotion
The pg:promote
command will set a particular database to the application
default by assigning it to the DATABASE_URL
:
:::term
$ heroku pg:promote POSTGRES_COPPER
-----> Promoting POSTGRES_COPPER to DATABASE_URL... done
Outside Connections
In addition to being available to the Heroku runtime, databases can be accessed directly by clients running elsewhere. All connections require SSL.
From other Heroku Applications
You can configure other applications running on Heroku to connect to your
database, provided that you have the database credentials. Simply override
the DATABASE_URL
of the other application to your desired database. Your
connection string details are available with pg:ingress
:
:::term
$ heroku pg:ingress POSTGRES_COPPER
Connection info string:
"dbname=evening_river_6980 host=ec2-57-31-9-2.compute-1.amazonaws.com
port=5432 user=kmwpocwjo password=3yXlSTyUAd453Xp1bMOp sslmode=require"
PSQL Console
If you have PostgreSQL installed on your local system, you can open a direct psql console to your remote database:
:::term
$ heroku pg:psql POSTGRES_COPPER
-----> Connecting to POSTGRES_COPPER... done
psql (9.1.0, server 9.1.2)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
evening_river_6980=>
If you do not specify a database, pg:psql
will use your DATABASE_URL
by
default.
Monitoring
You can get stats on your database with the pg:info
command:
:::term
$ heroku pg:info POSTGRES_COPPER
=== POSTGRES_COPPER
Status: ok
Tables: 5
Bytes: 81920
Connections: 6
Idle connections: 5
Idle connections in txn: 0
Active connections: 1
To continuously monitor the status of your database, pass pg:info
through the
Unix watch command:
Destroying a Database
In order to destroy a database, you need to remove the addon. If you have more
than one of the addon databases installed you will need to specify the
database config var. For example to remove POSTGRES_COPPER
,
you would run:
:::term
$ heroku addons:destroy POSTGRES_COPPER
-----> Removing POSTGRES_COPPER from sushi... done, v200 (free)