CODEWITHSUNDEEP
postgresql
streetcornerlurker
streetcornerlurker

Reputation: 501

Is there any value in keeping more than one PostgreSQL base backup?

I have a database that was configured by someone else, there's a cron script to take a basebackup every morning, and currently we are keeping 15 days of basebackups. As I understand it, when pg_basebackup successfully completes, the database deletes WAL files that are older than the basebackup generated, which means that point in time recovery can only be achieved as of the last basebackup (unless you want to restore the database to the exact point in time of a previous basebackup, I guess).

It seems to me that this means that there's no point in keeping any basebackups older than the last successful one. Does anyone have any advice on this? The basebackups are taking up too much space on a smallish server and either I need to delete some of them or I need to increase the size of the server, and hence the cost.

Upvotes: 1

Views: 206

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324395

Yes, there is a point; as Drew notes, it'll help you if you didn't notice for a while that there's undetected table corruption, accidental data deletion, etc.

Rather than keeping frequent base backups, consider using the free and open source PgBarman tool to manage continuous archiving for you. Set a retention period, take less frequent base backups, and use the WAL (write-ahead log) archives to "replay" database changes onto a base backup to acheive a restore from a target point-in-time.

(Disclosure: I work with the team who develop barman, though I don't work on it myself).

Also, if the base backups are on the same server as the database you're doing it wrong. Get them offsite, or at least on a different host (preferably attached to durable storage inside a flood-resistant fire-resistant safe).

Upvotes: 2

Drew
Drew

Reputation: 94

If something goes wrong with the data (let's say some sort of corruption) and it goes unnoticed for a few days or just overnight. You might end up with data loss if you only keep the last backup. Or if the last backup was somehow bad, you'd be in trouble.

For example if a table contains records that are accessed not so frequently like once a week for reporting or once a month and someone deleted some of them on accident then you'll be short a few records and shy a few days of backup protection.

You'll probably have to look at what's in the database and decide how many you want. Maybe just keep 7 and every week do a backup recovery to verify the last backup is usable. And then some scripts to verify the integrity of important data and some email notifications for information about the whole shebang.

Upvotes: 4

Related Questions