Logstash provides a grok pattern for postgresql logs. Unfortunately, it doesn’t seem to be compatible with our postgres version (9.4), and our messages were all tagged with “_grokparsefailure”.
Using the fantastic grok debugger, I was able to produce something that worked:
%{DATESTAMP:timestamp} %{TZ} %{DATA:user_id} %{GREEDYDATA:connection_id} %{DATA:level}: %{GREEDYDATA:msg}
I’ve created an issue here, to track it.
We recently started using Goose to run our migrations, and I wanted to connect to the Postgres instance on a local machine using a domain socket. The documentation states:
host
Name of host to connect to. If this begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. The default behavior when host is not specified is to connect to a Unix-domain socket in /tmp (or whatever socket directory was specified when PostgreSQL was built). On machines without Unix-domain sockets, the default is to connect to localhost.
So I assumed that specifying a connection string without a host would “just work”:
db, err := sql.Open("postgres", "dbname=foo")
This doesn’t return an error, but any attempt to use the connection resulted in a “bad connection” failure. For some reason I needed to specify the path to the socket (which wasn’t in /tmp on the Debian instance I was using!):
db, err := sql.Open("postgres", "host=/run/postgresql dbname=foo sslmode=disable")
I was trying to create a new postgres user, and kept getting the dreaded:
psql: FATAL: Peer authentication failed for user "fred"
despite the fact I’d added the user to the pg_hba.conf file. It turns out that you need to specify the host:
psql -U fred -h localhost
as well as the user, to get psql to use md5 auth rather than peer.
