db_backup

#!/bin/bash
## 2025-06-25 Fine tuning backup script to be more selective when backing up
for DB in $(su postgres -c "psql -At -c \"select datname from pg_database where datistemplate = false and datname <> 'postgres';\" postgres")
do
    echo "Kontrollerar $DB..."
    DO_BACKUP=$(sudo su postgres -c "psql $DB -c \"DO \\$\\$
DECLARE
    tabeller text[] := ARRAY['res_users', 'res_partner', 'sale_order', 'account_move', 'mrp_production', 'project_task', 'stock_picking', 'ir_session'];
    t text;
    sql text := '';
    first boolean := true;
    active boolean;
BEGIN
    FOR t IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public'
          AND table_name = ANY(tabeller)
    LOOP
        IF first THEN
            sql := sql || format('SELECT MAX(write_date) AS write_date FROM %I', t);
            first := false;
        ELSE
            sql := sql || format(' UNION ALL SELECT MAX(write_date) FROM %I', t);
        END IF;
    END LOOP;

    IF sql <> '' THEN
        sql := 'SELECT (MAX(write_date) > (CURRENT_DATE - INTERVAL ''5 days'')) AS active FROM (' || sql || ') AS all_dates;';
        EXECUTE sql INTO active;
        RAISE NOTICE '%', active;
    ELSE
        RAISE NOTICE 'Inga tabeller hittades.';
        -- Alternativt: RETURN FALSE;
    END IF;
END \\$\\$;
 \"" 2>&1|cut -c10) ;

    if [ "$DO_BACKUP" == 't' ]; then
        echo "Ändringar hittades ($DO_BACKUP). Tar backup..."
        su postgres -c "pg_dump -Fp $DB" | gzip > /var/backups/"$DB".sql.gz
    else
        echo "Ingen relevant ändring hittades i $DB. Ingen backup tas."
    fi
done