Skip to content

Latest commit

 

History

History
66 lines (41 loc) · 3.55 KB

postgresdb_load_data.md

File metadata and controls

66 lines (41 loc) · 3.55 KB
Raw

Dump data from production Postgres DB and Load them in staging Postgres DB

This document describes the steps to dump a production database and load the dump file into the staging database.

Dump data from production

  1. ssh to one of the production VMs. Find a list of the production Vms.

ssh deploy@<productionVMName>

  • Run:
    env | grep -i postgres to find which postgres production VM the application is connected to. env | grep -i DB to find the production set in the env variable.

  1. ssh to the postgres production VM that the is connected to.

    If this is lib-postgres-prod1:

ssh pulsys@lib-postgres-prod1

  • Run:
    sudo su - postgres to connect to postgres pg_dump -d <productionDBname> -Fc -f /tmp/<productionDBname>_db.dump to generate the productionDBname>_db.dump dump file.

Load data into the application staging database

  1. ssh to one of the staging VMs. Find a list of the staging VMs.

ssh deploy@<stagingVMName>

  • Run:
    env | grep -i postgres to find which postgres staging VM the application is connected to. env | grep -i DB to find the staging and the staging :

  1. scp the productionDBname>_db.dump dump file you generated in section - Dump data from production - to your local and then to the /tmp/ directory in the postgres staging VM that the is using. If the postgres-production VM has the public key from the postgres-staging VM then you can scp the file directly from the postgres-production VM into the postgres-staging VM.

  2. ssh as pulsys in all the staging machines. Find a list of the staging Vms and stop the nginx service.

    ssh pulsys@<stagingVMName> sudo service nginx stop

  3. ssh to the postgres staging VM that the is connected to.

    If this is lib-postgres-staging1:

    ssh pulsys@lib-postgres-staging1 sudo su - postgres to connect to postgres ls /tmp/ confirm that the file <productionDBname>_db.dump you transferred in step 2 exists in /tmp. If it does not exist do not continue. Go back to step 2 and make sure to transfer the dump file to /tmp.

    dropdb <stagingDBname> to drop the staging database createdb -O <stagingRoleDBName> <stagingDBname> to create a new with role

    pg_restore -d <stagingDBname> /tmp/<productionDBname>_db.dump to load the dump file into the database.

  4. Deploy the application to the staging environment.

    • From your local main branch deploy the application using capistrano to the staging environment.
    • OR deploy the application to the staging environment using ansible tower.

  5. ssh as pulsys in all the staging machines. Find a list of the staging Vms and start the nginx service.

e.g.: ssh pulsys@<stagingVMName> sudo service nginx start

  1. Go to the url and make sure the application loads and works as expected.