PostgreSQL Guide - ganmath/learners GitHub Wiki

Technical documentation covering the steps and commands to install, configure, and troubleshoot PostgreSQL on a Windows system, using Chocolatey as the installation method and focusing on common issues such as configuration and service management.

Installation of PostgreSQL via Chocolatey

1. Open PowerShell as Administrator:

   • Press Win + X and choose “Windows PowerShell (Admin)”.

2. Install PostgreSQL using Chocolatey:

   choco install postgresql --params '/Password:<YourPassword>'
   

   Replace <YourPassword> with a strong password for the PostgreSQL postgres user.

3. Verify Installation:

   choco list --local-only
   

Post-Installation Configuration

1. Add PostgreSQL to System PATH (optional but recommended for easy command-line access):

   • Open System Properties (type sysdm.cpl in Run dialog).
   • Navigate to the “Advanced” tab and click “Environment Variables”.
   • In the “System variables” section, find and select “Path”, then click “Edit”.
   • Click “New” and add the path C:\Program Files\PostgreSQL\16\bin.
   • Click “OK” to close all dialogs.

2. Start PostgreSQL Service:

   • Press Win + R, type services.msc, and press Enter.
   • Locate the PostgreSQL service (e.g., postgresql-x64-16), right-click it, and select “Start”.

3. Set the PGDATA Environment Variable:

   [System.Environment]::SetEnvironmentVariable("PGDATA", "C:\Program Files\PostgreSQL\16\data", "Machine")
   

Initialization of Database Cluster

1. Initialize the PostgreSQL Data Directory:

   cd "C:\Program Files\PostgreSQL\16\bin"
   initdb -D "C:\Program Files\PostgreSQL\16\data"
   

Starting PostgreSQL Manually (Not Recommended for Production)

1. Open a Command Prompt (not as Administrator):
   • Navigate to the bin directory:

     cd "C:\Program Files\PostgreSQL\16\bin"
     

   • Start PostgreSQL:

     postgres -D "C:\Program Files\PostgreSQL\16\data"
     

Verifying PostgreSQL Configuration Files

1. Locate and Inspect Configuration Files:

   • postgresql.conf and pg_hba.conf are located in C:\Program Files\PostgreSQL\16\data.

2. Edit Configuration Files if Necessary:

   • Use a text editor to adjust settings such as listen_addresses and connection permissions in pg_hba.conf.

Troubleshooting Common Errors

1. Service Not Visible in Windows Services:

   • Re-register the service:

     pg_ctl register -N "postgresql" -D "C:\Program Files\PostgreSQL\16\data" -U "NT AUTHORITY\NetworkService"
     

2. Configuration File Not Found Error:

   • Ensure PGDATA is set correctly and points to a directory initialized with initdb.
   • Verify that the configuration files exist in the data directory.

3. Permission Errors When Starting PostgreSQL:

   • Ensure PostgreSQL is not run as an administrator for regular operations.

General Maintenance and Operation

• Starting and Stopping PostgreSQL:

  pg_ctl start
  pg_ctl stop
  

• Accessing PostgreSQL via Command Line:

  psql -U postgres
  

Reinstallation or Reset

• If troubleshooting fails:

  choco uninstall postgresql
  choco install postgresql --params '/Password:<YourNewPassword>'
  

This documentation provides a comprehensive guide to managing a PostgreSQL installation on Windows, addressing installation, configuration, daily operation, and common troubleshooting scenarios.