Backup aging

[alexign]

Hello!
I think pg_probackup need something like backup aging mechanism.
How it should work:

1. Let say at the 1 day of month we make full backup of database and set its max age 12 months(or 365 day whenever). Lets it id is 1
2. After that we also make full backup say at every sunday and set its max age at 30 days their id is say 10 20 30 40
3. During week we make incremental backup.
4. Every day we have retention policy work and delete our expired backup with their age > max_age we set in p 1 and 2

After 30 days have passed we have in our backup repo something like that:

Full backup (id 1 29 or more days ago) ==> Full backup (id 10 28 days ago) ==> Full backup (id 20 21 days ago) ==>Full backup (id 30 14 days ago) ==>Full backup (id 40 7 days ago) ==>Incremental backup ==> Incremental backup...==> now
After 30 days have passed and retention mechanism clear aged backup we have in our repo:
Full backup (id 1) ==> Full backup (id 20 ) ==> Full backup (id 30) ==>Full backup (id 40) ==> Full backup (id 50 ) ==> Incremental backup ==> Incremental backup...==> now
As you can see there is no full backup with id = 10 because its age is greater than its max_age set on p 2. and retention policy removed it from backup chain
and so on...

With this feature in mind pg_probackup can make some simple(or maybe complex) backup archive policy without using any external tools.

[gsmolk]

Sounds interesting, but how exactly pinning should work?
I think, the most obvious way is to use time, for example: --pinned-for=number_of_days or --pinned-until=timestamp.

[gsmolk]

And we will need an interface for backup manipulation. Something like separate command set-backup

[alubennikova]

And we will need an interface for backup manipulation. Something like separate command set-backup

I would rather we reuse existing subcommand.
pg_probackup set-config -B backupdir --instance instance_name [options] - to set instance specific config

pg_probackup set-config -B backupdir --instance instance_name -i backup_id [options] - to set backup specific config

[gsmolk]

I would rather we reuse existing subcommand.

I think it will be too complicated from user point of view. I vote for separate command =)

[triwada]

I would rather we reuse existing subcommand.

I think it will be too complicated from user point of view. I vote for separate command =)

+1
Every organization has own backup policy, and the complex of this policy is solved by backup script. So need only an interface for backup manipulation

[gsmolk]

Currently implemented the following interface:

1. Set the aging via command set-backup:

pg_probackup set-backup --instance node -i PYFEF4 --ttl=30d

2. View via new backup attribute expire-time :

pg_probackup show --instance node -i PYFEF4
...
expire-time = '2019-010-25 09:38:16+03'
...

3. Unset:

pg_probackup set-backup --instance node -i PYFEF4 --ttl=0

[gsmolk]

4. Additional parameter --expire-time:

pg_probackup set-backup --instance node -i PYFEF4 --expire-time='2020-01-01 00:00:00+03'

[gsmolk]

Review and testing are greatly appreciated.