Page 1 of 1
Postgress DB is using all available space
Posted: Wed May 17, 2017 2:48 pm
by bosecorp
Our development server keeps running out space because Postgress is taking all available space.
is there a way to purge old data. we dont really care about historical data since this is our development server
Re: Postgress DB is using all available space
Posted: Wed May 17, 2017 4:10 pm
by tgriep
Below is the procedure for truncating the Postgres database tables. Run the following as root.
Code: Select all
service nagios stop
service ndo2db stop
service crond stop
service postgresql restart
pkill -9 -u nagios
echo "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;" | psql nagiosxi nagiosxi
service crond start
service ndo2db start
service nagios start
service npcd restart
Then the database should be vacuumed and there are 3 different ways to to that (Depending on the version of Postgress that is installed) the command below work on most systems. Run it as root.
Code: Select all
echo "vacuum;vacuum analyze;vacuum full;"|psql nagiosxi postgres
If it fails, let us know and I can post the other procedure for vacuuming.
If the system was upgraded and then the Postgress database started to grow, it could be that some of the tables did not get updated. If the XI install files are still on the system, you can run this to update the tables.
Code: Select all
service nagios stop
psql nagiosxi nagiosxi -f /tmp/nagiosxi/nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql
service postgresql restart
service nagios start
If all goes well, the database shouldn't grow to fill the hard drive anymore.
FYI, the Postgres database (nagiosxi) does not store historical data, mostly user info and temporary tables.
Re: Postgress DB is using all available space
Posted: Thu May 18, 2017 8:53 am
by bosecorp
Worked
do you know why postgres was taking all available space?
Re: Postgress DB is using all available space
Posted: Thu May 18, 2017 9:06 am
by tgriep
Glad it worked for you.
If the SQL tables did not get updated, the dbmaint script and the event handler scripts cannot remove the processed data and over time, is fills the hard drive up.
Re: Postgress DB is using all available space
Posted: Thu May 18, 2017 9:46 am
by bosecorp
Thank you
you can close this ticket