7 ms·
Why PostgreSQL High Availability Matters and How to Achieve It
- BrentOzar 3y agoOr, uh, just use AWS RDS Aurora.
- Topgamer7 3y agoOr, uh, just self host.
- whitepoplar 3y agoWhat's the self-hosted Postgres HA story these days?
- porsager 3y agopg_auto_failover makes it an absolute breeze. I cannot understand how it's not mentioned in the article. I've been runnning it for over 3 years with great success, and 0 downtime. Not to mention the extreme price difference compared to managed solutions - https://github.com/hapostgres/pg_auto_failover/discussions/618 https://github.com/hapostgres/pg_auto_failover/discussions/6...
- whitepoplar 3y agoHow are major version upgrades handled on PGAF these days? Last time I used it (it's amazing), there wasn't a great path forward for minimal-downtime major version upgrades.
- paulddraper 3y agoSame. But major upgrades are only available once a year.
- mistrial9 3y agozero downtime for three years - congrats !
- candiddevmike 3y agoWhat about BU/DR? Is walg still the best?
- vbezhenar 3y agoI hosted postgres with cnpg operator in kubernetes. It does everything automagically: backups, wal backups and high-availability. Can't say nothing bad about it, it just worked for me. My only issue is that it releases versions too fast, I'd prefer some more relaxed schedule, but that's probably just me.
- franckpachot 3y agoOn self-hosted you can use the same as what cloud vendors are doing. Patroni or pg_auto_failover to manage single-primary + replicas. Maybe Neon to run an Aurora-like. CitusData... Azure did a lot around to get elasticity. Not easy to do the same. YugabyteDB on Kubernetes can be a cloud-native self-hosted solution
- whitepoplar 3y agoWhich cloud vendors are using PGAF?
- rad_gruchalski 3y agoYugabyteDB can be self-hosted.
- dmagda 3y ago[dead]
- chzblck 3y agoHow did that work out for people in the east region yesterday?
- deleted 3y ago[deleted]
- proleisuretour 3y agonot good lol
- proleisuretour 3y agoyeah... no burgers or tacos https://www.theverge.com/2023/6/13/23759857/amazon-aws-down-outage-taco-bell-mcdonalds-burger-king https://www.theverge.com/2023/6/13/23759857/amazon-aws-down-... that's WHY you need at least a multi-region HA database for your apps
- cebsoto 3y agoNo Roomba either. No multi-tasking housework while you work.
- paulddraper 3y agoPerfectly for me. (Maybe if I had used IAM auth for the database it wouldn't have?)
- franckpachot 3y ago“Multi-AZ” in Amazon RDS and how it may differ from High Availability: https://dev.to/aws-heroes/multi-az-in-amazon-rds-and-how-it-differs-from-high-availability-gn9 https://dev.to/aws-heroes/multi-az-in-amazon-rds-and-how-it-...
- rad_gruchalski 3y agoThis is a completely different story. YugabyteDB is about transparent sharding and shard horizontal scalability. Every table is split into tablets. A tablet can be thought of as a shard but one still thinks of a table as a whole. Each tablet is replicated. Replicas can be placed in different regions, even in different computing environments. Want to run it across clouds or span the cloud and bare metal? Ignoring the cost and minding latency, why not. It’s still logical single database with whole tables. Want to put some rows in selected regions? No problem, still one logical table. Very different from master-replica architecture.
- proleisuretour 3y agopretty interesting ... covers replication strategies and tools, read replicas, coordinators, sharding, and the new tech stack approach... job well done
- deleted 3y ago[deleted]
- conqrr 3y agoVery Interesting. Glossing over the article, it states HA within a Region. Curious if anyone is successful in running any Database with multi-region HA? How are you doing it?
- chzblck 3y agoThere's another article from them that covers it https://www.yugabyte.com/blog/exploring-multi-region-database-deployment-options-with-a-slack-like-corporate-messenger/ https://www.yugabyte.com/blog/exploring-multi-region-databas...
- audioheavy 3y agoFauna can do multi-region HA, across clouds, serverless, with transparent sharding and replication. We get plenty of people struggling with not being able to do low-latency, performant, strict serializability distributed writes. It is designed to be a turnkey hosted solution. https://dev.to/luiseduardocolon/fauna-deep-dive-architecting-a-distributed-serverless-database-307a https://dev.to/luiseduardocolon/fauna-deep-dive-architecting... - Luis
- electroly 3y agoIn SQL Server you can use a multi-subnet failover cluster. https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/sql-server-multi-subnet-clustering-sql-server https://learn.microsoft.com/en-us/sql/sql-server/failover-cl...
- bob1029 3y agoWe are going down the path of using Azure SQL Hyperscale to address availability concerns: https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale?view=azuresql#database-high-availability-in-hyperscale https://learn.microsoft.com/en-us/azure/azure-sql/database/s...
- I_am_tiberius 3y agoI wished AWS, Google and others would also add a Citus solution. I fear that once migrated to Citus, we face the full price pressure from the Azure monopoly.
- zorgmonkey 3y agoGoogle will never touch Citus because it is AGPL licensed[0], I wouldn't be surprised if the other cloud providers have similar policies. [0] https://opensource.google/documentation/reference/using/agpl-policy https://opensource.google/documentation/reference/using/agpl...
- benglish11 3y agoCitus is owned by Microsoft as well. https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquires-citus-data-re-affirming-its-commitment-to-open-source-and-accelerating-azure-postgresql-performance-and-scale/ https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquir...
- I_am_tiberius 3y agoYes, the company, but the plugin is some sort of open source.
- znpy 3y agoOpen source stuff are still subject to ownership via copyright. The license allows you to do many things, but not everything. The copyright owner on the other had, can do everything. That’s why many projects nowadays require you to assign copyright to the team before accepting a contribution.
- chungy 3y agoI personally don't consider the AGPL to be a free software nor open source license. The burden it places on operating and using the software sufficiently violates freedom 0 in my view.
- vocatan 3y agoAnother option for open source aficionados might be https://www.pgedge.com https://www.pgedge.com - with their spock extension they also achieve multi-master replication. Looks very interesting!
- iotapi322 3y agoI just implemented master with read replica with the bitnami postgres-repmgr image. It's not perfect, but it works and running my own instances in aws instead of rds is going to save me close to 80% when i also add in purchasing a savings plan. By setting this up I've learned more about postgres than i ever ever wanted too. lol
- sunderous 3y agoJust a heads up, we were using the bitnami charts in our first attempt at an HA postgres instance in our k8s cluster, and we're currently figuring out how to move away from them because they had so many issues. Granted, we might've messed something up and there are lots of factors, but you should manually check your individual nodes every so often to make sure none of them are going out of sync. Need to connect to each one directly, can't rely on what the bouncer/pgpool/loadbalancer is showing you to check the data is the same. It happened repeatedly to us, and wasn't obvious from any of our monitoring. In the end we had to scale down to 1 node while we sort out moving to a different operator.
- iotapi322 3y agointeresting thanks for the heads up, i really appreciate it! I do need to find some sort of monitoring to ensure the db's are staying insync.
- rad_gruchalski 3y agoGood product. Used it for a multi tenant implementation. Pretty easy to get started with, can get hairy before reaching operational excellence but when running, it’s pretty solid. I like it because it’s Postgres under the hood. Even extensions work. Happy self-hosting “customer”.
- merb 3y agoone of the solutions which made it pretty simple for us to run postgresql in a ha environment (mostly in k8s, but works standalone as well) is zalandos patroni: https://github.com/zalando/patroni https://github.com/zalando/patroni it's really solid and worked for us for a few years already. (it also comes with a haproxy config to have a single leader connection) or for k8s their operator: https://github.com/zalando/postgres-operator https://github.com/zalando/postgres-operator (docker image: https://github.com/zalando/spilo https://github.com/zalando/spilo) we've also tried other operators which were easier to get started, but they failed miserably (crunchyrolls operator is basically based on the zalando one)
- urza 3y agoHow does patroni can do some self healing after primary db gets down and then up again? Or is manual intervention required? How does it look?
- merb 3y agomanual intervention is not required. Patroni will just use a Standby as the new master. If the old master will be alive again it will be started as a backup. Of course there might be dataloss if it was used with async replication. Patroni has great docs for this here: https://patroni.readthedocs.io/en/latest/replication_modes.html https://patroni.readthedocs.io/en/latest/replication_modes.h... (Actually this is more or less a thing to do with Postgres)
- endisneigh 3y agoIt’s amazing that this isn’t a solved problem, but we have all of this crazy language model stuff. Unfortunately Spanner isn’t open source. Yugabyte and Citus are close but have annoying issues. Cockroach isn’t 100% compatible (and has its own issues) and things like FoundationDB which are truly HA and comparable to Spanner in terms of consistency and fault tolerance are not easily plugged into Postgres as the underlying storage engine since sadly it’s only a key value store. edit: when I say close, I'm talking strictly about HA, not general functionality. lately I've been thinking of using FoundationDB, which is closest to Spanner in terms of ACID and serializability and mvsqlite. Then, I was thinking, since SQLite doesn't have online schema changes (nor does mvsqlite) to have a schema such as: [UUID, Data, Version, CreatedAt, UpdatedAt] Where Data is a JSON or Proto and Version is an integer. You then could mimic an online schema change by in your application code supporting two adjacent "versions", and then in an eventually consistent manner run [small] transactions to update the Data to the new Version as necessary. You would index Version, and UpdatedAt as necessary to find the rows in the table that are not "migrated." In SQLite you can also create indexes on expressions so technically all of your JSON or Proto could also have indexes.
- roncesvalles 3y agoHonestly, just use Spanner. It's the perfect database*. If you need to trade freshness for savings, put a cache in front of it. If I were building a new startup in 2023, I would need a mountain of evidence against using Spanner. It's ugly that it locks you into GCP but hey an iPhone locks you into Apple's ecosystem, that's just the price you pay to get good things. * unless you need timeseries, columnar, FTS, geospatial, graph or something special like that
- hot_gril 3y agoI've built my own little pile of evidence against Spanner using it for several years, after previously being on Postgres. It has good potential but isn't there yet. And if you're making a startup, you're probably a long way from the scale where sharded DBs become necessary.
- hot_gril 3y agoGreat summary. Multi-node relational DBs are very much double-edged swords. This article mentions the potential data loss during failover events. I've complained in the past that Heroku Postgres advertises HA like a strict improvement and leaves the actual failover mechanism in the very fine print; the standby master is async, so you can lose data. There's no free lunch here, you either tolerate some data loss or tolerate extra latency before commits. When you get into sharded DBs, a lot of limitations aren't obvious at first. I've used Citus a long time ago and Spanner much more recently. Spanner feels almost like NoSQL: Each table is like a distributed KV store. Indexes are just tables where the PK is the indexed col(s) and the val is the main table's PK, though you can also store additional cols (aka denormalize) in the index to avoid an extra join. You can't combine two indexes to filter on two cols; you need one composite index. More advanced things like order-limit, WHERE (NOT) IN, and subqueries tend to be slow. The query planner is pretty limited, and often I just force things. You also have to really know what you're doing with the pkeys. Which is all understandable, given its requirements. I forget the limitations of Citus ACID, but they're significant. Spanner has full ACID, but even basic operations are quite slow. Single-node Postgres actually doesn't have full ACID unless you put it in the slower SERIALIZABLE mode, but you don't really need it. I agree with those who say it's better to focus on sharding at the application layer if possible. If you can't do that, it's probably due to the underlying nature of your problem, in which case sharding at the DB layer in an efficient way tends to be even harder. Sometimes it makes sense, but it's not magic.
- azurelake 3y ago> You can't combine two indexes to filter on two cols; you need one composite index Can you expand on how you perceive traditional RDMSs to be different for this case?
- hot_gril 3y agoCREATE TABLE foo (id bigserial, bar int, baz int); CREATE INDEX foobar ON foo(bar); CREATE INDEX foobaz ON foo(baz); SELECT \* FROM foo WHERE bar = 2 AND baz = 4; Postgres (and I think MySQL) will use both indexes in the above query*. Spanner can only use one index, which will be slow if there are many non-matching bar=2 or baz=4 rows. So Spanner needs CREATE INDEX foobarbaz ON foo(bar, baz); Which Postgres could also use, and it'd be a bit faster, but the index-combining is decently fast too and much nicer when you consider a table with like 10 cols and many ways to filter/join. * https://www.postgresql.org/docs/current/indexes-bitmap-scans.html https://www.postgresql.org/docs/current/indexes-bitmap-scans...
- JoshGlazebrook 3y agoSomewhat related, but I'm looking at hosting solutions for a hobby SaaS I'm building, and I would like to use google cloud run, but the cloud sql pricing seems extremely expensive if you want HA and are just starting up. They also don't seem to offer any cloud sql as part of their free like like AWS does with RDS, it just seems very odd. I looked at Planetscale thinking I might be willing to just use MySQL, but even if I were to take a risk with their confusing pricing it turns out there are no foreign keys. And there is no equivalent of Cloud run on AWS. It just seems like there is a gap here in when it comes to managed databases, not just GCP laking any kind of "free tier" option, but just a lack of players in the space between free and established business pricing for HA.
- samlambert 3y agoWe have FKs and new pricing coming soon.
- JoshGlazebrook 3y agoI've been reading this on reddit for quite a while, what quarter might "soon" be?
- endisneigh 3y ago> It just seems like there is a gap here in when it comes to managed databases, not just GCP laking any kind of "free tier" option, but just a lack of players in the space between free and established business pricing for HA. Use serverless cockroachdb. Yugabyte also has a free tier. As does Fauna and Planetscale.
- 5Qn8mNbc2FNCiVV 3y agoIf it's a hobby SaaS you are best of just using docker-compose on some VM and pgBackRest. If you have multiple hobby projects, use different databases in the same instance. You can always pull out a single one when issues arise. Make your life easy, tons of applications are running for years even without (good) backups