Rainlily ✨

PostgreSQL Guide

PostgreSQL is a powerful, open-source relational database management system. This guide covers installation, setup, and common operations on macOS and Linux.

What is PostgreSQL?

PostgreSQL (often called Postgres) is an advanced, enterprise-class relational database system that supports both SQL (relational) and JSON (non-relational) querying. It’s known for its reliability, feature robustness, and performance.

Key Features

  • ACID Compliance: Ensures data integrity and reliability
  • Extensibility: Supports custom functions, data types, and operators
  • Advanced Indexing: Multiple indexing strategies including B-tree, Hash, GiST, SP-GiST, GIN, and BRIN
  • Full-Text Search: Built-in support for full-text search
  • JSON Support: Native JSON and JSONB data types
  • Concurrent Access: Handles multiple users simultaneously

Installation Methods

macOS

  • Homebrew (recommended): Easy package management
  • Postgres.app: GUI application for macOS
  • Official Installer: Direct download from postgresql.org

Linux

  • Distribution packages (recommended): apt, dnf, pacman, etc.
  • Official packages from postgresql.org for some distributions

Quick Start

After installation, PostgreSQL typically runs as a background service. You can connect using:


                psql postgres

Or connect to a specific database:


                psql -d your_database_name

Common Operations

Check PostgreSQL Version


                psql --version

Start / Stop Service (macOS with Homebrew)


                brew services start postgresql@<version>
# Example: brew services start postgresql@15
 
brew services stop postgresql@<version>

Start / Stop Service (Linux with systemd)


                sudo systemctl start postgresql
sudo systemctl stop postgresql

Check Service Status

macOS (Homebrew)


                brew services list | grep postgresql

Linux (systemd)


                sudo systemctl status postgresql

Next Steps