CloudSQL - Old backups are never deleted

[Silvest89]

TL;DR

We do multiple CloudSQL backups a day so that's how we came across this issue (using just a 7 day retention time). Old backups won't get deleted.

By default googleapis.sqladmin.v1.backupRuns.list returns only 20 items. So if for example doing daily backups and you have a retention time higher than 21, you will never get the backups old enough to be deleted :P

It would be nice to

      call: googleapis.sqladmin.v1.backupRuns.list
      args:
        maxResults: 100  (either configurable or a high enough value)
        project: ${project}
        instance: ${instanceName}

Expected behavior

Old backups should be deleted

Observed behavior

Backups higher than retentionTime are not visible in the list response

Terraform Configuration

.

Terraform Version

.

Additional information

No response

[github-actions]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 7 days

[imrannayer]

@Silvest89 can you plz create an issue for the API team. This is not something we can fix it in module.

[tjespers]

@imrannayer
We can fix this within the module, the problem lies in the API call used for listing the backups: googleapis.sqladmin.v1.backupRuns.list operation defined here:

terraform-google-sql-db/modules/backup/templates/backup.yaml.tftpl

Line 35 in a013572

call: googleapis.sqladmin.v1.backupRuns.list

This operation supports an argument that specifies the maximum amount of backups returned with it's default page size being 20 if left unset.

We worked around this now by using this exact backup.yaml template but simply passing in a maxResults large enough for us to accomodate our scenario (bi-hourly backups with a retention time of 7 days) setting maxResults: 100

2 possible ways of fixing this within the module are:

• hardcoding maxResults to a more "sane" default, and documenting it in the README for other to know the limitation
• leave it up to the enduser and max this an optional module variable for the enduser to have more control based on their schedule.

[imrannayer]

@tjespers option 2 sounds like a better option.

[tjespers]

@imrannayer Sure thing! I've opened #566 as a bugfix for this.