Release the freed-up disk space from Extended Log Storage (PostgreSQL/TimescaleDB) back to Windows.

[SangKim]

Warning

Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support for assistance.

Issue

After deleting unnecessary TrendLog objects from EBO and their logged data from the Extended Log Storage (PostgreSQL/TimescaleDB) by selecting the “Clear log” from the System folder (i.e.: Server1/System/Extended Log Storage/TimescaleDB/Trend/Tree View/…”), the Windows still shows the same free disk space size as it was before the deletion.

Product Line

EcoStruxure Building Operation

Environment

• Building Operation Enterprise Server
• Building Operation Automation Server
• External Log Storage
• PostgreSQL (pgAdmin 4)
• TimescaleDB 

Cause

PostgreSQL databases require periodic maintenance known as vacuuming for Windows to recover or reuse the freed-up disk space occupied by PostgreSQL/TimescaleDB.

Resolution

There are two variants of VACUUM: standard VACUUM and VACUUM FULL.  VACUUM FULL can reclaim more disk space but runs much more slowly.  VACUUM FULL requires an exclusive lock on the table it is working on, and therefore cannot be done in parallel with other use of the table. Refer to the documentation mentioned at  https://www.postgresql.org/docs/9.5/routine-vacuuming.html  before performing the VACUUM command.

Below is an example of the procedure to get the freed-up disk space occupied by PostgreSQL/TimescaleDB back to Windows.

1. Back up the current PostgreSQL/TimescaleDB database and save to a safe location.

1. Backup the “ebo_db” from the PostgreSQL/TimescaleDB server using the pgAdmin 4 (refer to https://www.pgadmin.org/docs/pgadmin4/development/backup_dialog.html , or refer to page 8 “Backup” section of the “EBO External Log Storage Installer.pdf” document for detail).

2. Stop the External Log Storage from EBO.

1. Open the Properties of the TimescaleDB object (/Server/System/External Log Storage/).\
2. Remember (take a note) the current value settings of the “Host Address” & the “Database Name” attributes.
3. Change them to the wrong value (or clear them) so that EBO fails to connect to the PostgreSQL/TimescaleDB server.
4. Repeat the above operation for all the other servers (AS’s) that are enabled/connected to the PostgreSQL/TimescaleDB server.

3.Delete the old logs from the PostgreSQL/TimescaleDB database (the “ebo_db”).

1. Run the pgAdmin 4, select the “ebo_db”, open the “Query Tool”, and execute the below scripts:

  Delete from trend_data where DATE_PART('day', now() - timestamp) > 365

  Delete from event_data where DATE_PART('day', now() - timestamp) > 365

4. Vacuum the Database with Full option.

1. From the same Query Tool on the “ebo_db”, run the below command: Vacuum Full
2. Wait until it completes (this may take a while depending on the number of old logs that have been deleted from the tables).

5. Start the External Log Storage from EBO.

1. Open the Properties of the TimescaleDB object (/Server/System/External Log Storage/).
2. Set the value of the “Host Address” & the “Database Name” back to the correct value, so that the EBO server successfully connects to the PostgreSQL/TimescaleDB server.
3. Verify the EBO server is connected to the PostgreSQL/TimescaleDB server, and it works normally.   
4. Repeat the above operations for all the other EBO servers (AS’s) that were disabled/disconnected from the PostgreSQL/TimescaleDB server.