[Pgpool-general] Pgpool and/or Slony

[Brad Nicholson]

On Wed, 2007-01-10 at 23:42 +0100, Dragan Zubac wrote:
> Hello all

> Master server A accepts real-time data,and replicate it to slave
> server B.But because the vacuum operation is a must in such
> configuration,we'd like to be able to switchover server's roles
> (master->slave,slave->master) but without  loosing connections from
> frontends (applications connecting to databases) ? 

I might be misreading this, but it sounds like you are planning on
changing which server is the master when you vacuum.  If this is
correct, can I ask why?  A normal vacuum (not a vacuum full) is a
non-blocking operation.  You will use some I/O, but it won't interfere
with the access to the db.  As long as you have your fsm setting set
properly, and you vacuum the db properly, you should never need a VACUUM
FULL (in fact, it will hurt performance).

If you really do need to flip flop you masters and slaves, Slony is not
the answer for you.  Moving the master is a blocking operation.

Also, there is a fundamental difference between how Slony and pgpool
replicate data.  Pgpool replicates statements, Slony replicates
transactions.  You need to be aware of how this affects your data.

Slony will give you an exact copy of your data, pgpool is not guaranteed
to do so (this depends entirely on your app)

Here's an example - say you have this statement.

UPDATE foo SET t=now();

With slony, now() will get fired on the master, update foo with the
timestamp, and then replicate that exact timestamp to the subscribers.

With pgpool you will get the value of now() on each server, which are
very likely not going to match.

> I think we could accept a few minutes delay between inserting data to
> master and slave server,for example each 5th minute for Slony to
> replicate data from master to slave,and each 3th minute to go for
> statistical select from server B ? 
> Is there some other solutions which could solve non-interrupted
> connections from frontends to backends in PostgreSQL case ?

What ever solution you use, you are going to have to interrupt your
connections at some point to move it.  Say you have connection C1 to the
master, you can't take that connection and move it to the slave.  You
have to disconnect C1 from the master and connect it to the slave.

-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.