Urgent Question about PostgreSQL Compatibility

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Urgent Question about PostgreSQL Compatibility

Paniagua, Eric
Hi,

I am running a Galaxy instance at Cold Spring Harbor Laboratory, and in the process of preparing an update I have encountered some problems.  When I try to create a new dataset (e.g. by running a tool), I receive an error message indicating a syntactic problem with backend database commands.  Specifically, the error mentions a read-only cursor and occurs when the system is trying to look up the history hid number for the new dataset, a value which (as far as I understand it) should be an autoincrementing primary key.

My codebase is the 68a8b0397947 commit on the stable branch, plus some custom tool definitions, but no real modification to any core Galaxy code.  The database I am using is PostgreSQL 9.1.  I am using Python 2.6.4.  In the target (production) environment, we run with 3 web server processes and 3 job runner processes, but I am encountering this error in a single-Galaxy-process test instance.  The error is reported both in the logs (with a traceback originating in the bowels of SQLAlchemy) and via the web interface.

I dug through the commit history and found a comit (e1bc855165bc) with the comment "Upgrade psycopg2 to 2.5.1 (statically linked to PostgreSQL 9.2.4)." on Sep 23, 2013.  I am wondering if this means I need to upgrade my database backend to PostgreSQL 2.4.x.  The wrinkle lies in the fact that I need to keep 2 parallel Galaxy instances running for an extended time period (~1 month).  The first is the current (not updated) local Galaxy instance, and the second is the new (updated) Galaxy instance, which naturally implies diverging databases.  Does this mean I will need to maintain parallel installations of PostgreSQL 9.1 and 9.2.x?  How would I go about setting up that configuration?

Any assistance on debugging this error would be greatly appreciated.  Our initial goal was to roll out this update tomorrow afternoon, but that may need to be delayed because of this show-stopper.  If I can provide any further useful information, please let me know, and I will be happy to do so.

Best,
Eric Paniagua

___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Urgent Question about PostgreSQL Compatibility

Dannon Baker-2
Hey Eric,

Looking into this.  Can you send the exact error/trace if available?

-Dannon


On Thu, May 22, 2014 at 10:17 AM, Paniagua, Eric <[hidden email]> wrote:
Hi,

I am running a Galaxy instance at Cold Spring Harbor Laboratory, and in the process of preparing an update I have encountered some problems.  When I try to create a new dataset (e.g. by running a tool), I receive an error message indicating a syntactic problem with backend database commands.  Specifically, the error mentions a read-only cursor and occurs when the system is trying to look up the history hid number for the new dataset, a value which (as far as I understand it) should be an autoincrementing primary key.

My codebase is the 68a8b0397947 commit on the stable branch, plus some custom tool definitions, but no real modification to any core Galaxy code.  The database I am using is PostgreSQL 9.1.  I am using Python 2.6.4.  In the target (production) environment, we run with 3 web server processes and 3 job runner processes, but I am encountering this error in a single-Galaxy-process test instance.  The error is reported both in the logs (with a traceback originating in the bowels of SQLAlchemy) and via the web interface.

I dug through the commit history and found a comit (e1bc855165bc) with the comment "Upgrade psycopg2 to 2.5.1 (statically linked to PostgreSQL 9.2.4)." on Sep 23, 2013.  I am wondering if this means I need to upgrade my database backend to PostgreSQL 2.4.x.  The wrinkle lies in the fact that I need to keep 2 parallel Galaxy instances running for an extended time period (~1 month).  The first is the current (not updated) local Galaxy instance, and the second is the new (updated) Galaxy instance, which naturally implies diverging databases.  Does this mean I will need to maintain parallel installations of PostgreSQL 9.1 and 9.2.x?  How would I go about setting up that configuration?

Any assistance on debugging this error would be greatly appreciated.  Our initial goal was to roll out this update tomorrow afternoon, but that may need to be delayed because of this show-stopper.  If I can provide any further useful information, please let me know, and I will be happy to do so.

Best,
Eric Paniagua

___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/


___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Urgent Question about PostgreSQL Compatibility

Paniagua, Eric
Hi Dannon,

I am in a meeting right now.  I should be done at noon.  I'll send you more info then.  Thank you for your assistance!

Best,
Eric


-------- Original message --------
From: Dannon Baker
Date:2014/05/22 11:08 (GMT-05:00)
To: "Paniagua, Eric"
Cc: "Galaxy ‎[[hidden email]]‎"
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hey Eric,

Looking into this.  Can you send the exact error/trace if available?

