pg_timetable/samples/yaml/Backup.yaml at master · cybertec-postgresql/pg_timetable · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
# Backup job example with program tasks

chains:
  - name: "Database Backup Job"
    schedule: "0 3 * * 0"  # Sunday at 3 AM
    live: true
    max_instances: 1
    timeout: 7200000  # 2 hour timeout
    client_name: "backup-worker"

    tasks:
      - name: "Create backup directory"
        kind: "PROGRAM"
        command: "mkdir"
        parameters:
          - "-p"
          - "/backups/$(date +%Y-%m-%d)"
        ignore_error: true

      - name: "Database dump"
        kind: "PROGRAM"
        command: "pg_dump"
        parameters:
          - "-h"
          - "localhost"
          - "-U"
          - "postgres"
          - "-d"
          - "mydb"
          - "-f"
          - "/backups/$(date +%Y-%m-%d)/mydb_backup.sql"
          - "--verbose"
        timeout: 5400000  # 90 minutes for dump

      - name: "Compress backup"
        kind: "PROGRAM"
        command: "gzip"
        parameters:
          - "/backups/$(date +%Y-%m-%d)/mydb_backup.sql"
        timeout: 600000  # 10 minutes for compression

      - name: "Cleanup old backups"
        kind: "PROGRAM"
        command: "find"
        parameters:
          - "/backups"
          - "-type"
          - "f"
          - "-name"
          - "*.sql.gz"
          - "-mtime"
          - "+7"  # older than 7 days
          - "-delete"
        ignore_error: true

      - name: "Log backup completion"
        kind: "SQL"
        command: |
          INSERT INTO backup_log (backup_date, backup_type, status, file_path)
          VALUES (
            CURRENT_DATE,
            'full_database',
            'completed',
            '/backups/' || TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') || '/mydb_backup.sql.gz'
          )