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@14

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

brew services start postgresql@14

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@14

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@14
//second parameter , in this case postgres is name of database

Get list of users in PostgreSQL

$ psql postgres@14

Then run command
\du 

Command to create a new user with super privilege

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

Check if PLSQL is working or not

> brew services list

Uninstalling PostgreSQL

Run below commands

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

//Stop instance 
$ brew services stop postgres@14

//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