Ansible PostgreSQL Management

Ansible provides the community.postgresql collection with dedicated modules for every PostgreSQL administration task — from installation and database creation to backup, replication, and security hardening. This guide covers the complete PostgreSQL automation workflow with practical playbook examples.

Prerequisites

Install the PostgreSQL Collection

ansible-galaxy collection install community.postgresql

Python Dependencies

The PostgreSQL modules require the psycopg2 library on the managed host:

- name: Install psycopg2 for Ansible PostgreSQL modules
  ansible.builtin.pip:
    name: psycopg2-binary
    state: present

Or via system packages:

- name: Install psycopg2 (Debian/Ubuntu)
  ansible.builtin.apt:
    name: python3-psycopg2
    state: present

PostgreSQL Module Reference

Module	Purpose
`community.postgresql.postgresql_db`	Create, drop, rename, backup, restore databases
`community.postgresql.postgresql_user`	Create and manage users/roles
`community.postgresql.postgresql_privs`	Grant/revoke privileges
`community.postgresql.postgresql_query`	Execute SQL queries
`community.postgresql.postgresql_pg_hba`	Manage pg_hba.conf entries
`community.postgresql.postgresql_set`	Set PostgreSQL configuration parameters
`community.postgresql.postgresql_info`	Gather PostgreSQL server information
`community.postgresql.postgresql_slot`	Manage replication slots
`community.postgresql.postgresql_schema`	Manage schemas
`community.postgresql.postgresql_ext`	Manage extensions
`community.postgresql.postgresql_tablespace`	Manage tablespaces
`community.postgresql.postgresql_copy`	Copy data between files and tables
`community.postgresql.postgresql_membership`	Manage role membership
`community.postgresql.postgresql_owner`	Change database object ownership

Installing PostgreSQL

Debian/Ubuntu

- name: Install PostgreSQL on Ubuntu
  hosts: db_servers
  become: true
  tasks:
    - name: Install PostgreSQL packages
      ansible.builtin.apt:
        name:
          - postgresql
          - postgresql-contrib
          - python3-psycopg2
        state: present
        update_cache: true

    - name: Ensure PostgreSQL is running
      ansible.builtin.service:
        name: postgresql
        state: started
        enabled: true

RHEL/Fedora

- name: Install PostgreSQL on RHEL
  hosts: db_servers
  become: true
  tasks:
    - name: Install PostgreSQL packages
      ansible.builtin.dnf:
        name:
          - postgresql-server
          - postgresql-contrib
          - python3-psycopg2
        state: present

    - name: Initialize PostgreSQL database
      ansible.builtin.command: postgresql-setup --initdb
      args:
        creates: /var/lib/pgsql/data/PG_VERSION

    - name: Ensure PostgreSQL is running
      ansible.builtin.service:
        name: postgresql
        state: started
        enabled: true

Database Management

Create a Database

- name: Create application database
  community.postgresql.postgresql_db:
    name: myapp_production
    encoding: UTF-8
    lc_collate: en_US.UTF-8
    lc_ctype: en_US.UTF-8
    template: template0
    state: present
  become: true
  become_user: postgres

Drop a Database

- name: Drop test database
  community.postgresql.postgresql_db:
    name: myapp_test
    state: absent
  become: true
  become_user: postgres

Rename a Database

- name: Rename database
  community.postgresql.postgresql_db:
    name: old_name
    target: new_name
    state: rename
  become: true
  become_user: postgres

User/Role Management

Create a User with Password

- name: Create application database user
  community.postgresql.postgresql_user:
    name: app_user
    password: "{{ vault_db_password }}"
    role_attr_flags: LOGIN,NOSUPERUSER,NOCREATEDB
    state: present
  become: true
  become_user: postgres
  no_log: true

Grant Privileges

- name: Grant privileges on database
  community.postgresql.postgresql_privs:
    database: myapp_production
    type: database
    roles: app_user
    privs: CONNECT
    state: present
  become: true
  become_user: postgres

- name: Grant table privileges
  community.postgresql.postgresql_privs:
    database: myapp_production
    schema: public
    type: table
    objs: ALL_IN_SCHEMA
    roles: app_user
    privs: SELECT,INSERT,UPDATE,DELETE
    state: present
  become: true
  become_user: postgres

Configure pg_hba.conf

- name: Allow md5 authentication for app_user
  community.postgresql.postgresql_pg_hba:
    dest: /etc/postgresql/16/main/pg_hba.conf
    contype: host
    databases: myapp_production
    users: app_user
    source: 192.168.1.0/24
    method: md5
    state: present
  become: true
  notify: Reload PostgreSQL

Running SQL Queries

- name: Run a SELECT query
  community.postgresql.postgresql_query:
    db: myapp_production
    query: "SELECT count(*) FROM users WHERE active = true"
  become: true
  become_user: postgres
  register: user_count

