Welcome to our guide on how to install and use PostgreSQL 14 on macOS Monterey. Our first question will be what PostgreSQL is, and in the most understandable terms, we will try and answer the question. PostgreSQL is a relational database management system, that is highly stable, open source, and advanced. PostgreSQL is known to support JSON and SQL and hence used in both most web, analytic and mobile applications as the primary database. Moreover, this relational database management system supports most of the popular programming languages, which include;

  • C+, works well for systems that run the applications themselves
  • Java, often associated with client-server applications
  • JSON, used to transmit data(send) in web applications
  • C, forms a good platform for programming when combined with Java and Python
  • C#, supports the development of Microsoft applications, mobile devices, Windows, browser plug ins
  • Python, easy to learn and build on, often used with startup companies
  • JavaScript, known to be most popular and used to build websites that are interactive
  • Go, named Golang used in system-level programming
  • Ruby,
  • Tcl, easy to learn, and very powerful, used in networking, testing, administration, web and desktop applications
  • Perl, suitable for large scale project developments and prototyping

Before we venture into the features of PostgreSQL 14, we can look at the few use cases of this relational database management system. One of them is as a transactional database, another one is as Geospatial database with the help of GIS extension and finally as a back-end database for websites. There are so many features associated with PostgreSQL 14, which makes it the latest most advanced relational open source database management system. Those features include the following;

  • You can access JSON data with subscripts, and also multi range data types supported
  • Heavy workloads performance improvement, with PostgreSQL 14 ability to pipeline queries to a database, which in turn increases performance workloads over high latency connections
  • PostgreSQL 14 can assign read-only and write-only privileges to users hence improving on security
  • With query planning and execution on PostgreSQL 14, there is an improvement on conformance and convenience on SQL
  • Distributed workloads enhancements, with logical replication, progress transactions can be streamed in to subscribers
  • Analyze on PostgreSQL 14 now works far much better and faster on collecting database statistics hence promoting better administration and observability

 Install PostgreSQL 14 on macOS Monterey

Having looked at a brief description on PostgreSQL, its features, the programming languages that it supports and a few use cases scenarios, we can then proceed to the next sector of our tutorial. On this next section, we will look at the two processes on how to install PostgreSQL 14 on macOS Monterey.

Method 1: Install PostgreSQL 14 Manually on macOS Monterey

First we will look at how to install PostgreSQL on macOS Monterey, then we will proceed to look at how to do the installation with the help of brew software package management system. So, stay tuned to the end of the article;

Step 1. Download the Postgres.app

Go to the Postgresapp website and download the latest version of Postgresapp onto your system. The file contains everything you need to get started with PostgreSQL, it also comes with PostGIS. Ensure to tap on the section indicated download for the file to be downloaded;

Step 2. Move the Downloaded File to Applications Folder

Since when a download is made, the file usually settles on the downloads section. For this case, once the download is complete, you ought to move the file to the applications folder. To do that, drag the file from downloads to Applications, until it shows in the Applications folder like below;

Double click on the file, that is when you will see another window that pops up. Drag Postgresapp to Applications for the files to copy. Check below, the direction of the arrow is how you will drag it. Once done, close the window below.

Step 3. Open Launch Pad and Open Postgresapp

Once done with step 2, open your launch pad on macOS Monterey and search for Postgresapp, then double click on it to open, then click on initialize to create a new server. The first window you will see when you open Postgresapp is shown below;

Below is the second window that you will see. Now the service is running;

Step 4. Configure your $PATH

To be able to locate the libraries on postgresapp, you need to configure your $PATH using the commands shown below, however once you have implemented the commands on your terminal, ensure to close that terminal for the changes to take effect.

sudo mkdir -p /etc/paths.d
echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp

Once that terminal has been closed, open another one and type the command below to check the PATH of PostgreSQL;

which psql

Method 2. Install PostgreSQL on macOS Monterey

There is also another method on how you can install PostgreSQL 14 on macOS Monterey. On this method we will use Homebrew to do the whole installation process. Below are the steps you are going to do it.

  • Install Homebrew on macOS Monterey
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
  • Use HomeBrew to Install PostgreSQL on macOS Monterey
brew install postgresql
  • Launch PostgreSQL by running the command shown below
brew services start postgresql
  • Uninstall PostgreSQL on macOS Monterey with brew
$ brew uninstall postgres
$ rm -rf /usr/local/var/postgres
$ rm /usr/local/var/log/postgres.log
$ rm -f ~/.psqlrc ~/.psql_history

Using PostgreSQL 14 on macOS Monterey

The following steps will help you in figuring out how to use PostgreSQL once installed on your system. Check below for more insights regarding the use case scenario;

Step 1. Launch PostgreSQL and use on macOS Monterey

Since the PATH has been configured, you can then proceed to your terminal and launch PostgreSQL using the command below;

psql

To list databases on PostgreSQL, run the following command;

\l

Below is the output;

 postgres=# \l
                                   List of databases
    Name     |    Owner    | Encoding |   Collate   |    Ctype    |   Access privileges
-------------+-------------+----------+-------------+-------------+-----------------------
 annalianie | annalianie | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres    | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0   | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
             |             |          |             |             | postgres=CTc/postgres
 template1   | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
             |             |          |             |             | postgres=CTc/postgres
(4 rows)

To list users on PostgreSQL, run the command below on the PostgreSQL prompt;

