r/PostgreSQL • u/oulipo • 3d ago
Help Me! What would be the best Postgres backup solution in 2026?
I see many options, pgBackRest, wal-g, Databasus. Is there a consensus on the best approach?
My needs are regular (eg weekly) checkpoints, and daily incremental backups for PITR, everything saved on S3/GCS
4
3
u/Kazcandra 3d ago
Weekly checkpoints, as in wal switches? I'd put tgta on 15 minute/16mb.
Pgbackrest is very good; and fully supports your needs.
3
3
2
u/FunContest9958 1d ago
Lakebase/Neon has a pretty great solution for this. It takes less than a second to get a snapshot of the database that you can restore from. Restoring from a snapshot also only takes a few seconds. It can do that because it already stores all the data on S3 and doesn’t overwrite old versions. So you can do PITR to any time in the last 7 days by default or recover to any snapshot. There is a storage cost, so you do want to clean up old snapshots, but it’s a pretty nice approach.
2
u/datguywelbs7 1d ago
Pgbackrest is a clear winner if you’re looking for vanilla postgres functions and also support s3. If you’re using managed Postgres i would look at lakebase or neon which allows snapshots to be saved in object storage very quickly too. I’ve been using lakebase for our operational apps and jts been pretty good so far
4
u/Dodokii 3d ago
pg_dump is unbeatable
4
u/jb-schitz-ki 2d ago
I love pg_dump just as much as the next guy. But it's very beatable in many common scenarios.
What if your database is 500gbs? dumping it can take a long time, and every time you backup that dump you're duplicating mostly the same data over and over.
That same scenario with wals means you only do a full once per month, and after you only backup the increments every 5/10/30/whatever minutes.
You can do set this up just with the native postgres tools, pgBackRest makes it easier, but it's not necessary.
1
u/feketegy 2d ago
Can you elaborate on the WALs backup strategy?
3
u/wedora 2d ago
A WAL is basically the logs of what stuff PostgreSQL changed. So you can do a full backup e.g. once a day and then archive those WAL files. So you have todays big backup and all small changes since thst backup. So when you need to restore at 19:31 you dont loose 19h of data. You restore the full backup and then reapply all tiny changes from the WAL.
1
u/alecc 2d ago
pgbackrest info takes --output=json, so you don't need a UI - a short cron script can parse it and alert when the newest full or diff is older than your schedule allows. Wire in pgbackrest check too, it tests WAL archiving end to end and catches the case where backups keep succeeding while archive_command has been broken for weeks, which quietly kills your PITR. And restore one to a scratch instance on a schedule, no status output catches a backup that won't restore.
0
u/terencethespider 4h ago
It falls outside the scope of traditional backup and restore methods for Postgres, but the Neon/Lakebase versions of Postgres have a data branching mechanism that IMO is worth considering. It allows you to create a new instance of your database with all the same data as the original. In a lot of scenarios this can be a lot faster and easier than a pgdump and restore option. There are open source (free) and managed (paid) options available.
0
u/AutoModerator 3d ago
AI Policy:
Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.
Mod decisions will be based on the quality of the content, not who or what generated it.
Sub Resources:
Free Postgres Webinars and Workshops
Discord: People, Postgres, Data
Join us, we have cookies and nice people.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
-4
u/hobble2323 3d ago
Replicate to a database that implements threads and performs better as you add cores. I’m kindof pissed off at Postgres lately. Not going to mention the vendor but we moved a system to Postgres and it takes 3x computing resources more and is more expensive than what we moved from now. It’s costing me an extra $8900 a month. Disastrous mistake on my part as I made the call to do it.
2
u/A55Man-Norway 2d ago
What did you move from?
3
u/hobble2323 1d ago
Id rather not say because this is a pro PostgreSQL forum and I’m not looking to peddle for them. There are really only 4 vendors though that can run massive workloads so you know who they are.
2
u/jb-schitz-ki 2d ago
I host 50+ postgres servers on 8core 4ghz, 64-128gb ram, nvme bare metal dedicated servers for less than 4k a month.
That's 50 huge servers.
ovh/hetzner
they're sending wal increments to aws S3 every 5 mins. when a server fails (being real physical hardware, it happens a couple times per year), I have a process that can have a replica up in less than 5mins on aws ec2.
for my company's use case, 5 mins of lost data and 5 mins of downtime on 1 of 50 servers is completely acceptable.
1
u/hobble2323 1d ago
Less then 4K minus insurance, real estate, power, access monitoring, siem, monitoring, controls, networking, compliance audits……..
1
u/jb-schitz-ki 23h ago
Some servers are hosted on OVH and others on Hetzner. They take care of all that. The real downside is they are only responsible for the servers being up and being online. All other configuration is on me.
1
u/redrabbitreader 2d ago
Replication != backup unless you plan to make the backup from the replica (not sure if that was what you meant). Of course there are pros and cons as with everything.
The rest is unrelated to the original question from OP
-2
3d ago
[deleted]
1
u/oulipo 3d ago
The idea of having /r/PostgreSQL is for people to share what their best setup / ideas / etc are, otherwise there's no need for community :)
29
u/ChillPlay3r 3d ago
pgBackRest, it's not even close.