Drupal does not allow for PostgreSQL connections via unix socket. I have written a patch that allows it to do so. The effect of this patch is that a hostname of 'localhost' in a pgsql:// $db_url will use Unix sockets unless a port it set in the URL. A hostname of '127.0.0.1' will use TCP/IP as before.
Examples:
Unix socket: pgsql://user:pass@localhost/db
TCP/IP: pgsql://user:pass@localhost:4444/db
TCP/IP: pgsql://user:pass@127.0.0.1/db
TCP/IP: pgsql://user:pass@127.0.0.1:4444/db
The patch is attached to this bug report. From a top-level Drupal directory, type 'patch -p0 < /path/to/drupal-pgsql-socket.patch' to apply it (omit the single-quotes).
Comment | File | Size | Author |
---|---|---|---|
#21 | database.pgsql_.inc_.patch | 960 bytes | Sajut |
#16 | database.pgsql_unixsockets.patch | 626 bytes | StevenPatz |
drupal-pgsql-socket.patch | 917 bytes | iamtheari | |
Comments
Comment #1
chx CreditAttribution: chx commentedFirst, welcome among contributors and thanks for the patch, it looks useful. There are a few general problems: only bugfixes go into 4.6.x everything else, like this should be for HEAD. Other problem is with coding style, we use
also you spacing issues, like spaces are a dot. The rule, as explained to me by Steven is that there is never a space between a dot and a quote always otherwise. There is also coding style guideline in CVS.
Comment #2
iamtheari CreditAttribution: iamtheari commentedI don't run the CVS version, but somehow I doubt that the file in question has changed significantly enough to prevent the patch from working. As to the coding style, see now that Drupal's guidelines require the space between "if" and "(", but the extra spaces around the dots was from the original code in the patched file, as you can see from the lines of the patch beginning with a "-". Regardless, I hope that my code can be hammered into shape so as to fit into Drupal.
Just be sure to warn people that the behavior of 'pgsql://user:password@localhost/db' has changed, if this makes it in.
Comment #3
Steven CreditAttribution: Steven commentedAre Unix sockets something that is standard supported and enabled for PGSQL? If not, it is confusing to change the behaviour just because you enter "localhost" with no port. Why not make a special reserved hostname (e.g. 'unixsocket') for this? It can be documented easily in settings.php.
Comment #4
Cvbge CreditAttribution: Cvbge commentedAFAIK sockets are supported by both mysql and postgres... but I think they might not allways be accessible (well, the same can be said about tcp connections).
I also think that we should not break existing configurations and use other syntax for specifying. Also you might need to provide path to unix socket.
Comment #5
iamtheari CreditAttribution: iamtheari commentedPostgres almost prefers Unix socket connections to TCP/IP. It is easier to set up working Unix sockets than TCP/IP connections with Postgres.
The PHP pg_connect() function's connection string will connect via Unix socket if the "host=" element is omitted. That is how my patch works, but Drupal's means of handling the database URL prevented a blank hostname from being accepted. Also, using a made-up hostname to signify a Unix socket is kludgy, at best.
Note that no path needs to be specified. The way PHP connects via Unix socket is to use the local Postgres libraries, and it seems to find the socket just fine if it exists.
Comment #6
Steven CreditAttribution: Steven commentedI still think we should go for the "made up name" path. It means there is no hidden switch between unix socket and tcp/ip just because you remove the port number for example.
We get enough support questions about configuration already. Let's just document the magic socket host name (I'd go for 'socket' for simplicity) and do it that way.
Comment #7
Cvbge CreditAttribution: Cvbge commentedI've just checked php documentation. It seems pg_connect() do not have socket-path argument :(
OTOH mysql_connect() accepts socket path (you can put it in place of port number). [mysql is weird anyway because it will try to connect using sockets when you use 'localhost' as a server name, even if you specify normal tcp/ip port number...]
I think that if we are going to support sockets officially we should support socket paths as well!
So the next question is about string syntax. I think it was wrong to use dbtype://user:pass@host:port/dbname syntax. Clearer, simpler, easier to modify would be some sort of hash, like
etc. But it's too late to change it now, right?
So, we have to go with current syntax. I'm not sure what would be the best solution.
But, about the . I don't see how removing port number would make the switch?
Adding socket path support is kludgy. It's because of slashes in the path which collide with /dbname...
I really don't know how to solve this problem, but I belive it needs to be solved. If it can't be solved using current syntax maybe we should go for array syntax as an alternative syntax?
Comment #8
iamtheari CreditAttribution: iamtheari commentedI guess I wouldn't complain about 'socket' being the magic name, as long as it's clearly and obviously documented. It took me enough time to find out that PG sockets weren't supported in the first place, reading through the documentation carefully.
As to socket paths, if pg_connect() doesn't support them then I don't see either an obvious way to implement them or an obvious reason to do so. It would be nice to be consistent and complete, yes, but if the underlying software doesn't support it then there's no real need to waste our time with it, either. It obviously works well enough for all the PHP/Postgres developers of the world.
Comment #9
Cvbge CreditAttribution: Cvbge commentedmysql_connect() supports socket path.
Comment #10
iamtheari CreditAttribution: iamtheari commentedWhy exactly are mysql_connect()'s capabilities relevant?
Comment #11
Cvbge CreditAttribution: Cvbge commentedAre they now enough relevant?
Comment #12
Cvbge CreditAttribution: Cvbge commentedAnd topic change.
Comment #13
Uwe Hermann CreditAttribution: Uwe Hermann commentedPatch doesn't apply anymore.
Comment #14
Jaza CreditAttribution: Jaza commentedMoving to 6.x-dev queue. This may still be relevant for MySQL (doesn't seem to be possible for PgSQL, since pg_connect() doesn't have any (documented) socket support).
Comment #15
alsh CreditAttribution: alsh commentedI would like to to stress, that still (quoting original comment on this issue) "Drupal does not allow for PostgreSQL connections via unix socket." It's a serious problem, because on many hosting companies you can only use PostgreSQL via unix sockets.
I am not sure this bug is that important for MySQL (correct me if I am wrong).
If consensus will be to use some special domain name for this (e.g. "unixsocket"), patch is very easy (for PostgreSQL):
Comment #16
StevenPatzHere is an actual patch.
Comment #17
Dries CreditAttribution: Dries commentedLooks good, but we definitely want to document this. Either in the install file or in the settings.php -- or both.
Comment #18
catchPatch is a bit fuzzy and could do with being rolled from root. Marking to needs work for the documentation anyway.
Comment #19
klando CreditAttribution: klando commented- if (isset($url['host'])) {
+ if (isset($url['host']) && $url['host'] != 'unixsocket') {
Can I suggest :
- if (isset($url['host'])) {
+ if (isset($url['host']) && strlen(trim($url['host']))) {
As it is more like pg_connect work.
Comment #20
dkg CreditAttribution: dkg commentedCan one of these patches be merged? A similar patch for install.php would be good, so that it works during the install as well. The default postgresql install on debian sets up really clean username-based UNIX socket permissions, so the only administrative work needed to set up a drupal site on a drupal system would be:
Comment #21
Sajut CreditAttribution: Sajut commentedNew Patch, tested with pgsql and socket connection.
Comment #22
dkg CreditAttribution: dkg commentedthis is useful functionality. it looks like it has been reviewed by a lot of people. why is it still in the "needs review" status?
Comment #23
suvisor CreditAttribution: suvisor commentedThe correct way to connect to PostgreSQL via unix domain socket is to specify the `host' and `port'
(no matter if you do that from PHP, C, C++). Using the socket path itself won't work.
For example, if the path to a PostgreSQL socket is /some/where/.s.PGSQL.5432 (/var/run/postgresql/.s.PGSQL.5432
on Debian), the corresponding connection string which should be passed to PHP's pg_connect is
"host=/some/where/ port=5432 dbname=someuserdb user=someuser password=top_secret"
That is, the host part is the directory where the socket resides (`/some/where/' in our example),
and the port is numeric suffix of the full socket path (5432 in our example). Please mind the trailing
slash ("/some/where/", not "/some/where"), it's mandatory.
There's one more thing to take into account: special characters (such as slash) in the $db_url
should be escaped, since Drupal treats slashes as separators. Therefore, slashes in the `host name'
should be replaced with %2f, like this:
$db_url = 'pgsql://db_user:db_user_password@%2some%2fwhere%2f:5432/drupal_dbname';
More annoying^W technical details: the PQconnectdb (libpq function which establishes connection
to the DB) always looks for the host in the connections string, even if one connects via unix domain
socket. This sounds a bit silly, but on the other hand it provides a uniform syntax for connections strings.
pg_connect (a PHP function) is just a wrapper for PQconnectdb.
Comment #24
jordi CreditAttribution: jordi commentedI suspect it's getting late to consider this for Drupal 7, or if the DB changes in D7 make this irrelevant or obsolete. It would be good if this useful patch could be updated so it can have a chance of getting merged sometime.
Comment #26
threading_signals CreditAttribution: threading_signals commented#23 works, thus the failed patch is not needed. Thank you.
Since I use Debian: $db_url = 'pgsql://username:password@%2fvar%2frun%2fpostgresql%2f:5432/databasename';
This removes some of the performance penalty vs mysql.
Comment #27
architectJpres CreditAttribution: architectJpres commentedWe found a massive boost by following the aforementioned.
We are considering a cloud architecture on EC2 with multiple webservers which connect to the postgres database.
How would you suggest making the connection if the database is not on localhost? meaning postgres is not installed on the machine at var/run/postgresql
It would be nice if comments were added to the settings.php file to suggest making the direct connection.
Comment #28
threading_signals CreditAttribution: threading_signals commentedPgbouncer will help you there.
Comment #29
architectJpres CreditAttribution: architectJpres commented#21: database.pgsql_.inc_.patch queued for re-testing.
Comment #31
architectJpres CreditAttribution: architectJpres commentedWhy are the patches failing? Because they do not work with mysql?
Would you mind elaborating on how you use pgbouncer?
Any recommendations on how one would connect directly to the socket (/var/run/postgresql/) on an external database server?
Comment #32
suvisor CreditAttribution: suvisor commentedAre you about connecting to database, located on remote machine, using UNIX domain socket? No, this won't work. You can operate with UNIX domain sockets only from local machine. If you can access to socket file (without network FS) -- you can establish connection. If not ...
Comment #33
DevElCuy CreditAttribution: DevElCuy commentedI confirm #26, there is no patch needed for Drupal 6, it is just a configuration matter, in settings.php as mentioned above and in PostgreSQL as follows:
Taken from: http://blog.deliciousrobots.com/2011/12/13/get-postgres-working-on-ubunt...
Comment #34
thedavidmeister CreditAttribution: thedavidmeister commented