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.
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:
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 email@example.com firstname.lastname@example.org email@example.com 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
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
brew services stop postgresql # or brew services stop postgresql@10
Postgres comes with its own set of shell executables to manage databases, to name a few:
You can check the list of binaries inside of the directory:
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:
In my case the path is:
However if you installed the version 10, for example, your path might rather look like:
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
postgresas 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
Open your terminal and run the following commands:
createuser my_user createdb --owner=my_user my_database
TIP: you can omit the
--passwordoption to create a user without password if you want to make easy to automate scripts later.
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 "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 -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)
If you need to delete a database with all its contents run the command:
Similarly you might want to delete users, you can do that with:
I hope you found this useful, thanks for reading.
Photo by Michael Browning on Unsplash.