Clean Up ArcGIS Monitor Postgres Database

[SteveMcCarthy]

My PostgreSQL directory has grown to 810 GB and I would like to reduce the size of this.  I have adjusted my reporting frequency to help this in the future.

Is there a way to reduce the size of this database and keep my historical data?

[SteveMcCarthy]

Can the vacuumdb be used to reduce the size of the ArcGIS Monitor Postgres Database?

[AndrewSakowicz]

In short, no. You need to drop partitioned data as per below.

[ReeseFacendini]

The best way to reduce the size of the database is to configure the database retention policy to delete data more frequently than 180 days (60 to 90 days perhaps). 

[SteveMcCarthy]

So this will remove the data older then the retention policy?  How long does that take?

I changed it from 180 to 60 days,

[MarceloMarques]

Configure database settings—ArcGIS Monitor | Documentation

in the doc link above see "Configure data retention policies".

You still need to perform regular maintenance for the PostgreSQL database, see example below, I execute this every Sunday.

-----------------------------------

set hh=%time:~0,2%
set stamp=%date:~4,2%-%date:~7,2%-%date:~12,2%_%hh%%time:~3,2%%time:~6,2%
set stamp=%stamp: =%

echo Begin: %stamp%

SET PGDIR="C:\Program Files\PostgreSQL\14\bin"
SET PGPASSWORD=*****

%PGDIR%\psql --version

echo Vaccum: %date% %time%
%PGDIR%\psql -p 5434 -U postgres -w -d agmon1 -c "vacuum"

echo Reindex: %date% %time%
%PGDIR%\psql -p 5434 -U postgres -w -d agmon1 -c "reindex database topo1"

echo Analyze: %date% %time%
%PGDIR%\psql -p 5434 -U postgres -w -d agmon1 -c "analyze"

-----------------------------------

[AndrewSakowicz]

To clarify, ArcGIS Monitor admins don’t need to run manually the above maintenance. The autovacume and autoanalyze are sufficient. 

You can check with the following sql:
select relname,last_autovacuum, last_autoanalyze from pg_stat_user_tables
where last_autoanalyze is not null

In some cases, these default jobs might be interrupted (e.g. locks, see below) or not running.  In that case, a manual analyze (as above or using PGAdmin UI) might be required. Keep in mind, it will place a lock and interfere with data collections while it’s executing.

For more information , see documentation.

https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM

For many installations, it is sufficient to let vacuuming be performed by the autovacuum daemon, which is described in Section 25.1.6.

Autovacuum workers generally don't block other commands. If a process attempts to acquire a lock that conflicts with the SHARE UPDATE EXCLUSIVE lock held by autovacuum, lock acquisition will interrupt the autovacuum.
Warning
Regularly running commands that acquire locks conflicting with a SHARE UPDATE EXCLUSIVE lock (e.g., ANALYZE) can effectively prevent autovacuums from ever completing.

[ReeseFacendini]

The database maintenance schedule is when it will run and clear out the items. You can click the "run now" button (below the retention policy table) to fire off that process now.

[SteveMcCarthy]

I don't see the run button.  I'm running version 2023.2.

[AndrewSakowicz]

2023.2 does not have run now option, but the job will be executed daily