Kenhuang Academy
  • English

      • PostgreSQL Installation Guide

      System Requirements

      Before installing PostgreSQL, ensure your system meets the following requirements:

      Hardware Requirements

      ComponentMinimumRecommended
      CPU1 GHz2+ GHz multi-core
      RAM1 GB4+ GB
      Disk Space512 MB1+ GB
      NetworkOptionalRequired for remote access

      Supported Operating Systems

      • Windows 10/11 (64-bit)
      • macOS 10.15 (Catalina) or higher
      • Linux (Ubuntu, Debian, Fedora, CentOS, RHEL, etc.)

      Windows Installation

      Step 1: Download PostgreSQL

      1. Visit the official PostgreSQL download page: https://www.postgresql.org/download/windows/
      2. Click on "Download the installer"
      3. Choose the latest PostgreSQL version (e.g., PostgreSQL 16)
      4. Download the Windows x86-64 installer (.exe file)

      Step 2: Run the Installer

      1. Double-click the downloaded .exe file
      2. If prompted by User Account Control, click "Yes" to allow
      3. The PostgreSQL Setup Wizard will start

      Step 3: Choose Installation Directory

      1. Accept the default directory or choose a custom location
      2. Default: C:\Program Files\PostgreSQL\16
      3. Click "Next" to continue

      Step 4: Select Components

      Select the components you want to install:

      • PostgreSQL Server (required)
      • pgAdmin 4 (graphical administration tool)
      • Stack Builder (additional tools installer)
      • Command Line Tools

      Step 5: Data Directory

      1. Accept the default data directory
      2. Default: C:\Program Files\PostgreSQL\16\data
      3. Click "Next" to continue

      Step 6: Set Password

      1. Enter a strong password for the postgres superuser
      2. Remember this password - you'll need it to connect to the database
      3. Click "Next" to continue

      Step 7: Port Configuration

      1. Accept the default port (5432)
      2. If port 5432 is already in use, choose an alternative port
      3. Click "Next" to continue

      Step 8: Locale

      1. Select the default locale for your database
      2. Default: "[Default locale]" uses your system locale
      3. Click "Next" to continue

      Step 9: Complete Installation

      1. Review your installation settings
      2. Click "Next" to begin installation
      3. Wait for the installation to complete (may take a few minutes)
      4. Uncheck "Launch Stack Builder at exit" if not needed
      5. Click "Finish" to close the installer

      Method 2: Chocolatey

      If you have Chocolatey package manager installed:

      # Install PostgreSQL
choco install postgresql16

# Start PostgreSQL service
choco start postgresql16

      Method 3: Winget

      Using Windows Package Manager:

      # Install PostgreSQL
winget install PostgreSQL.PostgreSQL

      Verify Windows Installation

      # Check PostgreSQL version
psql --version

# Connect to PostgreSQL
psql -U postgres

      macOS Installation

      Install Homebrew (if not already installed)

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

      Install PostgreSQL

      # Update Homebrew
brew update

# Install PostgreSQL 16
brew install postgresql@16

# Add PostgreSQL to PATH
echo 'export PATH="/usr/local/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

      Start PostgreSQL

      # Start PostgreSQL as a background service
brew services start postgresql@16

# Or start manually
pg_ctl -D /usr/local/var/postgresql@16 start

      Initialize Database

      # Create a database cluster (if needed)
initdb /usr/local/var/postgresql@16

# Create your first database
createdb mydatabase

      Method 2: PostgreSQL macOS Installer

      1. Download the installer from https://www.postgresql.org/download/macos/
      2. Open the downloaded .dmg file
      3. Run the PostgreSQL installer
      4. Follow the on-screen instructions
      5. Enter your admin password when prompted
      6. Set the postgres user password
      7. Launch pgAdmin to verify installation

      Method 3: Postgres.app

      1. Download Postgres.app from https://postgresapp.com/
      2. Move Postgres.app to Applications folder
      3. Double-click to start
      4. Click "Initialize" to create a new server
      5. Add to PATH:
      echo 'export PATH="/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

      Verify macOS Installation

      # Check version
psql --version

