PostgreSQL backup retention: how many backups to keep (GFS explained)

Every backup setup has to delete old backups eventually, and the rule you pick decides how far back you can go when something goes wrong.

The problem with "keep the last 7"

Keeping the last 7 nightly backups is simple, and it protects you against anything you notice within a week. But plenty of problems surface late. A bug quietly corrupts a column, a customer reports missing records a month later, or someone asks what a record looked like before last quarter's migration. With seven daily files, the oldest copy you have is a week old.

Keeping more daily files works but gets expensive fast: 365 nightly copies of a 2 GB database is 730 GB.

Grandfather-father-son (GFS)

GFS keeps fewer backups the further back you go:

• Daily: the newest backup of each of the last 7 days
• Weekly: the newest backup of each of the last 4 weeks
• Monthly: the newest backup of each of the last 12 months
• optionally Yearly: the newest of each of the last few years

That's at most 7 + 4 + 12 = 23 files covering a year. Several rules often pick the same file (last night's backup is also this week's and this month's), so the real count is usually lower. A backup is kept if any rule keeps it.

Picking numbers

A GFS cleanup script for Windows

This PowerShell function keeps the newest backup per day, ISO week and month for the counts you give it, and deletes the rest. It reads the date from the file name name-yyyyMMdd-HHmmss.dump that the main backup script writes:

function Remove-OldBackups([string]$Dir, [string]$Database, [int]$Daily = 7, [int]$Weekly = 4, [int]$Monthly = 12) {
    $pattern = '^' + [regex]::Escape($Database) + '-(\d{8}-\d{6})\.dump$'
    $files = Get-ChildItem $Dir -File | Where-Object { $_.Name -match $pattern } | ForEach-Object {
        [pscustomobject]@{ File = $_; Taken = [datetime]::ParseExact($Matches[1], 'yyyyMMdd-HHmmss', $null) }
    } | Sort-Object Taken -Descending

    $keep = @{}
    function Keep-Newest($groupKey, [int]$count) {
        $files | Group-Object -Property $groupKey | Select-Object -First $count |
            ForEach-Object { $keep[$_.Group[0].File.FullName] = $true }   # groups keep newest-first order
    }
    Keep-Newest { $_.Taken.ToString('yyyy-MM-dd') } $Daily
    Keep-Newest { $_.Taken.Date.AddDays(-(([int]$_.Taken.DayOfWeek + 6) % 7)).ToString('yyyy-MM-dd') } $Weekly  # week's Monday
    Keep-Newest { $_.Taken.ToString('yyyy-MM') } $Monthly

    $files | Where-Object { -not $keep[$_.File.FullName] } | ForEach-Object {
        Remove-Item $_.File.FullName
        Write-Output "Deleted $($_.File.Name)"
    }
}

Remove-OldBackups -Dir 'D:\PostgresBackups' -Database 'shop' -Daily 7 -Weekly 4 -Monthly 12

It works in both Windows PowerShell 5.1 and PowerShell 7. Weeks start on Monday. Call it in place of the "delete older than N days" lines, and only after a backup succeeded. That way a run of failures never deletes your last good copies.

Don't forget: retention isn't off-site

GFS decides how many copies you have, not where. Twenty-three backups on the same disk as the database still disappear together. Keep at least the weekly and monthly copies somewhere else. See off-site PostgreSQL backups with OneDrive.

How PostgresBackup does retention

Each PostgresBackup job has Keep last N files plus keep hourly, daily, weekly, monthly and yearly rules. A backup is kept if any rule keeps it, and setting every rule to 0 keeps everything. Old files are pruned right after a successful backup, and a job only ever touches its own files, even when several jobs share a folder. The job list shows each policy at a glance, for example "Last 7 · 7d · 4w · 12m".