RServ patch to support multiple slaves (sorta)

Attached is a patch that provides *VERY* limited support for multiple slave
servers. I haven't tested it very well, so use at your own risk (and I
recommend against using it in production).

Basically, I have a central database server that has 4 summary tables inside
it replicated to a remote slave (these database tables are for my mail server
authentication, so these are replicated to another server tuned for many
connections, and so I don't have postgres connections opened straight to my
back-end database server).

Unfortunately, I also wanted to implement a replication database server for
hot-backups. I realized, too late, that the replication process is pretty
greedy and will try to replicate all tables marked as a "MasterAddTable".

To make a long story, I made a patch to RServ.pm and Replicate that allows you
to specify, on the command line, a list of tables that you want to
replicate...it'll ignore all others.

I haven't finished, since this has to be integrated with CleanLog for
instance, but this should (and does) suffice for the moment.

I have yet to test it with two slaves, but at least my mail server replication
database now works (it was failing every time it tried to replicate, for a
variety of reasons).

Anyone have any suggestions on how to improve on this? (or, if someone more
familiar with this code wants to take the ball and run with it, you're
welcome to).

--
Michael A Nachbaur <mike(at)nachbaur(dot)com>

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Michael A Nachbaur wrote:
> Attached is a patch that provides *VERY* limited support for multiple slave
> servers. I haven't tested it very well, so use at your own risk (and I
> recommend against using it in production).
>
> Basically, I have a central database server that has 4 summary tables inside
> it replicated to a remote slave (these database tables are for my mail server
> authentication, so these are replicated to another server tuned for many
> connections, and so I don't have postgres connections opened straight to my
> back-end database server).
>
> Unfortunately, I also wanted to implement a replication database server for
> hot-backups. I realized, too late, that the replication process is pretty
> greedy and will try to replicate all tables marked as a "MasterAddTable".
>
> To make a long story, I made a patch to RServ.pm and Replicate that allows you
> to specify, on the command line, a list of tables that you want to
> replicate...it'll ignore all others.
>
> I haven't finished, since this has to be integrated with CleanLog for
> instance, but this should (and does) suffice for the moment.
>
> I have yet to test it with two slaves, but at least my mail server replication
> database now works (it was failing every time it tried to replicate, for a
> variety of reasons).
>
> Anyone have any suggestions on how to improve on this? (or, if someone more
> familiar with this code wants to take the ball and run with it, you're
> welcome to).
>
> --
> Michael A Nachbaur <mike(at)nachbaur(dot)com>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Patch applied. Thanks.

---------------------------------------------------------------------------

Michael A Nachbaur wrote:
> Attached is a patch that provides *VERY* limited support for multiple slave
> servers. I haven't tested it very well, so use at your own risk (and I
> recommend against using it in production).
>
> Basically, I have a central database server that has 4 summary tables inside
> it replicated to a remote slave (these database tables are for my mail server
> authentication, so these are replicated to another server tuned for many
> connections, and so I don't have postgres connections opened straight to my
> back-end database server).
>
> Unfortunately, I also wanted to implement a replication database server for
> hot-backups. I realized, too late, that the replication process is pretty
> greedy and will try to replicate all tables marked as a "MasterAddTable".
>
> To make a long story, I made a patch to RServ.pm and Replicate that allows you
> to specify, on the command line, a list of tables that you want to
> replicate...it'll ignore all others.
>
> I haven't finished, since this has to be integrated with CleanLog for
> instance, but this should (and does) suffice for the moment.
>
> I have yet to test it with two slaves, but at least my mail server replication
> database now works (it was failing every time it tried to replicate, for a
> variety of reasons).
>
> Anyone have any suggestions on how to improve on this? (or, if someone more
> familiar with this code wants to take the ball and run with it, you're
> welcome to).
>
> --
> Michael A Nachbaur <mike(at)nachbaur(dot)com>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Patch applied. Thanks.

> Michael A Nachbaur wrote:
>> Attached is a patch that provides *VERY* limited support for multiple slave
>> servers. I haven't tested it very well, so use at your own risk (and I
>> recommend against using it in production).
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

