Copy prod (secnum) DB to nightly (secnum-nightly) DB #567
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: Copy prod (secnum) DB to nightly (secnum-nightly) DB | |
| on: | |
| workflow_dispatch: | |
| schedule: | |
| - cron: "57 2 * * *" | |
| jobs: | |
| duplicate-prod-db-to-nightly: | |
| runs-on: ubuntu-latest | |
| name: Dump and restore the remote database data. | |
| steps: | |
| - name: Install scalingo CLI | |
| run: | | |
| wget -qO- https://cli-dl.scalingo.com/install.sh | bash | |
| echo "$HOME/bin" >> $GITHUB_PATH | |
| - name: Login with api-token | |
| run: scalingo login --api-token=${{ secrets.SCALINGO_API_TOKEN_SECNUM }} | |
| - name: Dump the remote database data and restore it to the local database. | |
| run: >- | |
| scalingo --region osc-secnum-fr1 --app if-prod-nightly-db run bash -c " | |
| echo '> activate Scalingo CLI, PG then login' && | |
| install-scalingo-cli && | |
| dbclient-fetcher pgsql && | |
| scalingo login --api-token ${{ secrets.SCALINGO_API_TOKEN_SECNUM }} && | |
| echo '> Getting PG Addon ID' && | |
| ADDON_ID_LINE=\$(scalingo --region osc-secnum-fr1 --app if-prod-back addons | grep 'PostgreSQL') && | |
| regex='PostgreSQL \| (.*) \| postgresql' && | |
| [[ \$ADDON_ID_LINE =~ \$regex ]] && | |
| echo '> Downloading last DB backup' && | |
| scalingo --region osc-secnum-fr1 --app if-prod-back --addon=\${BASH_REMATCH[1]} backups-download --output=dump.tar.gz && | |
| tar --to-stdout -xzf dump.tar.gz > dump.pgsql && | |
| echo '> Create a Table of content for restore, ignore postgis related schemas and tables:' && | |
| pg_restore -l dump.pgsql | grep -v tiger | grep -v spatial_ref_sys | grep -v topology > list.txt && | |
| echo '> Dropping data and schema owned by current user' && | |
| psql \$SCALINGO_POSTGRESQL_URL -c 'DROP OWNED BY CURRENT_USER CASCADE;' && | |
| echo '> Add Postgis extension if not exists' && | |
| psql \$SCALINGO_POSTGRESQL_URL -c 'CREATE EXTENSION IF NOT EXISTS postgis;' && | |
| echo '> Start pg_restore' &&\ | |
| pg_restore\ | |
| --use-list=list.txt\ | |
| --verbose\ | |
| --no-owner\ | |
| --no-privileges\ | |
| --no-comments\ | |
| --exit-on-error\ | |
| --dbname \$SCALINGO_POSTGRESQL_URL dump.pgsql && | |
| echo '> Finished restoring schema' && | |
| exit 0" |