Replace materialized view refresher trigger with pg_cron commands

[rija]

• [x] Add a daily (out of office hours, before the backup to S3 - so the materialized views are part of the backup) cronjob on bastion server to run these queries:
  • REFRESH MATERIALIZED VIEW FILE_FINDER
  • REFRESH MATERIALIZED VIEW SAMPLE_FINDER
  • REFRESH MATERIALIZED VIEW DATASET_FINDER
• [x] Add migration file of a "schema" to remove all custom triggers related to the above function from DB schema
• [x] Remove commands fro dropping and recreating triggers from our various scripts

Will fix #1662

This will also fix a possible bug whereby newest dataset are not indexed for the search function

[kencho51]

% env OBJC_DISABLE_INITIALIZE_FORK_SAFETY=YES ansible-playbook -i ../../inventories bastion_playbook.yml -e "reset_database_cronjob_state=present" -e "upload_database_backup_to_S3_cronjob_state=present" --extra-vars="gigadb_env=staging"

% ssh -i ~/.ssh/id-rsa-aws-seoul-ken.pem centos@3.36.204.163
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Fri May 10 07:49:43 2024 from 3.36.204.163
[centos@ip-10-99-0-175 ~]$ crontab -l
#Ansible: Check daily the disk usage and notify from 50%
00 23 * * * swatch -c swatch.conf -p ./diskUsage
#Ansible: Reset database every day after a new backup is available on the ftp server
05 10 * * * /usr/local/bin/databaseReset 2> $HOME/logs/errors.log 1> $HOME/logs/output.log
#Ansible: Create database dump file from RDS and upload to S3 every day
00 20 * * * docker run --env-file .env -v /home/centos/backups:/backups -v /home/centos/.config/rclone/rclone.conf:/root/.config/rclone/rclone.conf registry.gitlab.com/gigascience/forks/kencho-gigadb-website/production_s3backup:staging
[centos@ip-10-99-0-175 ~]$ 
[centos@ip-10-99-0-175 ~]$ cat .env

[kencho51]

Close as fix in PR #1866