How to set up a local PostgreSQL development server on MacOS

Homebrew

Homebrew is an extremely useful package manager for Mac. To check whether you have it installed, run the following command:

$ brew -v

The output should start with a version number:

Homebrew 3.1.9

If instead, you get command not found or nothing, install it with the following command:

$ /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

Installing postgresql

PostgreSQL is a popular, feature-rich, and open source relational database. To install it using Homebrew, use the following command:

brew install postgres

The command will create a default database cluster stored at /usr/local/var/postgres. If for some reason the default database was not created, you can create it by running the command:

initdb --locale=C -E UTF-8 /usr/local/var/postgres

If the default database already exists, it will not be overwritten, and you will get an error response initdb: error: directory "/usr/local/var/postgres" exists but is not empty. As long as the default database is created you can proceed to the next step.

Starting and stopping the postgresql server

To start the database server, run:

pg_ctl -D /usr/local/var/postgres start

To stop it, run:

pg_ctl -D /usr/local/var/postgres stop

Creating and deleting database

While the database server is running, run the following (replacing YOUR_DATABASE_NAME with the desired database name):

createdb YOUR_DATABASE_NAME

Similarly, to delete a database, run:

dropdb YOUR_DATABASE_NAME

Creating a user for your database

postgresql installed via Homebrew creates a database user with the same name as the installing user, with no password. When creating a database using the createdb command, that user will be automatically given privileges for the new database.

When connecting to the database while developing our application, we will want to use a specific user account with a password, like we would in production.

psql is a terminal front-end for PostgreSQL that you can use to execute queries to your database. To connect to your newly created database with psql using the default account, run the following (replacing YOUR_DATABASE_NAME with the actual name of your database):

psql YOUR_DATABASE_NAME

Now, within the psql console, create a new user and grant it privileges to your database. Replace YOUR_NEW_USERNAME, YOUR_NEW_PASSWORD and YOUR_DATABASE_NAME with the case-sensitive, relevant values. The semicolons are important, as they terminate each query:

CREATE USER YOUR_NEW_USERNAME WITH PASSWORD 'YOUR_NEW_PASSWORD';

GRANT ALL PRIVILEGES ON DATABASE YOUR_DATABASE_NAME TO YOUR_NEW_USERNAME;

Type \q and press enter to exit the psql console.

Connecting from your application

You can now connect to the database from your application with the following parameters:

• host: localhost
• port: 5432
• database: the name of the database you created
• user: the username you created
• password: the password you set

Or, using a connection string:

postgresql://YOUR_NEW_USER:YOUR_NEW_PASSWORD@localhost:5432/YOUR_DATABASE_NAME