Setting up PostgreSQL on Mac with Homebrew

Setting up PostgreSQL on Mac with Homebrew

Homebrew provides an easy way to install open-source software in your Mac, if you don't have it installed please head to brew.sh and follow the instructions before following the rest of this tutorial.

In this tutorial I'll show how I usually setup PostgreSQL on Mac (OSX) via Homebrew.

Install Postgres

Go to your terminal and install the latest version of PostgreSQL with the following command:

brew install postgresql

Checking the PostgreSQL version

You can check the version of PostgreSQL you have installed by running the following command:

psql --version

At the time I wrote this post the output was:

psql (PostgreSQL) 13.3

Installing a different version of PostgreSQL

If whatever latest version of PostgreSQL Homebrew installed for you is not enough you can install a specific version.

Open your terminal search for all the available PostgreSQL versions with the command:

brew search postgresql

If everything works you should get an output like this:

==> Formulae
postgresql ✔         postgresql@10        postgresql@11        postgresql@12        postgresql@9.4       postgresql@9.5       postgresql@9.6       qt-postgresql

Notice a ✔ in the postgresql formula, that's the we installed a couple of steps ago.

For example, suppose you prefer to install PostgreSQL 10.
First, make sure you uninstall the current version, in your terminal run:

brew uninstall -f postgresql

Now install version 10:

brew install postgresql@10

Follow the instructions regarding the setup of the PATH environment variable to make the PostgreSQL executables visible to your terminal.
In this case it would be in /usr/local/opt/postgresql@10/bin but you should make sure to check the output of the install command above.

Ensure it is running

With Homebrew Services  you can start and stop services such as PostgreSQL as managed daemons. You can the full documentation of this tool by running the command brew services --help.

For now you can focus on listing, starting and stopping services with the following commands.

Listing Existing Services

brew services list

Starting the latest version of PostgreSQL

brew services start postgresql

Again, if you have a different version, such as PostgresSQL 10

brew services start postgresql@10

Stopping PostgreSQL

brew services stop postgresql
# or
brew services stop postgresql@10

Executables

Postgres comes with its own set of shell executables to manage databases, to name a few:

  • createdb
  • createuser
  • dropdb
  • dropuser
  • psql

You can check the list of binaries inside of the directory:

/usr/local/Cellar/postgresql/<version>/bin/

where instead of <version> you have whatever version you installed a few steps back and, as I mentioned earlier, you can check by running the command:

psql --version

In my case the path is:  

/usr/local/Cellar/postgresql/13.3/

However if you installed the version 10, for example, your path might rather look like:

/usr/local/Cellar/postgresql@10/10.17/
Note of comparison with Linux: On a Linux server those binaries are usually executable by a specific system user and group, if memory serves I think Ubuntu uses postgres as both username and group.

Creating a new database and user.

Let's create a user with the name my_user and a database named my_database owned by my_user.
Open your terminal and run the following commands:

createuser my_user
createdb --owner=my_user my_database
TIP: you can omit the --password option to create a user without password if you want to make easy to automate scripts later.

Running Queries

First let's create a table using the fancy EOF syntax of your shell, also known as Here Documents:

psql -d my_database <<EOF
    CREATE TABLE COMPANY(
       ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(20),
       SALARY         REAL
    );
EOF

As you see, the psql command reads queries from the stdin, so let's use that to create a couple of rows piping echo into psql:

echo "INSERT INTO COMPANY " \
     "(ID,NAME,AGE,ADDRESS,SALARY) "\
     "VALUES (1, 'Paul', 32, 'California', 20000.00);" \
     | psql -d my_database

echo "INSERT INTO COMPANY " \
     "(ID,NAME,AGE,ADDRESS,SALARY) "\
     "VALUES (2, 'Foo', 32, 'New York', 20000.00);" \
     | psql -d my_database

You can also interact with psql directly:

psql -d my_database

You should get no errors and a shell like this

psql (13.3)
Type "help" for help.

my_database=#

Finally, let's run a SELECT interactively:

my_database=# SELECT * FROM COMPANY;

 id | name | age |                      address             | salary
----+------+-----+------------------------------------------+--------
  1 | Paul |  32 | California                               |  20000
  2 | Foo  |  32 | New York.                                |  20000
(2 rows)

Dropping Database

If you need to delete a database with all its contents run the command:

dropdb my_database

Dropping Users

Similarly you might want to delete users, you can do that with: dropdb my_user


I hope you found this useful, thanks for reading.

Photo by Michael Browning on Unsplash.
Show Comments