DUMP #36
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
| # Um dump do banco de dados do dadosjusbr será realizado às 0h de todo dia 17. | |
| name: DUMP | |
| on: | |
| workflow_dispatch: | |
| schedule: | |
| - cron: "0 3 17 * *" | |
| jobs: | |
| Dump: | |
| runs-on: ubuntu-20.04 | |
| env: | |
| AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }} | |
| AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }} | |
| name: Dump do banco de dados | |
| steps: | |
| - uses: actions/checkout@v3 | |
| - name: Criando coleta.csv... | |
| run: PGPASSWORD=${{ secrets.POSTGRES_PASSWORD }} psql -h "${{ secrets.POSTGRES_HOST }}" -U ${{ secrets.POSTGRES_USER }} -d ${{ secrets.POSTGRES_DB }} -c "\copy (SELECT id as chave_coleta, id_orgao as orgao, mes, ano, TO_CHAR(timestamp, 'YYYY-MM-DD\"T\"HH24:MI:SS.US\"Z\"') as timestampb_coleta, repositorio_coletor, versao_coletor, repositorio_parser, versao_parser FROM coletas WHERE (procinfo is null or procinfo::text = 'null') and atual=true) TO 'dump/coleta.csv' csv header delimiter ';';" | |
| - name: Criando metadados.csv... | |
| run: PGPASSWORD=${{ secrets.POSTGRES_PASSWORD }} psql -h "${{ secrets.POSTGRES_HOST }}" -U ${{ secrets.POSTGRES_USER }} -d ${{ secrets.POSTGRES_DB }} -c "\copy (SELECT id_orgao as orgao, mes, ano, formato_aberto, acesso, extensao, estritamente_tabular, formato_consistente, tem_matricula, tem_lotacao, tem_cargo, detalhamento_receita_base, detalhamento_outras_receitas, detalhamento_descontos, replace(indice_completude::text, '.', ',') as indice_completude, replace(indice_facilidade::text, '.', ',') as indice_facilidade, replace(indice_transparencia::text, '.', ',') as indice_transparencia FROM coletas WHERE (procinfo is null or procinfo::text = 'null') and atual=true) TO 'dump/metadados.csv' csv header delimiter ';';" | |
| - name: Criando contracheque.csv... | |
| run: PGPASSWORD=${{ secrets.POSTGRES_PASSWORD }} psql -h "${{ secrets.POSTGRES_HOST }}" -U ${{ secrets.POSTGRES_USER }} -d ${{ secrets.POSTGRES_DB }} -c "\copy (SELECT id as id_contracheque, orgao, mes, ano, nome, matricula, funcao, local_trabalho, replace(salario::text, '.', ',') as salario, replace(beneficios::text, '.', ',') as beneficios, replace(descontos::text, '.', ',') as descontos, replace(remuneracao::text, '.', ',') as remuneracao, situacao FROM contracheques) TO 'dump/contracheque.csv' csv header delimiter ';';" | |
| - name: Criando remuneracao.csv... | |
| run: PGPASSWORD=${{ secrets.POSTGRES_PASSWORD }} psql -h "${{ secrets.POSTGRES_HOST }}" -U ${{ secrets.POSTGRES_USER }} -d ${{ secrets.POSTGRES_DB }} -c "\copy (SELECT id_contracheque, orgao, mes, ano, tipo, categoria, item, replace(valor::text, '.', ',') as valor FROM remuneracoes) TO 'dump/remuneracao.csv' csv header delimiter ';';" | |
| - name: Realizando dump... | |
| run: | | |
| cd dump | |
| POSTGRES_USER="${{ secrets.POSTGRES_USER }}" POSTGRES_PASSWORD="${{ secrets.POSTGRES_PASSWORD }}" POSTGRES_DB="${{ secrets.POSTGRES_DB }}" POSTGRES_HOST="${{ secrets.POSTGRES_HOST }}" POSTGRES_PORT="${{ secrets.POSTGRES_PORT }}" AWS_REGION="${{ secrets.AWS_REGION }}" S3_BUCKET="${{ secrets.S3_BUCKET }}" go run . |