Installing PostgreSQL on a Mac

In this walkthrough tutorial, I will install PostgreSQL on a Mac. Sometimes I may sound old school, but I insist on calling “things” by their name so PostgreSQL is PostgreSQL, not Postgre, not Postgres, not pgSQL… Feel free to add any other stupid acronyms / nickname in the comments!

Environment

• Mac OS X 10.9.1 (13B42) – Mavericks
• PostgreSQL 9.3.2-2

Downloads

PostgreSQL for Mac can be downloaded from http://www.postgresql.org/download/macosx/.

Starting the installation process

When you double click on the postgresql-9.3.2-2-osx.app icon (after you unzipped it), the system may warn you that he does not trust this application. You will need to authorize it. Open the System Preferences. Double click on Security & Privacy.

On the General tab, you will see a warning telling you which last application has not been allowed to run. Click on Open Anyway. Don’t do that if it is not PostgreSQL here (or any other unknown app).

Enter your credentials.

Follow the wizard.

Don’t try to be a smart ass, enter a password.

The installer will create (if you do not have it) a PostgreSQL user on your Mac. The user’s account name will be postgres. This is not the same user as the database user: if you forget the database server’s password, you cannot change it via the Users & Groups panel of System Preferences. To do so, psql contains a command \password that can be used to change a role’s password. It will do so without exposing the clear text password.

Make sure you allow connections to your server.

Installing other components

Simply follow the wizard.

Selection

Select what you need. In my case, I picked:

• pgJDBC, JDBC driver v9.2-1001-1.
• The Apache v2.4.7 and PHP v5.4.23-2 bundle.
• phpPgAdmin v5.1-1 to administer the database.

If you plan on using Maven, it is not required to install it through Stack Builder.

Apache & PHP bundle

This is required if you want to have ready-to-use Apache server with PHP and PostgreSQL . It is also required if you plan to use phpPgAdmin, the web tool for managing the server.

JDBC driver

PostgreSQL comes with a JDBC type 3 and type 4 driver. It is called pgJDBC. It is not required if you plan to use Maven.

phpPgAdmin

The host should read 127.0.0.1 not 127.0.0.15.

Conclusion

Your server is now up and running. We can now test that everything is fine.

Testing

There is 3 tools you can try: UI, command line and the web…

Web

If you installed the Apache module connect to http://localhost:8080/phpPgAdmin/.

Once you’re connected, you will see all the information:

 Command line

You can run psql from the terminal if you have set up all the environment variables correctly. You can also run it from the Applications (and PostgreSQL 9.3 folder).

UI tool: pgAdmin III

I have seen nicer admin tools, but pgAdmin III will do the job if you like the point and click interface. I liked the fact that the tool can generate SQL commands as you “point and click”, which allows you to run them in psql with modification if you need to.

If you have trouble, check the troubleshooting.

Troubleshooting

 Checking that PostgreSQL runs

Open a terminal and run:

$ ps auxwww | grep postgres

You should get something like:

postgres          234   0,0  1,3  2718932 216712   ??  Ss   Dim04     0:15.86 com.apple.IconServicesAgent
postgres          233   0,0  0,0  2496600   2696   ??  Ss   Dim04     0:00.15 /usr/libexec/xpcd
postgres          171   0,0  0,0  2510160    744   ??  Ss   Dim04     0:03.94 postgres: stats collector process    
postgres          170   0,0  0,0  2654608   2484   ??  Ss   Dim04     0:01.62 postgres: autovacuum launcher process    
postgres          169   0,0  0,0  2638092    756   ??  Ss   Dim04     0:01.82 postgres: wal writer process    
postgres          168   0,0  0,0  2638092   1592   ??  Ss   Dim04     0:01.94 postgres: writer process    
postgres          167   0,0  0,0  2638092    836   ??  Ss   Dim04     0:00.06 postgres: checkpointer process    
postgres          162   0,0  0,0  2485584    520   ??  Ss   Dim04     0:00.00 postgres: logger process    
postgres          101   0,0  0,1  2639116  13832   ??  Ss   Dim04     0:01.21 /Library/PostgreSQL/9.3/bin/postmaster -D/Library/PostgreSQL/9.3/data
jgp             18603   0,0  0,0  2452240    684 s003  S+    1:33     0:00.00 grep postgres
postgres        18595   0,0  0,0  2518912   5992   ??  SN    1:32     0:00.04 /System/Library/Frameworks/CoreServices.framework/Frameworks/Metadata.framework/Versions/A/Support/mdworker -s mdworker -c MDSImporterWorker -m com.apple.mdworker.shared
postgres        18594   0,0  0,0  2518912   5952   ??  SN    1:32     0:00.04 /System/Library/Frameworks/CoreServices.framework/Frameworks/Metadata.framework/Versions/A/Support/mdworker -s mdworker -c MDSImporterWorker -m com.apple.mdworker.shared
postgres        18593   0,0  0,0  2494336   5952   ??  SN    1:32     0:00.04 /System/Library/Frameworks/CoreServices.framework/Frameworks/Metadata.framework/Versions/A/Support/mdworker -s mdworker -c MDSImporterWorker -m com.apple.mdworker.shared
postgres        18559   0,0  0,0  2490000   5812   ??  S     1:28     0:00.08 /System/Library/Frameworks/CoreServices.framework/Frameworks/Metadata.framework/Versions/A/Support/mdworker -s mdworker-sizing -c MDSSizingWorker -m com.apple.mdworker.sizing
postgres        18558   0,0  0,0  2514280   1252   ??  S     1:28     0:00.02 /usr/sbin/cfprefsd agent
postgres        18557   0,0  0,0  2529128   1664   ??  U     1:28     0:00.02 /usr/sbin/distnoted agent
postgres        18552   0,0  0,0  2508252   1004   ??  Ss    1:28     0:00.03 /sbin/launchd