# Connect to PostgreSQL
psql postgres

      Linux Installation

      Ubuntu/Debian

      Update System

      sudo apt update
sudo apt upgrade

      Install PostgreSQL

      # Install PostgreSQL and additional useful packages
sudo apt install postgresql postgresql-contrib

# Install additional modules (optional)
sudo apt install postgresql-16-postgis-3 postgresql-16-pgrouting

      Manage PostgreSQL Service

      # Start PostgreSQL service
sudo systemctl start postgresql

# Enable PostgreSQL to start on boot
sudo systemctl enable postgresql

# Check status
sudo systemctl status postgresql

# Restart PostgreSQL
sudo systemctl restart postgresql

      Access PostgreSQL

      # Switch to postgres user
sudo -i -u postgres

# Or use sudo to run commands as postgres user
sudo -u postgres psql

      CentOS/RHEL

      Add PostgreSQL Repository

      # For CentOS/RHEL 8+
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# For CentOS/RHEL 7
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

      Install PostgreSQL

      # For CentOS/RHEL 8+
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server postgresql16-contrib

# For CentOS/RHEL 7
sudo yum install -y postgresql16-server postgresql16-contrib

      Initialize Database

      # For CentOS/RHEL 8+
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

# For CentOS/RHEL 7
sudo /usr/pgsql-16/bin/postgresql16-setup initdb

      Start PostgreSQL

      # Enable and start PostgreSQL
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

# Check status
sudo systemctl status postgresql-16

      Fedora

      # Install PostgreSQL
sudo dnf install -y postgresql-server postgresql-contrib

# Initialize database
sudo postgresql-setup --initdb

# Enable and start service
sudo systemctl enable --now postgresql

      Docker Installation

      Install Docker

      Windows/macOS

      1. Download Docker Desktop from https://www.docker.com/
      2. Install the application
      3. Start Docker Desktop

      Linux

      # Ubuntu/Debian
sudo apt install docker.io docker-compose

# Start Docker service
sudo systemctl start docker
sudo systemctl enable docker

# Add user to docker group
sudo usermod -aG docker $USER

      Run PostgreSQL with Docker

      # Pull PostgreSQL image
docker pull postgres:16

# Create and run container
docker run --name postgres-container \
  -e POSTGRES_PASSWORD=your_secure_password \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v postgres_data:/var/lib/postgresql/data \
  -d postgres:16

# View running containers
docker ps

# Connect to PostgreSQL
docker exec -it postgres-container psql -U myuser -d mydb

# Stop container
docker stop postgres-container

# Start container
docker start postgres-container

# Remove container
docker rm postgres-container

      Docker Compose

      Create a docker-compose.yml file:

      version: '3.8'

services:
  postgres:
    image: postgres:16
    container_name: postgres_db
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mysecretpassword
      POSTGRES_DB: mydb
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    restart: always

volumes:
  postgres_data:

      Run with:

      # Start services
docker-compose up -d

# Stop services
docker-compose down

# View logs
docker-compose logs -f

      Verifying Installation

      Check PostgreSQL Version

      # Using command line tool
psql --version

# Using SQL query
psql -U postgres -c "SELECT version();"

      Test Connection

      # Connect to PostgreSQL
psql -U postgres

# Or using docker
docker exec -it postgres-container psql -U postgres

      Basic Commands

      -- List all databases
\l

-- List all users/roles
\du

-- List all tables
\dt

-- Show current user
SELECT current_user;

-- Show current database
SELECT current_database();

-- Show PostgreSQL version
SELECT version();

-- Exit psql
\q

      Initial Configuration

      Setting Up Password Authentication

      # Connect to PostgreSQL
sudo -u postgres psql

-- Change password for postgres user
ALTER USER postgres WITH PASSWORD 'your_new_password';

-- Exit
\q

      Or using psql password command:

      -- In psql, change password
\password postgres

      Configuring Remote Access

      Edit pg_hba.conf

      # Find pg_hba.conf location
sudo -u postgres psql -c "SHOW hba_file;"

# Edit the file
sudo nano /etc/postgresql/16/main/pg_hba.conf

