DEV Community

Aravin
Aravin

Posted on

Monitoring EDB BigAnimal console

#database #monitoring #postgres

This is Part 5 of the BigAnimal in PostgreSQL series.

👉 Previous: Access Model
👉 Next: Backup Concepts

Scheduled Jobs Monitoring

pgAgent stores all job definitions and logs in pgagent catalog tables, located in the pgagent schema (usually in the postgres or maintenance database).

Main tables:

  1. pgagent.pga_job → Job definitions
  2. pgagent.pga_jobstep → Steps of each job
  3. pgagent.pga_joblog → Job execution history
  4. pgagent.pga_jobsteplog → Each step’s execution log

1️⃣Check if pgAgent service is running (system-level)

sudo systemctl status pgagent

or if it’s running as a background process (for EDB pgAgent):

ps aux | grep pgagent If you see the pgAgent process, it’s running.

2️⃣ Check current running jobs (SQL query)
Run this in the database where pgAgent is installed:

SELECT j.jobid, j.jobname, j.enabled, l.jlgid AS joblogid, l.jlgstatus AS status, l.jlgstart AS start_time
FROM pgagent.pga_job j
LEFT JOIN pgagent.pga_joblog l ON j.jobid = l.jlgjobid
WHERE l.jlgstatus = 'r'
ORDER BY l.jlgstart DESC;
Enter fullscreen mode Exit fullscreen mode

🔹 jlgstatus = 'r' → job is running
🔹 s = success, f = failed, i = idle
If you get no rows → no job is running currently.

3️⃣ Check recent job run status

SELECT j.jobname, l.jlgstatus, l.jlgstart, l.jlgduration
FROM pgagent.pga_job j
JOIN pgagent.pga_joblog l ON j.jobid = l.jlgjobid
ORDER BY l.jlgstart DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

This shows the 10 most recent job runs with their statuses.

4️⃣ Check step-level details (optional)
If you want to see which step is currently running or failed:

SELECT j.jobname, s.jstname AS step_name, sl.jslstatus AS step_status, sl.jslstart AS step_start
FROM pgagent.pga_job j
JOIN pgagent.pga_jobstep s ON j.jobid = s.jstjobid
JOIN pgagent.pga_jobsteplog sl ON s.jstid = sl.jsljstid
ORDER BY sl.jslstart DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

5️⃣ pgAdmin (GUI method)

  • Go to pgAdmin → Jobs → [select job] → Statistics tab → you can see last run, next run, and last result.
  • You can also open pgAgent → Job Logs to view running or completed jobs.

Quick Troubleshooting Tips

  1. Job not starting - Verify pgAgent service is running (systemctl status pgagent on the instance, if visible)
  2. Job not listed - Ensure you’re connected to the same database where pgAgent was configured
  3. Job failed - Inspect pgagent.pga_joblog.jlgoutput for error details
  4. Job never runs - Check jobenabled = true and the schedule in pgagent.pga_schedule

Monitoring Dashboard BigAnimal Console

  1. Open the BigAnimal Console: https://portal.biganimal.com
  2. From the left sidebar, click Clusters → select your cluster.
  3. Click the Monitoring & Logging tab. You’ll now see:
  4. Overview (Summary) — CPU, memory, storage usage
  5. Metrics — time-series graphs (like Grafana)
  6. Logs — PostgreSQL logs & events
  7. Queries — slow or long-running query insights

Query Monitoring (Performance tab)

BigAnimal automatically collects query performance data using pg_stat_statements and auto_explain.

We can view below in dashboard:

  1. Top Queries - Queries ranked by total execution time.
  2. Slow Queries - Queries exceeding configured thresholds.
  3. Query Plans - Execution plans of slowest queries.
  4. Query Frequency - Number of times a query pattern ran.

Save and reload configuration (no restart needed for these).

We can enable additional query logging from the console:

  • Go to Configuration → Parameters
  • Set log_min_duration_statement = 1000 (Logs queries slower than 1s)
  • log_statement_stats = on
  • log_parser_stats = on
  • log_executor_stats = on

Storage & Bloat Monitoring

In the Monitoring → Storage tab:

  • We will see table/index size growth. 
SELECT relname AS table_name, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(pg_table_size(relid)) AS table_size, pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables;
Enter fullscreen mode Exit fullscreen mode
  • Autovacuum statistics (dead tuples, last vacuum time). 
SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze 
ROM pg_stat_all_tables;
Enter fullscreen mode Exit fullscreen mode
  • Index usage efficiency (from pg_stat_user_indexes). 
SELECT relname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes;
Enter fullscreen mode Exit fullscreen mode

If idx_scan is 0 or very low → consider dropping the index.

If you enable pgstattuple and pg_repack extensions, the console can show bloat percentage for each table/index.

Optional Add-ons
If you enable these extensions in BigAnimal:

  • pgstattuple → shows table and index bloat %
  • pg_repack → allows reclaiming bloated space without downtime

Logs Dashboard

Under Monitoring & Logging → Logs,
We can:

  • Filter by severity (ERROR, WARNING, LOG)
  • Search for specific text (e.g., “autovacuum”, “statement timeout”)
  • View PostgreSQL logs per node
  • Export logs in .txt or .json format for external analysis

Log Sources Behind the Scenes
The dashboard aggregates data from:

  • PostgreSQL log files (pg_log)
  • System logs (e.g., WAL archiver, autovacuum, checkpoint activity)

Top comments (0)