# How to Use Ansible in Windows: Installing PostgreSQL

This article is part of the **“Ansible for Windows”** series. In this guide, we automate installation of **PostgreSQL (Enterprise Edition)** on a Windows host using Ansible. The playbook performs a silent and unattended install, starts the service, and cleans up the installer file.

---

## Prerequisites

Before running the playbook:

1. **Ansible controller node**  
    You have Ansible installed on a Linux/macOS controller and can reach the Windows host.
    
2. **WinRM configured**  
    The target Windows machine is accessible to Ansible over WinRM (HTTP or HTTPS), and your inventory/group vars are set appropriately (e.g., `ansible_connection=winrm`, `ansible_user`, `ansible_password`, `ansible_winrm_transport`).
    
3. **Installer available locally on the controller**  
    Download the PostgreSQL Windows installer (`.exe`) **to your Ansible controller**.  
    This workflow is ideal when the Windows machine has **no internet access**; Ansible will copy the installer to the host and run it there.
    
4. **Online alternative (optional)**  
    If the Windows host **does** have internet access, you can install directly from a URL using `win_package`, skipping the copy step. An example is provided later in this article.
    

---

## Full Playbook (copy-paste ready)

```yaml
---
- name: Install PostgreSQL silently on Windows
  hosts: windows
  gather_facts: true

  vars:
    # Dummy paths & credentials for illustration; replace for your environment.
    pg_installer_local_path: "/dummy/path/postgresql-installer.exe"
    pg_installer_remote_path: "C:\\Temp\\postgresql-installer.exe"
    pg_password: "MyDummyPassword123"
    pg_port: 5433
    pg_major_version: 17
    pg_data_dir: "C:\\PostgresData\\{{ pg_major_version }}\\data"

  tasks:
    - name: Gather installed PostgreSQL info (64-bit)
      ansible.windows.win_shell: |
        Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall" |
        Get-ItemProperty |
        Where-Object { $_.DisplayName -like "PostgreSQL*" } |
        Select-Object DisplayName, DisplayVersion, PSChildName
      register: pg_registry_info_64

    - name: Filter PostgreSQL info
      ansible.builtin.set_fact:
        pg_installed_info: "{{ (pg_registry_info_64.stdout_lines) | unique }}"

    - name: Display PostgreSQL installed info
      ansible.builtin.debug:
        msg: "Installed PostgreSQL info: {{ pg_installed_info }}"

    - name: Copy PostgreSQL installer if not already present
      ansible.windows.win_copy:
        src: "{{ pg_installer_local_path }}"
        dest: "{{ pg_installer_remote_path }}"
      args:
        # Kept to match the original approach; see "Idempotent copy alternative" below.
        creates: "{{ pg_installer_remote_path }}"

    - name: Install PostgreSQL silently
      ansible.windows.win_command: >
        "{{ pg_installer_remote_path }}"
        --mode unattended
        --unattendedmodeui none
        --superpassword "{{ pg_password }}"
        --servicename postgresql
        --serverport "{{ pg_port }}"
        --datadir "{{ pg_data_dir }}"
      args:
        creates: "C:\\Program Files\\PostgreSQL\\{{ pg_major_version }}\\bin\\psql.exe"

    - name: Ensure PostgreSQL service is running
      ansible.windows.win_service:
        name: postgresql
        state: started
        start_mode: auto

    - name: Remove PostgreSQL installer if still present
      ansible.windows.win_file:
        path: "{{ pg_installer_remote_path }}"
        state: absent
```

> **Important:** The password and port are hard-coded here **for demo only**. For real environments, prefer **Ansible Vault**, a secrets manager, or a simple .env file.

---

## Step-by-Step Explanation (with code)

### 1) Variables

```yaml
vars:
  pg_installer_local_path: "/dummy/path/postgresql-installer.exe"
  pg_installer_remote_path: "C:\\Temp\\postgresql-installer.exe"
  pg_password: "MyDummyPassword123"
  pg_port: 5433
  pg_major_version: 17
  pg_data_dir: "C:\\PostgresData\\{{ pg_major_version }}\\data"
```

* `pg_installer_local_path`: Where the `.exe` resides on the **controller**.
    
* `pg_installer_remote_path`: Where Ansible will place it on the **Windows host**.
    
* `pg_password`: PostgreSQL superuser password.
    
* `pg_port`: PostgreSQL server port (5432 is default; using 5433 here as an example).
    
* `pg_data_dir`: Data directory PostgreSQL will initialize.
    

Replace the dummy paths/credentials with values for your environment.

---

### 2) Detect existing PostgreSQL (Windows Registry)

```yaml
- name: Gather installed PostgreSQL info (64-bit)
  ansible.windows.win_shell: |
    Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall" |
    Get-ItemProperty |
    Where-Object { $_.DisplayName -like "PostgreSQL*" } |
    Select-Object DisplayName, DisplayVersion, PSChildName
  register: pg_registry_info_64

- name: Filter PostgreSQL info
  ansible.builtin.set_fact:
    pg_installed_info: "{{ (pg_registry_info_64.stdout_lines) | unique }}"

- name: Display PostgreSQL installed info
  ansible.builtin.debug:
    msg: "Installed PostgreSQL info: {{ pg_installed_info }}"
```

