Re: HA for high insert volume

Andrew

1) Would you use erServer in a non-DBA environment?
2) Could you point me to further info on what type of failures could
possibly occur using the solution below with Rsync?

Initially when I read the article below, I was skeptical it would work,
as I said to myself how is that any different than taking an OS backup
of the db why the db is still up and active - something you normally
don't want to do.

Like John, I am looking for a HA solution (standby database), but my
solution must be _Simple_ .I was initially looking for something like
Oracle's standby database, where they use the transaction log to
continuously roll-forward the standby database.

I have done some simple testing using the solution mentioned below
(using scripted FTP instead, to move the dbs between servers since they
were small).

I ran the test for 1 week, every 30 minutes I would copy the db across,
startup postgres on the remote server and dump the complete database to
verify I could read all the records. On the master (original db) I had a
very simple transaction running continuously (update, insert, delete).
From the postgres log on the remote, I could see that it would detect
the database was _not_ shutdown cleanly and would go through its
recovery process to verify everything was sane. In all tests it can
dumped the database without a problem.

I will be doing further testing with Rsync and a heavier load on the
system to see if I can break it. I haven't dug into the source code yet
to see if I could shed any light on how it might break.

I would like to find a test where I could make the solution fail to get
a better understanding of the issues with using this situation. My
problem is I need a _Simple_ solution, and I'm concerned that using one
of the replication solutions is not simple enough. (I have used Oracle's
Advanced replication in the past)

Thanks
Eric Comeau
<ecomeau(at)signiant(dot)com>

-----Original Message-----
From: Andrew Sullivan [mailto:andrew(at)libertyrms(dot)info]
Sent: Friday, November 01, 2002 1:27 PM
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: HA for high insert volume

On Wed, Oct 30, 2002 at 04:27:33PM -0500, John Sequeira wrote:

> over has a very high # of INSERTS (it does a lot of logging), and it
> seems like there might be too much overhead using something like
usogres
> or one of the replication options. We expect it to grow very quickly
to

How many inserts? We have tested eRServer (commercial version) to
> 1000 inserts per second, and it kept up reasonably well.

> Are people having luck using something like rsync in this instance?
> This document :
> http://www.taygeta.com/ha-postgresql.html
> describes using rsync, but in the case where the # of inserts is
low.
>
> Is that working for people, or is there a better way to do this?

It will not work safely. This is exactly as dangerous as doing tar
backups of your filesystem, except that tar has to get all of the
files whereas rsync can get just the things that changed. But there
is _certainly_ a window in which you could get an inconsistent
snapshot, and then your backup would be completely useless. A high
volume of write operations is a good way to cause the problem.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110