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.

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.

   cp restore_dbs.sh /var/tmp

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

8. Become the user postgres.

   sudo -u postgres -i

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

   cd /var/tmp

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

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