-Dannon


On Thu, May 22, 2014 at 10:17 AM, Paniagua, Eric <[hidden email]<mailto:[hidden email]>> wrote:
Hi,

I am running a Galaxy instance at Cold Spring Harbor Laboratory, and in the process of preparing an update I have encountered some problems.  When I try to create a new dataset (e.g. by running a tool), I receive an error message indicating a syntactic problem with backend database commands.  Specifically, the error mentions a read-only cursor and occurs when the system is trying to look up the history hid number for the new dataset, a value which (as far as I understand it) should be an autoincrementing primary key.

My codebase is the 68a8b0397947 commit on the stable branch, plus some custom tool definitions, but no real modification to any core Galaxy code.  The database I am using is PostgreSQL 9.1.  I am using Python 2.6.4.  In the target (production) environment, we run with 3 web server processes and 3 job runner processes, but I am encountering this error in a single-Galaxy-process test instance.  The error is reported both in the logs (with a traceback originating in the bowels of SQLAlchemy) and via the web interface.

I dug through the commit history and found a comit (e1bc855165bc) with the comment "Upgrade psycopg2 to 2.5.1 (statically linked to PostgreSQL 9.2.4)." on Sep 23, 2013.  I am wondering if this means I need to upgrade my database backend to PostgreSQL 2.4.x.  The wrinkle lies in the fact that I need to keep 2 parallel Galaxy instances running for an extended time period (~1 month).  The first is the current (not updated) local Galaxy instance, and the second is the new (updated) Galaxy instance, which naturally implies diverging databases.  Does this mean I will need to maintain parallel installations of PostgreSQL 9.1 and 9.2.x?  How would I go about setting up that configuration?

Any assistance on debugging this error would be greatly appreciated.  Our initial goal was to roll out this update tomorrow afternoon, but that may need to be delayed because of this show-stopper.  If I can provide any further useful information, please let me know, and I will be happy to do so.

Best,
Eric Paniagua

___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/


___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Urgent Question about PostgreSQL Compatibility

Paniagua, Eric
Hi Dannon,

I have attached a screenshot of the error as it appears in the Galaxy web site and a screenshot of the log.

Please let me know if I can provide any more information.

Also, please note that the version information I gave for PostgreSQL before was incorrect.  I am actually running PostgreSQL 8.1.22.

Thanks for your assistance,
Eric

________________________________________
From: [hidden email] [[hidden email]] on behalf of Paniagua, Eric [[hidden email]]
Sent: Thursday, May 22, 2014 11:14 AM
To: Dannon Baker
Cc: Galaxy ‎[[hidden email]]‎
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hi Dannon,

I am in a meeting right now.  I should be done at noon.  I'll send you more info then.  Thank you for your assistance!

Best,
Eric


-------- Original message --------
From: Dannon Baker
Date:2014/05/22 11:08 (GMT-05:00)
To: "Paniagua, Eric"
Cc: "Galaxy ‎[[hidden email]]‎"
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hey Eric,

Looking into this.  Can you send the exact error/trace if available?

-Dannon


On Thu, May 22, 2014 at 10:17 AM, Paniagua, Eric <[hidden email]<mailto:[hidden email]>> wrote:
Hi,

I am running a Galaxy instance at Cold Spring Harbor Laboratory, and in the process of preparing an update I have encountered some problems.  When I try to create a new dataset (e.g. by running a tool), I receive an error message indicating a syntactic problem with backend database commands.  Specifically, the error mentions a read-only cursor and occurs when the system is trying to look up the history hid number for the new dataset, a value which (as far as I understand it) should be an autoincrementing primary key.

My codebase is the 68a8b0397947 commit on the stable branch, plus some custom tool definitions, but no real modification to any core Galaxy code.  The database I am using is PostgreSQL 9.1.  I am using Python 2.6.4.  In the target (production) environment, we run with 3 web server processes and 3 job runner processes, but I am encountering this error in a single-Galaxy-process test instance.  The error is reported both in the logs (with a traceback originating in the bowels of SQLAlchemy) and via the web interface.

I dug through the commit history and found a comit (e1bc855165bc) with the comment "Upgrade psycopg2 to 2.5.1 (statically linked to PostgreSQL 9.2.4)." on Sep 23, 2013.  I am wondering if this means I need to upgrade my database backend to PostgreSQL 2.4.x.  The wrinkle lies in the fact that I need to keep 2 parallel Galaxy instances running for an extended time period (~1 month).  The first is the current (not updated) local Galaxy instance, and the second is the new (updated) Galaxy instance, which naturally implies diverging databases.  Does this mean I will need to maintain parallel installations of PostgreSQL 9.1 and 9.2.x?  How would I go about setting up that configuration?

