Connecting to Postgres Via database_url and Unix Socket in Rails

Question

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,

Karl Amort · Accepted Answer · 2019-11-18 19:01:35Z

31

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

edited Nov 18, 2019 at 19:01

Karl Amort

16.4k8 gold badges69 silver badges77 bronze badges

answered Mar 30, 2015 at 20:53

blnc

4,4141 gold badge30 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

None Over a year ago

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.

Otheus Over a year ago

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

None Over a year ago

@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

Otheus Over a year ago

@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. :(

Kris Over a year ago

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: edgeguides.rubyonrails.org/…

|

Nicolas Goy · Accepted Answer · 2015-03-16 16:18:09Z

25

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.

answered Mar 16, 2015 at 16:18

Nicolas Goy

1,43812 silver badges23 bronze badges

3 Comments

Asfand Qazi Over a year ago

This does not work with Rails 4.2.5 and PostgreSQL 9.3

stuartc Over a year ago

Although seemingly unrelated, I was missing the third slash on my app that I was porting to Rails 5... And that solved this issue I was experiencing.

Pavel Zaitsev Over a year ago

interesting, adding ? you can add parameters like sslmode=disable postgres:///dbname?ssmode=disable

Otheus · Accepted Answer · 2016-12-02 12:45:49Z

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

stephane · Accepted Answer · 2021-04-13 15:55:18Z

2

On Archlinux with default path for unix_socket_directories:

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

answered Apr 13, 2021 at 15:55

stephane

414 bronze badges

Comments

Jacob Atzen · Accepted Answer · 2015-01-24 12:54:13Z

1

You can pass the socket as a query parameter:

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

answered Jan 24, 2015 at 12:54

Jacob Atzen

5571 gold badge4 silver badges5 bronze badges

1 Comment

jlandercy Over a year ago

socket is not a valid parameter for most PostgreSQL API

Collectives™ on Stack Overflow

Connecting to Postgres Via database_url and Unix Socket in Rails

5 Answers 5

6 Comments

3 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related