Skip to content

DB Dump

DB Dump #444

Workflow file for this run

name: DB Dump
on:
push:
branches:
- master
- develop
schedule:
- cron: '0 0 * * *'
jobs:
sync:
if: github.repository == 'PecanProject/bety'
runs-on: ubuntu-latest
services:
postgres:
image: mdillon/postgis:9.5
ports:
- 5432:5432
options: >-
--health-cmd pg_isready
--health-interval 10s
--health-timeout 5s
--health-retries 5
steps:
- uses: actions/checkout@v2
- name: Setup Database
run: |
psql -h localhost -U postgres -c "CREATE ROLE bety WITH LOGIN CREATEDB NOSUPERUSER NOCREATEROLE PASSWORD 'bety'"
psql -h localhost -U postgres -c "CREATE DATABASE bety WITH OWNER bety"
psql -h localhost -U postgres -d bety -c "CREATE EXTENSION postgis;"
- name: Sync with EBI
run: script/load.bety.sh -a "postgres" -p "-h localhost" -d "bety" -o bety -m 99 -r 0 -c -w http://www.betydb.org/dump/bety.tar.gz
- name: Sync with BU
run: script/load.bety.sh -a "postgres" -p "-h localhost" -d "bety" -o bety -m 99 -r 1
- name: Sync with BNL
run: script/load.bety.sh -a "postgres" -p "-h localhost" -d "bety" -o bety -m 99 -r 2 -w ftp://anon:[email protected]/outgoing/betydb/bety.tar.gz
- name: Sync with Wisconsin
run: script/load.bety.sh -a "postgres" -p "-h localhost" -d "bety" -o bety -m 99 -r 5 -w http://tree.aos.wisc.edu:6480/sync/dump/bety.tar.gz
- name: Dump Database
run: |
PG=$(docker ps | awk '/postgis/ { print $1 }')
docker exec ${PG} pg_dump -h localhost -U postgres -F c bety > initdb/db.dump
- name: Build Docker with Database dump
run: |
cd initdb
docker build --tag image --file Dockerfile .
- name: Build smaller dump for CI runs
# update on scheduled runs, not every PR
if: github.event.schedule != ''
run: |
# Nuke some large tables not used in testing
# (to avoid foreign key constraints when deleting their child inputs)
docker exec ${PG} psql -h localhost -U bety -c "
TRUNCATE runs,likelihoods,benchmarks CASCADE"
# Drop unneeded records from inputs table
# (loop is because many are referenced only by descendent inputs
# that are themselves deleted here.)
while [ "$DEL_RES" != "DELETE 0" ]; do
DEL_RES=$(
docker exec ${PG} psql -h localhost -U bety -c "
DELETE FROM inputs WHERE
id NOT IN (
SELECT DISTINCT container_id FROM dbfiles
WHERE container_type = 'Input'
AND container_id IS NOT null)
AND id NOT IN (
SELECT DISTINCT parent_id FROM inputs
WHERE parent_id IS NOT null);"
) || break
echo ${DEL_RES}
done
# CI tests don't need any records from the excluded tables, just schemas
docker exec ${PG} pg_dump \
-h localhost -U bety \
--exclude-table-data='runs*' \
--exclude-table-data='inputs_runs*' \
--exclude-table-data='likelihoods*' \
--exclude-table-data='ensembles*' \
--exclude-table-data='posteriors_ensembles*' \
--exclude-table-data='benchmarks*' \
--exclude-table-data='reference_runs*' \
-F c \
bety > initdb/db.dump
docker build --tag image_ci --file Dockerfile .
- name: Login into registry
run: |
echo "${{ secrets.GITHUB_TOKEN }}" | docker login docker.pkg.github.com -u ${{ github.actor }} --password-stdin
if [ -n "${{ secrets.DOCKERHUB_USERNAME }}" -a -n "${{ secrets.DOCKERHUB_PASSWORD }}" ]; then
echo "${{ secrets.DOCKERHUB_PASSWORD }}" | docker login -u ${{ secrets.DOCKERHUB_USERNAME }} --password-stdin
fi
- name: Push docker image
run: |
IMAGE_ID=docker.pkg.github.com/${{ github.repository }}/db
IMAGE_ID=$(echo $IMAGE_ID | tr '[A-Z]' '[a-z]')
for T in $(date +'%Y-%V') 'latest'; do
docker tag image $IMAGE_ID:$T
docker push $IMAGE_ID:$T
if [ -n "${{ secrets.DOCKERHUB_USERNAME }}" -a -n "${{ secrets.DOCKERHUB_PASSWORD }}" ]; then
docker tag image pecan/db:$T
docker push pecan/db:$T
fi
done
if [ -n "$(docker image ls -q image_ci)" ]; then
docker tag image_ci $IMAGE_ID:ci
docker push $IMAGE_ID:ci
docker tag image_ci pecan/db:ci
docker push pecan/db:ci
fi