Any assistance on debugging this error would be greatly appreciated.  Our initial goal was to roll out this update tomorrow afternoon, but that may need to be delayed because of this show-stopper.  If I can provide any further useful information, please let me know, and I will be happy to do so.

Best,
Eric Paniagua

___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/


___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/

___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/

error_web.png (7K) Download Attachment
error_log.png (56K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Urgent Question about PostgreSQL Compatibility

Paniagua, Eric
Hi Dannon,

There have been some developments, and I would like to update my question accordingly.

I have set up PostgreSQL 9.1 on a separate box from our Galaxy server, and ferried the database contents over.  My question is this:

What is the appropriate syntax to use for the parameter "database_connection" in universe_wsgi.ini to use a remote database server?  Specifically:

Galaxy host: genomics.cshl.edu
Database host: wigserv5.cshl.edu
Database name: glxeric
Database user/role: glxeric
Database port: 5432 (postgresql default port)
Database password: not required under the current configuration.

Can someone please explain how to fill in the "database_connection" parameter appropriately?

Many thanks,
Eric

________________________________________
From: [hidden email] [[hidden email]] on behalf of Paniagua, Eric [[hidden email]]
Sent: Thursday, May 22, 2014 1:38 PM
To: Dannon Baker
Cc: Galaxy ‎[[hidden email]]‎
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hi Dannon,

I have attached a screenshot of the error as it appears in the Galaxy web site and a screenshot of the log.

Please let me know if I can provide any more information.

Also, please note that the version information I gave for PostgreSQL before was incorrect.  I am actually running PostgreSQL 8.1.22.

Thanks for your assistance,
Eric

________________________________________
From: [hidden email] [[hidden email]] on behalf of Paniagua, Eric [[hidden email]]
Sent: Thursday, May 22, 2014 11:14 AM
To: Dannon Baker
Cc: Galaxy ‎[[hidden email]]‎
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hi Dannon,

I am in a meeting right now.  I should be done at noon.  I'll send you more info then.  Thank you for your assistance!

Best,
Eric


-------- Original message --------
From: Dannon Baker
Date:2014/05/22 11:08 (GMT-05:00)
To: "Paniagua, Eric"
Cc: "Galaxy ‎[[hidden email]]‎"
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hey Eric,

Looking into this.  Can you send the exact error/trace if available?

-Dannon


On Thu, May 22, 2014 at 10:17 AM, Paniagua, Eric <[hidden email]<mailto:[hidden email]>> wrote:
Hi,

I am running a Galaxy instance at Cold Spring Harbor Laboratory, and in the process of preparing an update I have encountered some problems.  When I try to create a new dataset (e.g. by running a tool), I receive an error message indicating a syntactic problem with backend database commands.  Specifically, the error mentions a read-only cursor and occurs when the system is trying to look up the history hid number for the new dataset, a value which (as far as I understand it) should be an autoincrementing primary key.

My codebase is the 68a8b0397947 commit on the stable branch, plus some custom tool definitions, but no real modification to any core Galaxy code.  The database I am using is PostgreSQL 9.1.  I am using Python 2.6.4.  In the target (production) environment, we run with 3 web server processes and 3 job runner processes, but I am encountering this error in a single-Galaxy-process test instance.  The error is reported both in the logs (with a traceback originating in the bowels of SQLAlchemy) and via the web interface.

I dug through the commit history and found a comit (e1bc855165bc) with the comment "Upgrade psycopg2 to 2.5.1 (statically linked to PostgreSQL 9.2.4)." on Sep 23, 2013.  I am wondering if this means I need to upgrade my database backend to PostgreSQL 2.4.x.  The wrinkle lies in the fact that I need to keep 2 parallel Galaxy instances running for an extended time period (~1 month).  The first is the current (not updated) local Galaxy instance, and the second is the new (updated) Galaxy instance, which naturally implies diverging databases.  Does this mean I will need to maintain parallel installations of PostgreSQL 9.1 and 9.2.x?  How would I go about setting up that configuration?

Any assistance on debugging this error would be greatly appreciated.  Our initial goal was to roll out this update tomorrow afternoon, but that may need to be delayed because of this show-stopper.  If I can provide any further useful information, please let me know, and I will be happy to do so.

Best,
Eric Paniagua

___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/


___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/

___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Urgent Question about PostgreSQL Compatibility

Dannon Baker-2
Hey Eric,

We use sqlalchemy, and the detailed documentation is here:


That said, the basic format is:  dialect+driver://username:password@host:port/database

So, you're going to be looking at something like: postgres://glxeric@...:5432/glxeric



On Fri, May 23, 2014 at 3:49 PM, Paniagua, Eric <[hidden email]> wrote:
Hi Dannon,

There have been some developments, and I would like to update my question accordingly.

I have set up PostgreSQL 9.1 on a separate box from our Galaxy server, and ferried the database contents over.  My question is this:

What is the appropriate syntax to use for the parameter "database_connection" in universe_wsgi.ini to use a remote database server?  Specifically:

Galaxy host: genomics.cshl.edu
Database host: wigserv5.cshl.edu
Database name: glxeric
Database user/role: glxeric
Database port: 5432 (postgresql default port)
Database password: not required under the current configuration.

Can someone please explain how to fill in the "database_connection" parameter appropriately?

Many thanks,
Eric

________________________________________
From: [hidden email] [[hidden email]] on behalf of Paniagua, Eric [[hidden email]]
Sent: Thursday, May 22, 2014 1:38 PM
To: Dannon Baker
Cc: Galaxy ‎[[hidden email]]‎
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hi Dannon,

I have attached a screenshot of the error as it appears in the Galaxy web site and a screenshot of the log.

Please let me know if I can provide any more information.

Also, please note that the version information I gave for PostgreSQL before was incorrect.  I am actually running PostgreSQL 8.1.22.

Thanks for your assistance,
Eric

________________________________________
From: [hidden email] [[hidden email]] on behalf of Paniagua, Eric [[hidden email]]
Sent: Thursday, May 22, 2014 11:14 AM
To: Dannon Baker
Cc: Galaxy ‎[[hidden email]]‎
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hi Dannon,

I am in a meeting right now.  I should be done at noon.  I'll send you more info then.  Thank you for your assistance!

Best,
Eric


-------- Original message --------
From: Dannon Baker
Date:2014/05/22 11:08 (GMT-05:00)
To: "Paniagua, Eric"
Cc: "Galaxy ‎[[hidden email]]‎"
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hey Eric,

Looking into this.  Can you send the exact error/trace if available?

-Dannon


On Thu, May 22, 2014 at 10:17 AM, Paniagua, Eric <[hidden email]<mailto:[hidden email]>> wrote:
Hi,

I am running a Galaxy instance at Cold Spring Harbor Laboratory, and in the process of preparing an update I have encountered some problems.  When I try to create a new dataset (e.g. by running a tool), I receive an error message indicating a syntactic problem with backend database commands.  Specifically, the error mentions a read-only cursor and occurs when the system is trying to look up the history hid number for the new dataset, a value which (as far as I understand it) should be an autoincrementing primary key.

My codebase is the 68a8b0397947 commit on the stable branch, plus some custom tool definitions, but no real modification to any core Galaxy code.  The database I am using is PostgreSQL 9.1.  I am using Python 2.6.4.  In the target (production) environment, we run with 3 web server processes and 3 job runner processes, but I am encountering this error in a single-Galaxy-process test instance.  The error is reported both in the logs (with a traceback originating in the bowels of SQLAlchemy) and via the web interface.

I dug through the commit history and found a comit (e1bc855165bc) with the comment "Upgrade psycopg2 to 2.5.1 (statically linked to PostgreSQL 9.2.4)." on Sep 23, 2013.  I am wondering if this means I need to upgrade my database backend to PostgreSQL 2.4.x.  The wrinkle lies in the fact that I need to keep 2 parallel Galaxy instances running for an extended time period (~1 month).  The first is the current (not updated) local Galaxy instance, and the second is the new (updated) Galaxy instance, which naturally implies diverging databases.  Does this mean I will need to maintain parallel installations of PostgreSQL 9.1 and 9.2.x?  How would I go about setting up that configuration?

Any assistance on debugging this error would be greatly appreciated.  Our initial goal was to roll out this update tomorrow afternoon, but that may need to be delayed because of this show-stopper.  If I can provide any further useful information, please let me know, and I will be happy to do so.

Best,
Eric Paniagua

___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/


___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/


___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Urgent Question about PostgreSQL Compatibility

Paniagua, Eric
In reply to this post by Paniagua, Eric
Hi Dannon,

I just wanted to provide you with some new information regarding the situation.  I have installed PostgreSQL 9.1 on another box and migrated the database.  I have Galaxy communicating with the remote database.

However, I still see the same error I originally reported.  So using a newer version of PostgreSQL (at least 9.1) didn't fix the problem.

Thanks for your help!

Thanks,
Eric

________________________________________
From: Paniagua, Eric
Sent: Thursday, May 22, 2014 1:36 PM
To: Dannon Baker
Cc: Galaxy ‎[[hidden email]]‎
Subject: RE: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hi Dannon,

I have attached a screenshot of the error as it appears in the Galaxy web site and a screenshot of the log.

Please let me know if I can provide any more information.

Also, please note that the version information I gave for PostgreSQL before was incorrect.  I am actually running PostgreSQL 8.1.22.

Thanks for your assistance,
Eric

________________________________________
From: [hidden email] [[hidden email]] on behalf of Paniagua, Eric [[hidden email]]
Sent: Thursday, May 22, 2014 11:14 AM
To: Dannon Baker
Cc: Galaxy ‎[[hidden email]]‎
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hi Dannon,

I am in a meeting right now.  I should be done at noon.  I'll send you more info then.  Thank you for your assistance!

Best,
Eric


-------- Original message --------
From: Dannon Baker
Date:2014/05/22 11:08 (GMT-05:00)
To: "Paniagua, Eric"
Cc: "Galaxy ‎[[hidden email]]‎"
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hey Eric,

Looking into this.  Can you send the exact error/trace if available?

-Dannon


On Thu, May 22, 2014 at 10:17 AM, Paniagua, Eric <[hidden email]<mailto:[hidden email]>> wrote:
Hi,

I am running a Galaxy instance at Cold Spring Harbor Laboratory, and in the process of preparing an update I have encountered some problems.  When I try to create a new dataset (e.g. by running a tool), I receive an error message indicating a syntactic problem with backend database commands.  Specifically, the error mentions a read-only cursor and occurs when the system is trying to look up the history hid number for the new dataset, a value which (as far as I understand it) should be an autoincrementing primary key.

My codebase is the 68a8b0397947 commit on the stable branch, plus some custom tool definitions, but no real modification to any core Galaxy code.  The database I am using is PostgreSQL 9.1.  I am using Python 2.6.4.  In the target (production) environment, we run with 3 web server processes and 3 job runner processes, but I am encountering this error in a single-Galaxy-process test instance.  The error is reported both in the logs (with a traceback originating in the bowels of SQLAlchemy) and via the web interface.

I dug through the commit history and found a comit (e1bc855165bc) with the comment "Upgrade psycopg2 to 2.5.1 (statically linked to PostgreSQL 9.2.4)." on Sep 23, 2013.  I am wondering if this means I need to upgrade my database backend to PostgreSQL 2.4.x.  The wrinkle lies in the fact that I need to keep 2 parallel Galaxy instances running for an extended time period (~1 month).  The first is the current (not updated) local Galaxy instance, and the second is the new (updated) Galaxy instance, which naturally implies diverging databases.  Does this mean I will need to maintain parallel installations of PostgreSQL 9.1 and 9.2.x?  How would I go about setting up that configuration?

Any assistance on debugging this error would be greatly appreciated.  Our initial goal was to roll out this update tomorrow afternoon, but that may need to be delayed because of this show-stopper.  If I can provide any further useful information, please let me know, and I will be happy to do so.

Best,
Eric Paniagua

___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/


___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/

___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Urgent Question about PostgreSQL Compatibility

Paniagua, Eric
In reply to this post by Dannon Baker-2
Thank you!

-------- Original message --------
From: Dannon Baker
Date:2014/05/23 16:02 (GMT-05:00)
To: "Paniagua, Eric"
Cc: "Galaxy ‎[[hidden email]]‎"
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hey Eric,

We use sqlalchemy, and the detailed documentation is here:

http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html#postgresql

That said, the basic format is:  dialect+driver://username:password@host:port/database

So, you're going to be looking at something like: postgres://[hidden email]:5432/glxeric<http://glxeric@...:5432/glxeric>



On Fri, May 23, 2014 at 3:49 PM, Paniagua, Eric <[hidden email]<mailto:[hidden email]>> wrote:
Hi Dannon,

There have been some developments, and I would like to update my question accordingly.

I have set up PostgreSQL 9.1 on a separate box from our Galaxy server, and ferried the database contents over.  My question is this:

What is the appropriate syntax to use for the parameter "database_connection" in universe_wsgi.ini to use a remote database server?  Specifically:

Galaxy host: genomics.cshl.edu<http://genomics.cshl.edu>
Database host: wigserv5.cshl.edu<http://wigserv5.cshl.edu>
Database name: glxeric
Database user/role: glxeric
Database port: 5432 (postgresql default port)
Database password: not required under the current configuration.

Can someone please explain how to fill in the "database_connection" parameter appropriately?

Many thanks,
Eric

________________________________________
From: [hidden email]<mailto:[hidden email]> [[hidden email]<mailto:[hidden email]>] on behalf of Paniagua, Eric [[hidden email]<mailto:[hidden email]>]
Sent: Thursday, May 22, 2014 1:38 PM
To: Dannon Baker
Cc: Galaxy ‎[[hidden email]<mailto:[hidden email]>]‎
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hi Dannon,

I have attached a screenshot of the error as it appears in the Galaxy web site and a screenshot of the log.

Please let me know if I can provide any more information.

Also, please note that the version information I gave for PostgreSQL before was incorrect.  I am actually running PostgreSQL 8.1.22.

Thanks for your assistance,
Eric

________________________________________
From: [hidden email]<mailto:[hidden email]> [[hidden email]<mailto:[hidden email]>] on behalf of Paniagua, Eric [[hidden email]<mailto:[hidden email]>]
Sent: Thursday, May 22, 2014 11:14 AM
To: Dannon Baker
Cc: Galaxy ‎[[hidden email]<mailto:[hidden email]>]‎
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hi Dannon,

I am in a meeting right now.  I should be done at noon.  I'll send you more info then.  Thank you for your assistance!

Best,
Eric


-------- Original message --------
From: Dannon Baker
Date:2014/05/22 11:08 (GMT-05:00)
To: "Paniagua, Eric"
Cc: "Galaxy ‎[[hidden email]<mailto:[hidden email]>]‎"
Subject: Re: [galaxy-user] Urgent Question about PostgreSQL Compatibility

Hey Eric,

Looking into this.  Can you send the exact error/trace if available?

-Dannon


On Thu, May 22, 2014 at 10:17 AM, Paniagua, Eric <[hidden email]<mailto:[hidden email]><mailto:[hidden email]<mailto:[hidden email]>>> wrote:
Hi,

I am running a Galaxy instance at Cold Spring Harbor Laboratory, and in the process of preparing an update I have encountered some problems.  When I try to create a new dataset (e.g. by running a tool), I receive an error message indicating a syntactic problem with backend database commands.  Specifically, the error mentions a read-only cursor and occurs when the system is trying to look up the history hid number for the new dataset, a value which (as far as I understand it) should be an autoincrementing primary key.

My codebase is the 68a8b0397947 commit on the stable branch, plus some custom tool definitions, but no real modification to any core Galaxy code.  The database I am using is PostgreSQL 9.1.  I am using Python 2.6.4.  In the target (production) environment, we run with 3 web server processes and 3 job runner processes, but I am encountering this error in a single-Galaxy-process test instance.  The error is reported both in the logs (with a traceback originating in the bowels of SQLAlchemy) and via the web interface.

I dug through the commit history and found a comit (e1bc855165bc) with the comment "Upgrade psycopg2 to 2.5.1 (statically linked to PostgreSQL 9.2.4)." on Sep 23, 2013.  I am wondering if this means I need to upgrade my database backend to PostgreSQL 2.4.x.  The wrinkle lies in the fact that I need to keep 2 parallel Galaxy instances running for an extended time period (~1 month).  The first is the current (not updated) local Galaxy instance, and the second is the new (updated) Galaxy instance, which naturally implies diverging databases.  Does this mean I will need to maintain parallel installations of PostgreSQL 9.1 and 9.2.x?  How would I go about setting up that configuration?

Any assistance on debugging this error would be greatly appreciated.  Our initial goal was to roll out this update tomorrow afternoon, but that may need to be delayed because of this show-stopper.  If I can provide any further useful information, please let me know, and I will be happy to do so.

Best,
Eric Paniagua

___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/


___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/


___________________________________________________________
The Galaxy User List is being replaced by the Galaxy Biostar
User Support Forum at https://biostar.usegalaxy.org/

Posts to this list will be disabled in May 2014.  In the
meantime, you are encouraged to post all new questions to
Galaxy Biostar.

For discussion of local Galaxy instances and the Galaxy
source code, please use the Galaxy Development list:

  http://lists.bx.psu.edu/listinfo/galaxy-dev

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/
Loading...