This gives visibility into existing installs and helps avoid redundant work.

---

### 3) Copy the installer to the Windows host

```yaml
- name: Copy PostgreSQL installer if not already present
  ansible.windows.win_copy:
    src: "{{ pg_installer_local_path }}"
    dest: "{{ pg_installer_remote_path }}"
  args:
    creates: "{{ pg_installer_remote_path }}"
```

This transfers the `.exe` from the controller to the Windows machine—ideal for **offline** Windows hosts.

**Idempotent copy alternative (recommended):**

```yaml
- name: Check if installer already exists
  ansible.windows.win_stat:
    path: "{{ pg_installer_remote_path }}"
  register: installer_stat

- name: Copy PostgreSQL installer
  ansible.windows.win_copy:
    src: "{{ pg_installer_local_path }}"
    dest: "{{ pg_installer_remote_path }}"
  when: not installer_stat.stat.exists
```

---

### 4) Silent, unattended installation

```yaml
- name: Install PostgreSQL silently
  ansible.windows.win_command: >
    "{{ pg_installer_remote_path }}"
    --mode unattended
    --unattendedmodeui none
    --superpassword "{{ pg_password }}"
    --servicename postgresql
    --serverport "{{ pg_port }}"
    --datadir "{{ pg_data_dir }}"
  args:
    creates: "C:\\Program Files\\PostgreSQL\\{{ pg_major_version }}\\bin\\psql.exe"
```

* Runs the Enterprise PostgreSQL installer with no UI.
    
* `creates` makes the task idempotent by checking for `psql.exe` in the expected location.
    

---

### 5) Ensure the Windows service is up and enabled

```yaml
- name: Ensure PostgreSQL service is running
  ansible.windows.win_service:
    name: postgresql
    state: started
    start_mode: auto
```

Ensures PostgreSQL starts now and on reboot.

---

### 6) Tidy up: remove the installer

```yaml
- name: Remove PostgreSQL installer if still present
  ansible.windows.win_file:
    path: "{{ pg_installer_remote_path }}"
    state: absent
```

Keeps the host clean once installation is complete.

---

## Post-Install Verification (optional)

Add one or both of these checks to confirm the install succeeded.

**Check** `psql.exe` is present:

```yaml
- name: Verify psql exists
  ansible.windows.win_stat:
    path: "C:\\Program Files\\PostgreSQL\\{{ pg_major_version }}\\bin\\psql.exe"
  register: psql_stat

- name: Report psql status
  ansible.builtin.debug:
    msg: "psql.exe found: {{ psql_stat.stat.exists }}"
```

**Check port is listening:**

```yaml
- name: Test PostgreSQL port
  ansible.windows.win_shell: |
    Test-NetConnection -ComputerName localhost -Port {{ pg_port }} | Select-Object -ExpandProperty TcpTestSucceeded
  register: port_test

- name: Report port status
  ansible.builtin.debug:
    msg: "Port {{ pg_port }} listening: {{ port_test.stdout.strip() }}"
```

---

## Online Alternative with `win_package` (skip copy step)

If the Windows host has internet access, install straight from a URL:

```yaml
- name: Install PostgreSQL from URL (online hosts)
  ansible.windows.win_package:
    path: "https://example.com/path/to/postgresql-installer.exe"   # replace with the official URL
    arguments: >
      --mode unattended
      --unattendedmodeui none
      --superpassword "{{ pg_password }}"
      --servicename postgresql
      --serverport "{{ pg_port }}"
      --datadir "{{ pg_data_dir }}"
    state: present
    # Use creates_path for idempotency if supported by your Ansible version:
    creates_path: "C:\\Program Files\\PostgreSQL\\{{ pg_major_version }}\\bin\\psql.exe"
```

---

## Troubleshooting

* **WinRM connection errors**  
    Verify inventory variables (`ansible_host`, `ansible_connection=winrm`, `ansible_port`, `ansible_winrm_transport`, `ansible_user`, `ansible_password`) and that WinRM listeners/firewall rules are configured.
    
* **Installer path issues**  
    Confirm both `pg_installer_local_path` (controller) and `pg_installer_remote_path` (Windows host) are correct and accessible.
    
* **UAC/permissions**  
    Run with a user that has rights to install software and write to `C:\Program Files\` (or choose a different target path).
    
* **Port already in use**  
    If `5432/5433` is in use, change `pg_port` and re-run.
    
* **Service name conflicts**  
    If another PostgreSQL service exists, either uninstall it or use a unique `--servicename`.
    

---

## Conclusion

This playbook automates a silent PostgreSQL installation on Windows, making it ideal for **offline** hosts where you stage the installer on the controller and copy it over. For **online** hosts, switch to `win_package` and install directly from a URL. The approach is repeatable, idempotent, and production-friendly with a few hardening steps (Vault, service tuning, post-install configuration).