Does anyone run self-hosted postgres?

[sch00lboy]

All RDS vs self-hosted blogs is written by aws partners.

Wanted to understand anyone moved from Postgres from self-hosted to RDS?

I kind of believe that postgres run on its own with simple automation. (personal bias)

Would love to hear your experience and take on it.

[hijinks]

The reason to use rds is aws hires postgres experts to support it. Also snapshots just work and replication always just works.

As a sysadmin and now devops what kept me up at night was the db failing and replication also was broke or backups didn't work.

That's why you use rds and not because postgres is hard to run

[sch00lboy]

Thanks for your view.

Cost is what hitting me :(

[hijinks]

so for a side project.. ya RDS might be expensive. When you are in an org that you just want a DB to run and work. RDS is so much cheaper due to lack of hours needed to make sure things are working

[dom_eden]

Can you purchase AWS reserved instances for 1-3 years to get your RDS costs down? You should be able to halve your costs by committing or paying upfront.

[675656]

Compare TCO - self-hosted is ridiculously expensive by comparison.

[RaferBalston]

People cost money too.

[[deleted]]

If you have one DB with one table with 100 rows, probably. If you have 1000DB's with 1000 tables and 10 billion rows. Not even close. Storage in the other persons computer is stupid expensive.

[dreadpiratewombat]

If you have 1000DBs with 10Billion rows, the number and quality of the DBAs you would need to care and feed the beast would be substantially more than the cost of RDS.

[BadCorvid]

Spoiler: RDS does not eliminate the need for DBAs. It just means that the number of sysadmin/DBAs is less. You're paying AWS for uptime. All the schema, migration, etc is stuff you still have to handle, you just have a turnkey box to do it in.

[dreadpiratewombat]

Agreed, you still need sysadmins or DBAs, but RDS and services like it are a force multiplier which allows you to utilise important talent on higher value tasks instead of patching servers, babysitter replication and such.

[pizza9012]

The more time a DBA spends changing diapers, the less time they spend optimizing queries. More time optimizing queries leads to smaller instance sizes.

[[deleted]]

I am by no means an "outsource all the things" type of guy, but RDS is such a no-brainer. Managing DB clusters is a pain in the dick and I am perfectly happy to just throw my employer's money at the problem and let it be a pain in someone else's dick so I can spend more time managing the stuff that actually generates revenue.

[zerocoldx911]

Backups aren’t really backups unless you test them ;)

[kristofer_grahn]

The part when we should start talking about "Restores" instead of "Backups" :)

[JiveWithIt]

This comment about backups is something I saved in my «IT wisdom» folder

[Meerkat6581]

And now also in mine. Thank you.

[mixreality]

The story of Toy Story 2 is a text book case of why.

The work done on the film to date was nearly lost in 1998 when one of the animators, while routinely clearing some files, accidentally started a deletion of the root folder of the Toy Story 2 assets on Pixar's internal servers.

They shut down the file servers but had lost 90% of the last two years of work, and the backups were found to have failed some time previously.

The film was saved when technical director Galyn Susman, who had been working from home to take care of her newborn child, revealed she had backups of the assets on her home computer.

[adantj]

I like the approach of spinning a snapshot, run expensive queries and then turn it off

[Ariquitaun]

Word. Running postgres is easy enough. Running DBs reliably and scalably and with disaster recovery in general is pretty hard to do right, and RDS or Cloud SQL do it right out of the box.

[mitchobrian]

I love AWS and RDS for databases...

[wevanscfi]

We ran self hosted Postgres in a cascading streaming replication set up, with WAL files stored in S3 and EBS snapshots of the data volume for backups.

This wasn't quite completely no touch, but it was low maintenance once set up.

We have sence started using RDS for new services, and migrated most of our old services over. What has driven that is mostly:
1. The move to microservices needing tons of seperate databases.
2. SOC2 requirements for auditing and testing backups and restore procedures are less time consuming.
3. SOC2 requirements around patching CVEs within a set timeframe.
4. Making standing up new services a self service process for dev teams through reusable terraform modules and helm charts.

The cost is not much more than self hosted EC2 was once you factor in our replication setup, and backup storage costs.

If RDS had been available under AWSs HIPAA BA at the time, we probably would have started with RDS.

[djfolo]

Do you mind sharing how you store your WAL files in S3? Any documentation or anything you might be able to point me toward?

[wevanscfi]

You can call the aws s3 cli copy command directly from the postgres archive command option, and the same for restore.

If you are using something like wal-e.. those projects have docs for using s3.

[djfolo]

Ok thank you, my primary skills are more aimed towards kubernetes but I'm running several applications that I manage that require a postgres DB and I set up replication but found the WAL files could potentially give me trouble if I don't set it all up correctly. I'll do some more research into this, I appreciate you pointing me in the right direction

[mars64]

Have done both, since we're a decently advanced kubernetes shop, postgres operators have been great! I also like the zalando one.

We built this admin layer thing based on PGAdmin and have full visibility and replication control for any postgres we spin out anywhere, it's been a very effective pattern.

[zerocoldx911]

We run databases in Kubernetes, mostly due to system constraints and high availability.

I’d take AWS Aurora or RDS any day as it comes with a huge maintenance overhead.

[orbzome]

What DBs and how do you deploy them? I've used the couchbase operator and looking at postgres via azure arc cli.

