PostgreSQL at Engine Yard

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:

  • Set up an application to use PostgreSQL

• Are you running with PostgreSQL on a platform other than Engine Yard Cloud? Then do these tasks:

  • Set up an application to use PostgreSQL
  • Dump and restore an existing PostgreSQL database.

• Are you running Engine Yard Cloud with a different version of PostgreSQL now? Then do these tasks:

  • Set up an application to use PostgreSQL
  • Dump and restore an existing PostgreSQL database

• 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.

Note: You cannot upsize a 32-bit PostgreSQL instance to a 64-bit instance. See the known issue for more information. If you need help, submit a ticket with Engine Yard Support.

Set up an environment to use PostgreSQL

Introduction

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

1. 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"

2. 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.

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.

1. 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).

2. 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.

3. SSH to the database instance.

4. 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.

5. 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]

   where
   [app_name] is the name of the database
   [filename] is the name of the dump file

   for example

   sudo /engineyard/bin/load_foreign_postgres_db.sh dumpfile.pgz dbname

6. 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.)

1. Find your generated PostgreSQL password (the MySQL instructions on passwords describe this step).

2. Extract list of databases:

    psql -l -U deploy 

3. Connect to your database:

    psql -U deploy -h localhost -d dbname 

More information

If you have feedback or questions about this page, add a comment below. If you need help, submit a ticket with Engine Yard Support.