Galaxy migration from sqlite to postgres almost worked

1

Good Day. This is my first post and I’m completely new to Galaxy (2 weeks) so please be gentle. I’ve been given the task to migrate a multiuser 3-year old Galaxy installed in 2016 to the latest Galaxy version and move from sqlite to PostgreSQL. It will run on Ubuntu and is about 1T in size after cleaning up old histories. The current Galaxy server hardware must be retired so we are investigating a virtual cloning environment as a long term archive solution for FDA reproducability (definitely open to other suggestions/approaches too).

In lieu of a virtual clone, I tried pgloader, SEQUEL, and even did a manual migration (painful) without success for the universe.sqlite file. A tool called “ESF Database Migration Toolkit” plus some manual fixes worked enough to get Galaxy to come up and appear functional for new analysis runs. However, when users login they can see their histories but get errors downloading historical datasets(see below). I see two options: build a fresh empty Galaxy install and have the users migrate their histories using the Galaxy app or I can try to fix the current errors. User confidence is low that the installation will be 100% working after observing the errors with historical data and given FDA dependencies I can understand this completely. Before I abandon the migration process I hope someone may have some high level guidance on the best route to take or notice something stupid I did like not copy something over.

The errors encountered are:
1) when trying to download their historical data on the newly migrated Galaxy they get: "could not display BAM file, error was: [Errno 2] could not open alignment file “:No such file or directory”. I checked the galaxy.log and it says something similar:
OSError: [Errno 2] No such file or directory: ‘’
[pid: 106775|app: 0|req: 70/84] 10.10.10.87 () {40 vars in 812 bytes} [Wed Jan 15 09:20:26 2020] GET /datasets/005c58e6d9301fed/display?to_ext=bam => generated 0 bytes in 26 msecs (HTTP/1.1 500) 1 headers in 63 bytes (0 switches on core 3)

To fix this I tried copying over the old ../galaxy/tool-data directory to the new instance at ../galaxy_app/tool-data.  I was hoping this would bring with it all the data not stored in the DB that I migrated...  but no go.  

Do I need to migrate any other data/directories other than the universe.sqlite file?

2) While running manage_db.sh after postgres migration, the table "library_dataset_dataset_association_tag_association" failed to create so I manually created it as an empty table.  If it was supposed to be populated during the manage_db.sh script, that may be a problem as well.

Sorry for the length.

Sincerely Derek

2

just double checking: Have you actually copied the galaxy/database/files/ directories?

Also, you might find my old blog post about moving from MySQL to PostgreSQL helpful: https://galaxyproject.org/blog/2015-07-mysql-2-postgresql/
Back then, I did the database switch, before I did the actual hardware switch

Regards, Hans-Rudolf

3

@hrhotz: Many thanks for replying! I did not copy any directories outside of …/galaxy_app/tool-data/* directories. I did migrate the universe.sqlite file. Where do I get a list of directories I need to copy?

Although I migrated from sqlite, your post followed similar issues I also encountered when migrating the database manually.

Thanks in advance.

4

Hi Derek

It is difficult to say, which directories need to be copied. This really depends on the number of modifications made. Nevertheless, copying the galaxy/database/files/ directory is pretty important. It contains all the data (i.e. history items).
Have you considered copying the complete galaxy directory tree first?

A potential, step wise scenario could look like:

  1. switch the old server (running on the old hardware) to PostgreSQL
  2. Once, you are convinced everything is working (including user authentication), copy the complete galaxy directory tree to the new hardware
  3. Upgrade the galaxy code to the latest version

Hans-Rudolf

5

I did not consider copying the entire Galaxy directory because all the paths are completely different and appear in so many files. If I can overcome the path differences this seems like a feasible idea. Maybe I can use hard links to simulate the previous Galaxy starting directory structure. It may be a little time before I can test this out and reply back. Thanks again for this very useful suggestion.

6

just a little comment: the “*.loc” files aside, the path should not be hard coded in many places, since galaxy works with relative paths ($GALAXYROOT).