5 ms·

Great news! I'd love to move over to this from MongoDB for a project that has high uptime requirements. But while I think the JSON will really replace it, does

by atonse 12y ago

Great news! I'd love to move over to this from MongoDB for a project that has high uptime requirements. But while I think the JSON will really replace it, does PG have a solution for High Availability (like replica sets) in the works? I'm newer to Postgres so am not sure. Replica Sets are the killer feature for me, more so than just storing JSON documents. I'd appreciate if someone can chime in. I've done some googling but there seem to be multiple strategies for replication.

arthursilva 12y ago
PG has async/sync/hybrid replication for years already. It's not as tooled as MongoDB but there's some tools like http://www.repmgr.org/ http://www.repmgr.org/ to amend it.
- internetisthesh 12y ago
  Every time I look into tools like this they are quite far behind for example AlwaysOn in MSSQL. For example, with PG I have to reseed the original master if it comes back online after an outage. It's as far as I can tell not fully automatic and transparent to me as the guy responsible for managing it. With SQL and elasticsearch+ZooKeeper for example nodes can go up and down without anyone noticing it and me not having to do anything. Is this still a place where PG is behind?
  - tensor 12y ago
    Yes and no. There are differences in the data guarantees that something like elastic search and postgresql give. So it's not really appropriate to compare those. MS SQL does have easier tooling for replication. The setup for postrgresql is complex and it doesn't come with out of the box tools to easily manage failover and recovery as you mention. Progress is being made in making this easier, but it's still mostly in the low level functionality: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4#Replication_improvements https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_... https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#Replication_Improvements https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
  - amitlan 12y ago
    > For example, with PG I have to reseed the original master if it comes back online after an outage pg_rewind[1] is supposed to fix this. It's moving into core for 9.5, by the way. [1] https://github.com/vmware/pg_rewind https://github.com/vmware/pg_rewind
en4bz 12y ago
Did you take a look at Slony-I? http://slony.info/ http://slony.info/
gfodor 12y ago
AFAIK RDS has turnkey read only replicas (and hot standby.)
devonnulled 12y ago
Hey there. I gave a talk at PGConfNYC '14 earlier this year about our solution to HA using open source solutions. The animations and stuff don't really work too well on Slideshare, but it should point you in the right direction: http://www.slideshare.net/TeamARIN/building-a-high-availability-postgre-sql-cluster http://www.slideshare.net/TeamARIN/building-a-high-availabil... pgSQL doesn't have anything built in for fencing, failing-over, etc. by default, but by using stuff like Pacemaker, you can get the job done with a little elbow grease.
- illumen 12y ago
  Do you have any scripts to set up a cluster for this? Or know where to look?
tracker1 12y ago
There are some open-source options, and several closed source options for this... parts are closer to baked in with 9.4, but being realistic, you need to defer to a commercial option. EnterpriseDB pricing for this isn't too bad (about $7k/cpu-socket/year), which is a lot less than MS-SQL, DB2 or Oracle for most uses... but it's imho a feature that should be in the box.
- jsmeaton 12y ago
  Pricing per socket really really frustrates me. I work in an Oracle shop where we're hamstrung by similar pricing.

by atonse 12y ago

arthursilva 12y ago

PG has async/sync/hybrid replication for years already. It's not as tooled as MongoDB but there's some tools like http://www.repmgr.org/ http://www.repmgr.org/ to amend it.

internetisthesh 12y ago
Every time I look into tools like this they are quite far behind for example AlwaysOn in MSSQL. For example, with PG I have to reseed the original master if it comes back online after an outage. It's as far as I can tell not fully automatic and transparent to me as the guy responsible for managing it. With SQL and elasticsearch+ZooKeeper for example nodes can go up and down without anyone noticing it and me not having to do anything. Is this still a place where PG is behind?
- tensor 12y ago
  Yes and no. There are differences in the data guarantees that something like elastic search and postgresql give. So it's not really appropriate to compare those. MS SQL does have easier tooling for replication. The setup for postrgresql is complex and it doesn't come with out of the box tools to easily manage failover and recovery as you mention. Progress is being made in making this easier, but it's still mostly in the low level functionality: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4#Replication_improvements https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_... https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#Replication_Improvements https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
- amitlan 12y ago
  > For example, with PG I have to reseed the original master if it comes back online after an outage pg_rewind[1] is supposed to fix this. It's moving into core for 9.5, by the way. [1] https://github.com/vmware/pg_rewind https://github.com/vmware/pg_rewind

en4bz 12y ago

Did you take a look at Slony-I? http://slony.info/ http://slony.info/

gfodor 12y ago

AFAIK RDS has turnkey read only replicas (and hot standby.)

devonnulled 12y ago

Hey there. I gave a talk at PGConfNYC '14 earlier this year about our solution to HA using open source solutions. The animations and stuff don't really work too well on Slideshare, but it should point you in the right direction: http://www.slideshare.net/TeamARIN/building-a-high-availability-postgre-sql-cluster http://www.slideshare.net/TeamARIN/building-a-high-availabil... pgSQL doesn't have anything built in for fencing, failing-over, etc. by default, but by using stuff like Pacemaker, you can get the job done with a little elbow grease.

illumen 12y ago
Do you have any scripts to set up a cluster for this? Or know where to look?

tracker1 12y ago

There are some open-source options, and several closed source options for this... parts are closer to baked in with 9.4, but being realistic, you need to defer to a commercial option. EnterpriseDB pricing for this isn't too bad (about $7k/cpu-socket/year), which is a lot less than MS-SQL, DB2 or Oracle for most uses... but it's imho a feature that should be in the box.

jsmeaton 12y ago
Pricing per socket really really frustrates me. I work in an Oracle shop where we're hamstrung by similar pricing.