PostgreSQL Installation and Setup

Before writing any SQL, PostgreSQL must be installed on the machine. This topic covers how to install PostgreSQL on Windows, macOS, and Linux, how to verify the installation, and how to connect using the built-in command-line tool called psql. A graphical tool called pgAdmin is also introduced for those who prefer a visual interface.

Choosing an Installation Method

PostgreSQL can be installed in several ways depending on the operating system. The most common method for beginners is using the official installer, which handles everything automatically including the server setup and basic configuration.

Installing PostgreSQL on Windows

Step 1: Download the Installer

Go to the official PostgreSQL download page at https://www.postgresql.org/download/windows and click on the installer provided by EDB (EnterpriseDB). Choose the latest stable version.

Step 2: Run the Installer

Open the downloaded .exe file. The installer will guide through several steps:

• Choose an installation directory (the default is fine for beginners)
• Select components — keep all selected, including pgAdmin and the command-line tools
• Choose a data directory where PostgreSQL will store database files
• Set a password for the default superuser account called postgres — remember this password
• Choose a port number — the default is 5432, which is standard
• Select the locale — the default is fine

Step 3: Complete the Installation

Click through the remaining steps and let the installer finish. Once done, PostgreSQL will be running as a background service automatically.

Installing PostgreSQL on macOS

Option 1: Using Homebrew

Homebrew is a popular package manager for macOS. Open the Terminal and run:

brew install postgresql@16

After installation, start the PostgreSQL service:

brew services start postgresql@16

Option 2: Using the Graphical Installer

Visit https://www.postgresql.org/download/macosx and download the macOS installer. The steps are similar to the Windows process.

Installing PostgreSQL on Linux (Ubuntu/Debian)

Step 1: Update Package Lists

sudo apt update

Step 2: Install PostgreSQL

sudo apt install postgresql postgresql-contrib

Step 3: Start the Service

sudo systemctl start postgresql

Step 4: Enable Auto-Start on Boot

sudo systemctl enable postgresql

Verifying the Installation

After installation, verify that PostgreSQL is running correctly by checking its version. Open a terminal or command prompt and run:

psql --version

The output should display something like:

psql (PostgreSQL) 16.2

This confirms that the command-line client is installed and accessible.

Connecting to PostgreSQL Using psql

psql is the official command-line interface for PostgreSQL. It allows direct interaction with the database by typing SQL commands.

On Windows

Open the Start Menu, search for SQL Shell (psql), and open it. It will prompt for the server, database, port, username, and password. Press Enter to accept the defaults for each, then enter the password set during installation.

On macOS and Linux

Open the Terminal and switch to the default PostgreSQL user:

sudo -i -u postgres

Then launch the psql shell:

psql

The psql Prompt

Once connected, the prompt will look like this:

postgres=#

This means the connection is active and PostgreSQL is ready to accept commands.

Basic psql Commands

These are not SQL commands — they are special psql shortcuts that begin with a backslash:

Introduction to pgAdmin

pgAdmin is a graphical tool that provides a visual interface for managing PostgreSQL databases. It is included in the Windows installer and can be downloaded separately for macOS and Linux from https://www.pgadmin.org.

Connecting to PostgreSQL in pgAdmin

After opening pgAdmin in a browser, follow these steps:

• Click on Add New Server
• Enter a name for the connection (any label works)
• Go to the Connection tab
• Set Host to localhost
• Set Port to 5432
• Set Username to postgres
• Enter the password set during installation
• Click Save

Once connected, all databases, tables, and data are visible through a tree structure on the left side of the screen.

Understanding the Default Setup

The postgres Superuser

During installation, a default superuser account named postgres is created. A superuser has full access to all databases and can perform any operation. This account should be used carefully and only for administrative tasks.

Default Databases

PostgreSQL creates three databases by default:

• postgres — the default database for the postgres user
• template0 — a clean template used when creating new databases
• template1 — a customizable template; every new database is based on this by default

Example: First Interaction with psql

After connecting to psql, list the available databases:

\l

Connect to the default postgres database:

\c postgres

Run a simple SQL command to confirm everything works:

SELECT version();

The output will display the full PostgreSQL version string, confirming the connection is working correctly.

Key Points

• PostgreSQL installs a background server process and a command-line client called psql.
• The default superuser is postgres and the default port is 5432.
• psql backslash commands (like \l and \dt) are shortcuts for navigation — they are not SQL.
• pgAdmin provides a graphical alternative to psql for those who prefer a visual interface.
• Every SQL command in psql must end with a semicolon (;) to execute.