It sounded to me like that patch was intended for comment, not for
application.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Patch applied. Thanks.
>
> > Michael A Nachbaur wrote:
> >> Attached is a patch that provides *VERY* limited support for multiple slave
> >> servers. I haven't tested it very well, so use at your own risk (and I
> >> recommend against using it in production).
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> It sounded to me like that patch was intended for comment, not for
> application.

He said it wasn't all he wanted to do with the code, but that it did
work. With so few rserv patches, it seems like something we should get
in, but maybe not? Other comments? I am not sure myself.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, Jun 25, 2003 at 11:11:35AM -0400, Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Patch applied. Thanks.
> >
> > > Michael A Nachbaur wrote:
> > >> Attached is a patch that provides *VERY* limited support for multiple slave
> > >> servers. I haven't tested it very well, so use at your own risk (and I
> > >> recommend against using it in production).
> > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> >
> > It sounded to me like that patch was intended for comment, not for
> > application.
>
> He said it wasn't all he wanted to do with the code, but that it did
> work. With so few rserv patches, it seems like something we should get
> in, but maybe not? Other comments? I am not sure myself.

I don't remember the patch right now, but it seemed to me the patch
didn't have anything to do with multiple slaves anyway... When was it
posted? I can't find it in the archives... (it'd be nice to have the
date of the original message in the attribution when you quote other
people, that way it's much easier to find it in the archives)

Some 2 years ago I wrote a patch for multiple slaves and it worked
reasonably well... I wasn't too much in the Postgres world those days so
I didn't submit it. If I can get to my CVS archive I'll extract it and
post for review.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
A male gynecologist is like an auto mechanic who never owned a car.
- Carrie Snow

On Wednesday 25 June 2003 08:42 am, Alvaro Herrera wrote:
> On Wed, Jun 25, 2003 at 11:11:35AM -0400, Bruce Momjian wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > > Patch applied. Thanks.
> > > >
> > > > Michael A Nachbaur wrote:
> > > >> Attached is a patch that provides *VERY* limited support for
> > > >> multiple slave servers. I haven't tested it very well, so use at
> > > >> your own risk (and I recommend against using it in production).
> > >
> > > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > >
> > > It sounded to me like that patch was intended for comment, not for
> > > application.

Yes, this was my original intent. If anyone else thought it was worthy enough
to go into CVS, then great, but mainly I wanted a few more pairs of eyes to
look it over.

> > He said it wasn't all he wanted to do with the code, but that it did
> > work. With so few rserv patches, it seems like something we should get
> > in, but maybe not? Other comments? I am not sure myself.
>
> I don't remember the patch right now, but it seemed to me the patch
> didn't have anything to do with multiple slaves anyway... When was it
> posted? I can't find it in the archives... (it'd be nice to have the
> date of the original message in the attribution when you quote other
> people, that way it's much easier to find it in the archives)

All my patch does is allow you to limit what tables you replicate from a
slave. In this way, SlaveA can replicate tables X, Y and Z, while SlaveB can
replicate tables M, N and O.

I have a single master database, and different authentication databases at key
areas of my infrastructure (mail authentication, web server configuration,
etc). I was getting errors when trying to replicate SlaveA just after adding
SlaveB, because the necessary tables didn't exist on SlaveA.

> Some 2 years ago I wrote a patch for multiple slaves and it worked
> reasonably well... I wasn't too much in the Postgres world those days so
> I didn't submit it. If I can get to my CVS archive I'll extract it and
> post for review.

That'd be great. My patch, like I said in my original post (06/19/2003
07:36pm PST), is just a beginning, and I'm not even 100% sure it'll work
reliably. Although I'm an experienced Perl programmer, I'm not as familar
with PostgreSQL's internals as I'd like to be (e.g. I tread lightly when it
comes to the pg_* tables).

If someone else has better support, I'd much rather a) take the code and run,
and b) not have to do the same myself since I have too many items on my task
list as it is.

--
Michael A Nachbaur <mike(at)nachbaur(dot)com>

OK, I have backed out this patch. Hopefully we can get a more complete
patch sometime.

---------------------------------------------------------------------------

