Master Slave Replication

[Apoptosis]

A little background...

My development team has been using postgresql for years and we have a
nagging problem that it lacks good master slave replication (over a
wan). After replication being dropped from 8.4 I am looking for other
solutions.

I am interested in H2 in that its written in Java, and if I need too I
can contribute. I looked through the docs and it doesn't look like H2
implements master-slave replication, only master-master via statement
propagation to both instances. This wouldn't work over the WAN in
that you can easily get into a split brain situation.

So I was hoping I might be able to write what I want on top of H2.
Here is what I am looking for:

1) Master keeps a transaction log of all INSERT, UPDATE, DELETE,
CREATE, ALTER statements. (anything that changes the db)

2) No triggers, I don't want to alter schema to support this.

3) Slave is in read only mode, and knows what its last transaction id
is.

4) Slave runs job on defined schedule that sends last transaction id
to master asking for all transactions since its last.

5) Master responds with all transactions since given last transaction
id.

6) Slave executes received transactions.

7) If slave is unable to connect to master, keep trying on schedule
until max_tries is reached and send email notification.

So my question is, does H2 have the necessary internals for someone to
go write this? Any pointers at where to begin? Is there an API for
reading the database.X.log.db files? Will they contain the info I
need?

Thank you in advance,
Jacob

[Thomas Mueller]

Hi,

> it doesn't look like H2
> implements master-slave replication

No, so far not.

> 3) Slave is in read only mode

Is this a requirement? If not, then the replication could be on the
file level - http://en.wikipedia.org/wiki/Replication_(computer_science)#Disk_storage_replication
- even if you want read-only access you could still implement the
mechanism in the file level. That would be the easiest solution: H2
already supports a pluggable file system interface
(org.h2.store.fs.FileSystem).

> Is there an API for
> reading the database.X.log.db files?  Will they contain the info I
> need?

In theory, you could use the log file, but I'm currently writing a new
storage mechanism. You probably don't want to implement something that
will be removed later.

Regards,
Thomas

[Apoptosis]

I thought you couldn't cp or rsync the database while its running. I
can't take down the master to make a copy on slave. Also incrementals
are preferable to copying whole files when your db is pushing 10GB.

[Ryan How]

You could do an online backup, rsync it and then restore. But it would
use a lot more resources than just doing a one way transaction
replication. And it would need to take the slave down while restoring.
I'll be doing this on a project coming up, but it is acceptable for me
to take the slave down on a sync. And my db is only a few hundred mb so
it should backup and restore in acceptable time.

Ryan

[nick betteridge]

In TransportServer, the controller stacks up all requests for new
commits, waits for all current commits to finish, permits the db copy to
occur then finally releases all of the new commit requests to proceed.