PostgreSQL
Table of Contents
Heroku provides each app with a full-featured PostgreSQL database. For Rails apps, database connectivity is configured automatically – your app should just work. For apps that use the DataMapper or Sequel ORMs, the DATABASE_URL environment variable contains database connection details.
There are multiple database packages to choose from, and you can change plans at any time. Database redundancy and failover is managed by Heroku’s infrastructure regardless of plan.
Migrating from a shared to a dedicated database is a simple, user-managed process.
You can have multiple databases attached to a single Heroku app, but only one database from each of our plans.
Shared vs. Dedicated
Heroku offers you the choice of running on a shared or dedicated database package. The shared plan is suitable for development and staging applications. It runs PostgreSQL 8.3. The dedicated plans are suitable for production scale applications. In addition, the dedicated databases offer a number of advantages, including direct access (via psql or any native postgresql library), stored procedures, and PostgreSQL 9 support.
New apps created on Heroku automatically have a shared database installed.
Performance
Database performance on Heroku is measured by connections, memory, and CPU Units. Connections are the number of processes (dynos + workers + external connections) which can connect to a database simultaneously. Memory is the amount of physical RAM available to your database; loading your entire database into memory offers significant performance advantages. CPU Units are a measure of the the processor speed and overall IO performance of the underlying machine.
Note that CPU units are only a measure of database performance. Regardless of which database you choose, each dyno is its own independent grid process.
Regardless of which database you choose, your data is always protected, accessible only by your own application.
Backups
See PG Backups for information on backing up your database.
Database URLs
Each database installed on a Heroku app has a unique database URL which can be used by your app to connect to that database. The URLs are viewable and accessible through config vars. The config var name for the default shared database is SHARED_DATABASE_URL.
In addition, each app has a DATABASE_URL config var; this is the the database that Rails will use by default. You can change which database is set to DATABASE_URL with the heroku pg:promote command.
Using the DATABASE_URL environment variable (Rails)
To ease deployment of typical Rails applications, Heroku automatically generates a new database.yml file on deployment. This auto-generated database.yml file configures your RACK_ENV environment to your selected postgres db. This enables you to simply push a Rails app up to Heroku and have it automatically connect to your database.
$ heroku console
Ruby console for myapp.heroku.com
>> ENV['DATABASE_URL']
=> "postgres://username:password@hostname/database"
And the resulting database.yml:
production:
encoding: unicode
adapter: postgresql
username: username
port: 5432
host: hostname
database: database
password: password
If you would like to have your rails application connect to a non-Heroku provided database, you can take advantage of this same mechanism. Simply set your DATABASE_URL config var to point to any cloud-accessible database, and Heroku will automatically create your database.yml file to point to your chosen server. The Amazon RDS add-on does this for you automatically, though you can also use this same method to connect to non-RDS databases as well.
Using the DATABASE_URL environment variable (Sequel / DataMapper)
Your database credentials and connection details can be obtained from the DATABASE_URL environment variable. This is especially useful for ORMs like DataMapper and Sequel that use database URLs / connection strings instead of files for configuration.
A popular idiom is to use the value of the DATABASE_URL environment variable when defined, but fallback on a default database URL otherwise.
Configuring Sequel:
Sequel.connect(ENV['DATABASE_URL'] || 'sqlite://my.db')
Configuring DataMapper:
DataMapper.setup(:default, ENV['DATABASE_URL'] || 'sqlite3://my.db')
Common Issues Migrating to PostgreSQL
Below are a list of common mistakes that might result in problems when migrating your Rails app from SQLite or MySQL to PostgreSQL:
Invalid operator
PGError: ERROR: operator does not exist: character varying = integer
Cause: PostgreSQL is more sensitive with data types than MySQL or SQlite. PostgreSQL will check and throw errors when an operator is applied to an unsupported data type. For instance, you can’t compare strings with integers without casting.
Solution: Make sure the operator is adequate for the data type. ActiveRecord does this automatically when you use an interpolated condition form.
Array conditions:
:conditions => ['column1 = ? AND column2 = ?', value1, value2]
Hash conditions:
:conditions => { :column1 => value1, :column2 => value2 }
Binary field limit
Cause: PostgreSQL doesn’t limit binary fields. Any migrations adding a :binary field with the :limit option will raise a syntax error.
Solution: Omit the :limit for binary fields if possible – or test the DB before running it (see Testing the Database below).
Table doesn’t exist
PGError: ERROR: relation "documents" does not exist
Cause: This is the standard message displayed by Postgres when a table doesn’t exists. That means your query is referencing a table that is not on the database.
Solution: Make sure your migrations ran normally, and that you’re referencing a table that exists.
You can see what tables you have running the following command on heroku
console:
>> ActiveRecord::Base.connection.tables
Cannot change column type
PGError: ERROR: column "verified_at" cannot be cast to type "date"
Cause: PostgreSQL doesn’t know how to cast all the rows in that table to the specified type. Most likely it means you have an integer or a string in that column.
Solution: Inspect your records and make sure they can be converted to the new type. Sometimes it’s easier to just avoid using change_column, renaming/creating a new column instead.
Testing the Database
To fix database-specific queries where ActiveRecord won’t help (for instance, foreign keys), the only solution is to test the database before running a migration or query:
case ActiveRecord::Base.connection.adapter_name
when 'SQLite'
execute 'sqlite specific query'
when 'PostgreSQL'
execute 'postgres specific query'
else
raise 'Migration not implemented for this DB adapter'
end