Updated: February 14th, 2014
Read this page if you want to use a PostgreSQL database with Engine Yard.
Getting started with PostgreSQL
PostgreSQL 9.3.x is the default database; it includes new materialized views, auto-updatable simple views, new JSON data type features (e.g., operators and functions), event triggers, and more exciting features. See PostgreSQL 9.3 Release Notes for more info.
The process for setting up and running your application on PostgreSQL depends on your current environment.
Is this a new application? Then do these tasks:
Are you running with PostgreSQL on a platform other than Engine Yard Cloud? Then do these tasks:
Are you running Engine Yard Cloud with a different version of PostgreSQL now? Then do these tasks:
Are you currently using MySQL? Then:
Consult with Engine Yard Professional Services for assistance migrating your MySQL database to PostgreSQL.
Tip: Always test in a development or staging environment first.
Set up an environment to use PostgreSQL
Follow this procedure to set up a new environment that uses a PostgreSQL database. You can create a new application with a new environment or a new environment with an existing application.
If you are migrating an existing PostgreSQL database, you'll need to perform a dump and restore after creating a new environment (Dump and restore an existing PostgreSQL database).
To use PostgreSQL with Engine Yard Cloud
Create a new application with a new environment or add an environment to an existing application, making sure that:
The Database Stack is set to PostgreSQL.
You add the pg gem to your Gemfile for Rails 3 (or via the Dashboard for Rails 2).
source "http://rubygems.org" gem "pg"
Deploy the application.
Dump and restore an existing PostgreSQL database
If you are currently running a PostgreSQL database (9.0.x, 9.1.x, 9.2.x), you need to dump the database, create a new PostgreSQL environment in Engine Yard Cloud, move the database dump file to the database instance, and finally load the file into the PostgreSQL database.
You can perform these tasks yourself (as outlined below) or ask Engine Yard Professional Services to do the migration for you.
To dump and restore the PostgreSQL database
See the PostgreSQL documentation for full details on dumping and restoring a database.
Note: The following commands assume you are logged into the db_master instance.
Dump the database.
pg_dump -Fc dbname > dumpfile
Note: -Fc is needed to use PostgreSQL's custom dump format and compression (use the -o option only if your application explicitly references OID values).
Move the output file to the new server.
scp dumpfile newserver:/path/to/file/dumpfile
In this case, the new server is the database instance assigned for your PostgreSQL environment.
Note: To use the scp command, you need keys and scp setup.
SSH to the database instance.
If your database was dumped from an Engine Yard environment, import the output file to the new PostgreSQL database with this command:
pg_restore -d dbname dumpfile
Note: The dbname should correspond to the database name of your application.
If your database was dumped from a non-Engine Yard environment, import the output file to the new PostgreSQL database using a script (load_foreign_postgres_db.sh) that Engine Yard provides on the instance. This script works with dumps created using pg_dump, both normal SQL text dumps (if the file is compressed, you need to decompress it first) and dumps created using the pg_dump custom format.
WARNING! The script drops and recreates the database named [app_name]. The script assigns ownership of all non-system tables, views, sequences, and functions in the restored database to the deploy user. If the target database has data that you do not want to lose or if you need a more customized restore, submit a ticket with Engine Yard Support.
sudo /engineyard/bin/load_foreign_postgres_db.sh [filename] [app_name]
[app_name]is the name of the database
[filename]is the name of the dump file
sudo /engineyard/bin/load_foreign_postgres_db.sh dumpfile.pgz dbname
Test the application running in the new environment before deleting your original environment.
Connect to your PostgreSQL database
To connect to your PostgreSQL database
See the Managing your database documentation. (Many of the tasks apply to PostgreSQL as well as MySQL.)
Find your generated PostgreSQL password (the MySQL instructions on passwords describe this step).
Extract list of databases:
psql -l -U deploy
Connect to your database:
psql -U deploy -h localhost -d dbname
|For more information about...||See...|
|Configuring and deploying an Engine Yard Cloud application in general||Engine Yard Cloud Documentation|
|Database tasks||Manage your Database|
|PostgreSQL 9.3 Release Notes||PostgreSQL 9.3 documentation|
|PostgreSQL versioning policies||PostgreSQL Versioning Policies|
|Engine Yard DB upgrade policies||Database Version Upgrade Policies|
If you have feedback or questions about this page, add a comment below. If you need help, submit a ticket with Engine Yard Support.