...

Step-by-step Guide

1. 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.
2. Before an EAS database can be restored, the SQL-dump file must be available on the target EAS database server.  One way to do this is to copy the SQL-dump file from the source EAS database server to the target EAS database server by way of your desktop personal computer (PC).  One way to do this is to use FileZilla.
   1. These are example SQL-dump files for the eas_prod and the sfmaps_prod databases:
      1. easproddb.sfgov.org-eas_prod-20170613.dmp
      2. easproddb.sfgov.org-sfmaps_prod-20170613.dmp
   2. Copy the SQL-dump file(s)
      1. FROM: source EAS database server (/mnt/backup/pg/daily)
      2. TO: desktop PC
   3. Copy the SQL-dump file(s)
      1. FROM: desktop PC
      2. TO: target EAS database server (/var/tmp)
3. Log in to the target EAS database server using SSH.

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

   
   

8. Become the user postgres.

   Code Block
   sudo -u postgres -i

   
   

9. As the user postgres, move into the target directory.

   Code Block
   cd /var/tmp

   
   

10. Restore the database(s).  This normally just takes a couple of minutes to finish.

    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

    
    

11. Exit out of both shells to close the SSH session to the target EAS database server.
12. You should confirm that your database(s) have been restored by following the following steps:
    
    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.

Related Information

• PostgreSQL 9.0.23 Documentation Chapter 24. Backup and Restore