Restore or Load a Database

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.
  • Scenario 3: You want to use `eyrestore` to restore a backup from one environment in a different environment to:
    • Verifying your backup/restore integrity.
    • Update data in staging with data from production.
    • Migrate from an older stack version to a newer stack version.
    • Migrate to a newer major version of your database.

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 stored in S3. Note: in most cases the database name will be the same as the application name.

MySQL: To restore your database (eybackup method)

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

  2. Type (to list the backups):

    sudo -i eybackup -e mysql --list-backup [db_name] 
    or
     sudo -i eybackup -e mysql -l [db_name] 
  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:[db_name] 
    or
     sudo -i eybackup -e mysql -r N:[db_name]

    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:[db_name]

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 master database instance (for a clustered environment) or the application and database instance (for single server environment).

  2. Type (to list the backups):

    sudo -i eybackup -e postgresql --list-backup [db_name]
    or
    sudo -i eybackup -e postgresql -l [db_name]
  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='[db_name]'" 

    b. If there are active connections, connect to the host that is the source for the connection and 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_[app_name]

    2. Stop any background worker processes (usually via monit)
    3. Stop any cron tasks that may attempt to run against the database.

      crontab -e shows an edit window.

      • Manually comment each cron task that uses the database.
      • Save the file.
      • Note: Revert these changes after the backup has been restored.
    4. 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:[db_name] 
    or
    sudo -i eybackup -e postgresql -r N:[db_name]

    (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:[db_name])

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.

TIP: When transferring files between environments, connect to the source environment with key forwarding enabled (e.g. ssh -A... ) so that scp can authenticate to the remote instance.

Note: In the following examples `myapp` is a stand-in for your database name, substitute this out as appropriate.

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/myapp.2011-11-14T16-47-02.sql.gz
  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 -f < [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 -f < 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/myapp.2011-11-18T12-20-03.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 myapp.2011-11-18T12-20-03.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 myapp.2011-11-18T12-20-03.pgz myapp

Transfer your database (Scenario 3)

Note: As of the 3/1/2017 release the eyrestore tool is available on both the Stable-v4 (12.11) and Stable-v5 (16.06) Engine Yard stacks. It is untested but may be compatible with older versions of the stack. If you would like to try it against an older version please file a ticket with support and we can provide you a copy that can be installed from Custom Chef or manually.

Legacy Environments: Bucket naming has evolved with the cloud platform over time. If you encounter a message of "No Backups Found for that environment and database." review the source and destination environment bucket names using `cat /etc/.*backups.yml | grep backup_bucket`. If the source differs from the destination environment, pass the source bucket name to eyrestore with the option: `--backup_bucket #{name of source bucket}`.

The `eyrestore` tool is a wrapper that relies on existing `eybackup` functionality in order to perform safe and simple database restores between environments. It allows you to 'list', 'download', and 'restore' backups from one environment in a different environment by supplying the environment and database name you would like to reference.

The command is used the same for both MySQL and Postgres:

List Available Backups

sudo -i eyrestore --env production --database todo --action list

  Listing database backups for todo 
  3 backup(s) found 
  0:todo todo.2011–11–14T16–43–39.sql.gz 
  1:todo todo.2011–11–14T16–47–02.sql.gz 
  2:todo todo.2011–11–15T01–10–03.sql.gz

Restore A Backup by Index

sudo -i eyrestore --env production --database todo --action restore --index 1:todo

  You are restoring the backup for todo from 'production' into 'staging', THIS MAY BE 
  DESTRUCTIVE; are you sure you want to proceed (Y/n) ? [timeout=30secs]:y
  Running 'restore' on index '1:todo'.
  2017-02-02 20:53:52 +0000 Restoring todo
  2017-02-02 20:53:52 +0000 Downloading production.todo/todo.2011-11-14T16-47-02.sql.gz to /mnt/tmp
  Filename: /mnt/tmp/todo.2011-11-14T16-47-02.sql.gz
  Restore complete

Restore the Most Recent Backup

sudo -i eyrestore --env production --database todo --action restore --index last

  You are restoring the backup for todo from 'production' into 'staging', THIS MAY BE 
  DESTRUCTIVE; are you sure you want to proceed (Y/n) ? [timeout=30secs]:y
  Running 'restore' on index '2:todo'.
  2017-02-02 20:53:52 +0000 Restoring todo
  2017-02-02 20:53:52 +0000 Downloading production.todo/todo.2011–11–15T01–10–03.sql.gz to /mnt/tmp
  Filename: /mnt/tmp/todo.2011–11–15T01–10–03.sql.gz
  Restore complete

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

  • Avatar
    Permanently deleted user

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

    0
    Comment actions Permalink
  • 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

    0
    Comment actions Permalink
  • 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]

    0
    Comment actions Permalink
  • 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. 

    0
    Comment actions Permalink
  • 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=# 

    0
    Comment actions Permalink
  • Avatar
    Permanently deleted user

    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!

    0
    Comment actions Permalink
  • Avatar
    Permanently deleted user

    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.

    0
    Comment actions Permalink
  • 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?

    0
    Comment actions Permalink
  • Avatar
    Permanently deleted user

    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

    0
    Comment actions Permalink
  • Avatar
    Joshua McClintock

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

    0
    Comment actions Permalink
  • 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.

    0
    Comment actions Permalink
  • Avatar
    John Lauck

    pg_restore should work, as mentioned above: 

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

    0
    Comment actions Permalink
  • 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!

    0
    Comment actions Permalink
  • 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

     

    0
    Comment actions Permalink
  • Avatar
    Tim Baker

    Obvious one, but if anyone is getting this

    No backup found for database #: requested index: 1

    Check that you're referring to the correct backup, the index starts from 0.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk