Working with PostgreSQL on Mac locally

Working with data is always fun & we have many options to store data and perform analysis. In the context of Salesforce, we can use a data loader and export data in CSV files however analyzing CSV files could be daunting. My favorite method is to use Mulesoft and PostgreSQL to analyze data.

I like free stuff, free databases, however, I don’t like slower systems. In this post, I will share my quick tips on how I install PostgreSQL and start & stop service as needed so that my system is not using resources when I’m not working on PostgreSQL.

Install, Start & Stop PostgreSQL

The most convenient way to install a database is using the Homebrew command

brew install postgresql

Use the below command, when you would like to start database services

brew services start postgresql

and below command, if you are done with PostgreSQL so that system is not using unnecessary memory or CPU in background services

brew services stop postgresql

Install PGAdmin – Graphical Interface for Database Management

If you need a graphical interface to work with PostgreSQL, install pgadmin4 using the below command

brew install --cask pgadmin4

Once installation is done, refer below images to setup a connection with localhost

By default password is blank for newly created PostgreSQL

Mulesoft Connection with PostgreSQL

Refer below image for settings

• URL : jdbc:PostgreSQL://localhost:5432/postgres
• URL format : jdbc:PostgreSQL://localhost:<Port Number>/<databasename>
• Driver Class Name : org.postgresql.Driver

Other PostgreSQL-related Commands

Finding installation path

$ Which psql

Operating from the command line

$ psql postgres
//second parameter , in this case postgres is name of database

Get list of users in PostgreSQL

$ psql postgres

Then run command
\du 

Command to create a new user with super privilege

CREATE USER postgres SUPERUSER;
CREATE DATABASE postgres WITH OWNER postgres;  

Uninstalling PostgreSQL

Run below commands

//Check if instance is running 
$ launchctl list | grep -i sql

//Stop instance 
$ brew services stop postgres

//Remove PostgreSQL and all related files
$ brew uninstall --force postgres
$ rm -rf /usr/local/var/postgres
$ rm -rf .psql_history .psqlrc .psql.local .pgpass .psqlrc.local
$ brew cleanup