CODEWITHSUNDEEP
rails-activerecord
David L
David L

Reputation: 381

Connecting to Postgres Via database_url and Unix Socket in Rails

I've hunted around for this and just can't find a solution.

Currently I have a database.yml connected to a local pgbouncer server on a Unix socket successfully. However, I'm transitioning to setting this to a database_url environment variable and cannot work out at all how to connect to a local Postgres server via a Unix socket. localhost obviously works OK.

I was looking at a URL that looks like this as apparently you can use this with Postgres (http://www.postgresql.org/docs/9.2/interactive/libpq-connect.html):

export DATABASE_URL="postgresql://username@%Fvar%Frun%Fpostgresql%F.s.PGSQL.6432/dbname"

However, this will not get past the URI police:

rubies/ruby-2.1.5/lib/ruby/2.1.0/uri/common.rb:176:in `split': bad URI(is not URI?): postgresql://username@%Fvar%Frun%Fpostgresql%F.s.PGSQL.6432/dbname

Does anyone have any idea about the secret sauce needed for this? I've Googles endlessly and haven't found anything. It must be possible since the application currently connects over a socket now.

Thanks in advance,

Upvotes: 25

Views: 27818

Answers (5)

stephane
stephane

Reputation: 31

On Archlinux with default path for unix_socket_directories:

postgres:///<dbname>?host=/run/postgresql/

Upvotes: 1

blnc
blnc

Reputation: 4404

31.1.1.2. Connection URIs

The general form for a connection URI is:

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]`

The URI scheme designator can be either postgresql:// or postgres://. Each of the URI parts is optional. The following examples illustrate valid URI syntax uses:

postgresql://
postgresql://localhost
postgresql://localhost:5433
postgresql://localhost/mydb 
postgresql://user@localhost
postgresql://user:secret@localhost
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp

Components of the hierarchical part of the URI can also be given as parameters. For example:

postgresql:///mydb?host=localhost&port=5433

Percent-encoding may be used to include symbols with special meaning in any of the URI parts.

Any connection parameters not corresponding to key words listed in Section 31.1.2 are ignored and a warning message about them is sent to stderr.

For improved compatibility with JDBC connection URIs, instances of parameter ssl=true are translated into sslmode=require.

The host part may be either host name or an IP address. To specify an IPv6 host address, enclose it in square brackets:

postgresql://[2001:db8::1234]/database

The host component is interpreted as described for the parameter host. In particular, a Unix-domain socket connection is chosen if the host part is either empty or starts with a slash, otherwise a TCP/IP connection is initiated. Note, however, that the slash is a reserved character in the hierarchical part of the URI. So, to specify a non-standard Unix-domain socket directory, either omit the host specification in the URI and specify the host as a parameter, or percent-encode the path in the host component of the URI:

postgresql:///dbname?host=/var/lib/postgresql

postgresql://%2Fvar%2Flib%2Fpostgresql/dbname

Upvotes: 30

Otheus
Otheus

Reputation: 1042

The answer provided by @blnc is correct if using libpq in general. However, if you are using Activerecord or RubyonRails, at least in version 3.2.21, that module parses the URI to Ruby's URI.parse, instead of directly handing it to libpq. URI.parse cannot handle an empty hostname here.

irb(main):020:0> URI.parse "postgresql://user:pass@host/dbname"
=> #<URI::Generic:0x7f72b17f04d8 URL:postgresql://user:pass@host/dbname>

But, without host:

irb(main):021:0> URI.parse "postgresql://user:pass@/dbname"
URI::InvalidURIError: the scheme postgresql does not accept registry part: user:pass@ (or bad hostname?)
        from /usr/lib/ruby/1.8/uri/generic.rb:195:in `initialize'
        from /usr/lib/ruby/1.8/uri/common.rb:492:in `new'
        from /usr/lib/ruby/1.8/uri/common.rb:492:in `parse'
        from (irb):21
        from /usr/lib/ruby/1.8/uri/generic.rb:556

There appears to be no way to fix this without adding custom URI-parsing code (or altering activerecord).

Upvotes: 2

Nicolas Goy
Nicolas Goy

Reputation: 1430

You must omit the host to use unix socket, like so:

postgres://username@/dbname

or simply

postgres:///dbname

This works with psql > 9.2.

I am not sure it works with the rails handling of the database URL.

Upvotes: 25

Jacob Atzen
Jacob Atzen

Reputation: 557

You can pass the socket as a query parameter:

postgresql://user@host/database?socket=/path/to/socket

Upvotes: 1

Related Questions