Migrate DB on test environment for SCXA to the latest version

[ke4]

We are setting up a test environment for Single Cell Expression Atlas. We need to migrate the database for version 20 (currently latest) on this DB server:

jdbc:postgresql://pgsql-dlvm-062.ebi.ac.uk:5432/gxpscxatst

Currently it is on version 16.

• [x] Carefully check if every environment variable is set in $ATLAS_PROD/environments/${ATLAS}/${ATLAS_ENV}.sh: /nfs/production/irene/ma/atlas-prod/environments/sc/test.sh.
• [ ] Execute this Jenkins job: http://gene-expression.ebi.ac.uk/jenkins/view/SCXA%20Atlas%20Data%20Prod/job/Migrate_database_schema/ With params: ATLAS_ENV: test ATLAS: sc

[ke4]

First execution of above mentioned Jenkins job failed at applying version 18. After checking the logs, somebody renamed the existing 2 materialised views in the PostgreSQl DB and added old_ prefix to both of them.

ERROR: Migration of schema "atlasprd3" to version 18 - remove clusters markers tables failed! Changes successfully rolled back.
ERROR: 
Migration V18__remove_clusters_markers_tables.sql failed
--------------------------------------------------------
SQL State  : 42P01
Error Code : 0
Message    : ERROR: materialized view "scxa_marker_gene_stats" does not exist
Location   : /hps/nobackup/irene/ma/jenkins_fg_atlas/codon-galaxy-02/workspace/Migrate_database_schema/flyway/scxa/migrations/V18__remove_clusters_markers_tables.sql (/hps/nobackup/irene/ma/jenkins_fg_atlas/codon-galaxy-02/workspace/Migrate_database_schema/flyway/scxa/migrations/V18__remove_clusters_markers_tables.sql)
Line       : 1
Statement  : DROP MATERIALIZED VIEW scxa_marker_gene_stats

This was the migration script:

DROP MATERIALIZED VIEW scxa_marker_gene_stats;
DROP MATERIALIZED VIEW scxa_top_5_marker_genes_per_cluster;
DROP TABLE scxa_cell_clusters;
DROP TABLE scxa_marker_genes;

[ke4]

Second execution passed the previous error, but failed with out of memory error when it tried to apply version 19 schema change.

ERROR: Migration of schema "atlasprd3" to version 19 - anndata import changes failed! Changes successfully rolled back.
ERROR:
Migration V19__anndata_import_changes.sql failed
------------------------------------------------
SQL State  : 53200
Error Code : 0
Message    : ERROR: out of memory
  Detail: Failed on request of size 1048576 in memory context "AfterTriggerEvents".
Location   : /hps/nobackup/irene/ma/jenkins_fg_atlas/codon-galaxy-02/workspace/Migrate_database_schema/flyway/scxa/migrations/V19__anndata_import_changes.sql (/hps/nobackup/irene/ma/jenkins_fg_atlas/codon-galaxy-02/workspace/Migrate_database_schema/flyway/scxa/migrations/V19__anndata_import_changes.sql)
Line       : 32
Statement  : UPDATE scxa_coords
SET dimension_reduction_id = sdr.id
FROM scxa_dimension_reduction AS sdr
WHERE
  scxa_coords.experiment_accession = sdr.experiment_accession AND
  scxa_coords.method = sdr.method AND
  scxa_coords.parameterisation = sdr.parameterisation

[ke4]

I tried to execute the migration again with version 19, but got the same result.

[ke4]

My next actions are going to be start from scratch.

• [x] Clean the DB
• [x] Apply flyway migrations
• [x] Check if the DB status is fine

[ke4]

For cleaning the database I added a new Jenkins job: Clean_Database_Schema supporting by this Merge Request: https://gitlab.ebi.ac.uk/ebi-gene-expression/jenkins-jobs/-/merge_requests/17 that has ben approved by Pedro.

[ke4]

The DB on pgsql-dlvm-062.ebi.ac.uk:5432/gxpscxatst has been cleaned and migrated to the latest schema version (version 20).