Introduction

In most cases, when developing applications, it becomes necessary to store data in databases and interact with them. During development, you need to connect to a local database in your project and interact with it. This article will show you how to install PostgreSQL on macOS (including Apple Silicon) in various ways.

In the article I will tell you how to install PostgreSQL on a macOS system, how to use it through the terminal, connect through applications and connect to your applications, as well as basic commands for interacting through the terminal.

Installation

You can install in two ways:

• through the installer from the official site
• using the Homebrew package manager (recommended)

In any of the above cases, you can fully install PostgreSQL on your system.

Through the installer

In order to install through the installer, go to the official PostgreSQL website and download the required version:

• Select the required version (mine is v14.2) and navigate to the directory
• In the directory, download the file postgresql-[your version].tar.gz (I have the following file: postgresql-v14.2.tar.gz)
• Next, unzip the downloaded file and go to the newly created folder from this archive
• In the folder you will see a file called **INSTALL**, open it and read the necessary installation information, there will also be full instructions on how to install and what to do
• As a result, it will be necessary to open the folder in the system terminal and install everything according to the manual

Through Homebrew

Before installing PostgreSQL, you need to check the work of Homebrew and it is desirable to update:

• Check for updates via the brew update console command and wait for it to complete
• To upgrade Homebrew itself, just enter the command brew upgrade
• If any problems are displayed during the installation, you can try to fix them or get information for fixing through the brew doctor command.

Now the PostgreSQL installation itself. We open the terminal, this can be done through the Spotlight search built into the system (and find the terminal), then we take the following steps:

• Using the brew command, install the PostgreSQL DBMS through the formula, for this, in the terminal, enter the command brew install postgresql (formula).
• After the successful installation is complete, you need to start the postgresql service itself, for this use the brew services start postgresql command.
• You can stop the service with the command brew services stop postgresql.
• Check if the service is running with the brew services list command.
• If the service is running, the psql command will become available in the terminal.
• Let's try to enter PostgreSQL through the console command psql postgres.
• If the psql command is present and it was possible to enter the default user, then everything is set correctly.
• Now let's check the PostgreSQL accounts. To open a table with users, run the \du command (you must be logged in, see point 6).
• By default, you will see your login in the table and information that this user is an administrator (superuser).
• Use the \u command to exit.

Extras

• If you do not need the PostgreSQL background service, then run the command /opt/homebrew/opt/postgresql/bin/postgres -D /opt/homebrew/var/postgres in the terminal.
• If you need to start the service manually, then use the command pg_ctl -D /opt/homebrew/var/postgres start in the terminal, and to stop the command pg_ctl -D /opt/homebrew/var/postgres stop.

Errors

During installation, you may encounter various errors. Below will be listed the errors that you can get when trying to start the service itself or install it.

Bootstrap failed: 5: Input/output error

It appears after executing the brew services start postgresql command.

The solution to this error is to type launchctl remove homebrew.mxcl.postgresql in the terminal followed by brew services start postgresql.

Commands

Most of the commands for PostgreSQL that will help you with your work, such as creating a database, users, or import / export databases.

Authorization

Authorization psql postgres, if you are already authorized use \q to exit.

• Work under the default user: psql postgres.
• Work under a specific user: psql postgres -U [user_name].
• Exit - \q.

Databases

List of all databases: \l.

Key for creating databases create /database or CREATE DATABASE.

• Create a new database: create database [my_database_name];.
• Key to drop databases: drop database; or DROP DATABASE;.
• Deleting a database: drop database [my_database_name];.
• Rename the database: alter database [current_database_name] rename to [new_database_name];.

Import of databases.

• Method one: psql -h [hostname] -d [database_name] -U [user_name] -f ~/path/to/database_file.sql;.
• Method two: psql [database_name] < ~/path/to/database_file.sql;.

Exporting databases: pg_dump -U [username] -h [hostname] [database_name[ >> export_file_name.sql;.

Users

Key for creating users create role or CREATE ROLE;.

• Create a user with a password: create role [user_name] with login password '[user_password]';.
• Create a user with an encrypted password: create role [user_name] with login encrypted password '[user_password]';.
• Create a user without a password: create role [user_name] with login;.
• drop user or `DROP USER` user deletion key.
• Deleting a specific user: drop user [user_name];.
• Key for issuing roles to users alter role; or ALTER ROLE;.
• Allow users to create databases: alter role [user_name] createdb;;
• User rename key - alter role; or ALTER ROLE;.
• Rename a specific user: alter user [user_name] rename to [new_user_name];.
• Change the user's password: alter user [user_name] with password 'new_password';.

Tables

Create a simple table: create table accounts (user_id serial PRIMARY KEY, username VARCHAR ( 50 ) UNIQUE NOT NULL, password VARCHAR ( 50 ) NOT NULL, email VARCHAR ( 255 ) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP) ;.

Deleting a table: drop table accounts;;

Connecting to a database in projects

By default, the service starts on already known ports. For PostgreSQL, this port is marked with the number 5432, therefore, in order to connect to any server, you must enter in the connection settings the server with the IP address where PostgreSQL itself is located and its port, for example 100.100.100.100:5432 (this address was invented).

In order to set up a local development connection, you can use one of two options for the IP address: localhost or 123.0.0.1 with port 5432. For example localhost:5432.

Usually, ENV files (environments) are used for development and they indicate the ip addresses and ports needed for work.

Also, do not forget about the user under which you will need to enter the database itself, by default the basic (root parameters) are used, but it is better to create a separate user and give him the necessary roles, for example:

• Create a new user CREATE ROLE MyNewUserName WITH LOGIN PASSWORD 'MyNewUserPassword';.
• Allow the new user to interact with databases ALTER ROLE MyNewUserName CREATEDB;.
• Exit psql using the exit; command.
• Set the new user as primary psql postgres -U MyNewUserName.