Postgress DB is using all available space

[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

[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.

[bosecorp]

Worked

do you know why postgres was taking all available space?

[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.

[bosecorp]

Thank you

you can close this ticket