5 ms·

Hmm. PgSQL is rock solid as an RDBMS, but the lack of dependable replication options is quite annoying. I'm curious to know if anyone here has actually used {

by aditya 18y ago

Hmm. PgSQL is rock solid as an RDBMS, but the lack of dependable replication options is quite annoying. I'm curious to know if anyone here has actually used {londiste, pgpool2, slony} to do master/slave replication and made it work well?

olefoo 18y ago
I have set up and run both slony and mammoth-replicator in production. It is not a trivial task. The big problem I've encountered is that the database to be replicated must have defined primary keys. Far too few developers understand what is meant by referential integrity.
mdasen 18y ago
It is a great database, but replication is lacking. Slony-I is the best of the bunch currently, but it works by using triggers, requires you to set it up for every table and key, scales quadratically, and just generally has a lot of overhead (when compared to MySQL's replication). Mammoth Replicator is the most interesting project to me since it operates a lot more like MySQL's replication in that it works off log shipping. Currently they only have a beta for download off their site (https://projects.commandprompt.com/public/replicator https://projects.commandprompt.com/public/replicator), but I'm hopeful for it. Replication is a really hard problem to solve given all the little inconsistencies that can creep in, but it's one of those areas that's holding PostgreSQL back most. Hot failover and scalable read-only replicants are a big selling point for MySQL. I really hope that PostgreSQL nails this area in 2010 because it's query planner is much better at handling complex queries (really, even something as simple as a sub-query). But you're right, it it annoying that PostgreSQL doesn't have a nicer replication option. EDIT: If performance isn't your biggest concern and you don't mind a highly tedious setup, Slony-I is reliable. However, it's not suitable for larger clusters of machines since communication costs grow quadratically.
- neilc 18y ago
  it's not suitable for larger clusters of machines since communication costs grow quadratically. Quadratically? Why?
  - mdasen 18y ago
    http://www.pgadmin.org/docs/1.4/slony/slonylistenercosts.html http://www.pgadmin.org/docs/1.4/slony/slonylistenercosts.htm...
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 ago
  The 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 ago
    I 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.

by aditya 18y ago

olefoo 18y ago

I have set up and run both slony and mammoth-replicator in production. It is not a trivial task. The big problem I've encountered is that the database to be replicated must have defined primary keys. Far too few developers understand what is meant by referential integrity.

mdasen 18y ago

It is a great database, but replication is lacking. Slony-I is the best of the bunch currently, but it works by using triggers, requires you to set it up for every table and key, scales quadratically, and just generally has a lot of overhead (when compared to MySQL's replication). Mammoth Replicator is the most interesting project to me since it operates a lot more like MySQL's replication in that it works off log shipping. Currently they only have a beta for download off their site (https://projects.commandprompt.com/public/replicator https://projects.commandprompt.com/public/replicator), but I'm hopeful for it. Replication is a really hard problem to solve given all the little inconsistencies that can creep in, but it's one of those areas that's holding PostgreSQL back most. Hot failover and scalable read-only replicants are a big selling point for MySQL. I really hope that PostgreSQL nails this area in 2010 because it's query planner is much better at handling complex queries (really, even something as simple as a sub-query). But you're right, it it annoying that PostgreSQL doesn't have a nicer replication option. EDIT: If performance isn't your biggest concern and you don't mind a highly tedious setup, Slony-I is reliable. However, it's not suitable for larger clusters of machines since communication costs grow quadratically.

neilc 18y ago
it's not suitable for larger clusters of machines since communication costs grow quadratically. Quadratically? Why?
- mdasen 18y ago
  http://www.pgadmin.org/docs/1.4/slony/slonylistenercosts.html http://www.pgadmin.org/docs/1.4/slony/slonylistenercosts.htm...

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 ago
The 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 ago
  I 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.