DEV Community

Jon Webb
Jon Webb

Posted on • Updated on • Originally published at jonwebb.dev

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
Enter fullscreen mode Exit fullscreen mode

The output should start with a version number:

Homebrew 3.1.9
Enter fullscreen mode Exit fullscreen mode

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)"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

To stop it, run:

pg_ctl -D /usr/local/var/postgres stop
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Similarly, to delete a database, run:

dropdb YOUR_DATABASE_NAME
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode
GRANT ALL PRIVILEGES ON DATABASE YOUR_DATABASE_NAME TO YOUR_NEW_USERNAME;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Discussion (4)

Collapse
pjotre86 profile image
pjotre86

I recommend using docker:
hub.docker.com/_/postgres

Collapse
joncodes profile image
Jon Webb Author

Another great option.

Collapse
k776 profile image
Kieran Pilkington

Rather than installing via homebrew and initialising it yourself (which can cause headaches if you need to run different versions at once), I would recommend the following: postgresapp.com/

Collapse
joncodes profile image
Jon Webb Author

I've used that app too, and it works well. Good suggestion!