3 ms·
I've used pgpool2 in (lightly loaded) production. It is quite amazing and works as advertised. True multi-master for updates, and load balancing for reads. I
by Andys 18y ago
I've used pgpool2 in (lightly loaded) production.
It is quite amazing and works as advertised. True multi-master for updates, and load balancing for reads. I don't know why it doesn't receive more attention.
It is, however, very fiddly to set up and maintain, and you have to provide and roll your own "initial synchronization" which you can base on WAL archiving or pg_dump, or whatever.
It has also been a work in progress, with its design and features changing over time as it matures. This has made it a bit hard to wrap in easy to use scripts.
- mdasen 18y agoThe issue is that pgpool is that it doesn't keep data consistent necessarily. For example, MySQL's default replication is statement based. That means that if you execute "UPDATE table SET var=57 WHERE id=10" on the master, it will run that same statement on the mirror. BUT, MySQL's replication is smart. If you say, "UPDATE table set updated_at=now()" on the master, it won't run that on the mirror. Rather, it will run "UPDATE table set updated_at=<the time that now evaluated to on the master>". pgpool also requires table locking when dealing with SERIALs and that is just unacceptable for performance. It just has limitations that don't make it a good replication tool for production environments. pgpool is an awesome tool, but not for replication. Use it for load balancing and connection pooling which it is wonderful for.
- Andys 18y agoI believe pgpool also deals with timestamps properly. Secondly, no one is forcing you to use SERIAL datatypes - the table locking is there for backwards compatibility. SERIAL is not the best performance choice for replicated servers, and there's nothing to stop you using another style of ID field.