Re: Disaster recovery (server died)

Uwe C. Schroeder wrote:
> On Friday 19 June 2009, Scott Marlowe wrote:
>> On Fri, Jun 19, 2009 at 8:43 PM, Miguel
>>
>> Miranda<miguel(dot)mirandag(at)gmail(dot)com> wrote:
>>> Well, i just didnt explain in detail, what i have is just the 16897
>>> directory where i was storing the database, i tried just copying the
>>> files but it didnt work,
>>> should it be posible to import this database is any way?
>> Nope, you need the whole data directory.
>
> What I don't get is this: you said your CPU died. For me that's the processor
> or maybe some interpret that as the main board.
> So why don't you grab the harddisk from that server and plug it into the new
> one? Maybe something might be corrupt due to the failure, but most of the
> data should be on the disk (unless you use disks which lie about fsync).
> Yep - another reason why one has at least a daily backup (in my case 2
> replicas for every production server I run. I never had a major failure in
> over 15 years - knock on wood - but if that happens I don't lose a heck of a
> lot due to the backups and slony replicas)
>
>
> Uwe

For smaller databases, I run nightly pg_dumps to a file with the day of
the week number appended to the dump file. This way my nightly backups
grab the day's changes and my database sits in a nice plain text file.

For larger databases, I either stop PostgreSQL and rsync *all* the
Postgres files then restart or, if stopping isn't an option, use LVM and
make snapshots.

A third option is to have PostgreSQL sit on a DRBD partition. However,
if the DRBD link is only 1 GBit, it will be noticeably slower on writes,
but if that's okay it can be a solution useful for more than just
PostgreSQL.

A few options for people who feel replication is not feasible. :) Of
course, when you can, it is the best option. You never lose anything
that way.

Madi