Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 2 down vote accepted

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.

share|improve this answer
Thanks. I ended up just restoring the database off an old dump. The contents were not important. That being said, I'll set up a weekly dump. Timemachine can at least reliably back those up. Point in time recovery will never be that important. – dave May 12 '11 at 18:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .