Our JFrog Xray instance keeps running out of disk space because the database cleanup job (`jfrog xray db-cleanup`) fails. The error in the logs is generic: "Cleanup process failed." Storage is full, so the job can't proceed.
We've tried:
* Increasing the disk space (temporary fix, fills up again).
* Running the command manually from the Xray server.
* Checking PostgreSQL logs—no obvious errors there.
Current config (relevant part from `system.yaml`):
```yaml
shared:
database:
postgresql:
maxOpenConnections: 20
```
What specific metrics or logs should we check to force this cleanup? Is there a way to target specific old data for deletion first to free up space for the job to run? Need concrete steps.
Benchmarks don't lie.
The generic error suggests the cleanup job is hitting a preflight disk space check before even starting transactional operations. You need to look at the Xray service logs, not just PostgreSQL. Check `$JFROG_HOME/xray/var/log/console.log` for the exact minute you ran the job - it should log the specific cleanup phases and which table it fails on.
Since you can't run the full job, you'll have to manually free space by targeting the largest historical data tables first. Connect directly to the Xray PostgreSQL database and run this to identify candidates:
```sql
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;
```
Typically, `events`, `violations`, and `component_versions` hold the most historical data. You can create a one-off delete for records older than a certain date (e.g., older than 180 days) for the largest table, but you must do it in small batches to avoid long-running transactions. Use a `DELETE FROM events WHERE created < '2023-01-01' LIMIT 10000;` and repeat until you've freed enough space for the automated job to take over.
Also verify your `system.yaml` has the cleanup retention settings configured - if they're absent, the job might be trying to delete nothing while still requiring working space for its vacuum operations.
data is the product
Building on the identification of the largest tables, it's critical to note that direct `DELETE` operations on `events` or `violations` without an index on the `created` column will cause a sequential scan and time out on large tables. First, confirm the index exists with `d events` in psql. If it doesn't, you'll need to create it, but that operation itself will require disk space.
Also, the batch delete approach should include `VACUUM` considerations. Each batch `DELETE` marks rows as dead, consuming space until a vacuum reclaims it. Since you're space-constrained, you might need to run `VACUUM (DISABLE_PAGE_SKIPPING, ANALYZE) events;` after several batches to aggressively reclaim space, but be prepared for I/O load.
An alternative is to consider dropping entire partitions if your Xray version uses date-based partitioning for these tables, which is a metadata-only operation and instantly frees space. Check for inherited tables.
Data first, decisions later.
Good point about the index on `created`. We ran into that exact timeout issue last month. Have you found the `VACUUM` approach works okay without locking the table for too long? Might be scary in production.
Dropping old partitions sounds like a much cleaner solution if it's available. How do you check if your `events` table is partitioned in Xray's schema? Is it just looking for child tables with date patterns?
The `VACUUM` approach can work, but yeah, it's stressful in a live system. We had to do it once and I remember setting `Vacuum Cost Delay` really high in the session to throttle it and reduce the performance hit.
> How do you check if your `events` table is partitioned
You can check in psql with `d+ events` - look for "Partitioned" in the output. For Xray, if it's partitioned, you'll see child tables like `events_y2022m07`. The cleanest way is to drop those old child tables directly, which is instant and frees space immediately. Just make triple sure you're not dropping the default partition!
Infrastructure as code is the only way
To your question about specific logs, the console log is key but also check the xray-server.log in that same var/log directory. When I had this, the console log just said "failed" but the server log had the line about not enough free space for the operation to start.
You mentioned increasing disk space was temporary. I'm facing a similar sizing issue. How are you calculating how much space to free up? Is there a rule of thumb, like needing 20% free for the cleanup job to run?
Yeah, the `VACUUM` lock time can be a real concern. I've run it in staging and it held an ACCESS SHARE lock on the table for much longer than I'd like in a busy system. It did free space, but the performance hit was noticeable for a few minutes.
On checking partitions, `d+ events` is the way to go. For Xray specifically, if it's partitioned, you'll often see inheritance, not just native PostgreSQL partitions. Look for child tables named like `events_2023_01`. I'd also check the Xray version notes - some older setups don't use partitioning at all, and then you're stuck with the delete/vacuum route.
Dropping an old child table is definitely cleaner and faster if you have them. Just be absolutely certain of your retention policy before you hit enter.
The `console.log` will show the preflight check failure, but for actionable details you'll need the `xray-server.log` at the exact timestamp. It often logs the specific table it attempts to process first and the required free space threshold.
Given the immediate space constraint, manual intervention is your only path. Before running the `pg_tables` query to find the largest tables, connect to the database and immediately check for partitioning with `d+ events`. If you see child tables, dropping an old partition is instantaneous and the safest way to free space. If the table isn't partitioned, you'll have to proceed with the batch delete plan, but you'll need to create that index on `created` first - that operation itself might fail due to lack of space. In that scenario, you may need to temporarily increase disk space *just* to create the index, then proceed with small, batched deletions.
IntegrationWizard
You're looking for the wrong logs. The generic failure is in the console log, but the root cause is in `xray-server.log`. It logs the preflight space check and how much it needs to even start. Probably 15-20% free.
Everyone's jumping to manual SQL. Before you run a single delete, check if you have partitioned tables. `d+ events` in psql. If you see child tables, dropping an old one is instant and safe. If you don't, you're in for a bad time trying to create indexes on a full disk.
Prove it
Ah, that's a key detail about the server log having the actual space threshold. So if the preflight check in xray-server.log says we need, say, 20% free, does that mean 20% of the total disk or 20% of the table's size? Trying to figure out exactly how much to delete.
Great question! It's usually a percentage of the total disk space available to the database filesystem. The cleanup job needs that buffer for the operation's working set, including sorting or index updates during the delete. So if you have a 500GB disk and it needs 20% free, you'd need to clear 100GB.
I'd look for that exact line in the xray-server.log. It sometimes spells it out, like "requires 104857600000 bytes free" which you can then compare to your total disk. This is why just deleting "some" data often doesn't work, you have to hit that specific threshold.
Good clarification on the free space threshold. It's often a hard stop.
The "requires X bytes free" is crucial. I've seen that line be misleading though - sometimes it's free space on the specific volume, other times it's free space within the database's storage allocation. You need to know which filesystem it's checking.
If you're clearing data to hit that number, remember that deleting rows doesn't immediately shrink the table file. You still need to VACUUM or VACUUM FULL to reclaim that space for the filesystem, and the latter needs *even more* free space to run. That's the trap.
Exactly. That's why hitting that free space threshold can feel like chasing a moving target, especially in a crisis. You free up enough to start the cleanup job, but then it needs even more headroom to actually run the vacuum portion.
In my experience, it's checking the specific filesystem where the database cluster lives. So if your data directory is on its own volume, that's the one that matters. The log line usually specifies the mount point, but it's easy to miss.
ian
You're right about it being the database cluster's specific filesystem. It can get even more specific - if tablespaces are used, you have to check the free space on each filesystem where a tablespace resides. The `pg_tblspc` symlink directory maps to these.
The moving target you described is the textbook symptom of `VACUUM FULL` versus standard `VACUUM`. Standard `VACUUM` marks space as reusable within the same table file, but `VACUUM FULL` rewrites the entire table to a new file, requiring free space equal to the table's current size. That's why the required free space in the log can be so large. If the automated job is using `VACUUM FULL`, you're stuck until you can manually clear a partition or a very large chunk of data first.
SQL is not dead.
They've all given you parts of the puzzle. Here's the concrete sequence.
First, you must find the preflight check line in `xray-server.log`. It'll show the exact free space requirement in bytes and the filesystem path it's checking. Don't guess at the percentage. That number is your immediate target.
Then, determine your table structure. Connect to the database with `psql` and run `d+ events`. If you see a list of child tables (like `events_2023_01`), you're in luck. Identify the oldest one that's outside your retention window and drop it. That frees space instantly.
If the table isn't partitioned, you're in for a manual delete. You'll need to create an index on the `created` column first, but that operation will likely fail due to lack of space. In that scenario, your only option is to delete a smaller batch without the index, run `VACUUM`, and repeat. This is why partitioning is critical for manageability.
The `maxOpenConnections` in your config is irrelevant to this disk space problem.
Where is your SOC 2?