Determining when and which records were deleted

App Building
1

I have a table with about 400 records missing. Nothing serious as it's test data. I know the data was delete in the past 2 weeks.

So I know the table name and the date range. The data is in the resource database (PostgreSQL).

What are some ways I can trace when records were deleted? I have manually looked through thousands of records downloaded from the Audit logs but cannot find anything (it might be in there though).

3

Hello @Shawn_Optipath!

Great question, there are a couple options that are all specific to Postgres that would be useful for tracking down changes made to the database.

Most of these are involving tools that are not Retool to check back. If you believe these records were deleted by Retool queries, I can check on what options are available for self hosted users to look into their app's PostgreSQL to check the logs.

Going through logs is a lot of work as there are simply so many, but hopefully we can find tools to narrow this down to delete actions and the specific tables the records were removed from.

Here are some options for checking a PostgreSQL DB using the audit logs:

1. Enable Query Logging (if not already enabled)

Ensure PostgreSQL is configured to log all DELETE queries. You can enable this by adjusting the postgresql.conf file or using the following command:

ALTER SYSTEM SET log_statement = 'all';

Restart the PostgreSQL server for changes to take effect.

2. Search PostgreSQL Logs

Use a combination of grep or similar tools to extract relevant DELETE statements from the logs:

  • Navigate to your PostgreSQL log directory.
  • Use a command to filter by DELETE and the table name, along with timestamps.
grep -E "DELETE.*FROM your_table_name" postgresql-*.log | grep "2025-01-01"
  • Adjust your_table_name and 2025-01-01 to match your specific table and date range.

3. Extract Logs into a Temporary Table

If you want to analyze a significant number of logs, consider parsing them into a PostgreSQL table for better querying:

  • Create a table to store log entries:
CREATE TABLE log_entries (
    log_time TIMESTAMP,
    user_name TEXT,
    query TEXT
);
  • Load logs into this table using a script or bulk loader.

4. Use pgAudit Logs (if Configured)

If pgAudit is enabled, deletion statements will be recorded in the PostgreSQL logs in a structured format:

  • Look for log entries matching your criteria, e.g.:
grep "DELETE" postgresql-*.log | grep "your_table_name"

Useful Tools for Analyzing Logs

1. Log Management Tools

  • Graylog or ELK Stack (Elasticsearch, Logstash, Kibana): Centralize and search logs with a user-friendly interface.
  • pgBadger: A PostgreSQL log analyzer that provides visual reports of database activity, including deletions.
pg_badger /path/to/postgresql.log

2. Scripting Tools

  • Python Scripts: Use Python with regex to parse logs and filter for specific tables or date ranges.
import re

with open('postgresql.log', 'r') as log_file:
    for line in log_file:
        if re.search(r"DELETE.*FROM your_table_name", line):
            print(line)
  • awk: Process logs line-by-line in Unix-like systems:
awk '/DELETE.*FROM your_table_name/ && /2025-01-01/' postgresql.log

3. pg_logical

  • If logical replication or pg_logical is set up, use the replicated changes to analyze deletion activity.

4. Custom PostgreSQL Views

Create a custom PostgreSQL function to parse logs stored in a table and filter DELETE entries for specific tables and dates.

Combining Audit Logs with Table Backups

To confirm which rows were deleted, compare backups with current table states. Use a combination of:

  • EXCEPT queries to find missing rows:
SELECT * FROM backup_table
EXCEPT
SELECT * FROM current_table;