Knowledge Base/Engine Yard Cloud Documentation/Manage your Database

Restore or Load a Database

Engine Yard
posted this on February 16, 2012 10:02 AM

Updated: February 13th, 2014

Here are a couple of scenarios for restoring / loading your database:

  • Scenario 1: Your database has become corrupted and you want restore using a recent backup.

  • Scenario 2: You need to move an existing database to a new environment, for example, because:

    • You are upgrading database versions, for example, moving from a PostgreSQL 9.0 environment to a PostgreSQL 9.1 environment.
    • You want a copy of your production database for stress testing on a different non-production environment.
    • You are moving your application and database from another provider to Engine Yard Cloud.

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.

Restore your database (Scenario 1)

This assumes that you are logged into your Master Database instance and you want to overwrite the current database with one of the backups on the instance. Use eybackup to overwrite the existing database with one of the backups on the instance.

MySQL: To restore your database (eybackup method)

  1. Via SSH, connect to the application and database instance (for single server environment) or the master database instance (for a clustered environment).

  2. Type (to list the backups):

    sudo -i eybackup -e mysql --list-backup myapp 
    or
     sudo -i eybackup -e mysql -l myapp 
  3. Type (to restore the backup):

    Important! This command overwrites the current database with the backup. (If you want to keep a copy of the current backup, make an on-demand backup before restoring).

     sudo -i eybackup -e mysql --restore N:myapp 
    or
     sudo -i eybackup -e mysql -r N:myapp 

    where
    N is the number of the backup you want to overwrite the current database. For example, to restore the tenth backup type sudo -i eybackup -e mysql -d 10:myapp

PostgreSQL: To restore your database (eybackup method)

With PostgreSQL, it is not possible to drop a database while there are active connections to it. Because of this, you might need to stop the application before restoring the database (Step 3).

  1. Via SSH, connect to the application and database instance (for single server environment) or the master database instance (for a clustered environment).

  2. Type (to list the backups):

     sudo -i eybackup -e postgresql --list-backup myapp 
    or
     sudo -i eybackup -e postgresql -l myapp 
  3. Stop active connections to the target database.

    a. See how many active connections there are:

    psql -U postgres -t -c "select count(*) from pg_stat_activity where datname='myapp'" 

    b. If there are active connections, stop them by doing the following:

    1. Stop your application by stopping its application server. For example:

      Passenger: sudo /etc/init.d/nginx stop

      or

      Unicorn: sudo monit stop all -g unicorn_appname

    2. Stop any cron tasks that may attempt to run against the database.

      crontab -e shows an edit window.

    3. Manually comment each cron task that uses the database.
    4. Save the file.

      Note: Revert these changes after the backup has been restored.

    5. If you have an active psql console session open against the database, issue \q to logout of it.
  4. Type (to restore the backup):

    Important! This command overwrites the current database with the backup. (If you want to keep a copy of the current backup, make an on-demand backup before restoring).

     sudo -i eybackup -e postgresql --restore N:myapp 
    or
    sudo -i eybackup -e postgresql -r N:myapp 

    (where N is the number of the backup you want to overwrite the current database. For example, to restore the tenth backup type sudo -i eybackup -e postgresql -d 10:myapp)

Load your database (Scenario 2)

This scenario assumes that you are moving data from one environment (or instance) to another. You copy the database backup file to a new database instance and then use a native MySQL command or, for PostgreSQL, a script provided by Engine Yard to load the database backup file.

MySQL: To load your database

  1. Copy the database backup file to the database instance that you want to load it on.

    scp [database file] [username]@[database host]:[target directory]/[filename] 

    where
    [database file] is the name of the database backup file.
    [username] is the user for the database instance. (The default user for the Engine Yard Cloud database is deploy.)
    [database host] is the hostname of the database instance.
    [target directory] is the directory that you want to copy the backup file to.
    [filename] is the name for the file in its new location.

    for example

     scp myapp.2011-11-14T16-47-02.sql.gz deploy@ec2-174-129-17-196.compute-1.amazonaws.com:/tmp/mysql/dumpfile.sql
  2. Via SSH, connect to the application and database instance (for single server environment) or the master database instance (for a clustered environment), and change to the directory where you copied the database backup file in Step 1 (e.g.cd /tmp/mysql).

  3. Import the database backup file to the database:

     gunzip < [filename] | mysql [app_name] 

    where
    [app_name] is the name of the database.
    [filename] is the name of the database backup file.

    for example

     gunzip < myapp.2011-11-14T16-47-02.sql.gz | mysql myapp

PostgreSQL: To load your database

  1. Copy the database backup file to the database instance that you want to load it on.

     scp [database file] [username]@[database host]:[target directory]/[filename] 

    where
    [database file] is the name of the database backup file.
    [username] is the user for the database instance. (The default user for the Engine Yard Cloud database is deploy.) [database host] is the hostname of the database instance.
    [target directory] is the directory that you want to copy the backup file to.
    [filename] is the name for the file in its new location.

    for example

     scp myapp.2011-11-18T12-20-03.pgz deploy@ec2-172-16-139-19.us-west-1.compute.amazonaws.com:/tmp/postgres/dumpfile.pgz 
  2. Via SSH, connect to the application and database instance (for single server environment) or the master database instance (for a clustered environment), and change to the directory where you copied the database backup file in Step 1 (e.g.cd /tmp/postgres).

  3. If your database came from an Engine Yard environment, import the database backup file to the database with this command:

     pg_restore -d [app_name] [filename] --clean -U postgres 

    where
    [app_name] is the name of the database.
    [filename] is the name of the database backup file.
    --clean permits overwriting of the existing database with the backup file.
    -U postgres sets the user to the postgreSQL user who has permission to overwrite the database. (The deploy user does not have these permissions.)

    for example

     pg_restore -d myapp dumpfile.pgz --clean -U postgres 
  4. If your database is not from an Engine Yard environment, import the database backup file to the 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, make sure 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 database backup file

    for example

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

More information

For more information about...See...
How to download a backup file View and download database backups.
On-demand backup of the database Back up the database.
SSHing into an instance Connect to your instance via SSH.
Finding the password for your database Find key information about your database.

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

 

Comments

User photo
Gary Haran
Qwalify

How do you download a database from one environment (it's a .dump file) and feed it to another environment?

May 01, 2012 11:37 AM
User photo
Tyler Poland
Engine Yard Inc.

Hi Gary,

The download and reload process is the same as described. The change is that you have to download it in the source environment, copy the file from one environment to the other using a tool such as `scp`, and then load it in the destination environment.

Hope this helps,

Tyler

May 03, 2012 12:54 PM
User photo
Arturo Simon
summit-appliance

If i may suggest for Scenario 2 MySQL: Load your database. Document could also show how to import a .sql file, not just a .sql.gz file.

I ran into a situation where my dump file ended in .sql and couldn't import using the command. Had to modify it, replaced 'gunzip' with 'cat'

gunzip < [filename] | mysql -u [username] -p -h [database host] [app_name] 

to

cat < [filename] | mysql -u [username] -p -h [database host] [app_name] 
August 13, 2012 10:47 AM
User photo
Tom Hoen
GiveCorps

You should also keep in mind that the restore operation described here will only drop and create tables that are in the backup. So if you had run a migration that created a table, then restored from a backup that was taken prior to the creation of the table, that table will still exist in your restored database. 

If you then proceed to rerun migrations, they will fail when attempting to run the create table migration, as the table already exists. 

In this case, you need to drop and recreate the database before restoring. 

November 01, 2012 03:09 AM
User photo
John Lauck
roadtrippers

Trying to import a database in PostgreSQL 9.2.4 and EY stack v4 and I get a permission denied error.  I have to specify the host.

For ex:

$ psql -U postgres
psql: could not connect to server: Permission denied
Is the server running locally and accepting
connections on Unix domain socket "/run/postgresql/.s.PGSQL.5432"? 

 

$ psql -U postgres -h 127.0.0.1
psql (9.2.4)
Type "help" for help.

postgres=# 

August 18, 2013 06:11 PM
User photo
Mateusz Konikowski
Freeport Metrics

I had the same problem making db_dump:

$ pg_dump db_name > dump_file_name
  pg_dump: [archiver (db)] connection to database "db_name" failed: could not connect to server: Permission denied
  Is the server running locally and accepting
  connections on Unix domain socket "/run/postgresql/.s.PGSQL.5432"?

 

It worked after adding -h:

$ pg_dump <db_name> -h 127.0.0.1  > dump_file_name

 

John Lauck - thanks for the hint!

September 03, 2013 07:54 AM
User photo
Erik Jones
Engine Yard Inc.

Hi folks,

The issue with not being able to connect to PostgreSQL servers from localhost was an issue with the read permissions on the PostgreSQL's socket directory that has been fixed in a recent release by adding your system user to the postgres system group.  If you are current on your Environment's stack upgrades then, please, contact our Support team with the details of which environment is still showing the problem.

September 03, 2013 09:37 AM
User photo
Joshua McClintock
Guiding-Cancer

If the DB is in 'parts' 

 

company_name.date.sql.gpz.part1 and part2

 

Do I just cat part2 >>  part1 and then do my gpg operation?

February 26, 2014 08:37 AM
User photo
Erik Jones
Engine Yard Inc.

Hi Joshua,

No, the parts are split using Ruby file operations that include EOF characters at the end of each part, which means that simply cat'ing them won't work correctly.  If your backups are big enough that we are splitting them into parts then you will need to use the eybackup method of downloading the dumps to your database instance described here: https://support.cloud.engineyard.com/entries/21009872-viewing-and-d...

February 26, 2014 08:57 AM
User photo
Joshua McClintock
Guiding-Cancer

Thank you, it's downloading now according to the instructions you sent me :)

February 26, 2014 09:36 AM