Thread: "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
Attachment
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