- name: Show result
  ansible.builtin.debug:
    msg: "Active users: {{ user_count.query_result[0].count }}"

Parameterized Queries

- name: Insert data safely
  community.postgresql.postgresql_query:
    db: myapp_production
    query: "INSERT INTO config (key, value) VALUES (%s, %s)"
    positional_args:
      - app_version
      - "2.1.0"
  become: true
  become_user: postgres

Backup and Restore

Backup a Database

- name: Backup PostgreSQL database
  community.postgresql.postgresql_db:
    name: myapp_production
    state: dump
    target: /backups/myapp_{{ ansible_date_time.date }}.sql.gz
  become: true
  become_user: postgres

Restore a Database

- name: Restore database from backup
  community.postgresql.postgresql_db:
    name: myapp_production
    state: restore
    target: /backups/myapp_2026-04-01.sql.gz
  become: true
  become_user: postgres

Automated Backup with Cron

- name: Schedule daily PostgreSQL backup
  ansible.builtin.cron:
    name: "PostgreSQL daily backup"
    minute: "0"
    hour: "2"
    job: "pg_dump -U postgres myapp_production | gzip > /backups/myapp_$(date +\\%Y\\%m\\%d).sql.gz"
    user: postgres

Managing Extensions

- name: Install PostGIS extension
  community.postgresql.postgresql_ext:
    name: postgis
    db: myapp_production
    state: present
  become: true
  become_user: postgres

- name: Install uuid-ossp extension
  community.postgresql.postgresql_ext:
    name: uuid-ossp
    db: myapp_production
    state: present
  become: true
  become_user: postgres

Configuration Management

- name: Set PostgreSQL configuration parameters
  community.postgresql.postgresql_set:
    name: "{{ item.name }}"
    value: "{{ item.value }}"
  loop:
    - { name: max_connections, value: "200" }
    - { name: shared_buffers, value: "512MB" }
    - { name: effective_cache_size, value: "1536MB" }
    - { name: work_mem, value: "16MB" }
    - { name: log_min_duration_statement, value: "1000" }
  become: true
  become_user: postgres
  notify: Restart PostgreSQL

Complete Playbook Example

---
- name: Deploy PostgreSQL for Application
  hosts: db_servers
  become: true
  vars_files:
    - vault/db_secrets.yml
  handlers:
    - name: Reload PostgreSQL
      ansible.builtin.service:
        name: postgresql
        state: reloaded

    - name: Restart PostgreSQL
      ansible.builtin.service:
        name: postgresql
        state: restarted

  tasks:
    - name: Install PostgreSQL
      ansible.builtin.apt:
        name:
          - postgresql
          - postgresql-contrib
          - python3-psycopg2
        state: present

    - name: Start PostgreSQL
      ansible.builtin.service:
        name: postgresql
        state: started
        enabled: true

    - name: Create database
      community.postgresql.postgresql_db:
        name: "{{ db_name }}"
        encoding: UTF-8
        state: present
      become_user: postgres

    - name: Create app user
      community.postgresql.postgresql_user:
        name: "{{ db_user }}"
        password: "{{ db_password }}"
        state: present
      become_user: postgres
      no_log: true

    - name: Grant privileges
      community.postgresql.postgresql_privs:
        database: "{{ db_name }}"
        type: database
        roles: "{{ db_user }}"
        privs: ALL
        state: present
      become_user: postgres

    - name: Configure client auth
      community.postgresql.postgresql_pg_hba:
        dest: /etc/postgresql/16/main/pg_hba.conf
        contype: host
        databases: "{{ db_name }}"
        users: "{{ db_user }}"
        source: "{{ app_server_network }}"
        method: scram-sha-256
        state: present
      notify: Reload PostgreSQL

Detailed Article Guides

Installation

Usage

Run a SQL Command Query on PostgreSQL

Backup and Restore

Database Management

Access/User Management

Conclusion

Ansible's community.postgresql collection provides comprehensive automation for every PostgreSQL administration task. By combining these modules with Ansible Vault for credential security and roles for reusable patterns, you can achieve fully automated PostgreSQL lifecycle management — from initial installation through daily operations to disaster recovery.

Ansible PostgreSQL Management

Prerequisites

Install the PostgreSQL Collection

Python Dependencies

PostgreSQL Module Reference

Installing PostgreSQL

Debian/Ubuntu

RHEL/Fedora

Database Management

Create a Database

Drop a Database

Rename a Database

User/Role Management

Create a User with Password

Grant Privileges

Configure pg_hba.conf

Running SQL Queries

Parameterized Queries

Backup and Restore

Backup a Database

Restore a Database

Automated Backup with Cron

Managing Extensions

Configuration Management

Complete Playbook Example

Detailed Article Guides

Installation

Usage

Backup and Restore

Database Management

Access/User Management

Links

Related Articles

Conclusion