Skip to content

Latest commit

 

History

History
61 lines (41 loc) · 1.46 KB

database.md

File metadata and controls

61 lines (41 loc) · 1.46 KB
Raw

Database

Connecting to the database

  1. Run the Cloud SQL Auth Proxy script:

    ./proxy.sh $ENVIRONMENT

  2. Obtain the database password for the environment:

    gcloud secrets versions access latest --project dft-schemes-$ENVIRONMENT --secret database-password

  3. Connect using PSQL and enter the password when prompted:

    psql -h localhost -U schemes schemes

Creating a cold backup

To download the latest database backup for storing offline:

  1. Download the backup (you will be prompted to input your BitWarden master password):

    ./cold-backup.sh $ENVIRONMENT

This will create an encrypted compressed PostgreSQL custom-format archive schemes-$ENVIRONMENT-$BACKUP_TIMESTAMP.dump.gz.gpg.

Restoring a cold backup

To restore a backup to a local or proxied database:

  1. Decrypt the archive (you will be prompted to input your BitWarden master password):

    bw get password "UYCS Database Backup Passphrase" \
    | ( gpg --batch --decrypt --passphrase-fd 0 --output $ARCHIVE.gz $ARCHIVE.gz.gpg && rm $ARCHIVE.gz.gpg )

  2. Uncompress the archive:

    gunzip $ARCHIVE.gz

  3. Restore the backup:

    docker run --rm -i \
    --network=host \
    -e PGUSER=schemes \
    -e PGPASSWORD=$PGPASSWORD \
    postgres:16 \
    pg_restore -h localhost -d schemes --no-owner < $ARCHIVE