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.

   1. 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.
   
   1. sudo chown postgres:postgres easproddb.sfgov.org-eas_prod-20170613.dmp
   2. sudo chown postgres:postgres easproddb.sfgov.org-sfmaps_prod-20170613.dmp

Related Information

• PostgreSQL 9.0.23 Documentation Chapter 24. Backup and Restore