Postgresql – barman + backup and restore + how to enable auto deletion from backup files

backupbarmanlinuxpostgresql

I am using the barman tool to backup the postgresql DB

barman is working fine but I notice that backup files that created under
/var/lib/barman/main-db-server/base folder are not automatically being deleted after so many days.

I found the variable last_backup_maximum_age – but this only for reporting

 last_backup_maximum_age
 This option identifies a time frame that must contain the latest backup. If 
 the latest backup is older than the time frame, barman check command will 
 report an error to the user. If empty (default), latest backup is always 
 considered valid. Syntax for this option is: “i (DAYS | WEEKS | MONTHS)” 
 where i is a integer greater than zero, representing the number of days | 
 weeks | months of the time frame. Global/Server.

I can use

barman delete <server_name> oldest

in order to delete every time the oldest file; however, I want to understand if I've missed something that can automatically delete the files once they reach a certain age; an auto-rotate or auto-delete functionality.

Best Answer

You should configure retention_policy.

Here's the syntax :

retention_policy = {REDUNDANCY value | RECOVERY WINDOW OF value {DAYS | WEEKS | MONTHS}}

You will find documentation here

Related Solutions

Sql-server – Sql Server 2008 R2: Simple recovery model with transaction log backups

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?

Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.

A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.

Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.

For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.

Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.

Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)

Sql-server – How to know that , Is it Maintenance Cleanup Task is working properly from maintenance Plan or not through T-SQL

If you want to be able to view the exact backup history for longer time than those four weeks that you keep the backup files on disk, make sure that you set the cleanup task not to delete the backup history from the msdb database, you can then run the backup report from SSMS (right click database, select reports, standard reports, backup and restore events) or run the following to view the backup history:

First create this function - I'll just have it in msdb - which is not a good idea.

create FUNCTION dbo.fc_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

Then you can run the following

SELECT     
 sd.name AS database_name, 
 ISNULL(bs.backup_finish_date, 0) AS backup_date,
 ISNULL(CASE bs.[type] 
  WHEN 'D' THEN 'Full' 
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log' 
  END, 'NEVER') as type ,
 bm.physical_device_name, 
 dbo.fc_FileExists(bm.physical_device_name) as DoesFileExists,
 bs.backup_size / 1024 / 1024 AS backup_size, 
 DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS duration
 FROM master.dbo.sysdatabases AS sd 
 LEFT OUTER JOIN msdb..backupset AS bs ON sd.name = bs.database_name 
 LEFT OUTER JOIN msdb..backupmediafamily AS bm ON bm.media_set_id =     bs.media_set_id
  GROUP BY 
   sd.name, 
   bm.physical_device_name, 
   bs.backup_size / 1024 / 1024, 
   DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date),
   bs.backup_finish_date,
   bs.[type],
   dbo.fc_FileExists(filename)
 order by bs.backup_finish_date desc

Best Answer

Related Solutions

Sql-server – Sql Server 2008 R2: Simple recovery model with transaction log backups

Sql-server – How to know that , Is it Maintenance Cleanup Task is working properly from maintenance Plan or not through T-SQL

Related Question