How to resolve FortiSOAR /var/pgsql is full

This article describes the method to check the file filled up /var/pgsql in FortiSOAR and a solution to resolve the issue.

Scope

FortiSOAR.

Solution

FortiSOAR /var/pgsql contains data from multiple databases and it can be filled up easily if the configuration is not designed properly. Users can check with the following PSQL command to determine the database that is causing the issues:

#psql -U cyberpgsql das
#SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;

Notes:

The password for the database is Device UUID. It can be obtained via the command #csadmin license –get-device-uuid.

After that, users can switch to the next database and execute the same SQL query:

#/c venom
#SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;

Generally, this issue is commonly caused by the workflow database in Sealab. Most of the space will be consumed by the public.workflow_workflow. In that case, users can do a SQL query below to check which playbook is consuming the most:

#psql -U cyberpgsql sealab
# select name,COUNT(id) from workflow_workflow GROUP BY name ORDER BY COUNT(id) DESC;

From the output above, users can further analyze the playbook or change the mode from DEBUG to INFO to reduce the logs. If the space is used by another process, contact Fortinet support for further recommendations.