4 ms·

Aren't there other ways to do database replication?

by midas 16y ago

birken 16y ago
(post author) Yes, there are a lot of third-party replication solutions for Postgres (http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling http://wiki.postgresql.org/wiki/Replication,_Clustering,_and...), which offer various features (some of which offer even more complex and redundent replication options than the built-in replication). However, since they are all third-party solutions, there are such varying levels of complexity and support I think you would prefer to use the built-in versions of replication. And as discussed in the post, I believe that streaming replication is a very nice replication solution, and probably good enough for most applications.
- jswinghammer 16y ago
  How do you like Postgres? I'm doing a lot of work with MySQL lately and curious about Postgres.
  - birken 16y ago
    I can't say I have a ton of experience with Mysql, but I am a huge fan of Postgres. I think especially for people who just want their database to work, having to worry about which of the storage engines you should be using for MySQL would be a headache. With Postgres, they focused on one storage engine and making it work as broadly as possible, and I think that is the right way to go about it. In addition, administration + configuration is really nice, the community and mailing lists are active and helpful, and the performance has been great for our usage. We also use it heavily for geospacial stuff (using PostGIS), and I think Postgres is ahead of MySQL for that. I'm sure there are places online that do a much better job comparing them, but from my experiences with Postgres I would have no reason not to use it for any relational database needs.
  - pkteison 16y ago
    I know MySQL has drunk the cool-aid with innodb, but 10 years ago their FAQ entry about why ACID was a bad idea convinced me I didn't ever want to rely on them for anything I might normally rely on a database for. I'm cool with intentionally trading off database features for other features that I value more (like availability), but I want it to be an intentional choice, and then it shouldn't still pretend to be a database for me - admit it's an unreliable data store and just use this unreliable fast thing for a cache, not as a database.
- rosser 16y ago
  However, since they are all third-party solutions, there are such varying levels of complexity and support I think you would prefer to use the built-in versions of replication. Depends on what you need replication to do for you. If you just want a full replica of your db, the built-in stuff is getting really good with 9.x (as far as I've heard; I have no direct experience with it). That said, the PostgreSQL hackers are specifically making most-case tools, and for most people, they're great. If you have more complicated needs, though, you'll need to look elsewhere. The built-in tools are all-or-nothing, and single-master. I've worked in multiple environments where we had needs that went beyond what they'd have offered, had they existed at the time; multi-master, replicating a subset of your database(s), and transforming the data in-flight during replication are real needs that organizations find themselves facing, and based on what the core committee has said about their plans, those aren't going to be addressed with the built-in tools any time soon, if ever.

Aren't there other ways to do database replication?

by midas 16y ago

Aren't there other ways to do database replication?

(post author) Yes, there are a lot of third-party replication solutions for Postgres (http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling http://wiki.postgresql.org/wiki/Replication,_Clustering,_and...), which offer various features (some of which offer even more complex and redundent replication options than the built-in replication). However, since they are all third-party solutions, there are such varying levels of complexity and support I think you would prefer to use the built-in versions of replication. And as discussed in the post, I believe that streaming replication is a very nice replication solution, and probably good enough for most applications.

jswinghammer 16y ago
How do you like Postgres? I'm doing a lot of work with MySQL lately and curious about Postgres.
- birken 16y ago
  I can't say I have a ton of experience with Mysql, but I am a huge fan of Postgres. I think especially for people who just want their database to work, having to worry about which of the storage engines you should be using for MySQL would be a headache. With Postgres, they focused on one storage engine and making it work as broadly as possible, and I think that is the right way to go about it. In addition, administration + configuration is really nice, the community and mailing lists are active and helpful, and the performance has been great for our usage. We also use it heavily for geospacial stuff (using PostGIS), and I think Postgres is ahead of MySQL for that. I'm sure there are places online that do a much better job comparing them, but from my experiences with Postgres I would have no reason not to use it for any relational database needs.
- pkteison 16y ago
  I know MySQL has drunk the cool-aid with innodb, but 10 years ago their FAQ entry about why ACID was a bad idea convinced me I didn't ever want to rely on them for anything I might normally rely on a database for. I'm cool with intentionally trading off database features for other features that I value more (like availability), but I want it to be an intentional choice, and then it shouldn't still pretend to be a database for me - admit it's an unreliable data store and just use this unreliable fast thing for a cache, not as a database.
rosser 16y ago
However, since they are all third-party solutions, there are such varying levels of complexity and support I think you would prefer to use the built-in versions of replication. Depends on what you need replication to do for you. If you just want a full replica of your db, the built-in stuff is getting really good with 9.x (as far as I've heard; I have no direct experience with it). That said, the PostgreSQL hackers are specifically making most-case tools, and for most people, they're great. If you have more complicated needs, though, you'll need to look elsewhere. The built-in tools are all-or-nothing, and single-master. I've worked in multiple environments where we had needs that went beyond what they'd have offered, had they existed at the time; multi-master, replicating a subset of your database(s), and transforming the data in-flight during replication are real needs that organizations find themselves facing, and based on what the core committee has said about their plans, those aren't going to be addressed with the built-in tools any time soon, if ever.