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 exactly how to do that for PostgreSQL 9.0.x.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

...

1. FROM: source EAS database server (/mnt/backup/pg/daily)
2. TO: desktop PC

...

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.

   Code Block
   cp restore_dbs.sh /var/tmp

   
   

5. Move to the target directory.


   Code Block
   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.

   Code Block
   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.

   Code Block
   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.

   Code Block
   language text
   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.

   Code Block
   sudo -u postgres -i

   
   

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

    Code Block
    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.

    Code Block
    language text
    ./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 the 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
       they should be
       1. 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.

Anchor
FileZillaAnchor FileZillaAnchor

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