\du

To change the password of the default user postgres to secure it, run the command below;

postgres =# alter user postgres with password 'test123';
ALTER ROLE

To create a new user user_1 on Postgres, run the command shown below;

postgres=# create user user_1 with password 'test321';
CREATE ROLE

Now let’s check if user_1 was successfully created;

postgres=# \du
                                    List of roles
  Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
annalianie | Superuser, Create role, Create DB                          | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user_1      |                                                            | {}

(END)

As you can see from the output above, user_1 was succesfully created. Now, let’s proceed to assign privileges to the newly created user user_1. To do so, run the following command;

postgres=# alter user user_1 with superuser;
ALTER ROLE

To check if the role superuser was accorded to user_1, we need to list the users and check the attributes section;

postgres=# \du
  Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
 annalianie | Superuser, Create role, Create DB                          | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user_1      | Superuser                                                  | {}

Our task was successful. In case you want to delete a user, say user_1, use the command below;

postgres=# drop user user_1;
DROP ROLE

Step 2. Create a Test Database

For our case, we will create a test database named test_db. So on your PostgreSQL prompt, run the following command to create a test database. The first command is to create user_2, the second command is to create test database test_db1, and the last command is to grant all privileges on test_db1 to user_2.

postgres=# create user user_2 with password 'test321';
CREATE ROLE
postgres=# create database test_db1;
CREATE DATABASE
postgres=# grant all privileges on database test_db1 to user_2;
GRANT

Task 1. Connect to the Test Database

Our test database has been created successfully and it is named test_db1, now we want to connect to this database. To do so, on the same Postgres prompt, run the command below to connect to test_db1;

postgres=# \c test_db1 user_2;
You are now connected to database "test_db1" as user "user_2".

Task 2. Create a Schema

A Schema is not that necessary, however it is quite important so as to be able to sort out tables, and create an organized environment. Let’s proceed to create a Schema named my_data;

test_db1=> create schema my_data;
CREATE SCHEMA

Task 3. Create a Table under Schema

Our next task is to create a table named test under the Schema created in task 2, (my_data), to do that, run the command below, which describes what your table will contain and the number of characters that each name on the row should not exceed;

test_db1=> create table my_data.test( course CHAR(20), grade CHAR(5));
CREATE TABLE

Select information from the table;

test_db1=> select * from my_data.test;
 course | grade 
--------+-------
(0 rows)

To get a description of the database, run the command below;

test_db1=> \d my_data.test
                  Table "my_data.test"
 Column |     Type      | Collation | Nullable | Default 
--------+---------------+-----------+----------+---------
 course | character(20) |           |          | 
 grade  | character(5)  |           |          | 

Let’s input data on the table created above;

test_db1=> insert into my_data.test values( 'maths', 'A');
INSERT 0 1

Now let’s see how it looks like by selecting the information as follows;

test_db1=> select * from my_data.test;
        course        | grade 
----------------------+-------
 maths                | A    
(1 row)

You can repeat the same process for all other courses until you have a table with say, 4 rows, like the one shown below;

test_db1=> select * from my_data.test;
        course        | grade 
----------------------+-------
 maths                | A    
 english               | B    
 chemistry           | A    
 biology               | A    
(4 rows)

Task 4. Clean data from the Table

Since we have learnt how to input data on the table, let’s also learn how to clean data from the table. To do so, check below for more insights;

test_db1=> truncate my_data.test;
TRUNCATE TABLE

When you check whether there is still data, below is how the output looks like;

test_db1=> select * from my_data.test;
 course | grade 
--------+-------
(0 rows)

We have successfully managed to delete the data from the table. In case you want to delete the complete table, run the command below;

test_db1=> drop table my_data.test;
DROP TABLE

N/B If you created a Schema then a table under the Schema, in case you want to run any command that concerns the table created always include the Schema followed by the table, lest you get an error. Like for our case, most of our commands above begin with the Schema (my_data) followed by the table name(test), like my_data.test

Step 3. Uninstalling PostgreSQL on macOS Monterey

Having looked at almost everything about PostgreSQL, we also need to look at how to uninstall it from your system once done with it. To do so, you can do as follows.

  • First quit postgres prompt using the command below;
\q
  • Move Postgresapp to Bin. On your applications, right click on the PostgreSQL file and select move to Bin
  • Delete the data directory by running the command below
rm -rf ~/Library/Application Support/Postgres
  • Delete preferences for Postgres.app, execute the following command
defaults delete com.postgresapp.Postgres2
  • Remove the $PATH config by running the command
 sudo rm /etc/paths.d/postgresapp

Conclusion

Up to this point, we have managed to successfully install PostgreSQL on macOS Monterey. There are so many things that we have covered on this tutorial that concerns PostgreSQL. Aside from learning how to just install PostgreSQL on macOS Monterey, we have also learnt how to create a user on the database, how to alter the roles of the user and how to drop that user on Postgres database. On how to use PostgreSQL, we started by first creating a test database, and then granted a certain user all privileges on that test database. On the test database, we proceeded to create a schema on it, then we also created a table under the schema. Moreover, we also learnt how to input the data on the table, we deleted the data (contents of the table) and finally looked how to delete the table itself and how to uninstall PostgreSQL on macOS Monterey. Check below for more of our articles;

LEAVE A REPLY

Please enter your comment!
Please enter your name here