Re: failing over with postgresql - Mailing list pgsql-hackers

For a SCADA system (Supervisory Control and Data Akquisition) which consists
of one  master and one hot-standby server I have implemented such a
solution. To these UNIX servers client workstations are connected (NT and/or
UNIX). The database client programms run on client and server side.

When developing this approach I had to goals in mind:
1) Not to get dependend on the PostgreSQL sources since they change very
dynamically.
2) Not to get dependend on the fe/be protocol  since there are discussions
around to change it.

So the approach is quite simple: Forward all database requests to the
standby server on TCP/IP level.

On both servers the postmaster listens on port 5433 and not on 5432. On
standard port 5432 my program listens instead. This program forks twice for
every incomming connection. The first instance forwards all packets from the
frontend to both backends. The second instance receives the packets from all
backends and forwards the packets from the master backend to the frontend.
So a frontend running on a server machine connects to port 5432 of
localhost.

On the client machine runs another program (on NT as a service). This
program forks for every incomming connections twice. The first instance
forwards all packets to port 5432 of the current master server and the
second instance forwards the packets from the master server to the frontend.

During standby computer startup the database of the master computer is
dumped, zipped, copied to the standby computer, unzipped and loaded into
that database.
If a standby startup took place, all client connections are aborted to allow
a login into the standby database. The frontends need to reconnect in this
case. So the database of the standby computer is always in sync.

The disadvantage of this method is that a query cannot be canceled in the
standby server since the request key of this connections gets lost. But we
can live with that.

Both programms are able to run on Unix and on (native!) NT. On NT threads
are created instead of forked processes.

This approach is simple, but it is effective and it works.

We hope to survive this way until real replication will be implemented in
PostgreSQL.

Andreas Kardos

-----Ursprüngliche Nachricht-----
Von: Todd M. Shrider <tshrider@varesearch.com>
An: <pgsql-hackers@postgresql.org>
Gesendet: Donnerstag, 18. Mai 2000 17:48
Betreff: [HACKERS] failing over with postgresql


>
> is anyone working on or have working a fail-over implentation for the
> postgresql stuff. i'd be interested in seeing if and how any might be
> dealing with just general issues as well as the database syncing issues.
>
> we are looking to do this with heartbeat and lvs in mind. also if anyone
> is load ballancing their databases that would be cool to talk about to.
>
> ---
> Todd M. Shrider VA Linux Systems
> Systems Engineer
> tshrider@valinux.com www.valinux.com
>