Thank you for the reference! That does indeed solve the problem. Thanks, Eric -------- Original message -------- From: "Kandalaft, Iyad" Date:2014/05/28 08:48 (GMT-05:00) To: galaxy-dev@lists.bx.psu.edu,"Paniagua, Eric" Subject: Re: Problem using Galaxy with a PostgreSQL database on a remote host (Dannon Baker) Hey Eric, The connection string dialect is as follows for TCP/IP connections: dialect+driver://username:password@host:port/database So, the host=/tmp is not necessary when are specifying a hostname (resolves to the IP) and a port because that is referring to UNIX sockets. If you were using SQL Alchemy with a unix socket to connect to the postgres server, then it would look something like this. "postgresql+psycopg2://user:password@/dbname?host=/var/lib/postgresql" Note that there's no hostname and port. Just the socket is specified. See http://docs.sqlalchemy.org/en/rel_0_9/core/engines.html for more info. Iyad Kandalaft Bioinformatics Programmer Microbial Biodiversity Bioinformatics Science & Technology Branch Agriculture & Agri-Food Canada Iyad.Kandalaft@agr.gc.ca | (613) 759-1228 ________________________________________ From: galaxy-dev-bounces@lists.bx.psu.edu [galaxy-dev-bounces@lists.bx.psu.edu] on behalf of galaxy-dev-request@lists.bx.psu.edu [galaxy-dev-request@lists.bx.psu.edu] Sent: May 27, 2014 12:00 PM To: galaxy-dev@lists.bx.psu.edu Subject: galaxy-dev Digest, Vol 95, Issue 25 Send galaxy-dev mailing list submissions to galaxy-dev@lists.bx.psu.edu To subscribe or unsubscribe via the World Wide Web, visit http://lists.bx.psu.edu/listinfo/galaxy-dev or, via email, send a message with subject or body 'help' to galaxy-dev-request@lists.bx.psu.edu You can reach the person managing the list at galaxy-dev-owner@lists.bx.psu.edu When replying, please edit your Subject line so it is more specific than "Re: Contents of galaxy-dev digest..." HEY! This is important! If you reply to a thread in a digest, please 1. Change the subject of your response from "Galaxy-dev Digest Vol ..." to the original subject for the thread. 2. Strip out everything else in the digest that is not part of the thread you are responding to. Why? 1. This will keep the subject meaningful. People will have some idea from the subject line if they should read it or not. 2. Not doing this greatly increases the number of emails that match search queries, but that aren't actually informative. Today's Topics: 1. Re: Problem using Galaxy with a PostgreSQL database on a remote host (Dannon Baker) 2. Changes in admin menu - Upload files (Julien Daligault) 3. Re: Changes in admin menu - Upload files (Dannon Baker) 4. Re: New tool on TestToolShed still not tested (Greg Von Kuster) 5. Re: New tool on TestToolShed still not tested (Peter Cock) 6. Re: Main ToolShed wrong report: Repository does not have a test-data directory. (Greg Von Kuster) 7. Re: Installation failure on Test Tool Shed (Greg Von Kuster) 8. Re: Old Tool Shed URL http://community.g2.bx.psu.edu/ dead (Greg Von Kuster) 9. Re: ToolShed: Uploaded archives can only include regular directories and files (Greg Von Kuster) 10. Re: Old Tool Shed URL http://community.g2.bx.psu.edu/ dead (Peter Cock) 11. Re: ToolShed: Uploaded archives can only include regular directories and files (Peter Cock) 12. Re: Old Tool Shed URL http://community.g2.bx.psu.edu/ dead (Greg Von Kuster) 13. Re: Problem using Galaxy with a PostgreSQL database on a remote host (Paniagua, Eric) 14. Uploading files to galaxy from a folder (Kandalaft, Iyad) 15. complex help for conditional param (Jun Fan) 16. Re: complex help for conditional param (Peter Cock) 17. Re: Problem using Galaxy with a PostgreSQL database on a remote host (Dannon Baker) 18. Re: Problem using Galaxy with a PostgreSQL database on a remote host (Paniagua, Eric) 19. Re: Problem using Galaxy with a PostgreSQL database on a remote host (Dannon Baker) ---------------------------------------------------------------------- Message: 1 Date: Tue, 27 May 2014 07:40:13 -0400 From: Dannon Baker <dannon.baker@gmail.com> To: "Paniagua, Eric" <epaniagu@cshl.edu> Cc: "galaxy-dev@lists.bx.psu.edu" <galaxy-dev@lists.bx.psu.edu> Subject: Re: [galaxy-dev] Problem using Galaxy with a PostgreSQL database on a remote host Message-ID: <CAGn_WZn7WkihVYidHLOvL0J4cbU_xEeM02N1fKGGo8bcnYczCA@mail.gmail.com> Content-Type: text/plain; charset="utf-8" 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/