Introduction: PostgreSQL, commonly known as Postgres, has emerged as a top choice for relational database management in recent years. While there are multiple methods to install Postgres on Macs with Apple Silicon, including the M1, M2, and the newest M3 CPUs, the journey isn't always straightforward.
The official route suggests using the Postgres.app with its .dmg file, but I've found this path often involves cumbersome steps, like setting up a unique user account. Moreover, system upgrades can disrupt your Postgres environment, turning what should be a simple update into a daunting task.
Drawing from my experience, this article will guide you through a more efficient installation process for your Postgres server on Apple Silicon Macs. I'll also show you how to upgrade seamlessly to the latest Postgres version and modify the default postgres.conf for optimal performance.
My approach ensures Postgres not only installs smoothly but also runs exceptionally fast on your local machine.
Ready to streamline your Postgres setup? Let's dive in.
Step 1: Install Homebrew
Homebrew is a great package manager that makes it easy to install and uninstall software (especially for open-source tools)
Visit https://brew.sh/ to follow the step-by-step instructions, or copy and paste the following command into your terminal (iTerm 2 you just installed).
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"Step 2: Brew Install Postgres
PostgreSQL server
brew install postgresqlHomebrew will always search for the latest version of a formula; as of Nov, 2022, mine is Postgres 14.5;
After a few seconds, you should see a success message.
Step 2.1 Turn on the Postgres server automatically
brew services start postgresqlEvery time you restart your Mac, the Postgres server will be automatically running in the background.
Step 2.2 check Postgres server installation
On your command line, type
psql -d postgrespsql is a command-line utility that was installed together with the Postgres server, -d specifies which database to connect to. Postgres is a default database that was installed by default.
You should see something like the following:
$ psql -d Postgres
psql (13.2)
Type "help" for help.
postgres=#
Type \q to exit the database console.
To see other databases, type \l
To switch to a different database, use \c <other_db_name>
Step 3: change the default configuration file to speed up your Postgres server.
By default, your newly installed Postgres server is based on a very conservative list of configurations, including some of the most important memory utilization factors which hinder the server's performance. Let's change that.
First, we need to locate the file, in your command line, type the following in your terminal (If you are not using the latest Postgres 14 or on a Intel based Mac, the location will be different).
psql -c 'SHOW config_file' -d postgres
config_file
-----------------------------------------
/opt/homebrew/var/postgresql@14/postgresql.conf
Viola, your Postgres config file is located here (on an M1 Mac):
/opt/homebrew/var/postgresql@14/postgresql.confNext, let's edit it:
Since the file is protected, you will need to edit it as an admin.
In your command line, use sudo to execute the following:
sudo vim /opt/homebrew/var/postgresql@14/postgresql.confYou will notice it is a big file. The start of the file may look similar as the following:
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()". Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units: B = bytes Time units: us = microseconds
# kB = kilobytes ms = milliseconds
# MB = megabytes s = seconds
# GB = gigabytes min = minutes
# TB = terabytes h = hours
# d = days
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '' # write an extra PID file
# (change requires restart)
Since there are many variables that impact the performance of your Postgres server and explaining those factors is beyond the scope of this article, if you are interested to know more, please feel free to read them here on Postgres' official website.
https://www.postgresql.org/docs/14/config-setting.html
For our purposes: I recommend using an online tool to help you set your Postgres server parameters.
Enter your computer settings; here is what I entered for my M1 Mac MAX(2021)
You can then copy those settings and paste them at the bottom of your postgres.conf file.
# NEW CONFIG PARAMETERS
# DB Version: 14
# OS Type: mac
# DB Type: web
# Total Memory (RAM): 32 GB
# CPUs num: 8
# Connections num: 1000
# Data Storage: ssd
max_connections = 1000
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
work_mem = 2097kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4This is what the end of my file looks like:
Step 4. Finally, we need to restart the Postgres server.
In your command-line tool:
$ brew services restart postgresql
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
Congratulations, now you have a well-performed Postgres server on your Mac. If you run into any issues or questions, please feel free to reach out to me.
Eager to experiment with PostgreSQL without the setup? Dip your toes into the PostgreSQL Playground, where you can play with real data, completely free of charge.
