0

Running postgres 9.6, big data table lots of transactions occurring on table. After every 100,000 changes (or so) a VACUUM FREEZE is run against the table. My understanding was that this would do the same as the AUTO_VACUUM (to prevent wrap-around) but it still does the auto vacuum. So my question is how do I manually kick off the VACUUM process to do the same as the AUTO_VACUUM does ?

I would assume that since you can turn the auto vacuum completely off that there must be a way of doing this manually.

To further clarify I was getting these errors in the logs :

2018-01-23 20:14:16.393 UTC [70377] WARNING:  oldest xmin is far in the past
2018-01-23 20:14:16.393 UTC [70377] HINT:  Close open transactions soon to avoid wraparound problems.
notzippy
  • 468
  • 7
  • 10
  • Are you seeing auto-vacuum running *that particular task* on *that particular table*? Where are you seeing that, how often, for how long? – IMSoP Jan 24 '18 at 17:52
  • Yes it runs against that table. Autovacuum was running long enough and slow enough to mess things up with the next batch of transactions. I modified the autovacuum cost to be 0 and restarted the service and it made it through the table in about one hour (aprox 270 million rows) – notzippy Jan 24 '18 at 18:08
  • To clarify Laurenz, your statement, the error codes I saw were the result of a long running statement - not the autovacum process. And because of this postgres attempted to kick off an autovacum which would also never complete because of the same reasons. So the `vacuum freeze` commands where working up until this point but failed because something got clogged in the plumbing. – notzippy Jan 25 '18 at 19:48

0 Answers0