Install Postgres and schedule backups with Ansible

ansible bash homelab postgresql

In this article, we will install and configure Postgres and schedule database backups in Ubuntu using Ansible.

Prerequisites

  • Ansible
  • An Ubuntu (or other Debian-based distro) host

A variable postgres_password is required in the playbook. It is recommended to store it as a secret.

Also, some basic packages are expected to be installed in the host, you can also install them using a task as

- name: install apt packages
  apt:
    name:
      - python3-pip
      - acl
    update_cache: yes

Run the tasks as root. Either in the host definintion:

postgres ansible_user=me ansible_become=yes

or at play level:

hosts: postgres
become: yes

Installation and basic configuration

Install the required APT packages and the Python adapter (required by the Postgres collection in Ansible):

- name: install postgres
  apt:
    name:      
      - postgresql
      - postgresql-contrib
      - libpq-dev
    update_cache: yes

- name: enable postgres
  service:
    name: postgresql
    enabled: yes

- name: install postgres python library
  pip:
    name: psycopg2

Set the password for the default user postgres (recommended):

- name: set default user password
  postgresql_user:
    name: postgres
    password: "{{ postgres_password }}"
  become_user: postgres

Allow remote access

If we want to access the PostgreSQL server from a different host, we need to edit the Postgres settings and the host-based authentication (and allow the port used by the server).

First, we’ll need the Postgres settings to edit the required files:

- name: get postgres info
  postgresql_info:
    filter: settings
  become_user: postgres
  register: postgres_info

Listen all addresses to allow remove access:

- name: allow remote access
  lineinfile:
    dest: "{{ postgres_info.settings.config_file.setting }}"
    regexp: ^#?listen_addresses = 'localhost'(.*)
    line: listen_addresses = '*' \1
    backrefs: yes
    state: present
  notify:
    - restart postgres

Allow all users to connect remotelly:

- name: allow all users to connect
  postgresql_pg_hba:
    dest: "{{ postgres_info.settings.hba_file.setting }}"
    contype: host
    address: 0.0.0.0/0
  notify:
    - restart postgres

And allow the port 5432 in the firewall:

- name: allow port 5432
  ufw:
    rule: allow
    port: '5432'
    proto: tcp

When changing the configuration files we’ll trigger the handler restart postgres to restart the Postgres service:

handlers:
- name: restart postgres
  service:
    name: postgresql
    state: restarted

Schedule backups

We can create a simple script to backup all or some databases. Here’s an example of a script to backup all the databases and upload the files to a host named backups:

#!/bin/bash

set -e
set -o pipefail

dir=`mktemp -d`
echo Saving tmp postgres backups to $dir...
suffix=$(date +%Y%m%d).sql
echo Saving postgres globals...
sudo -i -u postgres pg_dumpall -g > $dir/globals.$suffix
query="SELECT datname FROM pg_database WHERE NOT datistemplate AND datname != 'postgres'"
for db in `sudo -i -u postgres psql -t -c "$query" | grep '\S'`; do
    echo Saving postgres database $db...
    path=$dir/$db.$suffix
    sudo -i -u postgres pg_dump $db > $path
done
for filename in `ls $dir`; do
    echo Uploading postgres database $db...
    scp $dir/$filename backups:/postgres/
done
rm -rf $dir

Note that we are running the psql commands as user postgres.

We can then copy that script and schedule its execution, say everyday at 00:00:

- name: copy backup script
  copy:
    src: files/postgres/backup.sh
    dest: "{{ lookup('env', 'HOME') }}/backup_postgres.sh"
    owner: "{{ ansible_user }}"

- name: schedule backup
  cron:
    name: "backup postgres databases"
    minute: "0"
    hour: "0"    
    job: "bash {{ lookup('env', 'HOME') }}/backup_postgres.sh 2>&1 | logger -t {{ ansible_user }}-cron"
  become_user: "{{ ansible_user }}"

The file and the cron job can be executed by any other sudoer, in this example, the ansible user configured in the hosts file.