Working with PostgreSQL ¶

Opening psql ¶

docker-compose exec db bash -c 'psql -U$POSTGRES_USER'

Dumping a database to .sql from command line ¶

docker-compose exec -T db bash -c 'pg_dump --no-owner -U$POSTGRES_USER $POSTGRES_DB' > ~/path/to/my-snapshot.sql

Restoring a database from .sql ¶

If the content is within the container:

docker-compose exec db bash -c 'cat database/restore/backup.sql | psql -U$POSTGRES_USER $POSTGRES_DB'

If the content is outside the container:

cat database/restore/backup.sql | docker-compose exec -T db bash -c 'psql -U$POSTGRES_USER'

Loading production data into local development database ¶

Start your local development instance with docker-compose up as described above (if you haven’t already)
Find the password for the dumper postgres user in the project’s shared password vault

From the project directory, use the pg_dump to connect to the replica database, logging in using the read-only dumper user:

#!/bin/bash
docker-compose exec db \
    pg_dump \
        -h replica.db.indevets.com -p 5432 -U dumper \
        --no-owner --no-acl \
        --schema=public \
        --exclude-table-data=audits \
        --exclude-table-data=activity_log \
        --file /root/prod_indevets.sql \
        prod_indevets;

Empty the local database if it is already populated with any tables:

docker-compose exec db bash -c 'dropdb -U$POSTGRES_USER $POSTGRES_DB'

docker-compose exec db bash -c 'createdb -U$POSTGRES_USER $POSTGRES_DB'

Restore the /root/prod_indevets.sql dump saved into the db container earlier:

docker-compose exec db bash -c 'psql -U$POSTGRES_USER $POSTGRES_DB -f /root/prod_indevets.sql'