Re: dblink question please - Mailing list pgsql-sql

Many thanks Joe.

I think my problem is quite typical one, let me try my best to describe the
project.

The project is about to build a "twin-database" system, which requires data
to be replicated
synchronously(only ONE single active master accepts request though), and
supports
"fail-over"(if one database server is crashed for some reason, then at later
time when it is up,
it should get synchronized with the current active master.)

The two servers' role can be changed, slave -> active master and master ->
slave are possible.

(I use dblink() functions to connect to the remote host)
8 possible cases identified on what it might happen and how to cope with it:

***********************************************************************
Case 1: Both OK, clients connect to S1.
Update synchronously.

Case 2: S1 OK, S2 failed, clients connect to S1
Update S1 only, mark S2 as unavailable.

Case 3: S1 failed, S2 OK, clients connect to S1.
Clients timed out, clients then connect to S2 (this has to ben hard-coded in
client codes),
S2 connects to S1 but failed,
upgrade S2 to master,
mark S1 as unavailable.
Update S1 only

Case 4: Both OK, clients connect to S2
S2 checks S1's status in S2's database.
S1 available and S2 is able to connect to S1
Return error (S2's not currently the master)
Clients then connect to S1.

Case 5: (extends case 3) S1 failed, S2 OK and then S1 OK
At the beginning, this is handled like case 3
When S1 UP, client should continue to connect to S2.
If a new client connects to S1, S1 check its current status, is master,
but its status in S2 is slave.
Then downgrade S1 to slave and keep S2's database data unchanged.
S1 and then should return error (coz it's not the master).
Finally clients should connect to S2.

Case 6: Case 5 + DB sync. completed.
External Program Lock exclusively (don't even allow others to read from it)
the status table
of S2 (currently the master).Downgrade S2 to slave and upgrade S1 to master
in S2.Then
upgrade S1 to master and downgrade S2 to slave in S1. Unlock the table.
During the process, if clients connect to S1, return error ( coz it's slave
now).
If clients connect to S2, wait for timeout. (coz its status table has been
locked.)
�o Unlock table, clients connect to S2, return error. (not master now.)

Case 7: Case 5 + DB sync processing, S2 failed.
NO SOLUTION, S1 cannot must not accept requests (coz not get sync'ed)

Case 8: Case 5 + S2 failed, and then S1 OK.
NO SOLUTION, S1 doesn't know itself has been failed previously.
***********************************************************************

There are several identical tables stored on both servers, we manipulate
those tables to
simulate transaction(stores the every update delete/insert of requests from
clients)

The database users mainly access the database by the mean of ADO over ODBC
(they are
VB programs), a wrapper VB6 library is written so that those VB programs do
not need
to change their codes much. (Clients are aware of presense of the slave
server
only when the current active master fails, so that it can try to connect to
another.)

Now the key problem is how I can just let it be when active master fails to
connect
to slave(of course it will mark the relative fields of status table, just
don't wanna wait too long.)

I wanna say thanks again here, for reading through this long and clumsy text
by me.

Regards, Frankie

"Joe Conway" <mail@joeconway.com> wrote in message
news:3E4B9003.6060907@joeconway.com...
> Frankie Lam wrote:
> > Now I use only persistent connection inside my PLPGSQL functions, the
> > EXCEPTION "ExecMakerTableFunctionResult: Invalid result from function
> >  returning tuple" has gone.
>
> Good.
>
> > It seems to me this is a matter of libPQ (because connect_timeout
> > doesn't work in the case), but someone told me this is nothing to do
> > with libPQ, and it's possible a matter of KERNEL and transport layer
> > of tcp protocol.(I don't really understand these stuff) Is this
> > true?(If this is true, then I have to abort my project :-( )
> >
>
> I'm afraid the person who gave you that answer knows much better than I.
> Perhaps there is some kernel tcp parameter you can adjust? Or maybe
> dblink just isn't an appropriate solution given your requirements. You
> haven't described what exactly you are trying to do, and what exactly
> are your requirements, so it's a bit hard to help there.
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)