Backup Policy
The chart below shows the “Recovery Window” and “Backup Storage Usage” on one timeline (0~108h).
Use it to compare the two metrics.
The 30-day example uses a database size of 100GB and daily writes of 10GB.
It shows both metrics with weekly full backups, daily incremental backups, and 14-day full-backup retention.
- When: Backup schedule
- Where: Backup repository
- How: Backup method
When to Backup
First, choose when to back up the database. Backup frequency affects recovery time. Recovery replays WAL from the last backup to the target point. More frequent backups reduce the amount of WAL to replay and shorten recovery.
Daily Full Backup
For production databases, we recommend a daily full backup as the simplest initial policy. This is also Pigsty’s default backup policy. Pigsty implements it through crontab.
Assume a database size of 100GB, daily writes of 10GB, and a duration of 1 hour for each full backup. The chart below shows the recovery window and storage usage for this daily full backup policy with a local repository.
The recovery window cycles between 25-49 hours, and storage usage is roughly 2 full backups plus around 2 days of WAL archives.
In practice, prepare at least 3~5 times the base database size as backup disk capacity for the default policy.
tooltip: { trigger: axis, formatter: $fn:tipMerged, axisPointer: { type: line, snap: true, label: { show: false } } }
axisPointer: { link: [ { xAxisIndex: [0, 1] } ] }
legend: { show: false, bottom: 10, itemGap: 18, data: ["Primary Backup", "Secondary Backup", "WAL Archive", "Transient Backup"] }
grid:
- { left: 82, right: "10%", top: 42, height: 218, containLabel: false }
- { left: 82, right: "10%", top: 286, height: 218, containLabel: false }
xAxis:
- type: category
gridIndex: 0
position: bottom
boundaryGap: false
data: [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108]
name: Time h
nameLocation: end
nameGap: 10
nameTextStyle: { align: left, verticalAlign: top, padding: [8, 0, 0, 0] }
axisLabel: { interval: 11, formatter: $fn:fmtHour }
axisLine: { show: true, symbol: [none, arrow], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
axisTick: { show: true, length: 6 }
splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.28, color: "#9ca3af" } }
minorTick: { show: true, splitNumber: 12, length: 3 }
minorSplitLine: { show: true, lineStyle: { type: dotted, width: 1, opacity: 0.14, color: "#9ca3af" } }
- type: category
gridIndex: 1
position: top
boundaryGap: true
data: [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108]
axisLabel: { show: false }
axisLine: { show: true, lineStyle: { width: 1.6, color: "#4b5563" } }
axisTick: { show: true, length: 6 }
splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.22, color: "#9ca3af" } }
yAxis:
- type: value
gridIndex: 0
min: 0
max: 52
interval: 5
name: Recovery Window h
nameLocation: end
nameRotate: 0
nameGap: 8
nameTextStyle: { align: left, verticalAlign: bottom, padding: [0, 0, 8, 4] }
axisLabel: { formatter: $fn:fmtWin }
axisLine: { show: true, symbol: [none, arrow], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
axisTick: { show: true, length: 6 }
splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.35, color: "#9ca3af" } }
minorTick: { show: true, splitNumber: 5, length: 3 }
minorSplitLine: { show: true, lineStyle: { type: dotted, width: 1, opacity: 0.18, color: "#9ca3af" } }
- type: value
gridIndex: 1
min: 0
max: 350
interval: 50
inverse: true
name: Backup Storage GB
nameLocation: end
nameRotate: 0
nameGap: 8
nameTextStyle: { align: left, verticalAlign: top, padding: [10, 0, 0, 4] }
axisLabel: { formatter: $fn:fmtGbTick }
axisLine: { show: true, symbol: [arrow, none], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
axisTick: { show: true, length: 6 }
splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.32, color: "#9ca3af" } }
series: [ { name: Recovery Window, type: line, smooth: false, symbol: none, showSymbol: false, xAxisIndex: 0, yAxisIndex: 0, lineStyle: { width: 3, color: "#f2a000" }, itemStyle: { color: "#f2a000" }, data: [[0,0],[1,0],[1,1],[2,2],[3,3],[4,4],[5,5],[6,6],[7,7],[8,8],[9,9],[10,10],[11,11],[12,12],[13,13],[14,14],[15,15],[16,16],[17,17],[18,18],[19,19],[20,20],[21,21],[22,22],[23,23],[24,24],[25,25],[26,26],[27,27],[28,28],[29,29],[30,30],[31,31],[32,32],[33,33],[34,34],[35,35],[36,36],[37,37],[38,38],[39,39],[40,40],[41,41],[42,42],[43,43],[44,44],[45,45],[46,46],[47,47],[48,48],[49,49],[49,25],[50,26],[51,27],[52,28],[53,29],[54,30],[55,31],[56,32],[57,33],[58,34],[59,35],[60,36],[61,37],[62,38],[63,39],[64,40],[65,41],[66,42],[67,43],[68,44],[69,45],[70,46],[71,47],[72,48],[73,49],[73,25],[74,26],[75,27],[76,28],[77,29],[78,30],[79,31],[80,32],[81,33],[82,34],[83,35],[84,36],[85,37],[86,38],[87,39],[88,40],[89,41],[90,42],[91,43],[92,44],[93,45],[94,46],[95,47],[96,48],[97,49],[97,25],[98,26],[99,27],[100,28],[101,29],[102,30],[103,31],[104,32],[105,33],[106,34],[107,35],[108,36]], markLine: { symbol: none, label: { show: false }, data: [ { xAxis: 0, lineStyle: { color: "#59a14f", type: "solid", width: 1.4, opacity: 0.75 } }, { xAxis: 24, lineStyle: { color: "#59a14f", type: "solid", width: 1.4, opacity: 0.75 } }, { xAxis: 48, lineStyle: { color: "#59a14f", type: "solid", width: 1.4, opacity: 0.75 } }, { xAxis: 72, lineStyle: { color: "#59a14f", type: "solid", width: 1.4, opacity: 0.75 } }, { xAxis: 96, lineStyle: { color: "#59a14f", type: "solid", width: 1.4, opacity: 0.75 } }, { xAxis: 1, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.8 } }, { xAxis: 25, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.8 } }, { xAxis: 49, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.8 } }, { xAxis: 73, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.8 } }, { xAxis: 97, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.8 } }, { yAxis: 25, label: { show: true, formatter: "lower 25h", position: "end", distance: 12, color: "#2563eb" }, lineStyle: { color: "#2563eb", type: "dashdot", width: 1.4, opacity: 0.75 } }, { yAxis: 49, label: { show: true, formatter: "upper 49h", position: "end", distance: 12, color: "#7c3aed" }, lineStyle: { color: "#7c3aed", type: "dashdot", width: 1.4, opacity: 0.75 } } ] } }, { name: Primary Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 5, itemStyle: { color: "#59a14f" }, data: [0,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100] }, { name: Secondary Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 5, itemStyle: { color: "#4e79a7" }, data: [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100] }, { name: WAL Archive, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 5, itemStyle: { color: "#edc949" }, data: [0,0,0.42,0.83,1.25,1.67,2.08,2.5,2.92,3.33,3.75,4.17,4.58,5,5.42,5.83,6.25,6.67,7.08,7.5,7.92,8.33,8.75,9.17,9.58,10,10.42,10.83,11.25,11.67,12.08,12.5,12.92,13.33,13.75,14.17,14.58,15,15.42,15.83,16.25,16.67,17.08,17.5,17.92,18.33,18.75,19.17,19.58,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20] }, { name: Transient Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 5, itemStyle: { color: "#9ca3af", opacity: 0.75 }, data: [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100,0,0,0,0,0,0,0,0,0,0,0,0] } ]Full + Incremental Backup
You can optimize backup space usage by adjusting these parameters.
If using Silo / S3 as a centralized backup repository, storage is no longer limited by the local disk. In this case, consider using full + incremental backup with a 2-week retention policy:
With weekly full backups and 14-day time-based retention, the steady-state recovery window is approximately 14–21 days. The exact window depends on successful backups and WAL archiving.
For a database of 100GB with daily writes of 10GB, the following chart shows backup size:
tooltip: { trigger: axis, formatter: $fn:tipMerged30, axisPointer: { type: line, snap: true, label: { show: false } } }
axisPointer: { link: [ { xAxisIndex: [0, 1] } ] }
legend: { show: false, bottom: 10, itemGap: 18, data: ["Primary Backup", "Secondary Backup", "Incremental Backup", "WAL Archive", "Transient Backup"] }
grid:
- { left: 82, right: "10%", top: 42, height: 218, containLabel: false }
- { left: 82, right: "10%", top: 302, height: 218, containLabel: false }
xAxis:
- type: value
gridIndex: 0
position: bottom
boundaryGap: false
min: 0
max: 31
interval: 1
name: Time
nameLocation: end
nameGap: 10
nameTextStyle: { align: left, verticalAlign: top, padding: [8, 0, 0, 0] }
axisLabel: { formatter: $fn:fmtDay30 }
axisLine: { show: true, symbol: [none, arrow], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
axisTick: { show: true, length: 6 }
splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.28, color: "#9ca3af" } }
minorTick: { show: true, splitNumber: 4, length: 3 }
minorSplitLine: { show: true, lineStyle: { type: dotted, width: 1, opacity: 0.14, color: "#9ca3af" } }
- type: category
gridIndex: 1
position: top
boundaryGap: true
z: 10
data: [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30]
axisLabel: { show: false }
axisLine: { show: true, lineStyle: { width: 1.6, color: "#4b5563" } }
axisTick: { show: true, alignWithLabel: true, length: 6 }
splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.22, color: "#9ca3af" } }
yAxis:
- type: value
gridIndex: 0
min: 0
max: 360
interval: 48
name: Recovery Window h
nameLocation: end
nameRotate: 0
nameGap: 8
nameTextStyle: { align: left, verticalAlign: bottom, padding: [0, 0, 8, 4] }
axisLabel: { formatter: $fn:fmtWin30 }
axisLine: { show: true, symbol: [none, arrow], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
axisTick: { show: true, length: 6 }
splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.35, color: "#9ca3af" } }
minorTick: { show: true, splitNumber: 4, length: 3 }
minorSplitLine: { show: true, lineStyle: { type: dotted, width: 1, opacity: 0.18, color: "#9ca3af" } }
- type: value
gridIndex: 1
min: 0
max: 600
interval: 50
inverse: true
z: 10
name: Storage GB
nameLocation: end
nameRotate: 0
nameGap: 8
nameTextStyle: { align: left, verticalAlign: top, padding: [10, 0, 0, 4] }
axisLabel: { formatter: $fn:fmtGbTick30 }
axisLine: { show: true, symbol: [arrow, none], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
axisTick: { show: true, length: 6 }
splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.32, color: "#9ca3af" } }
series:
- { name: Recovery Window, type: line, smooth: false, symbol: none, showSymbol: false, xAxisIndex: 0, yAxisIndex: 0, lineStyle: { width: 3, color: "#f28e2c" }, itemStyle: { color: "#f28e2c" }, data: [[1,24],[2,48],[3,72],[4,96],[5,120],[6,144],[7,168],[8,192],[9,216],[10,240],[11,264],[12,288],[13,312],[14,336],[14,168],[15,192],[16,216],[17,240],[18,264],[19,288],[20,312],[21,336],[21,168],[22,192],[23,216],[24,240],[25,264],[26,288],[27,312],[28,336],[28,168],[29,192],[30,216]], markLine: { symbol: none, label: { show: false }, data: [ { xAxis: 7, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.65 } }, { xAxis: 14, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.65 } }, { xAxis: 21, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.65 } }, { xAxis: 28, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.65 } }, { yAxis: 168, label: { show: true, formatter: "lower 7d", position: "end", distance: 12, color: "#2563eb" }, lineStyle: { color: "#2563eb", type: "dashdot", width: 1.4, opacity: 0.72 } }, { yAxis: 336, label: { show: true, formatter: "upper 14d", position: "end", distance: 12, color: "#7c3aed" }, lineStyle: { color: "#7c3aed", type: "dashdot", width: 1.4, opacity: 0.72 } } ] } }
- { name: Primary Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 16, itemStyle: { color: "#59a14f" }, data: [100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100] }
- { name: Secondary Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 16, itemStyle: { color: "#4e79a7" }, data: [0,0,0,0,0,0,0,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100] }
- { name: Incremental Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 16, itemStyle: { color: "#76b7b2" }, data: [0,10,20,30,40,50,60,70,80,90,100,110,120,130,70,80,90,100,110,120,130,70,80,90,100,110,120,130,70,80] }
- { name: WAL Archive, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 16, itemStyle: { color: "#edc949" }, data: [10,20,30,40,50,60,70,80,90,100,110,120,130,140,80,90,100,110,120,130,140,80,90,100,110,120,130,140,80,90] }
- { name: Transient Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 16, itemStyle: { color: "#9ca3af", opacity: 0.75 }, data: [0,0,0,0,0,0,0,0,0,0,0,0,0,110,0,0,0,0,0,0,110,0,0,0,0,0,0,110,0,0] }Backup Location
Pigsty provides two default backup repository definitions: local and minio.
local: Default option, uses local/pg/backupdirectory (symlink topg_fs_backup:/data/backups)minio: Uses SNSD single-node MinIO cluster (supported by Pigsty, but not enabled by default)