# Add remote access rule (add this line)
# host    all             all             0.0.0.0/0            md5

      Edit postgresql.conf

      # Find postgresql.conf location
sudo -u postgres psql -c "SHOW config_file;"

# Edit the file
sudo nano /etc/postgresql/16/main/postgresql.conf

# Change listen_addresses (uncomment and modify)
# listen_addresses = '*'

      Restart PostgreSQL

      # On systemd systems
sudo systemctl restart postgresql

# Or reload configuration
sudo systemctl reload postgresql

      Creating a New Database

      # Using createdb command
createdb mydatabase

# Or using SQL
sudo -u postgres psql
      CREATE DATABASE mydatabase;

      Creating a New User

      -- Create user with password
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';

-- Create user with privileges
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword' CREATEDB CREATEROLE;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

-- Grant schema privileges
GRANT ALL ON SCHEMA public TO myuser;

      Common Installation Issues

      Port Already in Use

      # Check what's using port 5432
sudo lsof -i :5432

# Or change PostgreSQL port in postgresql.conf
# port = 5433

      Permission Issues

      # Fix ownership
sudo chown -R postgres:postgres /var/lib/postgresql

# Fix permissions
sudo chmod -R 700 /var/lib/postgresql

      Disk Space Issues

      # Check disk space
df -h

# Clean up old logs
sudo find /var/lib/postgresql -name "*.log" -mtime +30 -delete

      Connection Issues

      # Check PostgreSQL status
sudo systemctl status postgresql

# Check listening ports
sudo netstat -tlnp | grep postgres

# Test local connection
psql -h localhost -U postgres

# Check firewall
sudo ufw allow 5432/tcp

      Authentication Failed

      1. Check pg_hba.conf for correct authentication method
      2. Verify password is correct
      3. Ensure user exists and has proper permissions
      4. Restart PostgreSQL after configuration changes

      Uninstalling PostgreSQL

      Windows

      1. Open Control Panel > Programs > Programs and Features
      2. Find PostgreSQL in the list
      3. Right-click and select "Uninstall"
      4. Follow the uninstaller instructions
      5. Manually delete remaining files if needed:
        • C:\Program Files\PostgreSQL
        • C:\Users\YourUser\AppData\Roaming\postgresql

      macOS

      # Using Homebrew
brew uninstall postgresql
brew cleanup

# Remove data directory
rm -rf /usr/local/var/postgres

# Remove history
rm -rf ~/.psql_history

      Linux

      # Ubuntu/Debian
sudo apt remove --purge postgresql postgresql-contrib
sudo rm -rf /var/lib/postgresql
sudo rm -rf /etc/postgresql

# CentOS/RHEL
sudo yum remove postgresql16-server postgresql16-contrib
sudo rm -rf /var/lib/pgsql
sudo rm -rf /etc/postgresql

      Docker

      # Stop and remove container
docker stop postgres-container
docker rm postgres-container

# Remove image
docker rmi postgres:16

# Remove volume
docker volume rm postgres_data

      Post-Installation Steps

      After successful installation:

      1. Set a strong password for the postgres user
      2. Configure authentication in pg_hba.conf
      3. Enable remote access if needed (postgresql.conf)
      4. Create databases and users for your applications
      5. Install pgAdmin for graphical administration
      6. Set up backups using pg_dump or pg_basebackup
      7. Configure logging for monitoring
      8. Optimize settings based on your hardware

      Next Steps

      • Learn basic PostgreSQL commands and SQL syntax
      • Explore pgAdmin for database management
      • Set up regular backups
      • Configure performance tuning
      • Implement security best practices

      Summary

      This guide covered PostgreSQL installation on:

      • Windows (Interactive Installer, Chocolatey, Winget)
      • macOS (Homebrew, Official Installer, Postgres.app)
      • Linux (Ubuntu, Debian, CentOS, RHEL, Fedora)
      • Docker (Container, Docker Compose)

      Remember to:

      • Keep PostgreSQL updated
      • Use strong passwords
      • Configure proper authentication
      • Set up regular backups
      • Monitor performance and logs

      PostgreSQL is now ready for use!