Installing PostgreSQL: Step-by-step Guide for Windows, macOS, Linux

Introduction

Hope you’re enjoying SQL and ready to get down to business. It’s time to find out what this PostgreSQL thing is all about and install it on your computer. That way, you’ll get to know all the ins and outs of working with PostgreSQL, discover all the hidden pitfalls, and probably bump your head a few times. That’s the way it goes 😎

The PostgreSQL DBMS (often just called Postgres) is a powerful, scalable, and super-reliable open-source relational DBMS. It’s been around forever—since 1986—as a research project at the University of California, Berkeley. And for decades now, PostgreSQL has been making developers happy with its reliability, flexibility, and awesome features 🥰

Here are a few reasons why developers and companies all over the world love PostgreSQL:

• ACID Support: PostgreSQL follows the principles of Atomicity, Consistency, Isolation, and Durability (ACID), making it a must-have for working with important data.
• Extensibility: you can add your own functions, data types, and even indexes.
• JSONB: built-in support for working with JSON, making it a powerful tool for modern web apps.
• Open Source: actively supported by the community and various organizations. No hidden fees or licenses.
• Scalability: PostgreSQL is great for both small projects and huge enterprise systems.

Unlike MySQL, PostgreSQL keeps up with the times. It’s picked up some cool NoSQL features, but still keeps all the perks of relational databases. And since it’s free, you’re definitely going to love it ❤️

Installing PostgreSQL

If you mess up installing PostgreSQL, you’re in for a world of pain: the server won’t start, clients can’t connect, and instead of creating a database, you’ll be Googling stuff like “Why won’t PostgreSQL start on Linux?” If you follow my step-by-step guide, you’ll dodge all that drama and step right into the world of PostgreSQL experts, no sweat.

Installing PostgreSQL on Windows

1. Download the installer

   • Go to the official PostgreSQL website.
   • Select the Windows platform. Click the "Download the installer certified by EDB" link to download.
   • Download the latest PostgreSQL distribution (in our case, it’s version 17.5)

2. Run the installer

   • Run the downloaded .exe file.
   • Choose the installation folder path (it’s best to leave it as is).

3. Choose components

   • Leave everything as default: PostgreSQL server, pgAdmin, Stack Builder (if you don’t know what that is, don’t worry about it).
   • Make sure the data directory path is convenient and accessible. For example: C:\Program Files\PostgreSQL\17.

4. Set port and password

   • Set a password for the postgres user. This is the main database user, so remember it or write it down (yeah, we all know about sticky notes).
   • Set the server port (usually 5432). If that port’s taken, pick another one (5433, 5434, etc).

5. Set locale and start installation

   • Next, you’ll be asked to set the database locale. It’s best to leave it as default.
   • And now PostgreSQL is finally ready to install on your Windows computer.

6. Finish

   Towards the end of the installation process, you’ll be asked if you want to run StackBuilder. You can uncheck it and hit “Finish.” Congrats, you’ve installed PostgreSQL! 🎉 The PostgreSQL server should start automatically in the background.

   StackBuilder is a utility that comes with the PostgreSQL installer from EDB (EnterpriseDB). It’s meant for downloading and installing extra tools, drivers, and apps that complement your PostgreSQL setup.

7. Check the installation

   • Open the command prompt (cmd) and run:

     psql -U postgres
     

     Enter the password you set during installation. If you connect successfully, congrats: you’re talking to the server!

Installing PostgreSQL on macOS

If you’re a macOS fan, the easiest way to install PostgreSQL is with Homebrew (seriously, it’s the most convenient way for macOS).

1. Check if Homebrew is installed

   Open Terminal and run:

   brew --version
   

   If Homebrew is installed, you’ll see the version. If not, fix that by running:

   /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
   

2. Install PostgreSQL

   Run the command:

   brew install postgresql
   

3. Start the server

   After installation, start the PostgreSQL server with:

   brew services start postgresql
   

4. Check the installation

   Make sure the server is running:

   psql -U postgres
   

   The first time you run it, you might be asked to create a user. Use createuser for that:

   createuser --interactive
   

5. Set port (optional)

   PostgreSQL on macOS usually uses port 5432 by default. If you need to change the port, edit the config file:

   nano /usr/local/var/postgres/postgresql.conf
   

   Find the port parameter and change its value.

Installing PostgreSQL on Linux

Linux and PostgreSQL get along great. But different distros need slightly different approaches.

For Ubuntu/Debian

1. Update system repositories

   Run the command:

   sudo apt update
   

2. Install PostgreSQL

   Install the PostgreSQL server and client:

   sudo apt install postgresql postgresql-contrib
   

3. Start the server

   Make sure the server is running:

   sudo systemctl start postgresql
   

4. Add PostgreSQL to autostart

   So the server starts automatically:

   sudo systemctl enable postgresql
   

5. Check the installation

   Switch to the postgres user:

   sudo -i -u postgres
   

   Connect to the server:

   psql
   

   If you see the postgres=# prompt, congrats, the server is running!

For CentOS/RedHat

1. Add the PostgreSQL repository

   Download the repository RPM file from the PostgreSQL website:

   sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %rhel)-x86_64/pgdg-redhat-repo-latest.noarch.rpm
   

2. Install PostgreSQL

   Install the server and client:

   sudo yum install postgresql17-server postgresql17
   

3. Initialize the database

   Run:

   sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
   

4. Start the server

   Enable and start the server:

   sudo systemctl enable postgresql-17
   sudo systemctl start postgresql-17
   

5. Check the installation

   Connect to the server as the postgres user:

   sudo -u postgres psql
   

Hope you managed to install and launch everything on the first try. If not—head to the next lecture, where we’ll go over troubleshooting.

2

Task

SQL SELF, level 4, lesson 0

Locked

Starting the PostgreSQL Console

Starting the PostgreSQL Console

Open