Michael A Nachbaur wrote:
> On Wednesday 25 June 2003 08:42 am, Alvaro Herrera wrote:
> > On Wed, Jun 25, 2003 at 11:11:35AM -0400, Bruce Momjian wrote:
> > > Tom Lane wrote:
> > > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > > > Patch applied. Thanks.
> > > > >
> > > > > Michael A Nachbaur wrote:
> > > > >> Attached is a patch that provides *VERY* limited support for
> > > > >> multiple slave servers. I haven't tested it very well, so use at
> > > > >> your own risk (and I recommend against using it in production).
> > > >
> > > > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > > >
> > > > It sounded to me like that patch was intended for comment, not for
> > > > application.
>
> Yes, this was my original intent. If anyone else thought it was worthy enough
> to go into CVS, then great, but mainly I wanted a few more pairs of eyes to
> look it over.
>
> > > He said it wasn't all he wanted to do with the code, but that it did
> > > work. With so few rserv patches, it seems like something we should get
> > > in, but maybe not? Other comments? I am not sure myself.
> >
> > I don't remember the patch right now, but it seemed to me the patch
> > didn't have anything to do with multiple slaves anyway... When was it
> > posted? I can't find it in the archives... (it'd be nice to have the
> > date of the original message in the attribution when you quote other
> > people, that way it's much easier to find it in the archives)
>
> All my patch does is allow you to limit what tables you replicate from a
> slave. In this way, SlaveA can replicate tables X, Y and Z, while SlaveB can
> replicate tables M, N and O.
>
> I have a single master database, and different authentication databases at key
> areas of my infrastructure (mail authentication, web server configuration,
> etc). I was getting errors when trying to replicate SlaveA just after adding
> SlaveB, because the necessary tables didn't exist on SlaveA.
>
> > Some 2 years ago I wrote a patch for multiple slaves and it worked
> > reasonably well... I wasn't too much in the Postgres world those days so
> > I didn't submit it. If I can get to my CVS archive I'll extract it and
> > post for review.
>
> That'd be great. My patch, like I said in my original post (06/19/2003
> 07:36pm PST), is just a beginning, and I'm not even 100% sure it'll work
> reliably. Although I'm an experienced Perl programmer, I'm not as familar
> with PostgreSQL's internals as I'd like to be (e.g. I tread lightly when it
> comes to the pg_* tables).
>
> If someone else has better support, I'd much rather a) take the code and run,
> and b) not have to do the same myself since I have too many items on my task
> list as it is.
>
> --
> Michael A Nachbaur <mike(at)nachbaur(dot)com>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thursday 19 June 2003 07:36 pm, Michael A Nachbaur wrote:
> Attached is a patch that provides *VERY* limited support for multiple slave
> servers. I haven't tested it very well, so use at your own risk (and I
> recommend against using it in production).
<snip>

Okay, I just did some more testing, and found out that my code actually
doesn't work. Consider the attached test case (if run with "-delete", it
will delete the master, slavea - slavec databases that it creates).

Anyway, it looks like it replicates the "A" table just fine, and the slaveb
and slavec databases replicate just fine, but the "SyncID" was incremented by
the SlaveA replication, and therefore "b" and "c" never get updated.

I don't know enough about how the RServ code works right now to fix this right
away. Any ideas? Or should I just figure it out for myself? (I know
everyone is busy getting ready for the feature freeze)

--
Michael A Nachbaur <mike(at)nachbaur(dot)com>

On Wed, Jun 25, 2003 at 02:47:52PM -0700, Michael A Nachbaur wrote:

> Anyway, it looks like it replicates the "A" table just fine, and the slaveb
> and slavec databases replicate just fine, but the "SyncID" was incremented by
> the SlaveA replication, and therefore "b" and "c" never get updated.

> I don't know enough about how the RServ code works right now to fix this right
> away. Any ideas? Or should I just figure it out for myself? (I know
> everyone is busy getting ready for the feature freeze)

Hm, I don't quite recall the code right now, but I think you should be
trying to put the _rserv_servers_ table to use. In the current version
I think it's unused. Then you can store the SyncId on _rserv_sync_ for
each slave. You have to modify almost everything to use a SlaveId that
references parameters from _rserv_servers_, and pass that as parameter
instead of hostnames and such.

