One reason that you might want to restore a database from a SQL-dump is to update the DEV database with the contents of the PROD database.  These instructions describe how to restore the DEV and QA databases with the latest daily backup from PROD.

Name and Location of Daily Database Backups

Each EAS database in each of the environments is backed up every night, so there is always a SQL-dump file available for database restoration.

• Path to daily database backups:

/mnt/backup/pg/daily

• File format for daily backups:

easproddb.sfgov.org-eas_prod-YYYYMMDD.dmp
easproddb.sfgov.org-sfmaps_prod-YYYYMMDD.dmp

• Example backup files:

easproddb.sfgov.org-eas_prod-20170613.dmp
easproddb.sfgov.org-sfmaps_prod-20170613.dmp

Name and Location of Database Restoration Scripts

Each EAS environment has a shell script named 'restore_dbs.sh' that is located in the dev user's home directory.

When the script is run with the correct arguments it performs the entire EAS database restoration process for the given server.

Step-by-Step EAS Database Restoration Guide

1. Find a new or updated EAS address that is in the PROD database but not the DEV or QA databases. Hint: To find a new address see the daily automated email containing a list of recent address changes.
2. Copy the SQL-dump files from the daily PROD backups to DEV or QA server.
   
   1. FTP the daily backup from PROD to a local computer (e.g. your desktop computer).
   2. See the 'FTP Using FileZilla' section below for one method of transferring files.
   3. FTP the SQL-dump backup files from your local computer to the DEV or QA server.
   4. For consistency with the steps below upload the files to '/var/tmp/' on the DEV or QA server.
3. Log in to the target EAS database server using SSH.
   1. See also PuTTY and SSH Instructions

4. Copy the database restore shell script from your home directory to the target directory.  This is only necessary the very first time that you do this.

   cp restore_dbs.sh /var/tmp

5. Move to the target directory.


   cd /var/tmp

6. Change the ownership of the database restore shell script.  This is only necessary the very first time that you do this.

   sudo chown postgres:postgres restore_dbs.sh

7. If the database restore shell script is not executable, then modify this permission for the file owner.

   sudo chmod u+x restore_dbs.sh

8. Change the ownership of the SQL-dump file(s).  The following examples use the SQL-dump files from 2a above.

   sudo chown postgres:postgres easproddb.sfgov.org-eas_prod-20170613.dmp
   sudo chown postgres:postgres easproddb.sfgov.org-sfmaps_prod-20170613.dmp

9. Become the user postgres.

   sudo -u postgres -i

10. While logged in as the user postgres, move into the target directory.

    cd /var/tmp

11. Restore the database(s).  This normally just takes a couple of minutes to finish.  If the one-argument example below does not work, then use the two-argument example.

    ./restore_dbs.sh DEV easproddb.sfgov.org-eas_prod-20170613.dmp
    ./restore_dbs.sh DEV easproddb.sfgov.org-sfmaps_prod-20170613.dmp
                             OR
    ./restore_dbs.sh DEV easproddb.sfgov.org-eas_prod-20170613.dmp easproddb.sfgov.org-sfmaps_prod-20170613.dmp
    						 OR
    ./restore_dbs.sh QA easproddb.sfgov.org-eas_prod-20170613.dmp easproddb.sfgov.org-sfmaps_prod-20170613.dmp

    The restoration order of the two dmp files matters. The *eas_prod* dump must be restored before the *sfmaps_prod* dump. Otherwise a 'Data Retrieval Error' will occur.

12. Exit out of both shells to close the SSH session to the target EAS database server.
13. You should confirm that your database(s) have been restored by following these steps:
    
    1. Check Database
       1. Establish your SSH tunnel to the target EAS database server.
       2. Invoke pgAdmin.
       3. Navigate to a restored database and verify that its contents are what you expect.
    2. Check Web App
       1. Confirm by browsing the the DEV or QA web versions of EAS and verifying that the address from Step 1 is now on the map of the updated server.

FTP Using FileZilla

1. Download and install FileZilla from https://filezilla-project.org/

   1. See the network folder W:\Software\FileZilla for previously downloaded versions.
2. Open FileZilla, navigate to 'Site Manager', and set up connections.

Related Information

• PostgreSQL 9.0.23 Documentation Chapter 24. Backup and Restore

• PuTTY and SSH Instructions