Install PostgreSQL 14.7 for MacOS
In this tutorial, you'll learn how to install PostgreSQL 14.7 on your MacBook using Homebrew, a popular package manager for macOS.
The process is straightforward and consists of the following steps:
- Install Homebrew
- Install PostgreSQL
- Download the Northwind PostgreSQL SQL file
- Create a New PostgreSQL Database
- Import the Northwind SQL file
- Verify the Northwind database installation
- Connecting to the Database Using Jupyter Notebook
Prerequisites
You'll need a MacBook or iMac running macOS 10.13 or later to follow this tutorial.
Install Xcode Command Line Tools
First, you need to install the Xcode Command Line Tools, which provide essential tools for building software on your Mac.
- Open the Terminal app (you can find it in Applications > Utilities) and enter the following command:
xcode-select --install
A pop-up window will appear, prompting you to install the Command Line Tools. Click on "Install" to proceed. Once the installation is complete, you can move on to the next step.
Step 1: Install Homebrew
With the Xcode Command Line Tools installed, you can now install Homebrew itself. Homebrew is a package manager for macOS that will make it easier for us to install PostgreSQL and manage other software packages.
After installing Homebrew, it's important to add it to your system's PATH. By doing this, we're telling the system where to find the Homebrew executables. This means you can run Homebrew commands directly from any location in your Terminal.
We will then verify the successful installation of Homebrew using the version check command. This crucial step confirms that Homebrew has been correctly installed and is accessible via your Terminal.
Now, let's get started with the installation of Homebrew and its addition to your system's PATH.
1. Copy and paste the following command into the Terminal app
The script will automatically download and install Homebrew on your Mac. You might be prompted to enter your admin password during the installation process. After the installation is complete, you will see a message saying, "Installation successful!"
/bin/bash -c "$(curl -fsSL <https://raw.githubusercontent.com/Homebrew/install/master/install.sh>)"
2. Add Homebrew to Your PATH
Follow the steps below to complete the Homebrew installation:
(echo; echo 'eval "$(/opt/homebrew/bin/brew shellenv)"') >> /Users/user_name/.zprofile
This command appends the necessary line to your .zprofile file to load Homebrew into your PATH. Replace /Users/user_name
with your actual user directory.
3. Running the command
Now, run the second command:
eval "$(/opt/homebrew/bin/brew shellenv)"
This command loads the Homebrew environment variables into your current Terminal session.
4. Verify the installation
To verify your Homebrew installation, run the following command:
brew --version
This should display the Homebrew version number, confirming that it's installed and available in your PATH.
Step 2: Install PostgreSQL
In this step, we will utilize Homebrew to set up PostgreSQL on your macOS system. The steps will guide you through updating Homebrew to guarantee that we're accessing the latest software packages. Next, we'll install PostgreSQL version 14 using a single Homebrew command, and start the PostgreSQL service, getting the database server up and running.
Finally, we'll secure our PostgreSQL installation by creating a new user (or "role") called postgres
with superuser privileges. This user will serve as our primary means of interacting with our PostgreSQL server, granting us broad administrative capabilities. Now, let's dive into these steps:
1. Ensure Homebrew is Installed
Make sure you have Homebrew installed. If not, you can install it using the following command:
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
2. Update Homebrew
Update Homebrew to ensure you have the latest package information
brew update
3. Install PostgreSQL 14
Install PostgreSQL 14 using Homebrew by entering the following command:
brew install postgresql@14
4. Start PostgreSQL Service
After the installation process is done, start the PostgreSQL service:
brew services start postgresql@14
You should see a message indicating that PostgreSQL has started successfully:
Successfully started postgresql@14
5. Verify PostgreSQL Installation
To ensure PostgreSQL is installed correctly, check the version:
psql --version
6. Create a PostgreSQL User
To ensure you will be able to use PostgreSQL without any issues, let’s create a new PostgreSQL user (also called a "role") named postgres with superuser privileges:
createuser -s postgres
7. Set a Password for the postgres
User
For security purposes, set a password for the postgres
user:
psql
\password postgres
8. Configure PostgreSQL Environment
Add PostgreSQL to your PATH by adding the following line to your shell profile (~/.zshrc or ~/.bash_profile
):
export PATH="/usr/local/opt/postgresql@14/bin:$PATH"
Then, reload your profile:
source ~/.zshrc # or source ~/.bash_profile
9. Troubleshooting Tips
If the PostgreSQL service does not start, check the logs for errors:
tail -f /usr/local/var/log/postgres.log
Ensure your system has enough resources and dependencies for PostgreSQL.
Step 3: Download the Northwind PostgreSQL SQL File
Now, we're going to introduce you to the Northwind database and help you download it. The Northwind database is a sample database originally provided by Microsoft for its Access Database Management System. It's based on a fictitious company named "Northwind Traders," and it contains data on their customers, orders, products, suppliers, and other aspects of the business. In our case, we'll be working with a version of Northwind that has been adapted for PostgreSQL.
The following steps will guide you on how to download this PostgreSQL-compatible version of the Northwind database from GitHub to your local machine. Let's get started:
First, you need to download a version of the Northwind database that's compatible with PostgreSQL. You can find an adapted version on GitHub. To download the SQL file, follow these steps:
-
Open your Terminal application.
-
Create a new directory for the Northwind database and navigate to it:
mkdir northwind && cd northwind
-
Download the Northwind PostgreSQL SQL file using curl:
curl -O <https://raw.githubusercontent.com/pthom/northwind_psql/master/northwind.sql>
This will download the
northwind.sql
file to thenorthwind
directory you created.
Step 4: Create a New PostgreSQL Database
Now that we've downloaded the Northwind SQL file, it's time to prepare our PostgreSQL server to host this data. The next steps will guide you in creating a new database on your PostgreSQL server, a crucial prerequisite before importing the Northwind SQL file.
Creating a dedicated database for the Northwind data is good practice as it isolates these data from other databases in your PostgreSQL server, facilitating better organization and management of your data. These steps involve connecting to the PostgreSQL server as the postgres
user, creating the northwind
database, and then exiting the PostgreSQL command-line interface.
Let's proceed with creating your new database:
-
Connect to the PostgreSQL server as the
postgres
user:psql -U postgres
-
Create a new database called
northwind
:postgres-# CREATE DATABASE northwind;
-
Exit the
psql
command-line interface:postgres-# \\q
Step 5: Import the Northwind SQL File
We're now ready to import the Northwind SQL file into our newly created northwind
database. This step is crucial as it populates our database with the data from the Northwind SQL file, which we will use for our PostgreSQL learning journey.
These instructions guide you through the process of ensuring you're in the correct directory in your Terminal and executing the command to import the SQL file. This command will connect to the PostgreSQL server, target the northwind
database, and run the SQL commands contained in the northwind.sql
file.
Let's move ahead and breathe life into our northwind
database with the data it needs!
With the northwind
database created, you can import the Northwind SQL file using psql
. Follow these steps:
- In your Terminal, ensure you're in the
northwind
directory where you downloaded thenorthwind.sql
file. - Run the following command to import the Northwind SQL file into the
northwind
database:psql -U postgres -d northwind -f northwind.sql
This command connects to the PostgreSQL server as the
postgres
user, selects thenorthwind
database, and executes the SQL commands in thenorthwind.sql
file.
Step 6: Verify the Northwind Database Installation
You've successfully created your northwind
database and imported the Northwind SQL file. Next, we must ensure everything was installed correctly, and our database is ready for use.
These upcoming steps will guide you on connecting to your northwind
database, listing its tables, running a sample query, and finally, exiting the command-line interface. Checking the tables and running a sample query will give you a sneak peek into the data you now have and verify that the data was imported correctly. This means we can ensure everything is in order before diving into more complex operations and analyses.
To verify that the Northwind database has been installed correctly, follow these steps:
-
Connect to the
northwind
database usingpsql
:psql -U postgres -d northwind
-
List the tables in the Northwind database:
postgres-# \\dt
You should see a list of Northwind tables: categories, customers, employees, orders, and more.
-
Run a sample query to ensure the data has been imported correctly. For example, you can query the
customers
table:postgres-# SELECT * FROM customers LIMIT 5;
This should return the first five rows from the
customers
table. -
Exit the
psql
command-line interface:postgres-# \\q
Congratulations! You've successfully installed the Northwind database in PostgreSQL using an SQL file and psql
.
Step 7: Connect to the Database Using Jupyter Notebook
As we wrap up our installation, we will now introduce Jupyter Notebook as one of the tools available for executing SQL queries and analyzing the Northwind database. Jupyter Notebook offers a convenient and interactive platform that simplifies the visualization and sharing of query results, but it's important to note that it is an optional step. You can also access Postgres through other means. However, we highly recommend using Jupyter Notebook for its numerous benefits and enhanced user experience.
To set up the necessary tools and establish a connection to the Northwind database, here is an overview of what each step will do:
-
!pip install ipython-sql
: This command installs theipython-sql
package. This package enables you to write SQL queries directly in your Jupyter Notebook, making it easier to execute and visualize the results of your queries within the notebook environment. -
%load_ext sql
: This magic command loads thesql
extension for IPython. By loading this extension, you can use the SQL magic commands, such as%sql
and%sql
, to run SQL queries directly in the Jupyter Notebook cells. -
%sql postgresql://postgres@localhost:5432/northwind
: This command establishes a connection to the Northwind database using the PostgreSQL database system. The connection string has the following format:'postgresql://username@hostname:port/database_name'
In this case,
username
ispostgres
,hostname
islocalhost
,port
is5432
, anddatabase_name
isnorthwind
. The%sql
magic command allows you to run a single-line SQL query in the Jupyter Notebook. -
Copy the following text into a code cell in the Jupyter Notebook:
!pip install ipython-sql` %load_ext sql` %sql postgresql://postgres@localhost:5432/northwind
-
Run the cell by either:
- Clicking the "Run" button on the menu bar.
- Using the keyboard shortcut:
Shift + Enter
orCtrl + Enter
.
-
Upon successful connection, you should see an output similar to the following:
'Connected: postgres@northwind'
This output confirms that you are now connected to the Northwind database, and you can proceed with the guided project in your Jupyter Notebook environment.
Once you execute these commands, you'll be connected to the Northwind database, and you can start writing SQL queries in your Jupyter Notebook using the %sql
or %%sql
magic commands.
Next Steps
Based on what you've accomplished, here are some potential next steps to continue your learning journey:
- Deepen Your SQL Knowledge:
- Try formulating more complex queries on the Northwind database to improve your SQL skills. These could include joins, subqueries, and aggregations.
- Understand the design of the Northwind database: inspect the tables, their relationships, and how data is structured.
- Experiment with Database Management:
- Learn how to backup and restore databases in PostgreSQL. Try creating a backup of your Northwind database.
- Explore different ways to optimize your PostgreSQL database performance like indexing and query optimization.
- Integration with Python:
- Learn how to use
psycopg2
, a popular PostgreSQL adapter for Python, to interact with your database programmatically. - Experiment with ORM (Object-Relational Mapping) libraries like
SQLAlchemy
to manage your database using Python.
- Learn how to use