Step-by-Step Guide to Install Enterprise PostgreSQL on Windows with Ansible

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