It seems like an auto-vacuum job got stuck which may have been why the data grew uncontrolled until we caught it. Our Postgres DB is external to the PuppetDB application. Checking on the version now. I think the repack is needed now to hopefully reduce that DB size (I'm not a database expert so I'm trying to wrap my head around the situation)

Postgresql version 17.5

Probably not auto-vacuum, but a transaction that gets left open will block auto-vacuum and result in that sort of growth curve.

And yes, if auto-vaccuum doesn't run, the pile of page files on disk grows because space used by "dead" rows can't be recycled to hold new data. Only way to knock that down is repack.

And, I don't think Postgres vendored

pg_repack

. The upcoming Postgres 19 release has a native implementation of the concept as the

REPACK

statement.

Also, the 8.x releases of OpenVoxDB/PuppetDB have timeouts that help ensure transactions don't get left open for long periods of time.

If the db still starts, you cms use the command endpoint to delete old data. You can also drop the partitioned tables for the reports

Manually deleting dead rows is a bit complicated. Best to let auto-vacuum take care of that. Dropping older report partitions can be a way to quickly free space if the disk is out of room and expanding it is not an option. Repack or

VACUUM_FULL

will require enough free space to build a copy of the live data set before dropping the old bloated tables.

Yes

You can start repack on individual tables. Then it requires less disk space

Just for fun, go through https://wiki.postgresql.org/wiki/Disk_Usage to find your biggest tables/partitions. It should be partitions for reports, but you never know. Maybe you have some big binary Blobs in your facts or catalogs

If you've set up the Postgres part of operational dashboards, check that.

Although, it might not work with more recent versions of postgres.

https://voxpupuli.slack.com/archives/C07RT1PCZ5H/p1792085780622519 We are running 8.x

Did you manage to clean stuff up?

If you are running 8.x, that makes it less likely to be a hung transaction. Could be a case where the DB service should have canceled something that it didn't. Or, could be a SQL query external to the DB service that got left open. Or, it could just be more data coming in. For example, reports that were previously a handful of kB or MB now coming in much larger due to a cascading failure that repeats run after run.

Alllll the flapping resources

@smortex I didn't think there WAS a released version that includes the json fixes, as stated earlier? They had to backport those fixes into a post-release version, can someone else confirm?

The

openvox-agent

8.31.0 and 9.1.0 releases that went out yesterday have compatibility fixes for

json

3.0.

nods

Yes, github.com/OpenVoxProject/openvox/pull/721 says "This PR hasn't been released yet, judging by its merge commit." but the merge commit itself github.com/OpenVoxProject/openvox/commit/af67c41f2210… shows it is included in 8.31.0. GitHub became a pile of trash 😞.

...this is about openvox-server?

for us, our agent is unaffected unless something else coincidentally pulls in the json gem

The server depends on the agent. Some code from the agent is executed in jruby by the server. So they are both connected. I am running my uncommitted work on my systems atm (to check if it works as expected), let me try the packages currently shipped by FreeBSD to see how broken they currently are.

In our case, specifically, the agent was not the problem, it was only on the server.

but only by happenstance.

if we had installed the actual json gem on the client, it would broken things that way too

the only fix we needed was rolling back the json gem on the server to a prior version from

/var/cache/pkg

and

pkg lock

ing it.

There were no json-specific fixes in today's 9.1.0

openvox-server

release. I tried to re-produce the duplicate key error yesterday, but couldn't get the server to fail when

json

3.1.0 was in use. If someone does have re-production steps and/or a stack trace for the error, please open an issue: github.com/OpenVoxProject/openvox-server/issues