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 16 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 15 hours ago | parent | prev [-]

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

▲

lobo_tuerto 15 hours ago | parent [-]

Better than losing all the data created between no backups.

▲

Tostino 15 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 13 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 13 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 10 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 8 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 9 hours ago \| parent \| prev [-]
		[flagged]

▲

dwedge 14 hours ago | parent | prev [-]

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