Postgres on My Mac

Some quick facts & tips for new Postgres users, especially on the Mac…

Postgres installs a folder in your /Applications folder. Those are gui front-ends to the underlying command line tools. You regularly use two of those: 

• "pgAdmin" app
  You create and manage databases here, including defining tables, columns, users & roles. You can also type in SQL statements interactively.
• "PostgreSQL documentation" app.
  Actually this is just leads to the docs stored locally in HTML format, and opens them in a web browser.

GUI Apps

The underlying Postgres is installed in /Library/PostgreSQL/ folder. Note that this is not the "Library" folder in your own user's home folder. This is the root "Library" folder.

Underlying Postgres Installation

The funky thing about Postgres is that it creates a new Unix user on your computer, named 'postgres'. The installer prompts you for your system password to gain admin rights to create this new user and a password. It also creates a default database by the same name. Apparently there is an old tradition in databases to assume that every system user will want to automatically login to their own private database. Seems silly to me, but remember that in those old days IBM considered SQL to be an end-user self-service tool. 

Anyways, note that the 'data' folder has a red stop sign on its icon. This means you cannot open that folder. The idea is extra security; if the regular user account is compromised, the bad guys will not have direct access to the database and its files. So choose a good long strange password for that 'postgres' user account, and write it down somewhere safe. The tools such as 'pgAdmin' app require that password to gain access as the 'postgres' user to perform actions such as creating a new database. 

You can begin learning Postgres using that 'postgres' user. But soon you should create at least one new Postgres user & password, to access each database without full admin rights, for regular use such as when developing end-user apps that connect but should only be allowed to add and drop records as opposed to deleting all databases. The 'postgres' user can wipe out all databases and do other dangerous acts not allowed 

How do your backup your database if you cannot reach its files in the Finder? Use the "pgDump" tool to pour out the data and scheme definitions as plain SQL text. Postgres and its MVCC implementation are built to do a pgDump run without shutting down the server. (Amazing to me!)

If you want to run the command line tools directly in a Terminal window, switch to that folder. Type the 'cd' (Change Directory) command, followed by a space, and then drag and drop the 'bin' folder from the Postgres folder. Press Return. To see verify, run this in the Terminal run the command to list all files: 

ls -a

To verify the version of Postgres currently running, run this in the Terminal:

./pg_ctl --version

Or in the SQL pane of pgAdmin, execute this SQL:

SELECT version();