Обсуждение: "Hot Backups"

I am new to postgresql -- coming from Oracle --
Is there an equivalent "Hot" Backup in postgresql --
or is the only backup utility available  pg_dump & pg_dumpall.
thanks

Pam,

> I am new to postgresql -- coming from Oracle --
> Is there an equivalent "Hot" Backup in postgresql --
> or is the only backup utility available  pg_dump & pg_dumpall.
> thanks

pg_dump and pg_dumpall may be run while the database is in use without
 difficulty.  Is this what you mean by "hot" backup?

-Josh

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?

Thanks
Pam Wampler

-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Monday, February 11, 2002 2:38 PM
To: Pam Wampler; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] "Hot Backups"


Pam,

> I am new to postgresql -- coming from Oracle --
> Is there an equivalent "Hot" Backup in postgresql --
> or is the only backup utility available  pg_dump & pg_dumpall.
> thanks

pg_dump and pg_dumpall may be run while the database is in use without
 difficulty.  Is this what you mean by "hot" backup?

-Josh

I think the question is will the data dumped be reliable.  Are there
sufficient database locking mechanisms that will prevent tables that are
being dumped from changing during the dump itself.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Josh Berkus
Sent: Monday, February 11, 2002 2:38 PM
To: Pam Wampler; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] "Hot Backups"


Pam,

> I am new to postgresql -- coming from Oracle --
> Is there an equivalent "Hot" Backup in postgresql --
> or is the only backup utility available  pg_dump & pg_dumpall.
> thanks

pg_dump and pg_dumpall may be run while the database is in use without
 difficulty.  Is this what you mean by "hot" backup?

-Josh

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

Doug,

> Would an alternative be to log all transactions to a file at the same
>  time
> that you're making the call to the db?  I was thinking of
>  implementing
> that, but do you think it would be faster as a postgres function, or
> perhaps in the native language that's doing the front-end processing?
>   I
> would think it would be the latter, and perhaps you could just fork a
>  call
> to do just that.  In my case I'd do it in perl, since that's what
>  we're
> primarily using.
>
> While I agree with point #2, it is of little consolation if the
> information is for monetary transactions!
>
> Comments?

I'd be surprised if someone hasn't already done something like this.
  As the very least, I'm pretty sure that there is a Perl-based
 replication solution for PostgreSQL.

If there's not, I'd love to see you post your solution to /Contrib.  I
 think it's a great idea, and a quick-and-dirty solution to a problem
 for which the bulletproof solution would require significant capital.

For true, immediate 100% availability, I'd reccommend building two
 databases on two different machines and writing to both from
 middleware calls (like from Perl::DBI).

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco