Restore or Load a Database

Updated:

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.

Was this article helpful?
5 out of 5 found this helpful
Have more questions? Submit a request

Comments

  • Avatar
    Gary Haran

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

  • Avatar
    Tyler Poland

    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

  • Avatar
    Arturo Simon

    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]

  • Avatar
    Tom Hoen

    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. 

  • Avatar
    John Lauck

    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=# 

  • Avatar
    Mateusz Konikowski

    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!

  • Avatar
    Erik Jones

    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.

  • Avatar
    Joshua McClintock

    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?

  • Avatar
    Erik Jones

    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-downloading-database-backups

  • Avatar
    Joshua McClintock

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

  • Avatar
    Dekiru Studios

    How can I import the .dump file into my local test database? The .dump file is not in a human readable format.

  • Avatar
    John Lauck

    pg_restore should work, as mentioned above: 

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

  • Avatar
    Dekiru Studios

    I was getting the following error 

    role "postgres" does not exist

    I got rid of the "-U postgres" and that fixed things. Hope this helps someone else. Thanks for the help John!

  • Avatar
    Imfuna Let

    Just in case its confusing anyone else, the instruction here for loading to another server are missing the

    sudo -i eybackup -e mysql --download N:myapp

     

    bit of the download instructions, which are in the https://support.cloud.engineyard.com/entries/21009872-viewing-and-downloading-database-backups article

     

Please sign in to leave a comment.