24

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,

Simone Carletti
  • 173,507
  • 49
  • 363
  • 364
David L
  • 371
  • 1
  • 2
  • 6

5 Answers5

29

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
Matthias Winkelmann
  • 15,870
  • 7
  • 64
  • 76
blnc
  • 4,384
  • 1
  • 28
  • 42
  • localhost connects via TCP/IP (127.0.0.1, ::1, fe80::1%lo0, etc). To use the Unix Socket like OP asked omit the host. – None Aug 23 '15 at 14:26
  • 1
    Lies lies! I am using `postgresql://user:pass/dbname` to no avail. I've tried many variations of the above. Seems to be rails 3.2.21. I get no meaningful error message from the application (openproject), but the tcp form at least logs an auth error with pg. @J.Money – Otheus Dec 01 '16 at 15:55
  • 1
    @Otheus My comment was about using localhost in the connection string. Since localhost is not empty and does not start with a slash it is interpreted as a host name and connects to the loopback connection via TCP/IP. "A Unix-domain socket connection is chosen if the host part is either empty or starts with a slash" so you still need to specify the host for non-standard sockets. Do you have the following in your database.yml file: `host: /path/to/socket/file` – None Dec 01 '16 at 20:54
  • 1
    @J.Money Correct, but omitting the host does not work -- but this is not because of `libpq`. Apparently activerecord (3.2.21) is doing something magical here. It tries to parse the string on its own via `URI.parse` instead of passing it to `PGconn`. :( – Otheus Dec 02 '16 at 12:30
  • 1
    The last option, but changing `lib` to `run` worked for me: `postgresql://%2Fvar%2Flib%2Fpostgresql/dbname`. The docs also have some good information about connection precedence: https://edgeguides.rubyonrails.org/configuring.html#connection-preference – Kris Jan 06 '20 at 15:07
  • rather than copy the manual page into an answer, try phrasing the answer in just a few words. after which, you can link to the documentation. – blee Nov 19 '22 at 17:04
24

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.

Nicolas Goy
  • 1,294
  • 9
  • 21
  • 2
    This does not work with Rails 4.2.5 and PostgreSQL 9.3 – Asfand Qazi Dec 16 '15 at 11:07
  • Although seemingly unrelated, I was missing the third slash on my app that I was porting to Rails 5... And that solved [this](https://stackoverflow.com/questions/47320772/runtimeerror-cant-modify-frozen-array-when-running-rspec-in-rails-5-1) issue I was experiencing. – stuartc Apr 04 '19 at 12:03
  • interesting, adding ? you can add parameters like sslmode=disable postgres:///dbname?ssmode=disable – Pavel Zaitsev Sep 22 '20 at 22:21
2

You can pass the socket as a query parameter:

postgresql://user@host/database?socket=/path/to/socket
Jacob Atzen
  • 559
  • 1
  • 4
  • 5
2

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).

Otheus
  • 785
  • 10
  • 18
1

On Archlinux with default path for unix_socket_directories:

postgres:///<dbname>?host=/run/postgresql/
stephane
  • 31
  • 4