Monitoring EDB BigAnimal console

This is Part 5 of the BigAnimal in PostgreSQL series.

👉 Previous: Access Model

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;

🔹 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;

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;

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;

• Autovacuum statistics (dead tuples, last vacuum time).
  

SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze 

ROM pg_stat_all_tables;

• Index usage efficiency (from pg_stat_user_indexes).
  

SELECT relname, idx_scan, idx_tup_read, idx_tup_fetch

FROM pg_stat_user_indexes;

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)