Saving My Data After a SQL Mistake: Selective Restore in PostgreSQL

Have you ever made a change and instantly regretted it? Well, I did — by accidentally deleting almost all my user data from a live database. Let’s dive into what happened, what I learned, and how I managed to recover my lost data without affecting new records.

How I deleted all my User data in Postgres (Story)

So here’s how it started. I was testing my web app, and I use a specific test email for all my checks: test@finnchristiansen.com. I even added unique numbers to the emails, like test+1, test+2, and so on, just to keep things organized.

But eventually, these test users were cluttering my database, so I figured, ‘Hey, let’s clean this up.’.

I hopped into the SQL console, ready to remove only those test accounts... and typed out:

DELETE FROM User;

I called it a day and went to the gym.

How I realized I deleted all the data in my user table in Postgres (Realization and Problem)

A few days later, I noticed something was offs. My user numbers were way lower than they should’ve been.

Then it hit me. I had deleted everything. Every single user.

All because I forgot one small, crucial detail: the WHERE clause.

DELETE FROM User
WHERE email LIKE 'test+%';

‘No problem,’ I thought, ‘I have backups!

But it wasn’t that simple. Restoring a full backup would mean losing all the new data created after the deletion. So, I couldn’t just roll back the clock on my entire database. I needed a way to restore only the deleted users, without touching the new ones.

Selective Data Restore in Postgres (Solution Overview)

Here’s what I came up with—a selective data recovery plan. Instead of restoring everything, I’d use an RDS snapshot to create a temporary copy of the database, extract only the missing data, and merge it back into production. Here’s how I did it.

Creating a Temporary RDS Database In AWS

First, I used Amazon RDS to create a new database instance from a recent snapshot. But I wanted this to be temporary, so I made sure it was easy to delete afterward. I used Terraform to set up the snapshot database, configuring it in the same private network as my production environment, but with permissions that allowed for easy deletion later.

Selectively Extracting the some records from some tables in Postgres

Once the snapshot instance was live, I connected via an SSH tunnel to export only the data I needed.

This simple SQL command helped me pull just the missing user records into a CSV file.

COPY (
  SELECT *
  FROM users
  WHERE email LIKE 'test+%'
) TO '/tmp/users_backup.csv' DELIMITER ',' CSV HEADER;

Restoring Data from a CVS file to a Postgres database table

With the data saved locally, I reconnected to the production database and used another SQL command to import it.

This way, I could bring back the deleted test users without overwriting any new ones.

COPY users (id, firstname, lastname, email, ...)
FROM '/tmp/users_backup.csv'
DELIMITER ','
CSV HEADER;

And just like that, my data was back!

Lessons Learned

So, what did I learn from this?

First, never make changes on the fly without a safety check. It’s so easy to make a mistake in SQL, especially without testing.

Next time, I’d probably create a dedicated admin route to delete test users, making sure it’s reviewed carefully.

app.post('/admin/delete-test-users', (req, res) => {
  // code with sql statements
  res.send('succesfully deleted test users')
})

Bonus: Restoring a DB snapshot using Terraform

Here is the code for the existing instance:

resource "aws_db_instance" "this" {
  identifier             = "main-db"
  db_name                = "platform"
  instance_class         = "db.t4g.micro"
  allocated_storage      = 20
  engine                 = "postgres"
  engine_version         = "15.7"
  skip_final_snapshot    = false # a final snapshot will be created
  publicly_accessible    = false
  vpc_security_group_ids = [aws_security_group.db.id]
  username               = aws_ssm_parameter.db_username.value
  password               = aws_ssm_parameter.db_password.value
  db_subnet_group_name   = aws_db_subnet_group.this.name

  # backup configuration
  backup_retention_period = 35 # number in days
  backup_window           = "02:00-03:00" # daily time in which backups are performed

  deletion_protection     = true
}

The snapshot instance is then this customized code:

resource "aws_db_instance" "snapshot" {
  identifier             = "main-db-snapshot" # important to use other name
  instance_class         = "db.t4g.micro"
  #engine                 = "postgres"
  engine_version         = "15.7"

  publicly_accessible    = false
  vpc_security_group_ids = [aws_security_group.db.id]
  db_subnet_group_name   = aws_db_subnet_group.this.name
  snapshot_identifier = "rds:diggital-platform-production-hono-2024-10-16-02-10" # this is the important part

  # easy deletion of db
  deletion_protection     = false
  skip_final_snapshot    = true
}

Leave out db_name, username and password because they are contained in the backup.

Disable deletion_protection and enable skip_final_snapshot to make it easy to delete the database. remember it is only there to quickly access the deleted data.

Tools

Code Snipes can be formated to an image using Chalk.ist