Azure Database for PostgreSQL server truncates the same table repeatedly

Question

Hi,

I have a running PostgreSQL server on Azure. The server instance configuration is Basic, 2 vCore(s), 20 GB.
My database has 10 tables in one schema. Among the 10 tables, the biggest table (11GB) has been truncated (become empty) repeatedly about every one or two weeks. All other tables don't have the same issue. I had to re-import data to the table again and again. When all the data is imported the storage usage is about 70% of disk space. Users only can read the data from the database. I am only one can write data.

I have added an alert rule to the server which should send me an email if the DB storage percentage drops below 30%. But I've never got any email even the storage percentage went below about 13% after the table is truncated from unknown reason. I also enabled some

If anyone experienced the same issue, I want to get some advice on how to analyze the issue.

Thanks,
Jisung

Answer 1

Hi @KimJisung - The best option here is to set up Audit Logging through the PostgreSQL (Single Server) extension pgAudit and then have events forwarded to Azure Monitor for investigation, or dig through the resulting file looking for the truncating events. Please see: Audit logging in Azure Database for PostgreSQL - Single Server

Alerting will be helpful, as you can be notified and respond in a timely manner in terms of limiting the amount of audit log detail being captured. As for the alert not being triggered, what is the storage % or storage_used figure when the specific table is truncated...is that figure below 30%? Perhaps the trigger isn't firing because the rule is not specific to the condition needing to be triggered upon. The storage_used and storage % may not be calculated as expected. Please see the Azure Database for PostgreSQL Metrics and how to setup alerts.

Regards,
Mike