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)

---
- 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

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)

- 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

- 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):

- 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

- 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

- 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

- 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:

- 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:

- 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:

- 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).