Use PostgreSQL with Engine Yard

Updated: September 09, 2015 13:13

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.

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

Introduction

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.

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.