Re: "Hot Backups"

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