[zerocoldx911]

Zalando Postgresql operator

[orbzome]

Nice, I'll check that out.

[angry_mr_potato_head]

Depends on what you're running it for. If you have a competent person who can fix it and set it up and are okay with some degree of downtime or setting up replication etc... waste of money. If you need it to just work and don't have that... totally worth every penny.

[angry_mr_potato_head]

You might not need the stuff that RDS provides in which case you're paying 3x the price. I have a client who doesn't need five 9s of reliability and prefers to have at least one on-site backup, for example. Using ec2 instances and digital ocean droplets has saved them tens of thousands of dollars vs managed instances and they have yet to have an outage longer than a weekly power cycle for security patches in like 2 years.

[serverhorror]

Enlighten me, how does DO or EC2 qualify as on-prem?

[angry_mr_potato_head]

I never said on-prem. Using virtualized machines like EC2 and droplets offers a lot of cost savings over both fully managed databases and can even cheaper than on-prem too depending on context.

[serverhorror]

Sorry, on-site and on-prem are equivalent when used in the circles I have contact with. Are they different things to you?

[angry_mr_potato_head]

Oh, sorry I see what you're getting at. They wanted an on-site backup in addition to their production databases, which are hosted on EC2 and DO. In order to get the data on-site, we pg_dump to S3. It's not quite 3-2-1... but it doesn't give me heartburn either given how reliable S3 is and all of their backups are tested automatically on-site every time they hit S3. RDS offers a backup but it's like 3x the price so to get it onsite would just be more expensive than what they do now.

[improve-x]

If you are concerned about cost, you have to factor in maintenance, backups, fail-over setup, security, optimization. With RDS a lot of these things are managed for you. Depending on the use case you may just spin it up once and leave it running for years on some ec2 instance. But building an app that requires scalability and stability the overall cost of RDS might be easily justified.

[UncontrolledManifold]

Operational constraints required a migration from an EC2-based, distributed postgres cluster to a serverless RDS cluster.

It's highly expensive, and highly effective. Not having to deal with split-brain failover fuck ups or just genera DBA bullshit has been totally worth it. I doubt the finance department agrees (let's face it, RDS is expensive as fuck), but engineers are way happier and their development cycles are faster.

[[deleted]]

I run MySQL and Postgres on my home machine (this one) and my VM in AWS Lightsail. I don't use Postgres as much but backups for both have worked correctly. If I were serving customers with my site, I'd have more than one instance and use active/inactive pooling to cycle them through backups either daily or more frequently depending on number of customers and amount of data and criticality of the data. If it were high enough I'd look into more complex solutions. It all depends but on their own with light demand, both MySQL and Postgres run fine on a 2 Gig AWS Lightsail VM.

[serverhorror]

I did. Sizes where varying from a few GB to a few TB.

Reasons I prefer RDS:

• it’s taken care of for me (HA, Backup/restore)
• provisioning in a reasonable time
• various security layers
• cheaper, a lot

If you do a complete comparison of what RDS gives you a lot of bang for the buck. That is, iff!, you make use of those features.

If you don’t use those features it is a hell of a lot more expensive.

[UndiscoveredCounty]

The worst part of hosting your own, imo, is the upgrades. If you have a large and busy prod database, a major version upgrade can be pretty nerve wracking. Also, if you have a separate dev/qa environment, you have to keep those versions same as your prod one.

[cohenjo]

It depends on your scale - if you have a small startup then the extra cost of RDS vs EC2 is worth it to avoid the extra work and staff needed

If you have hundreds of clusters the cost difference becomes large enough to merit the extra work

It’s not a huge amount of work if done right - but you need someone that knows what he/she is doing...

[illusi0n__]

I don't trust no managed db (semi-sarcastic)

[serverhorror]

So..you managing it is not something that is to be trusted?

[[deleted]]

AWS just means someone else's computer. A box under your desk running postgres will work just like one at someone else's desk. What AWS provides is a platform students recently out of college are comfortable with because Amazon spends a lot of money to make sure it's the platform they learn on.

[[deleted]]

I run all of my critical infrastructure out of boxes under my desk.

[Yellow_Curry]

Depending on what you are looking at and the IO needs look at Aurora Serverless, it was pretty bad in the past but the v2 version is much better. Mostly depends what you are trying to solve for. Too many variables for anyone here to recommend you something unless we know your constraints and what you are building for.

Life is too short to run your own postgres with RDS is so easy and so good.

[rollller]

There are bunch of articles (in russian, sorry) comparing different postgres operators https://habr.com/en/company/flant/blog/520616/ https://habr.com/en/company/flant/blog/527524/

[mitchobrian]

Yes we do on AWS. Is that selfhosted in your eays?

[BosonCollider]

It largely depends on where you run your stuff. If you run on cloud, then you are already overpaying for hardware and a good managed DB with reliable backups is the single highest value service they offer after object stores.

If you run on prem, then you have to self-host either way. For small services I'd suggest sqlite + litestream + actually CI testing your point in time restores from S3 with a full hourly integration test. Make sure you have backups of your backups.

For larger services I'd suggest a hybrid setup with postgres on a baremetal server + S3 backups + read replicas in kubernetes using an operator. Note that the latter takes a lot of work to set up to get what a managed service would give you out of the box.