Unexpected Data Loss in Cloud SQL PostgreSQL

[princechawla]

We experienced a critical issue with our Cloud SQL for PostgreSQL instance on 04-June, 2025 at 5.45 PM IST.

We have a partitioned table (partitioned by client) which has: ~48 fields containing 5million records. 

1. Recently, we inserted additional historical data into this table from a staging table, increasing the total row count to approximately 18 million.

However, shortly after this insert:

1. All data was deleted from the table (i.e., row count dropped to 0).
2. The table schema remained intact, but all partitions lost their data.
3. No DELETE, TRUNCATE, or DROP statements were executed by us.

At the same time, we observed autovacuum activity on the table in Cloud SQL logs. Screenshot added. 

We are confident that:

1. Data was successfully inserted and verified immediately after the operation by the reconcilation script.
2. No manual or application-triggered DML statements were responsible for deletion.
3. Autovacuum was the only observed process interacting with this table during that period.

We need urgent support to

1. Investigate the root cause of this unexpected data loss.
2. Confirm whether autovacuum could have played any role in it.

This incident is critical for our application, and timely resolution is appreciated.

Please let us know if additional logs or metadata are needed.

Configuration:

• Edition: Enterprise Edition

• vCPUs / Memory: 6 vCPUs, 16 GB RAM

• Storage: 250 GB

[nmagcalengjr]

Hi @princechawla, 

Welcome to Google Cloud Community!

Here’s a quick guide to help you investigate the issue:

1. Check for PITR (Point-in-Time Recovery)

Google Cloud SQL for PostgreSQL supports Point-in-Time Recovery (PITR), allowing you to restore your database to a specific point in time. If you have regular backups enabled, PITR is often the fastest way to recover lost data. To avoid overwriting your current instance, you can restore to a new one using a timestamp just before the data loss occurred.

1. Go to: Cloud SQL → Backups → Restore → "Point in time"

2. Choose a time around 5:30 PM IST, before the loss.

2. Check PostgreSQL Logs in Cloud SQL

Start with the Cloud SQL Logging documentation to understand how to access and filter PostgreSQL logs, which is key for troubleshooting. It also explains how to interpret these logs and use Cloud Logging to correlate them with other application logs across your Google Cloud environment.

• Use Cloud SQL logs → Filter for postgres.log, and search for:

           "autovacuum" lines around 5:45 PM IST.

           "DELETE", "TRUNCATE", "DROP" or "ALTER TABLE" involving your table or partitions.

           "ROLLBACK" or "FATAL" indicating a failed transaction.

To analyze a specific Cloud SQL log file or export logs from a certain time, and to understand possible causes of unexpected data loss in Cloud SQL for PostgreSQL, further troubleshooting may be needed. If the issue persists, contacting Google Cloud Support is recommended for deeper investigation.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.