Should one of the first things you do with a database not be to have a backup strategy? I understand that HA would be a "nice to have" when first starting out, but surly if you have a production db, a backup and restore plan should be on a survival guide? Neither appear to be mentioned here.

What do you all use for your pg backups? Is Barman ( https://pgbarman.org/) still the way many do it? (I haven't deployed a new pg instance for a while, but thinking about it for a new project).

▲

mjr00 12 hours ago | parent | next [-]

I might get flak for saying this but if you aren't a postgres expert already: just use RDS or a similar cloud DB. The amount of money you're saving by hosting and managing your own postgres instance is absolute peanuts compared to having battle-tested infrastructure for HA, backup and restores, point-in-time recovery, read replicas, etc.

▲

dwedge 11 hours ago | parent | next [-]

At $dayjob we have the same mentality and as a result have a load of managed read replicas that are never used for anything (not reporting, not read only queries, not backups because $cloud handles it) that cost every month. Plus managed database restricts what you can do with the database - sometimes in really annoying ways.

So while I partly agree with you, a lot of companies don't really need HA, read replicas, or even PITR (though I would argue the last one is so trivial and cheap to enable that why not), but they click the expensive check box, and I would argue that companies who do need these features should consider hiring at least a couple of DBAs and get more flexibility instead of the current status quo of everyone being scared of the database and everyone just hoping cloud support will come to their rescue if ever needed

▲

drdexebtjl 11 hours ago | parent | next [-]

> hiring at least a couple of DBAs and get more flexibility

Every place I’ve ever worked at that had DBAs had the complete opposite of more flexibility. You have to do things the DBA’s way, and if their way doesn’t work for your service, you need to fight for their time and priority.

Meanwhile every place I worked at where every team completely owned their databases + did periodic data recovery drills had much more flexibility and no data loss.

	▲	sgarland 9 hours ago \| parent [-]
		That’s usually because they’ve seen a lot of failure modes over the years, and what seems fine to you can have surprising outcomes later. My personal experience has been the opposite of yours: lots of data inconsistency issues, data loss only resolved by the database team spinning up backups, etc. And somehow, even after yet another incident, there’s never been appetite to properly fix things.

▲

zdc1 6 hours ago | parent | prev | next [-]

You can use RDS without HA. Many people probably should do this, but no one wants to tell their boss they want to disable RDS HA and wear 1h of downtime a year so they can cut 50% of their spend.

▲

throwaway894345 11 hours ago | parent | prev [-]

> At $dayjob we have the same mentality and as a result have a load of managed read replicas that are never used for anything (not reporting, not read only queries, not backups because $cloud handles it) that cost every month.

Obviously "let RDS manage your database" doesn't require egregious read replicas. The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them.

▲

dwedge 10 hours ago | parent [-]

> Obviously "let RDS manage your database" doesn't require egregious read replicas

Of course not, but an easy checkbox, a best practice AWS or terraform guide and someone doing AWS certified X associate makes it easier to happen without anyone ever really discussing it.

> The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them.

Assuming you're talking about letting RDS manage anything, then sure - apart from it being more likely to slip through the net if nobody has to configure them. Database is just an expensive cost nobody necessarily drills into.

However if you mean the decision to let $cloud manage the replicas (and keep the primary managed), that totally depends on the cloud and the options. For example have you ever tried having a primary in GCP Cloud SQL but the replica not in cloud SQL?

	▲	throwaway894345 9 hours ago \| parent [-]
		> Of course not, but an easy checkbox, a best practice AWS or terraform guide and someone doing AWS certified X associate makes it easier to happen without anyone ever really discussing it. I'm defending your employer or their mindset, I'm just disagreeing with your claim that this follows from the parent's cost analysis claims. It _seems_ like your organization's problems are precisely because they _weren't_ doing the kind of cost analysis that the parent advocated. In other words, nothing in the parent's comment advocated for blindly following some Terraform guide. It feels unfair to the parent to suggest that their mindset caused your organization problems when it seems like your organization's problems were caused by _not having_ the parent's mindset.

▲

manphone 4 hours ago | parent | prev | next [-]

There’s a bunch of features in compatibility, performance, and cost reasons not to choose these cloud hosted managed databases.

▲

vanviegen 8 hours ago | parent | prev | next [-]

And then.. you're basically trapped inside the AWS cloud (due to egress costs and db latency). No thanks!

▲

10 hours ago | parent | prev | next [-]

[deleted]

▲

frollogaston 10 hours ago | parent | prev | next [-]

RDS is nice even if you just want basic functionality with backups.

▲

9dev 6 hours ago | parent | prev [-]

It’s really not that hard, especially with an AI agent to help you. Running a Postgres server and a read replica in Hetzner with pgBackRest backing up to their S3 buckets and a cloud volume can be had for under 50€, has HA, PITR, 3-2-1 backups, and will carry you through your series A comfortably, with GDPR compliance built-in.

What does the equivalent RDS setup cost you?

	▲	OrangeDelonge 2 hours ago \| parent [-]
		RDS gets you easy integration with the rest of the AWS services, which as a startup, you’re probably using quite a few of. Also, if you are nickel and diming over expenses in the 50€ range, you’re probably at the wrong startup.

▲

rsyring 13 hours ago | parent | prev | next [-]

FWIW, we use: https://pgbackrest.org/

Offers point-in-time recovery which is an improvement over a custom solution we used to have which gave us nightly backups.

We have it backing up to Backblaze B2 (S3 like). Was relatively easy to setup and no problems really.

▲

CodesInChaos 11 hours ago | parent | next [-]

There was some recent uncertainty about pgBackRest getting discontinued due to lack of funding. But the maintainer secured funding, and pgBackRest development will continue.

https://pgbackrest.org/news.html

▲

Tostino 13 hours ago | parent | prev [-]

Can't recommend pgbackrest enough. It's fantastic software, and I love the work they put into doing inter-file deltas for backups (so if 8kb of a 1gb file changes, you only backup the difference). It saved my last company a ton of money on storage while keeping good RTO/RPO.

▲

k_bx 10 hours ago | parent [-]

It's great, but it's not incremental like git, e.g. you do need to make a full backup periodically, unfortunately.

I didn't understand that, and after 5 months of usage caught my backblaze to be using 40TB, and nightly restores taking forever for other reasons. So: not ideal, and be careful to check!

	▲	Tostino 10 hours ago \| parent [-]
		Heh, yeah I suppose there are still some foot guns if you don't understand how things work.

▲

ComputerGuru 13 hours ago | parent | prev | next [-]

There’s no need to get all complicated and fancy or introduce more dependencies. For most people, a cron job calling pg_dump_all piped to zstd and copying the output to s3/ftp/whatever is plenty good enough.

Obviously past a certain point carting around full backups becomes time/dollar prohibitive, but this can take you very far.

▲

rsyring 13 hours ago | parent | next [-]

FWIW, we started with a system that was essentially this. We eventually moved to pgbackrest and it wasn't any harder to setup. But the ROI on that investment is a lot higher because pgbackrest does a lot more for us than the home rolled solution.

Having done both, I'd recommend just starting with pgbackrest.

▲

Scarbutt 13 hours ago | parent | prev [-]

If you can afford to lose the data created between backups, sure.

▲

lobo_tuerto 12 hours ago | parent [-]

Better than losing all the data created between no backups.

▲

Tostino 12 hours ago | parent [-]

But the other option is just doing it right from the start and using a tool like pgbackrest. It's no harder to setup, and it puts you into best practices by default rather than having to work at it later.

I just don't understand why people seem so drawn to the bad solution just because it ships with the database.

▲

saltcured 10 hours ago | parent | next [-]

I think you need to analyze the disaster scenarios and recovery costs you are trying to balance before you can really evaluate these different options.

If you are on reasonable storage, I think it is arguable that the main reason you would have to recover is due to a software failure leading to corruption of the PostgreSQL backing state files. Otherwise, you would simply be restarting PostgreSQL on your durable and available storage. So, if the content has been corrupted by bugs, can you trust the recent WAL log in this scenario? Or can a plain dump be a more reliable "known-good" database recovery point?

And what is the business cost to rolling back to a less frequent dump such as nightly? Not every DB is some kind of multi-party OLTP ledger. Sometimes, a day of lost "new data" may be just a day of labor to repeat some data entry or other repeatable work...

A really robust contingency plan probably has both of these kinds of backup, and scenarios where one recovery versus the other is attempted. But if you are starting at a very small scale, hosted on some reasonable cloud storage like EBS, a cron-based dump that goes into a normal hierarchical file backup system may be totally sufficient for the extreme disaster scenario where you cannot simply restart your DB server on top of the surviving and available storage volume?

▲

Tostino 10 hours ago | parent [-]

Using something like EBS for your postgres data directory is, IMO, a bad idea. If you've already made that mistake, yeah I suppose using PG dump isn't that bad.

I'd much rather just do it right though.

Direct attached storage (or whatever storage is fastest / lowest latency for the environment you have available).

Setup pgbackreset or barman, use WAL archiving and setup block incremental backups with a new full backup every so often.

Now you have point in time recovery with very low RPO/RTO, and your database infrastructure can be treated a bit more like cattle instead of a pet, as you should be testing restores often, and this will become part of your yearly major postgres upgrade strategy.

You also get the ability to have a replica for almost free, as replicas can be created from the backup repo very cheaply, and get caught up to the primary without requiring the primary keep around the WAL for a long time. It just pulls it from the repo until caught up.

Any case where you think EBS was the right choice is better handled by having one (or more) replicas and a failover strategy.

Just do the right thing from the start and you save a lot of headaches. People have run production systems already, and have hit the pain points. Why keep hitting the same ones?

▲

outworlder 7 hours ago | parent [-]

If you are on AWS, the direct attached storage option you have is ephemeral volumes. That's bad. If the hypervisor fails you lose data. Which is fine, you have replicas. If there's an event that takes out multiple machines at once, even for a moment, you are hosed(this can be AWS issues, or could be as simple as automation misbehaving and shutting machines down).

I'm all for treating DB as cattle, but your cattle needs to be able to survive long enough.

EBS is expensive but it works for PG. AWS uses EBS for RDS databases. Calling that a 'mistake' is a tall order.

	▲	jashmatthews 5 hours ago \| parent \| next [-]
		If it works for you it’s great but the RDS EBS limitations are real enough… AWS built Aurora and for RDS a new 3 node topology using local SSDs for writes and EBS only for the data directly.
	▲	Tostino 6 hours ago \| parent \| prev [-]
		[flagged]

▲

dwedge 11 hours ago | parent | prev [-]

To play devils advocate, something that doesn't ship with the database is harder to setup than something that does

▲

CodesInChaos 11 hours ago | parent | prev | next [-]

An atomic volume snapshot should work for any database that's durable on power failure. Ideally preceded by a checkpoint, to minimize recovery time. Atomicity of the snapshot mechanism is essential to prevent data corruption using this approach.

We used EBS snapshots on AWS for multi-TB MongoDB to get incremental backups that are fast to create and fast to restore (with some performance degradation after restore).

It doesn't support point-in-time recovery, but since it's fast you can create frequent snapshots (e.g. hourly). I'd consider adding this as a secondary backup strategy, even if you use a higher-level postgres-specific backup tool.

▲

geoka9 10 hours ago | parent | prev | next [-]

If you already run k8s, why not just use cnpg?

https://github.com/cloudnative-pg/cloudnative-pg

▲

hoppp 12 hours ago | parent | prev | next [-]

Backups are mandatory for any serious deployment. But it's more devops and the guide is more about SQL layer.

This guide is only satisfactory if the database is managed, otherwise there are a whole bunch of things going on.

▲

pphysch 13 hours ago | parent | prev [-]

pgdump / pgrestore, using native binary format