Recovering lost PostgreSQL data

A “friend” recently dropped all the tables in a locally run database. All data is restored. Here’s how.

Laravel’s Artisan fresh/refresh wipes out all database data in a variety of ways and is used when running tests. If you PHPUnit.xml doesn’t have a test database defined, your tests will probably wipe out your data.

If you have PostgreSQL installed on MacOS using Homebrew on Apple Silicon and Time Machine backups you can get your data back in the following way.

  1. you need to know the internal id of the database you want to recover
  2. you need to know the data directory that postgresql uses on your machine.

Those two combined enable you to find the data in your Time Machine backup and restore it.

However you query PostgreSQL, run the following to get the database id.

SELECT oid, datname FROM pg_database;

This will list all your databases along with their object ID, e.g.

oid | datname

12345 | oops_i_did_it_again

Now run the following to find the data directory.

SHOW data_directory;

The result will be something like

/opt/homebrew/var/postgresql@13/base

Combining those two results gives you the directory in Time Machine.

/opt/homebrew/var/postgresql@13/base/12345

Now run

brew services stop postgresql

to stop postgresql as you can’t restore a database in this way while the server is running. You may need to add the postgresql version e.g. postgresql@17

Enter Time Machine, go back to a suitable point and press Shift + Command + . to show hidden directories.

Find your database directory and restore it.

Restart postgresql using

brew services start postgresql
#postgresql #laravel