Skip to main content
Question

PostreSQL incremental backup running as log only?

  • 28 June 2024
  • 3 replies
  • 31 views

Hello all! We are working on PostreSQL implementation process. Our customer wants to have this kind of schedule:

Log Backup Schedule = Every 2 hours
Schedule = 1 x full 6 x incr, 

 

So we have done that schedule:

 

For LOG backup:

There is “incremental” chosen and Data is NOT chosen 


Other schedule is created for “incremental” backup:

There is “incremental” chosen and Data IS chosen 


When i do run this schedule manually or even if it runs automatically, i can see only this:​​​​​​​

so no matter which schedule run JobType is always “Log Only” 

If I do run manually backup and chose incremental, it also uses “Log Only” job type.

 

This backup is based on FSBasedBackupSet

 

So if it is even a way to do a normal Incremental backup for PostgreSQL? Or it will always be “log only”?

While running Full backup i can see that there are also logs and Full backup:


I am wondering, hot to handle backup retentions if there is nothing like “straight forward incremental backup”

​​​​​​​

3 replies

A
Rank
Userlevel 6
Badge +15

Hello @Grzegorz 

Inc jobs will always run a t-log jobs for PostgreSQL
 

 

The schedules you create have a lot of options that may not be applicable to each agent as they can be referenced by a number of agents. This is why there is the option “data” but seems to do nothing as other agents that do support that option will honor that. 


Kind regards

Albert Williams

The best way to learn anything, is to question everything
G
Rank
Badge +5

Hi! Thanks for a response!

So while creating a schedule - I have chosen that is related to PostreSQL so i thought it will only show settings related to Postreg. Nevertheless, while creating manual “incremental” backup its also converted to log only.

So as You said - Postgre inc will always run as  t-log, so the another question is…

How to differentiate
-daily incremental backup which is supposed to be retained for few weeks?
-every 2h log backup which are supposed to be keep for 7 days?

 

If I assign “daily” backup to “daily backup storage policy” and then “every 2 hours log policy” to different “log storage policy” i believe there will still be some dependencies between those incremental backups.

So if daily backup will start at 8PM, it will also require having all previous “log” backup from last full?

I am confused…

Do you know if there is something like best practice to handle that?

Alex Deaconu
Rank
Userlevel 2
Badge +4

There is no distinction in PostgreSQL between an Incremental and a WAL File backup.

 
If you want to recover a database to a point in time, you need to apply all the logs until that selected point in time. If you miss logs, like if they were not backed up, or have been aged and pruned, then you can only recover to the Full Backup that contained its own set of WAL files, included in the job.
 
If you had a Full Backup Sunday, and you want to recover to the backup from Tuesday, you need all the log files from Sunday to Tuesday. If you want to not have to apply so many WAL Files, run daily Full Backups.
 
Full Backups with PostgreSQL are not that bad, because you should get a lot of deduplication, allowing you to run Full Backups more frequently, as if they were Incremental Backups, but it does get limiting when we are talking somewhat large databases, like 4 TB or more.
 
A Full Backup means that you will not need the previous WAL Files anymore, but you will still need the WAL files that get generated during the Full Backup Job, especially the WAL File forced to be written at the end by Commvault. Those are included in the Full Job.
 
If you really want to run PostgreSQL Incremental Backups, then that is only supported using Block Level Backup. LVM is my preferred choice for BLB. If you have a hardware snapshot array, I also suggest you keep PostgreSQL on LVM. Note that for BLB to work, PostgreSQL data directory needs to be in a separate logical volume than the root file system.
https://documentation.commvault.com/2023e/expert/configuring_subclient_for_block_level_backups_of_postgresql_databases.html
 
When you run an Incremental BLB, only the Changed Blocks will be added to the library.
https://documentation.commvault.com/2023e/expert/perform_block_level_backup_for_postgresql_databases.html
 
https://documentation.commvault.com/2023e/expert/performing_block_level_incremental_backup_for_postgresql_databases.html
 
In addition, your Full BLB will also massively benefit from deduplication as well. Only the changed blocks should be added to the backup library, making a subsequent Full Backup ‘virtually’ an Incremental Backup, but logically still a Full, because the Dedup Table still points to the data that was backed up in previous backups.
  
Keep in mind that with either more frequent full backups, or with BLB, no matter the approach, you might lose/restrict your RPO if you delete WAL files. If you keep WAL Files for only 7 days, then you have only the last week to recover to a Point in Time. If you need to recover to an earlier time, you can then only go to your Weekly Full or Daily Full Backup/Incremental BLB that are still kept for a few weeks.

Reply


Terms & ConditionsCookie settings