From Scratch to Restore: Automating PostgreSQL Setup & Backups with Ansible

Hello, I'm Maneshwar. I'm building git-lrc, an AI code reviewer that runs on every commit. It is free, unlimited, and source-available on Github. Star Us to help devs discover the project. Do give it a try and share your feedback for improving the product.

---

Setting up PostgreSQL isn’t hard. Forgetting to set it up the same way across servers? That’s where it gets messy. We wanted to automate it all—install, configure, create roles, set cron, restore dumps, and even back them up with alerts—using a single Ansible playbook and a couple of shell scripts.

Here’s how we automated our entire PostgreSQL lifecycle, from install to restore.

Step 1: Install PostgreSQL and Required Extensions

We start with installing PostgreSQL 16, plpython3u, and pg_cron. We also drop in the official APT repo and GPG key so we’re not stuck with the system default version.

- name: Install required dependencies for PostgreSQL
  ansible.builtin.apt:
    name:
      - curl
      - ca-certificates
      - gnupg
      - lsb-release
    state: present
    update_cache: yes

We then fetch the PostgreSQL signing key and register the repo:

- name: Add PostgreSQL APT repository
  ansible.builtin.apt_repository:
    repo: "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt {{ ansible_lsb.codename }}-pgdg main"
    state: present

Post that, we install the actual packages we care about:

- name: Install PostgreSQL 16 and cron extension
  ansible.builtin.apt:
    name:
      - postgresql-16
      - postgresql-16-cron
      - postgresql-plpython3-16
      - python3-psycopg2  # needed for Ansible postgres modules
    state: present

Step 2: Configure PostgreSQL Access and Superusers

We change the default user’s password, create our custom flyweight superuser, and make sure PostgreSQL accepts external connections:

- name: Alter custom superuser role attributes
  shell: |
    PGPASSWORD="{{ pg_postgres_password }}" psql -U postgres -c "ALTER ROLE {{ pg_flyweight_user }} WITH SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS;"
  become: true
  become_user: postgres

We also modify postgresql.conf and pg_hba.conf to:

• Allow external connections (listen_addresses = '*')
• Enable pg_cron via shared_preload_libraries
• Allow md5 authentication for all IPs (0.0.0.0/0)

Step 3: pg_cron Config

If you’re using pg_cron, you need to point it to a database. We do that using:

- name: Ensure cron.database_name is set to 'liveapi'
  lineinfile:
    path: /etc/postgresql/16/main/postgresql.conf
    line: "cron.database_name = 'liveapi'"
    state: present
    insertafter: EOF

Step 4: Restore Databases from Backup (Ansible + Shell Combo)

We restore a .tar archive which includes:

• roles.sql
• One or more .dump files (one per DB)

Here’s what the restore shell script does:

1. Downloads and extracts the archive
2. Restores roles (excluding postgres)
3. For each DB:

• Creates it
• Calls pg_restore
• Restarts PostgreSQL with updated cron.database_name if needed

📦 restore.sh.j2 looks like this:

curl -sSL "$ARCHIVE_URL" -o "$ARCHIVE_NAME"
tar -xf "$ARCHIVE_NAME" -C "$TMP_DIR"

for dumpfile in "$TMP_DIR"/*.dump; do
  dbname=$(basename "$dumpfile" .dump)
  createdb -h "$PG_HOST" -U "$PG_USER" "$dbname"
  pg_restore -h "$PG_HOST" -U "$PG_USER" -d "$dbname" "$dumpfile"
done

This script is rendered and executed from Ansible:

- name: Run restore.sh as sudo
  ansible.builtin.shell: /home/ubuntu/restore/restore.sh
  become: true

Step 5: Backups to S3 + Discord Alerts

Our backup script does the opposite: dumps everything, compresses it, uploads to S3, and alerts us on Discord with the file size.

PGPASSWORD=$password pg_dumpall -U postgres > full_backup.sql
tar -zcvf "$f" ./dump/full_backup.sql
aws s3 cp "$f" s3://fw-pgbackup

If backup fails or the file size is too small (<20MB), we get a Discord ping.

Why This Setup Works

• 🧠 One command to setup PostgreSQL from scratch
• 🚀 Restore production-like data to dev in seconds
• 🔁 pg_cron ready out of the box
• 🧼 Role and permission management handled automatically
• ☁️ Cloud backup and alerting done without manual opsIf you're building platforms with Postgres and want reproducibility with zero surprises, this combo of Ansible + shell + S3 + Discord is hard to beat.

---

With you can generate interactive API docs that allow users to search and execute endpoints directly from the browser.

If you're tired of updating manually or syncing collections, give it a shot.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

---

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements

git-lrc-intro-60s.mp4

Why

• 🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
• 🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
• 🔁 Build a habit, ship better code. Regular review → fewer bugs → more robust code → better results in your team.
• 🔗 Why git? Git is universal. Every editor, every IDE, every AI…

View on GitHub