Heroku Postgres is Heroku’s production database-as-a-service. It is available as a stand-alone database service or as part of the Heroku Add-Ons catalog. It complements Heroku’s free shared database service, which also provides PostgreSQL.
- Are superuser permissions available on databases?
-
No, Heroku Postgres users do not have superuser permissions.
- Can I create multiple databases on a single plan?
-
No. Each Heroku Postgres plan provides you with a single database. You must purchase separate database plans if you require more than one database.
- Can multiple databases be created on a single plan
-
No. Each plan provides a single database. Users do not have permission to create additional databases. If you require multiple databases, purchase additional plans.
- Can multiple Heroku Applications Connect to a single database?
-
Yes. You can configure multiple applications running on Heroku to connect to a single database, provided that you have the databases credentials. Simply override the DATABASE_URL of the apps that you wish to connect using the heroku config:add DATABASE_URL=... command.
- Connecting to Heroku Postgres with JDBC
-
To connect to a dedicated Heroku Postgres from a Java application that is running on your local machine your JDBC connection URL will need to have the following parameters:
jdbc:postgresql://host/database?user=user&password=password&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
If you leave off ssl=true you will get a connection error. If you leave off sslfactory=org.postgresql.ssl.NonValidatingFactory you may get an error like:
unable to find valid certification path to requested target
- Differences between a standalone vs add-ons database?
-
Databases provisioned to be standalone vs through the Heroku Add-On catalog are fundamentally the same. They are served by exactly the same backend infrastructure. The following are a few minor user experience differences between the two:
- Standalone and add-ons databases are denoted with different icons on the database list of https://postgres.heroku.com.
- Automatic monthly snapshots (i.e. backups) are automatically enabled for standalone databases. They must be manually enabled when database are provisioned as an add-on.
- Standalone databases do not appear through our command-line client.
- Does Heroku Postgres support multiple schemas?
-
Yes, Heroku Postgres supports multiple schemas.
The most common use case for using multiple schemas in a database is building a software-as-a-service application wherein each customer has their own schema. Although we do not place any limits on the number of schemas that you create, we have seen that a large number of schemas (> 1000) can severely impact the performance of many Heroku Postgres tools, including database snapshots (pg backups), fork, and follow.
- How Do Heroku Apps Store and Manage Database Credentials
-
Heroku stores database credentials (postgres://user:password@host:port/dbname) in config vars. On Heroku, the DATABASE_URL config vars will be used by Ruby on Rails applications as the default database. You can have many databases attached to a single application (to add more than one, simply repeat the heroku addons:add heroku-postgresql command). Heroku assigns each dedicated database a unique “color” so that you can distinguish between them. The color doesn’t have any special meaning.
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. If a database is not specified, most commands will use the database referred to by DATABASE_URL as a default. For example, to use a specific database with the pg:info command:
heroku pg:info HEROKU_POSTGRESQL_RED
- How do I connect to Heroku Postgres?
-
Databases on Heroku Postgres accept standard PostgreSQL connections on Port 5432. Connections can be made from PostgreSQL client applications (such as psql, or PGAdmin), Application Frameworks (such as Ruby-on-Rails or Django), or any other libpq compatible client.
- Is full text search available?
-
Yes, full-text search in built into PostgreSQL and available with Heroku Postgres. The following dictionaries are installed:
djpuk6jfxx11w8=> \dFd
List of text search dictionaries
Schema | Name | Description
------------+-----------------+-------------------------------
pg_catalog | danish_stem | snowball stemmer for danish language
pg_catalog | dutch_stem | snowball stemmer for dutch language
pg_catalog | english_stem | snowball stemmer for english language
pg_catalog | finnish_stem | snowball stemmer for finnish language
pg_catalog | french_stem | snowball stemmer for french language
pg_catalog | german_stem | snowball stemmer for german language
pg_catalog | hungarian_stem | snowball stemmer for hungarian language
pg_catalog | italian_stem | snowball stemmer for italian language
pg_catalog | norwegian_stem | snowball stemmer for norwegian language
pg_catalog | portuguese_stem | snowball stemmer for portuguese language
pg_catalog | romanian_stem | snowball stemmer for romanian language
pg_catalog | russian_stem | snowball stemmer for russian language
pg_catalog | simple | simple dictionary: just lower case and check for stopword
pg_catalog | spanish_stem | snowball stemmer for spanish language
pg_catalog | swedish_stem | snowball stemmer for swedish language
pg_catalog | turkish_stem | snowball stemmer for turkish language
(16 rows)
- Is hStore Available?
-
PostgreSQL hStore is available in limited private testing. Please contact us if you are interested.
- Is PostGIS available?
-
PostGIS is available in limited private testing. Please contact us if you are interested.
- What are the permissions for databases and users?
-
Heroku Postgres users are granted all non-superuser permissions on their database. These include SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.
The SQL used to create users and databases is:
CREATE ROLE user_name;
ALTER ROLE user_foo WITH LOGIN UNENCRYPTED PASSWORD
'password' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE DATABASE database_name OWNER user_name;
REVOKE ALL ON DATABASE database_name FROM PUBLIC;
GRANT CONNECT ON DATABASE database_name TO database_user;
GRANT ALL ON DATABASE database_name TO database_user;