Problem using Galaxy with a PostgreSQL database on a remote host
Dear Galaxy Developers, I've been banging my head against this one for a few days now. I have two Galaxy instances. One resides on a server called "genomics", which also hosts the corresponding PostgreSQL installation. The second also resides on "genomics", but its database is hosted on "wigserv5". Based on the tests I just ran and code I just read, sqlalchemy (not Galaxy) is ignoring the hostname/port part of the database_connection string. For reference, the connection strings I've tried are: postgresql://glxeric:XXXXX@/glxeric?host=/tmp postgresql://glxeric:XXXXX@wigserv5.cshl.edu/glxeric?host=/tmp postgresql://glxeric:XXXXX@wigserv5.cshl.edu:5432/glxeric?host=/tmp postgresql://glxeric:XXXXX@adgdgdfdflkhjfdhfkl/glxeric?host=/tmp All of these appear to result in Galaxy connecting to the PostgreSQL installation on genomics, as determined by Galaxy schema version discrepancies and other constraints. With each connection string, Galaxy starts up normally. I force database activity by browsing saved histories. It works every time. By all appearances, the second Galaxy instance is using the PostgreSQL database hosted on "genomics", not on "wigserv5". All databases and roles exist, and the databases are populated. When I comment out the "database_connection" line in universe_wsgi.ini, I get errors arising from the later configuration of PostgreSQL-specific Galaxy options, as expected. I can connect to the database server on "wigserv5" using "psql -h wigserv5.cshl.edu -d glxeric -U glxeric" from the server "genomics". Have you ever observed this behavior from Galaxy or sqlalchemy? Thanks, Eric
Hey Eric, It looks like you have connection info for both tcp/ip connections and unix sockets in the connection strings. If you're logging in using "psql -h wigserv5.cshl.edu <snip>", then you only want the tcp/ip connection info. Drop the ?host=tmp off the third option you listed and I think you'll be up and running, so: postgresql://glxeric:XXXXX@wigserv5.cshl.edu:5432/glxeric -Dannon On Sat, May 24, 2014 at 1:49 AM, Paniagua, Eric <epaniagu@cshl.edu> wrote:
Dear Galaxy Developers,
I've been banging my head against this one for a few days now.
I have two Galaxy instances. One resides on a server called "genomics", which also hosts the corresponding PostgreSQL installation. The second also resides on "genomics", but its database is hosted on "wigserv5".
Based on the tests I just ran and code I just read, sqlalchemy (not Galaxy) is ignoring the hostname/port part of the database_connection string. For reference, the connection strings I've tried are:
postgresql://glxeric:XXXXX@/glxeric?host=/tmp postgresql://glxeric:XXXXX@wigserv5.cshl.edu/glxeric?host=/tmp postgresql://glxeric:XXXXX@wigserv5.cshl.edu:5432/glxeric?host=/tmp postgresql://glxeric:XXXXX@adgdgdfdflkhjfdhfkl/glxeric?host=/tmp
All of these appear to result in Galaxy connecting to the PostgreSQL installation on genomics, as determined by Galaxy schema version discrepancies and other constraints. With each connection string, Galaxy starts up normally. I force database activity by browsing saved histories. It works every time. By all appearances, the second Galaxy instance is using the PostgreSQL database hosted on "genomics", not on "wigserv5".
All databases and roles exist, and the databases are populated.
When I comment out the "database_connection" line in universe_wsgi.ini, I get errors arising from the later configuration of PostgreSQL-specific Galaxy options, as expected.
I can connect to the database server on "wigserv5" using "psql -h wigserv5.cshl.edu -d glxeric -U glxeric" from the server "genomics".
Have you ever observed this behavior from Galaxy or sqlalchemy?
Thanks, Eric
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at: http://lists.bx.psu.edu/
To search Galaxy mailing lists use the unified search at: http://galaxyproject.org/search/mailinglists/
Hey Dannon, Thanks for pointing that out! I missed it. I am now connecting to the remote database. I ran "sh manage_db.sh upgrade" and it upgraded from schema 114 to 118 without error messages. I then ran "sh ./scripts/migrate_tools/0010_tools.sh install_dependencies" and received the following error: Traceback (most recent call last): File "./scripts/migrate_tools/migrate_tools.py", line 21, in app = MigrateToolsApplication( sys.argv[ 1 ] ) File "/localdata1/galaxy/glxmaint/src/lib/tool_shed/galaxy_install/migrate/common.py", line 59, in __init__ install_dependencies=install_dependencies ) File "/localdata1/galaxy/glxmaint/src/lib/tool_shed/galaxy_install/install_manager.py", line 122, in __init__ is_repository_dependency=is_repository_dependency ) File "/localdata1/galaxy/glxmaint/src/lib/tool_shed/galaxy_install/install_manager.py", line 506, in install_repository is_repository_dependency=is_repository_dependency ) File "/localdata1/galaxy/glxmaint/src/lib/tool_shed/galaxy_install/install_manager.py", line 345, in handle_repository_contents guid = self.get_guid( repository_clone_url, relative_install_dir, tool_config ) File "/localdata1/galaxy/glxmaint/src/lib/tool_shed/galaxy_install/install_manager.py", line 253, in get_guid tool = self.toolbox.load_tool( full_path ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/__init__.py", line 671, in load_tool return ToolClass( config_file, root, self.app, guid=guid, repository_id=repository_id, **kwds ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/__init__.py", line 1045, in __init__ self.parse( root, guid=guid ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/__init__.py", line 1260, in parse self.parse_inputs( root ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/__init__.py", line 1351, in parse_inputs display, inputs = self.parse_input_page( page, enctypes ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/__init__.py", line 1655, in parse_input_page inputs = self.parse_input_elem( input_elem, enctypes ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/__init__.py", line 1723, in parse_input_elem case.inputs = self.parse_input_elem( case_elem, enctypes, context ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/__init__.py", line 1679, in parse_input_elem group.inputs = self.parse_input_elem( elem, enctypes, context ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/__init__.py", line 1751, in parse_input_elem param = self.parse_param_elem( elem, enctypes, context ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/__init__.py", line 1764, in parse_param_elem param = ToolParameter.build( self, input_elem ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/parameters/basic.py", line 215, in build return parameter_types[param_type]( tool, param ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/parameters/basic.py", line 1566, in __init__ ToolParameter.__init__( self, tool, elem ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/parameters/basic.py", line 54, in __init__ self.validators.append( validation.Validator.from_element( self, elem ) ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/parameters/validation.py", line 23, in from_element return validator_types[type].from_element( param, elem ) File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/parameters/validation.py", line 283, in from_element tool_data_table = param.tool.app.tool_data_tables[ table_name ] File "/localdata1/galaxy/glxmaint/src/lib/galaxy/tools/data/__init__.py", line 35, in __getitem__ return self.data_tables.__getitem__( key ) KeyError: 'gatk_picard_indexes' I fixed this by adding the appropriate entries to tool_data_table_conf.xml. I then reran the migrate_tools command successfully. However, now my "history_dataset_association" table in the database was blown away at some point. The table is now completely empty. Have you ever seen this before? Thanks, Eric ________________________________ From: Dannon Baker [dannon.baker@gmail.com] Sent: Tuesday, May 27, 2014 7:40 AM To: Paniagua, Eric Cc: galaxy-dev@lists.bx.psu.edu Subject: Re: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host Hey Eric, It looks like you have connection info for both tcp/ip connections and unix sockets in the connection strings. If you're logging in using "psql -h wigserv5.cshl.edu<http://wigserv5.cshl.edu> <snip>", then you only want the tcp/ip connection info. Drop the ?host=tmp off the third option you listed and I think you'll be up and running, so: postgresql://glxeric:XXXXX@wigserv5.cshl.edu:5432/glxeric<http://glxeric:XXXXX@wigserv5.cshl.edu:5432/glxeric> -Dannon On Sat, May 24, 2014 at 1:49 AM, Paniagua, Eric <epaniagu@cshl.edu<mailto:epaniagu@cshl.edu>> wrote: Dear Galaxy Developers, I've been banging my head against this one for a few days now. I have two Galaxy instances. One resides on a server called "genomics", which also hosts the corresponding PostgreSQL installation. The second also resides on "genomics", but its database is hosted on "wigserv5". Based on the tests I just ran and code I just read, sqlalchemy (not Galaxy) is ignoring the hostname/port part of the database_connection string. For reference, the connection strings I've tried are: postgresql://glxeric:XXXXX@/glxeric?host=/tmp postgresql://glxeric:XXXXX@wigserv5.cshl.edu/glxeric?host=/tmp<http://glxeric:XXXXX@wigserv5.cshl.edu/glxeric?host=/tmp> postgresql://glxeric:XXXXX@wigserv5.cshl.edu:5432/glxeric?host=/tmp<http://glxeric:XXXXX@wigserv5.cshl.edu:5432/glxeric?host=/tmp> postgresql://glxeric:XXXXX@adgdgdfdflkhjfdhfkl/glxeric?host=/tmp All of these appear to result in Galaxy connecting to the PostgreSQL installation on genomics, as determined by Galaxy schema version discrepancies and other constraints. With each connection string, Galaxy starts up normally. I force database activity by browsing saved histories. It works every time. By all appearances, the second Galaxy instance is using the PostgreSQL database hosted on "genomics", not on "wigserv5". All databases and roles exist, and the databases are populated. When I comment out the "database_connection" line in universe_wsgi.ini, I get errors arising from the later configuration of PostgreSQL-specific Galaxy options, as expected. I can connect to the database server on "wigserv5" using "psql -h wigserv5.cshl.edu<http://wigserv5.cshl.edu> -d glxeric -U glxeric" from the server "genomics". Have you ever observed this behavior from Galaxy or sqlalchemy? Thanks, Eric ___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at: http://lists.bx.psu.edu/ To search Galaxy mailing lists use the unified search at: http://galaxyproject.org/search/mailinglists/
On Tue, May 27, 2014 at 11:26 AM, Paniagua, Eric <epaniagu@cshl.edu> wrote:
Thanks for pointing that out! I missed it. I am now connecting to the remote database. I ran "sh manage_db.sh upgrade" and it upgraded from schema 114 to 118 without error messages. I then ran "sh ./scripts/migrate_tools/0010_tools.sh install_dependencies" and received the following error:
line 35, in __getitem__ return self.data_tables.__getitem__( key ) KeyError: 'gatk_picard_indexes'
I fixed this by adding the appropriate entries to tool_data_table_conf.xml. I then reran the migrate_tools command successfully. However, now my "history_dataset_association" table in the database was blown away at some point. The table is now completely empty. Have you ever seen this before?
I have not seen the tool migration issue before, but it seems harmless. The fact that your history_dataset_association table is empty is concerning if there was ever anything in it. Can you verify that there are datasets in the same database that *should* be associated to a history? It sounds like this galaxy instance has been used with different databases, and my hope is that the wires are crossed up here and there actually should not be any.
The "dataset" table is populated. I looked at the SQL dump file I used to copy the database, and it has create table and copy into statements for history_dataset_association, but it looks like there may have been an error while executing them. Trying to figure out how to get my data in... ________________________________ From: Dannon Baker [dannon.baker@gmail.com] Sent: Tuesday, May 27, 2014 11:43 AM To: Paniagua, Eric Cc: galaxy-dev@lists.bx.psu.edu Subject: Re: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host On Tue, May 27, 2014 at 11:26 AM, Paniagua, Eric <epaniagu@cshl.edu<mailto:epaniagu@cshl.edu>> wrote: Thanks for pointing that out! I missed it. I am now connecting to the remote database. I ran "sh manage_db.sh upgrade" and it upgraded from schema 114 to 118 without error messages. I then ran "sh ./scripts/migrate_tools/0010_tools.sh install_dependencies" and received the following error: line 35, in __getitem__ return self.data_tables.__getitem__( key ) KeyError: 'gatk_picard_indexes' I fixed this by adding the appropriate entries to tool_data_table_conf.xml. I then reran the migrate_tools command successfully. However, now my "history_dataset_association" table in the database was blown away at some point. The table is now completely empty. Have you ever seen this before? I have not seen the tool migration issue before, but it seems harmless. The fact that your history_dataset_association table is empty is concerning if there was ever anything in it. Can you verify that there are datasets in the same database that *should* be associated to a history? It sounds like this galaxy instance has been used with different databases, and my hope is that the wires are crossed up here and there actually should not be any.
Since the database has lost consistency, I'd really try a fresh pg_dump / import if that's possible. If there's an error this time around, note it and send it on over and we can figure out where to go from there. On Tue, May 27, 2014 at 11:48 AM, Paniagua, Eric <epaniagu@cshl.edu> wrote:
The "dataset" table is populated. I looked at the SQL dump file I used to copy the database, and it has create table and copy into statements for history_dataset_association, but it looks like there may have been an error while executing them. Trying to figure out how to get my data in... ________________________________ From: Dannon Baker [dannon.baker@gmail.com] Sent: Tuesday, May 27, 2014 11:43 AM To: Paniagua, Eric Cc: galaxy-dev@lists.bx.psu.edu Subject: Re: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host
On Tue, May 27, 2014 at 11:26 AM, Paniagua, Eric <epaniagu@cshl.edu <mailto:epaniagu@cshl.edu>> wrote: Thanks for pointing that out! I missed it. I am now connecting to the remote database. I ran "sh manage_db.sh upgrade" and it upgraded from schema 114 to 118 without error messages. I then ran "sh ./scripts/migrate_tools/0010_tools.sh install_dependencies" and received the following error:
line 35, in __getitem__ return self.data_tables.__getitem__( key ) KeyError: 'gatk_picard_indexes'
I fixed this by adding the appropriate entries to tool_data_table_conf.xml. I then reran the migrate_tools command successfully. However, now my "history_dataset_association" table in the database was blown away at some point. The table is now completely empty. Have you ever seen this before?
I have not seen the tool migration issue before, but it seems harmless. The fact that your history_dataset_association table is empty is concerning if there was ever anything in it. Can you verify that there are datasets in the same database that *should* be associated to a history? It sounds like this galaxy instance has been used with different databases, and my hope is that the wires are crossed up here and there actually should not be any.
I have created a fresh dump with $ pg_dump -U galaxyprod galaxyprod This time the import proceeded cleanly. Further, using PostgreSQL 9.1, I no longer get the error regarding a read only database cursor and getting the next history item number. I am currently running a test job to confirm that things are working as expected. However, just the fact that this job is running is a very good sign. ________________________________ From: Dannon Baker [dannon.baker@gmail.com] Sent: Tuesday, May 27, 2014 11:58 AM To: Paniagua, Eric Cc: galaxy-dev@lists.bx.psu.edu Subject: Re: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host Since the database has lost consistency, I'd really try a fresh pg_dump / import if that's possible. If there's an error this time around, note it and send it on over and we can figure out where to go from there. On Tue, May 27, 2014 at 11:48 AM, Paniagua, Eric <epaniagu@cshl.edu<mailto:epaniagu@cshl.edu>> wrote: The "dataset" table is populated. I looked at the SQL dump file I used to copy the database, and it has create table and copy into statements for history_dataset_association, but it looks like there may have been an error while executing them. Trying to figure out how to get my data in... ________________________________ From: Dannon Baker [dannon.baker@gmail.com<mailto:dannon.baker@gmail.com>] Sent: Tuesday, May 27, 2014 11:43 AM To: Paniagua, Eric Cc: galaxy-dev@lists.bx.psu.edu<mailto:galaxy-dev@lists.bx.psu.edu> Subject: Re: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host On Tue, May 27, 2014 at 11:26 AM, Paniagua, Eric <epaniagu@cshl.edu<mailto:epaniagu@cshl.edu><mailto:epaniagu@cshl.edu<mailto:epaniagu@cshl.edu>>> wrote: Thanks for pointing that out! I missed it. I am now connecting to the remote database. I ran "sh manage_db.sh upgrade" and it upgraded from schema 114 to 118 without error messages. I then ran "sh ./scripts/migrate_tools/0010_tools.sh install_dependencies" and received the following error: line 35, in __getitem__ return self.data_tables.__getitem__( key ) KeyError: 'gatk_picard_indexes' I fixed this by adding the appropriate entries to tool_data_table_conf.xml. I then reran the migrate_tools command successfully. However, now my "history_dataset_association" table in the database was blown away at some point. The table is now completely empty. Have you ever seen this before? I have not seen the tool migration issue before, but it seems harmless. The fact that your history_dataset_association table is empty is concerning if there was ever anything in it. Can you verify that there are datasets in the same database that *should* be associated to a history? It sounds like this galaxy instance has been used with different databases, and my hope is that the wires are crossed up here and there actually should not be any.
Correction. The job has entered the "waiting to run" phase, and doesn't appear to be leaving it. There is nothing of note in the server log. ________________________________ From: Paniagua, Eric Sent: Tuesday, May 27, 2014 12:41 PM To: Dannon Baker Cc: galaxy-dev@lists.bx.psu.edu Subject: RE: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host I have created a fresh dump with $ pg_dump -U galaxyprod galaxyprod This time the import proceeded cleanly. Further, using PostgreSQL 9.1, I no longer get the error regarding a read only database cursor and getting the next history item number. I am currently running a test job to confirm that things are working as expected. However, just the fact that this job is running is a very good sign. ________________________________ From: Dannon Baker [dannon.baker@gmail.com] Sent: Tuesday, May 27, 2014 11:58 AM To: Paniagua, Eric Cc: galaxy-dev@lists.bx.psu.edu Subject: Re: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host Since the database has lost consistency, I'd really try a fresh pg_dump / import if that's possible. If there's an error this time around, note it and send it on over and we can figure out where to go from there. On Tue, May 27, 2014 at 11:48 AM, Paniagua, Eric <epaniagu@cshl.edu<mailto:epaniagu@cshl.edu>> wrote: The "dataset" table is populated. I looked at the SQL dump file I used to copy the database, and it has create table and copy into statements for history_dataset_association, but it looks like there may have been an error while executing them. Trying to figure out how to get my data in... ________________________________ From: Dannon Baker [dannon.baker@gmail.com<mailto:dannon.baker@gmail.com>] Sent: Tuesday, May 27, 2014 11:43 AM To: Paniagua, Eric Cc: galaxy-dev@lists.bx.psu.edu<mailto:galaxy-dev@lists.bx.psu.edu> Subject: Re: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host On Tue, May 27, 2014 at 11:26 AM, Paniagua, Eric <epaniagu@cshl.edu<mailto:epaniagu@cshl.edu><mailto:epaniagu@cshl.edu<mailto:epaniagu@cshl.edu>>> wrote: Thanks for pointing that out! I missed it. I am now connecting to the remote database. I ran "sh manage_db.sh upgrade" and it upgraded from schema 114 to 118 without error messages. I then ran "sh ./scripts/migrate_tools/0010_tools.sh install_dependencies" and received the following error: line 35, in __getitem__ return self.data_tables.__getitem__( key ) KeyError: 'gatk_picard_indexes' I fixed this by adding the appropriate entries to tool_data_table_conf.xml. I then reran the migrate_tools command successfully. However, now my "history_dataset_association" table in the database was blown away at some point. The table is now completely empty. Have you ever seen this before? I have not seen the tool migration issue before, but it seems harmless. The fact that your history_dataset_association table is empty is concerning if there was ever anything in it. Can you verify that there are datasets in the same database that *should* be associated to a history? It sounds like this galaxy instance has been used with different databases, and my hope is that the wires are crossed up here and there actually should not be any.
Restarting the Galaxy server in multiple process mode appears to have helped. The test job is now running. ________________________________ From: Paniagua, Eric Sent: Tuesday, May 27, 2014 12:43 PM To: Dannon Baker Cc: galaxy-dev@lists.bx.psu.edu Subject: RE: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host Correction. The job has entered the "waiting to run" phase, and doesn't appear to be leaving it. There is nothing of note in the server log. ________________________________ From: Paniagua, Eric Sent: Tuesday, May 27, 2014 12:41 PM To: Dannon Baker Cc: galaxy-dev@lists.bx.psu.edu Subject: RE: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host I have created a fresh dump with $ pg_dump -U galaxyprod galaxyprod This time the import proceeded cleanly. Further, using PostgreSQL 9.1, I no longer get the error regarding a read only database cursor and getting the next history item number. I am currently running a test job to confirm that things are working as expected. However, just the fact that this job is running is a very good sign. ________________________________ From: Dannon Baker [dannon.baker@gmail.com] Sent: Tuesday, May 27, 2014 11:58 AM To: Paniagua, Eric Cc: galaxy-dev@lists.bx.psu.edu Subject: Re: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host Since the database has lost consistency, I'd really try a fresh pg_dump / import if that's possible. If there's an error this time around, note it and send it on over and we can figure out where to go from there. On Tue, May 27, 2014 at 11:48 AM, Paniagua, Eric <epaniagu@cshl.edu<mailto:epaniagu@cshl.edu>> wrote: The "dataset" table is populated. I looked at the SQL dump file I used to copy the database, and it has create table and copy into statements for history_dataset_association, but it looks like there may have been an error while executing them. Trying to figure out how to get my data in... ________________________________ From: Dannon Baker [dannon.baker@gmail.com<mailto:dannon.baker@gmail.com>] Sent: Tuesday, May 27, 2014 11:43 AM To: Paniagua, Eric Cc: galaxy-dev@lists.bx.psu.edu<mailto:galaxy-dev@lists.bx.psu.edu> Subject: Re: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host On Tue, May 27, 2014 at 11:26 AM, Paniagua, Eric <epaniagu@cshl.edu<mailto:epaniagu@cshl.edu><mailto:epaniagu@cshl.edu<mailto:epaniagu@cshl.edu>>> wrote: Thanks for pointing that out! I missed it. I am now connecting to the remote database. I ran "sh manage_db.sh upgrade" and it upgraded from schema 114 to 118 without error messages. I then ran "sh ./scripts/migrate_tools/0010_tools.sh install_dependencies" and received the following error: line 35, in __getitem__ return self.data_tables.__getitem__( key ) KeyError: 'gatk_picard_indexes' I fixed this by adding the appropriate entries to tool_data_table_conf.xml. I then reran the migrate_tools command successfully. However, now my "history_dataset_association" table in the database was blown away at some point. The table is now completely empty. Have you ever seen this before? I have not seen the tool migration issue before, but it seems harmless. The fact that your history_dataset_association table is empty is concerning if there was ever anything in it. Can you verify that there are datasets in the same database that *should* be associated to a history? It sounds like this galaxy instance has been used with different databases, and my hope is that the wires are crossed up here and there actually should not be any.
participants (2)
-
Dannon Baker -
Paniagua, Eric