[... reads some code ...]

No, I think this is wrong, but I'm not sure.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Now I have my system running, not a byte was off the shelf;
It rarely breaks and when it does I fix the code myself.
It's stable, clean and elegant, and lightning fast as well,
And it doesn't cost a nickel, so Bill Gates can go to hell."

On Wed, 25 Jun 2003, Bruce Momjian wrote:

> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Patch applied. Thanks.
> >
> > > Michael A Nachbaur wrote:
> > >> Attached is a patch that provides *VERY* limited support for multiple slave
> > >> servers. I haven't tested it very well, so use at your own risk (and I
> > >> recommend against using it in production).
> > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> >
> > It sounded to me like that patch was intended for comment, not for
> > application.
>
> He said it wasn't all he wanted to do with the code, but that it did
> work. With so few rserv patches, it seems like something we should get
> in, but maybe not? Other comments? I am not sure myself.

Considering how many ppl have commented in the past how rserv was broken
anyway ... ? I'd say it can't hurt anything ...

That was my feeling, but the author wasn't sure about the patch either,
hence it was backed out.

---------------------------------------------------------------------------

The Hermit Hacker wrote:
> On Wed, 25 Jun 2003, Bruce Momjian wrote:
>
> > Tom Lane wrote:
> > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > > Patch applied. Thanks.
> > >
> > > > Michael A Nachbaur wrote:
> > > >> Attached is a patch that provides *VERY* limited support for multiple slave
> > > >> servers. I haven't tested it very well, so use at your own risk (and I
> > > >> recommend against using it in production).
> > > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > >
> > > It sounded to me like that patch was intended for comment, not for
> > > application.
> >
> > He said it wasn't all he wanted to do with the code, but that it did
> > work. With so few rserv patches, it seems like something we should get
> > in, but maybe not? Other comments? I am not sure myself.
>
> Considering how many ppl have commented in the past how rserv was broken
> anyway ... ? I'd say it can't hurt anything ...
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, Jun 25, 2003 at 08:33:04PM -0300, The Hermit Hacker wrote:
> On Wed, 25 Jun 2003, Bruce Momjian wrote:
>
> > Tom Lane wrote:
> > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > > Patch applied. Thanks.
> > >
> > > > Michael A Nachbaur wrote:
> > > >> Attached is a patch that provides *VERY* limited support for multiple slave
> > > >> servers. I haven't tested it very well, so use at your own risk (and I
> > > >> recommend against using it in production).
> > > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > >
> > > It sounded to me like that patch was intended for comment, not for
> > > application.
> >
> > He said it wasn't all he wanted to do with the code, but that it did
> > work. With so few rserv patches, it seems like something we should get
> > in, but maybe not? Other comments? I am not sure myself.
>
> Considering how many ppl have commented in the past how rserv was broken
> anyway ... ? I'd say it can't hurt anything ...

Are you saying that it doesn't matter that it is made more broken?
Sorry if I disagree... we should be trying to fix it, not the other way
around.

If it's so broken, why hasn't it received any improvement? Is there
some problem with the underlying design? I suppose the erServer code is
at least vaguely based on this, right?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Nadie esta tan esclavizado como el que se cree libre no siendolo" (Goethe)

On Wed, 25 Jun 2003, Alvaro Herrera wrote:

> Are you saying that it doesn't matter that it is made more broken? Sorry
> if I disagree... we should be trying to fix it, not the other way
> around.

> If it's so broken, why hasn't it received any improvement? Is there
> some problem with the underlying design? I suppose the erServer code is
> at least vaguely based on this, right?

eRServer is to rserv, at this stage, like night is to day ... eRServer is
a single master, multislave replication re-written completely in java to
make use of the threaded/parallel nature of java to eliminate the
deadlocks, and backlogs, that single-threaded presents ...

In fact, the *current* eRServer (which we're currently working on the
documentation for, and will be shipping out soon) now does multi-database
to multi-slave without having to start up several erserver processes,
where before you had to do one server per database ...