Postgres - large database

Support
,
1

Hi, I’ve got a VPS running ApisCP that’s currently low on diskspace. There’s a Postgres database/folder that’s nearly 30GB - /var/lib/pgsql/12/data/base/16385

I only use mySQL databases on the server, I’m assuming Postgres is needed for ApisCP, but 30GB? Is it possible to prune it down or see what’s using the space?

Cheers

2

Don’t remove these directories directly. PostgreSQL will crash.

To determine what’s contributing to storage, update the panel to latest code using upcp, then run:

cpcmd pgsql:get-table-sizes appldb

If this command does not exist, hop on edge with cpcmd scope:set cp.update-policy edge-major ; upcp, then run again.

3

Thanks for that. There’s an absolute ton of tables with names like;

****_unique_metric_check
****_chunk_metrics_ts_idx

I’ve attached the results of cpcmd pgsql:get-table-sizes appldb
data.txt (1.6 MB)

What’s creating all these and how do I clear them out? Google isn’t much help.

4

Total relation size is 25 GB. Looks primarily to be historical data from metrics.

What does this report?

cpcmd misc:cp-version

Next, log into the database from terminal:

psql appldb

Then run this:

SELECT MIN(ts) from metrics;

Followed by:

VACUUM FULL;

What is the storage usage within /var/lib/pgsql/12/data/base/16385?