Re: [GENERAL] Point in Time Recovery WAS: Hot Backup

Rod Taylor wrote:
>
<snip>
> Oh, if thats your problem then use asynchronous replication instead.

For specific info, the contrib/rserv package does master->slave
asynchronous replication as Rod is suggesting. From memory it was
having troubles working with PostgreSQL 7.2.x, but someone recently
submitted patches that make it work.

There's a HOW-TO guide that a community member wrote on setting up rserv
with PostgreSQL 7.0.3, although it should be practically identical for
PostgreSQL 7.2.x (when rserv is patched to make it work).

http://techdocs.postgresql.org/techdocs/settinguprserv.php

That could be the basis for your async replication solution.

Hope that helps.

:-)

Regards and best wishes,

Justin Clift

> It doesn't remove the slow time, but will distribute the slowness across
> every transaction rather than all at once (via creation of replication
> logs). Things won't degrade much during the snapshot transfer itself,
> as there isn't very much work involved (differences only).
>
> Now periodically backup the secondary box. Needs diskspace, but not
> very much power otherwise.
>
> #!/bin/sh
> while(true)
> do
> asynchreplicate.sh
> pg_dumpall > "`date`.bak"
> done
>
> > -----Original Message-----
> > From: scott.marlowe [mailto:scott(dot)marlowe(at)ihs(dot)com]
> > Sent: Wednesday, October 09, 2002 12:19 PM
> > To: Sandeep Chadha
> > Cc: Tom Lane; pgsql-hackers(at)postgresql(dot)org; pgsql-general
> > Subject: [GENERAL] Point in Time Recovery WAS: Hot Backup
> >
> >
> > Hi Sandeep. What you were calling Hot Backup is really called Point in
> > Time Recovery (PITR). Hot Backup means making a complete backup of the
> > database while it is running, something Postgresql has supported for a
> > very long time.
> >
> > On Mon, 7 Oct 2002, Sandeep Chadha wrote:
> >
> > > Hello to all the Doers of Postgres!!!
> > >
> > > Last time I went through forums, people spoke highly about 7.3 and its
> > > capability to do hot backups. My problem is if the database goes down
> > > and I lose my main data store, then I will lose all transactions back
> > > to the time I did the pg_dump.
> >
> > Let's make it clear that this kind of failure is EXTREMELY rare on real
> > database servers since they almost ALL run their data sets on RAID arrays.
> > While it is possible to lost >1 drive at the same time and all your
> > database, it is probably more likely to have a bad memory chip corrupt
> > your data silently, or a bad query delete data it shouldn't.
> >
> > That said, there IS work ongoing to provide this facility for Postgresql,
> > but I would much rather have work done on making large complex queries run
> > faster, or fix the little issues with foreign keys cause deadlocks.
> >
> > > Other databases (i e Oracle) solves this by retaining their archive
> > > logs in some physically separate storage. So, when you lose your data,
> > > you can restore the data from back-up, and then apply your archive log,
> > > and avoid losing any committed transactions.
> > >
> > > > Postgresql has been lacking this all along. I've installed postgres
> > > 7.3b2 and still don't see any archive's flushed to any other place.
> > > Please let me know how is hot backup procedure implemented in current
> > > 7.3 beta(2) release.
> >
> > Again, you'll get better response to your questions if you call it "point
> > in time recovery" or pitr. Hot backup is the wrong word, and something
> > Postgresql DOES have.
> >
> > It also supports WALs, which stands for Write ahead logs. These files
> > store what the database is about to do before it does it. Should the
> > database crash with transactions pending, the server will come back up and
> > process the pending transactions that are in the WAL files, ensuring the
> > integrity of your database.
> >
> > Point in Time recovery is very nice, but it's the last step in many to
> > ensure a stable, coherent database, and will probably be in 7.4 or
> > somewhere around there. If you're running in a RAID array, then the loss
> > of your datastore should be a very remote possibility.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
> --
> Rod Taylor
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi