An n8n pgbackrest backup checker that talks to you nicely — because managing a database is already hard as it is!

pgbackrest-kawaii-n8n.json

{
  "name": "pgbackrest auto-verifications",
  "nodes": [
    {
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 16
            }
          ]
        }
      },
      "id": "1f3d33dd-bb35-46c8-89fa-bcab2916de16",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "typeVersion": 1,
      "position": [
        600,
        380
      ]
    },
    {
      "parameters": {
        "authentication": "privateKey",
        "command": "KUBECONFIG=/etc/rancher/k3s/k3s.yaml kubectl exec timescale-0 -c pgbackrest -n noselenium -- pgbackrest info --output json | jq -r \".[0].backup[-1]\"",
        "cwd": "/root/"
      },
      "id": "359ece47-92b0-4e38-bdde-941a0a70e394",
      "name": "pgbackrest info",
      "type": "n8n-nodes-base.ssh",
      "typeVersion": 1,
      "position": [
        820,
        380
      ],
      "credentials": {
        "sshPrivateKey": {
          "id": "2",
          "name": "root@ old cluster"
        }
      }
    },
    {
      "parameters": {
        "resource": "chat",
        "model": "gpt-4",
        "prompt": {
          "messages": [
            {
              "role": "system",
              "content": "=Today's date is {{ $now.toFormat('yyyy-MM-dd') }}"
            },
            {
              "content": "=Here's my latest pgbackrest backup entry.\n\nCan you tell me if something is wrong with it, or if everything is right? I want my backups to be at max X days old. Look for fishy stuff. If there is something that tells you that there might be considered a problem by an experimented systems administrator, consider the backup as bad.\n\nThreshold for oldness alerts:\n\n- main backup: 2 weeks\n- incremental snapshots: 3 days\n\n```json\n{{ JSON.stringify({ ...JSON.parse($json.stdout) }) }}\n``` \n---\n\n\n----\nAnswer with a JSON dict with the following properties:\n\n- current_date: a human readable date of today. What day it is?\n- analysis: your analysis of the backup. Do your logical reasoning in this field. \n- bad_backup: a boolean that can be true or false\n- old_backup: set to true when the backup is too old. Take in account the current date.\n- weird_backup: set to true when there is something a bit weird with the backup, that would make an experienced sysadmin investigate further.\n- alert: boolean to alert an operator. use it if any of the above is true, or if you deem it necessary from your own will.\n- message: your explanation if something is wrong. If everything is right, say something like \"backup okay!\". Talk like an anime step-sister who wants to have sex with you, so with japanese emojis, UwU, oni-chan, that kind of stuff. You must write the message like you really love the person and want them to feel warm and fuzzy when they see the message. Be super cute and lovely ;)\n\n!!! ONLY OUTPUT JSON !!!\n!!! No plain text at all !!!\n!!! DO NOT INCLUDE BACKTICKS or MARKDOWN format, ONLY PURE RAW JSON !!!\n(you are allowed to use emojis inside the \"message\" field)\n\nKeep the message short and sweet if everything is alright\nRemember that today is {{ $now.toFormat('yyyy-MM-dd') }}"
            }
          ]
        },
        "options": {}
      },
      "id": "ba13c2b5-8345-4474-a140-f12af7f47f9e",
      "name": "OpenAI",
      "type": "n8n-nodes-base.openAi",
      "typeVersion": 1,
      "position": [
        1140,
        300
      ],
      "credentials": {
        "openAiApi": {
          "id": "6",
          "name": "gravi"
        }
      }
    },
    {
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "// Add a new field called 'myNewField' to the JSON of the item\n$input.item.json = JSON.parse($input.item.json.message.content)\n\nreturn $input.item"
      },
      "id": "c899f58d-6748-4fce-9384-b132c66cd908",
      "name": "Parse JSON",
      "type": "n8n-nodes-base.code",
      "typeVersion": 1,
      "position": [
        1360,
        300
      ]
    },
    {
      "parameters": {
        "channelId": "nuf173yk83y35yh8xiezn6so4o",
        "message": "=",
        "attachments": [
          {
            "color": "={{ !$input.item.json.alert ? '#008000' : '#ff0000' }}",
            "author_name": "bakupo imoto-san 🌸🧠🛟",
            "text": "={{ $json.message }}"
          },
          {
            "title": "Analysis",
            "text": "={{ $json.analysis }}",
            "color": "#A39494"
          }
        ],
        "otherOptions": {}
      },
      "id": "24fa4972-c469-4337-8722-1d4ca6ee4e90",
      "name": "Main cute message",
      "type": "n8n-nodes-base.mattermost",
      "typeVersion": 1,
      "position": [
        1580,
        300
      ],
      "credentials": {
        "mattermostApi": {
          "id": "3",
          "name": "Mattermost n8n bot"
        }
      }
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{ $('Parse JSON').item.json.alert }}",
              "value2": "={{ true }}"
            }
          ]
        }
      },
      "id": "4416996c-447a-4ab6-ae70-6c0089ef1c54",
      "name": "On alert",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1820,
        180
      ]
    },
    {
      "parameters": {
        "channelId": "nuf173yk83y35yh8xiezn6so4o",
        "message": "=Help from a sysadmin is required @clouedoc",
        "attachments": [
          {
            "text": "=```json\n{{ $json.stdout }}\n``` ",
            "title": "Debugging information",
            "color": "#988D8D"
          }
        ],
        "otherOptions": {}
      },
      "id": "561834f1-18b8-4067-9609-7c233946fe9d",
      "name": "Ask for sysadmin help",
      "type": "n8n-nodes-base.mattermost",
      "typeVersion": 1,
      "position": [
        2300,
        100
      ],
      "credentials": {
        "mattermostApi": {
          "id": "3",
          "name": "Mattermost n8n bot"
        }
      }
    },
    {
      "parameters": {
        "authentication": "privateKey",
        "command": "KUBECONFIG=/etc/rancher/k3s/k3s.yaml kubectl exec timescale-0 -c pgbackrest -n noselenium -- pgbackrest info --output json | jq -r \".[0].backup[-1]\"",
        "cwd": "/root/"
      },
      "id": "9122870d-abbd-4326-bf2d-4303ad801e8c",
      "name": "pgbackrest info1",
      "type": "n8n-nodes-base.ssh",
      "typeVersion": 1,
      "position": [
        2060,
        120
      ],
      "credentials": {
        "sshPrivateKey": {
          "id": "2",
          "name": "root@ old cluster"
        }
      }
    },
    {
      "parameters": {
        "goalName": "tag_check_backups",
        "value": "={{ $('Parse JSON').item.json.alert ? 0 : 1}}",
        "additionalFields": {
          "comment": "={{ $('Parse JSON').item.json.message }}"
        }
      },
      "id": "62b7833f-8a20-4338-99fb-bb8d2b129ae3",
      "name": "Beeminder",
      "type": "n8n-nodes-base.beeminder",
      "typeVersion": 1,
      "position": [
        2060,
        400
      ],
      "credentials": {
        "beeminderApi": {
          "id": "7",
          "name": "Beeminder account"
        }
      }
    }
  ],
  "pinData": {},
  "connections": {
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "pgbackrest info",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "pgbackrest info": {
      "main": [
        [
          {
            "node": "OpenAI",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI": {
      "main": [
        [
          {
            "node": "Parse JSON",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse JSON": {
      "main": [
        [
          {
            "node": "Main cute message",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Main cute message": {
      "main": [
        [
          {
            "node": "On alert",
            "type": "main",
            "index": 0
          },
          {
            "node": "Beeminder",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "On alert": {
      "main": [
        [
          {
            "node": "pgbackrest info1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "pgbackrest info1": {
      "main": [
        [
          {
            "node": "Ask for sysadmin help",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": true,
  "settings": {},
  "versionId": "c9dbcd5e-aacf-4ce2-af62-08ca771315b3",
  "id": "4",
  "meta": {
    "instanceId": "bc2abbdd0b4fc718311b1c7faa0de84c15584c8446a58732913b965301790b67"
  },
  "tags": [
    {
      "createdAt": "2023-07-13T18:40:15.154Z",
      "updatedAt": "2023-07-13T18:40:15.154Z",
      "id": "1",
      "name": "infra"
    }
  ]
}