Logo ← PostgreSQL Blog

Automating PostgreSQL Streaming Replication Setup with Ansible

Setting up PostgreSQL streaming replication manually can be a tedious and error-prone task — involving installing PostgreSQL, configuring…

Automating PostgreSQL Streaming Replication Setup with Ansible

Setting up PostgreSQL streaming replication manually can be a tedious and error-prone task — involving installing PostgreSQL, configuring the primary and replica servers, setting up replication users, and ensuring proper data synchronization. Fortunately, with Ansible, this entire workflow can be automated reliably and repeatably. In this article, we’ll walk through an Ansible-based automation for configuring streaming replication between a primary and a replica PostgreSQL 15 server on Debian/Ubuntu-based systems.

Why Automate PostgreSQL Replication with Ansible?

  • Consistency: Automation guarantees that your configurations are applied identically every time, reducing human error.
  • Speed: Deploy replication faster than manual setups.
  • Maintainability: Easily update or recreate your setup by rerunning Ansible plays.
  • Scalability: Quickly extend to multiple replicas.

Our Infrastructure & Inventory Setup

We assume two servers:

  • Primary: 172.26.235.243
  • Replica: 172.26.227.20

The Ansible inventory inventory.ini groups them:

[primary]
172.26.235.243

[replica]
172.26.227.20

[all:vars]
ansible_user=ansible

All hosts use the ansible SSH user.

Step 1: Adding PostgreSQL Repository

To install PostgreSQL 15, we first add the official PostgreSQL Apt repository using add_pg_repo.yml:

- name: PostgreSQL APT repository setup
  hosts: all
  become: true
  tasks:
    - name: Add PostgreSQL GPG key
      apt_key:
        url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
        state: present

- name: Add PostgreSQL Apt repo
      apt_repository:
        repo: "deb http://apt.postgresql.org/pub/repos/apt {{ ansible_distribution_release }}-pgdg main"
        state: present
        filename: "pgdg"
    - name: Update Apt cache
      apt:
        update_cache: yes

This ensures the latest PostgreSQL packages are available for installation.

Step 2: Installing PostgreSQL on Both Servers

The playbook setup_postgres.yml installs PostgreSQL 15 and ensures the service is running:

- name: Install PostgreSQL
  hosts: all
  become: true
  vars:
    postgres_version: 15
  tasks:
    - name: Install PostgreSQL and client packages
      apt:
        name:
          - postgresql-{{ postgres_version }}
          - postgresql-client-{{ postgres_version }}
        update_cache: yes
        state: present

- name: Start and enable PostgreSQL service
      systemd:
        name: postgresql
        enabled: yes
        state: started

Step 3: Configuring the Primary Server

The primary_config.yml playbook configures replication-specific PostgreSQL settings on the primary:

  • Sets wal_level to replica to enable WAL archiving.
  • Allows up to 10 WAL sender processes.
  • Keeps WAL files on disk for the replica.
  • Listens on all network interfaces.
  • Grants replication permission to the replicator user from the replica IP.
  • Creates the replication user with appropriate privileges.
- name: Configure Primary PostgreSQL server
  hosts: primary
  become: true
  tasks:
    - name: Configure postgresql.conf parameters
      lineinfile:
        path: "/etc/postgresql/15/main/postgresql.conf"
        regexp: "{{ item.regexp }}"
        line: "{{ item.line }}"
      loop:
        - { regexp: '^#?wal_level', line: 'wal_level = replica' }
        - { regexp: '^#?max_wal_senders', line: 'max_wal_senders = 10' }
        - { regexp: '^#?wal_keep_size', line: 'wal_keep_size = 64' }
        - { regexp: '^#?listen_addresses', line: "listen_addresses = '*'" }

- name: Allow replication connections in pg_hba.conf
      lineinfile:
        path: "/etc/postgresql/15/main/pg_hba.conf"
        line: "host replication replicator 172.26.227.20/32 md5"
    - name: Create replication user 'replicator' if not exists
      shell: |
        sudo -u postgres psql -tc "SELECT 1 FROM pg_roles WHERE rolname = 'replicator'" | grep -q 1 || \
        sudo -u postgres psql -c "CREATE ROLE replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'rep-pass';"
    - name: Restart PostgreSQL to apply changes
      systemd:
        name: postgresql
        state: restarted

Step 4: Configuring the Replica Server

The replica_config.yml playbook prepares the replica by:

  • Stopping PostgreSQL.
  • Removing existing data directory.
  • Using pg_basebackup to clone the primary's data directory.
  • Starting PostgreSQL.
- name: Configure Replica PostgreSQL server
  hosts: replica
  become: true
  tasks:
    - name: Stop PostgreSQL service
      systemd:
        name: postgresql
        state: stopped

- name: Remove old data directory
      file:
        path: /var/lib/postgresql/15/main
        state: absent
    - name: Copy base backup from primary
      shell: |
        sudo -u postgres PGPASSWORD='rep-pass' pg_basebackup -h 172.26.235.243 -D /var/lib/postgresql/15/main -U replicator -Fp -Xs -P -R
    - name: Start PostgreSQL service
      systemd:
        name: postgresql
        state: started

The -R flag creates a recovery.conf (or standby.signal in newer versions), enabling replication mode on the replica.

Running the Automation

Run the repo setup:

ansible-playbook -i inventory.ini add_pg_repo.yml

Install PostgreSQL on all nodes:

ansible-playbook -i inventory.ini setup_postgres.yml

Configure the primary:

ansible-playbook -i inventory.ini primary_config.yml

Configure the replica:

ansible-playbook -i inventory.ini replica_config.yml

Conclusion

Using Ansible to automate PostgreSQL streaming replication reduces manual errors and saves valuable time. The playbooks here cover adding repositories, installing PostgreSQL, configuring primary and replica servers, and synchronizing data using pg_basebackup.

This approach can be extended for:

  • Multiple replicas
  • Failover automation (using tools like Patroni or repmgr)
  • Backup and monitoring

If you’re managing PostgreSQL clusters regularly, automating with Ansible is a solid best practice.