Run VACUUM on postgreSQL from Thingworx?

[pselvaraj-1]

In our project we have maintained streams to store sensor data ingestes very frequently. Similarly we have different schedulers to purge or delete the older entries from streams.

Deleted rows will still there in PostgreSQL until a VACUUM is done. Hoping these process might have been automated in database but I want to know the way to run VACUUM on PostgreSQL from Thingworx after the tx services deleted or purged those old entries from streams.

[dupatel]

Prem Kumar Selvaraj​: I believe you will have to go to PostgreSQL database to run the Vacuum command. Running from Thingworx might cause issue with permissions as it needs database admin privilege to run this command.

-Durgesh

[supandey]

Hi Prem Kumar, i'm curious why you'd want to run these services manually, PostgreSQL engine runs these maintenance related services automatically (see Routine Vacuuming ). Could you share which version of PostgreSQL and ThingWorx are you working with?

Finally, as Durgesh said you will need Admin level rights for this so easiest would be via PGAdminIII client or if you want to consolidate all these in ThingWorx then you can test by creating JDBC connection and you'll need user which is part of the admin group in the database. Then with a SQL service this could be tested.