pgbacked

PostgreSQL with batteries included ⚡

• pgBackRest — Backup & Restore
• plpython — Run anything from within postgres
• pg_cron — Schedule sql jobs
• locales-all — All locales precompiled

Table of Contents

• pgbacked
  • Table of Contents
  • Changelog
    • 2.0.1
    • 2.0.0
    • 1.1.0
    • 1.0.0
  • Default paths
    • PostgreSQL
    • pgBackRest
  • Usage
    • Configuring pgBackRest
    • Configuring PostgreSQL
    • Calling pgBackRest using plpython
    • Scheduling a backup using pg_cron
    • Restoring from backup

Changelog

2.0.1

• Fix bash quotation (#3)

2.0.0

• Run as user postgres (#2)

1.1.0

• Make sure we don't start up with an incompletely initialized database (#1)

1.0.0

Initial release

Default paths

PostgreSQL

• Configuration: /etc/postgresql/postgresql.conf
• Data: /var/lib/postgresql/data
• Logs: /var/log/postgresql (must be enabled first)

pgBackRest

• Configuration: /etc/pgbackrest/pgbackrest.conf
• Data: /var/lib/pgbackrest
• Logs: /var/log/pgbackrest

Usage

The pgBackRest stanza default will be created on database initialization.

Configuring pgBackRest

[default]
pg1-path=/var/lib/postgresql/data

[global]
repo1-path=/var/lib/pgbackrest

Configuring PostgreSQL

listen_addresses = '*'

archive_mode = on
archive_command = 'pgbackrest archive-push --stanza=default %p'

shared_preload_libraries = 'pg_cron'

Calling pgBackRest using plpython

CREATE SCHEMA IF NOT EXISTS pgbackrest;

CREATE OR REPLACE FUNCTION pgbackrest.backup() RETURNS void AS $$
    import subprocess

    subprocess.run(["pgbackrest", "backup", "--stanza=default"], check=True)
$$ LANGUAGE plpython3u;

Scheduling a backup using pg_cron

SELECT cron.schedule('0 0 * * *', 'SELECT pgbackrest.backup()');

Restoring from backup

Example using Docker Compose.

docker compose stop SERVICE_NAME
docker compose run SERVICE_NAME find /var/lib/postgresql/data -mindepth 1 -delete
docker compose run SERVICE_NAME pgbackrest restore --stanza=default
docker compose start SERVICE_NAME

Refer to the pgBackRest User Guide.