I’ve been using Postgres db server for years in my day job. And for all those years I had a third party server installed on my Mac. But since Apple started bundling Postgres in Os X system I was thinking of ways to start using that one. Unfortunately it seems that Apple decided to make using it as hard as it’s possible so getting it to work needed some hacking.
The application I’m working with accesses db via network connection on localhost. So I needed to turn on listening on network interfaces. Postgres keeps it’s config file in /Library/Server/PostgreSQL/Data folder. You can find postgres.conf there. You need to edit this file and change two lines to show this:
port = 5432 # (change requires restart)
listen_addresses = '*' # what IP address(es) to listen on;
quick server restart:
# sudo serveradmin stop postgres
postgres:state = "STOPPED"
# sudo serveradmin start postgres
postgres:state = "RUNNING"
and… no luck. Still no connections are accepted on network interface. Checking settings:
# sudo serveradmin settings postgres
postgres:log_connections = "on"
postgres:unix_socket_directory = "/var/pgsql_socket"
postgres:listen_addresses = ""
postgres:unix_socket_group = "_postgres"
postgres:log_statement = "ddl"
postgres:log_line_prefix = "%t "
postgres:unix_socket_permissions = "0770"
postgres:log_lock_waits = "on"
postgres:logging_collector = "on"
postgres:log_filename = "PostgreSQL.log"
postgres:dataDir = "/Library/Server/PostgreSQL/Data"
postgres:log_directory = "/Library/Logs/PostgreSQL"
Shows that the listen_address parameter got somehow overwritten. It turns out that the server stores some parameters in plist files and those get priority over anything you set in postgres.conf file.
# sudo serveradmin set postgres:listen_addresses="*"
postgres:listen_addresses = "*"
changes that and now my application can access built in Postgres server on localhost.
But how about command line tools? Apple sets things up in a way that Postgres accepts socket connection only from users that belong to _postgres group. Now I needed to add myself to that group using dseditgroup command:
# sudo dseditgroup -o edit -a usertoaddtogroup -t user _postgres
and voila! I’ve gut running instance of Postgres (9.1.5) accepting connection from my application and letting me use all command line tools.
