DB Dump #494
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |