How to connect to embedded POSTGRESQL on Mac OSX Lion Server

Creating a role is just creating a user inside Postgres SQL

pgAdmin is not installed by default

I understand that pgAdmin isn't installed, as I've downloaded it and also Navicat. But I wouldn't know how to properly create any role in PostgreSQL if I can't login

You login using

sudo -u _postgres psql template1

Hi Tim,

To answer your question about creating a role in postgresql. This can be accomplished in psql (you don't need pgAdmin). To do so:

Start psql with the _postgres user which already has a role assigned:

sudo -u _postgres psql template1

(daefromlos angeles has graciously included a screen-shot above)

Then once in, create a new role:

create role <MYUSERNAME> with login createdb

The <MYUSERNAME> would be whatever you log-in as.

You can see all of the options by typing in:

\h create role

You can now log out using \q and re-log in using psql or Navicat.

When logging-in, if no parameters for the database are specified then it will assume that your username is the default database. As a convenience use the createdb command to create a brand new database (instead of psql).

This is actually not the right answer if you installed using mac ports. The reason is that the /usr/bin/psql application is not the right tool.

Run which psql and remove it if it's in /usr/bin/psql. Then open a new terminal and run which psql. It will then be the MacPorts psql instead.

It just works™

Hi guys! Just figured out how to enable TCP access to PostgreSQL! 🙂

by executing the following in Terminal:

ps ax | grep postgres

revealed that there are some mysterious command line parameters given to PostgreSQL at start. The most concerning is the "-c listen_addresses=" which effectively turns off all TCP listening!

By trying to kill the postgres process both nicely and forcefully didn't help - the ******* respawns! I tried:

sudo -u _postgres pg_ctl stop -D /var/pgsql

and

sudo kill {enter postgres pid here}

So... I Googled a bit to find out about what happens at OSX's bootup. Found out that there's a system called "launchd" since Tiger (10.4), which handles all the extensions loading, etc.: http://en.wikipedia.org/wiki/Launchd

After initial system boot, the launchd goes over all files found in /System/Library/LaunchDaemons. There's a file called org.postgresql.postgres.plist which includes all the command line parameters. By modifying the parameters, and restarting system, TCP connection to PostgreSQL works! 😎

Also remember to add all IP addresses allowed to access PostgreSQL to /var/pgsql/pg_hba.conf.

Btw. it's a good idea to add e.g. a user named postgres to access the db, because user _postgres uses the same password as system admin...

Cheers,

/Sami

tell me where are the files postgresql.conf pg_hba.conf

In /var/pgsql.

But check my reply to Yansolo; PostgreSQL initializes with some command line parameters, which override changes to postgresql.conf.

to connect to another computer, I did

1. edit file /system/library/LaunchDaemons/org.postgresql.postgres.plist

deteteв lines

<string>-c</string>

<string>listen_addresses=</string>

2. edit file /var/pgsql/pg_hba.conf

add lines

host all all 192.168.0.0/24 trust

3. edit file /var/pgsql/postgresql.conf

edit lines

#listen_addresses = 'localhost'

listen_addresses = '*'

#port = 5432

port = 5432

4. restart mac os x server

connect ok

I managed to open an TCP/IP port with the embedded PostgreSQL server so that I can connect to it with PGAdmin using localhost:5432. You need to open file "/System/Library/LaunchDaemons/org.postgresql.postgres.plist" and change the "listen_addresses" property. Then restart (serveradmin stop postgres, serveradmin start postgres) and you have the port open. Try using: telnet localhost 5432.

You can find more info here:

http://blogs.madplanet.com/2011/08/06/using-embedded-lion-server-postgresql-data base/

- Andy

The blog post linked to above is the way to go.

Please do read about launchd if you're new to it, as it's been in use since 10.4, so just over 6 years now :-)

Recommended reading:

http://www.afp548.com/article.php?story=20050620071558293

http://www.mactech.com/articles/mactech/Vol.25/25.09/2509MacEnterprise-launchdfo rLunch/index.html

https://developer.apple.com/library/mac/#documentation/MacOSX/Conceptual/BPSyste mStartup/Chapters/Introduction.html

Thanks for the post this worked great!

Thank you very much~~i am trying to work it out now~~

and the link become this one:

http://blogs.madplanet.com/?p=110

hi all!!!! it work so great!!!!!!!!!!!!! thanks every of you!!!!! love!!!!

I tried. It is not working for me. Interesting becuase:

1) my server setup is "standard"; no manual changes. All services working

2) not working using localhost on the server machine

Any idea where to look after or what may I doing wrong?