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)

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

Type (to list the backups):

sudo -i eybackup -e mysql --list-backup [db_name]

 sudo -i eybackup -e mysql -l [db_name]

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

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

Type (to list the backups):

sudo -i eybackup -e postgresql --list-backup [db_name]

sudo -i eybackup -e postgresql -l [db_name]

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.
If you have an active psql console session open against the database, issue \q to logout of it.

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]

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

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

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