How do I restore a Postgres database from file backups

Question

I use Time Machine on my Mac to backup my system. I have just managed to clobber my Postgres database and would like to restore it using my Time Machine backups. The latest changes don't matter, so I just want to wind it back to its state yesterday.

So, my questions are:

a) are Time Machine backups useful for databases?

b) where are the actual database files stored? I've looked and can not find anything useful.

c) can I just rollback those files and everything will work?

d) do I need to shutdown Postgres first?

I have an old dump of the database using Postgres' backup but would like to know what the story is with Postgres first.

Answer 1

In order:

a) Probably not. Time Machine certainly can back up databases (they're just files after all), but it probably isn't the most efficient or reliable way of doing so. Because Time Machine works on a file basis, if any one little thing changes in a file, it'll back up the entire file again in the next backup. In addition, there can be issues with doing file-level backups while Postgres is running due to the server caching changes (details). If you're serious about backing up your database, set up a Launchd job to do it properly - there's a whole section in the manual about backing up.

b) By default, somewhere like /usr/local/pgsql/data. This is set when you start up the server (with the -D flag), though, so it's easily changed.

c) It will either work or do the other thing. If the server was running when the backup was taken, the data directory may be in an inconsistent state - see comments about file-level backup while the server is running. Then again, you might get lucky (especially if your database wasn't heavily used) and it'll work.

d) Very much so.