Pam,
> Yes -- but what happens if the database crashes and you have to
> restore up
> to
> a point in time -- Can you restore using your last pg_dump file &
> then is
> there ** some** way to bring forth your **log** information so that
> there is
> no data lost. Example: 7pm you do a pg_dump 7am the next morning,
> the
> database
> crashes. You restore using the 7pm dump -- how do you recover the
> data from
> 7pm to 7am?
Aha! That's called "Point-in-Time Recovery". Unfortunately, Postgres
does not have Point-in-Time Recovery as of version 7.2. This is one
of the expensive projects that Greatbridge was working on when their
funders pulled the plug (along with in-the-database replication).
Oddly enough, I had a conversation with a contractor about this just
this morning. Here's your best solution:
1. Postgres recovers from an unexpected shutdown 90% of the time if the
host filesystem is intact. So put your Postgres server on a
Journaling File System, such as ReiserFS, Ext3, or JFS, which recover
99% of the time from power outages.
2. Do a pg_dump to a disk file every hour, not just once a day.
pg_dump runs quickly (under 45 seconds for a 4mb database) and can be
run while the database is in use. This can take the place of
point-in-time restore, because you won't lose more than 59 minutes of
data unless the host machine dies.
-Josh Berkus