Nbackup RDB$BACKUP_HISTORY cleanup

[Hugolarson]

Hello,

The FB4 GUID based nbackup and incremental restore are huge improvement and we would like to use it as an convent way for replica solution.
What makes it convenient, compared to the actual Replica functions is the zero need of configuration both on server and client side, which is a must since we have many hundreds of database files, and databases are created on the fly.

The issue with making nbackups very often is that the RDB$BACKUP_HISTORY will be flooded with records that is read-only.

I guess with GUID based nbackup it's not necessary to store all history since start of first nbackup.

There should be a way to delete these records, or when GUID nbackaup is executed old unnecessary records are automatically deleted.

[hvlad]

What is the real problem ? Few hundreds of records ? Looks strange for me ;)

Anyway, I see following possible ways to keep less records in RDB$BACKUP_HISTORY:

• make engine to allow to DELETE FROM RDB$BACKUP_HISTORY, probably by SYSDBA\Owner only, or
• add command-line switch to the nbackup to avoid inserting record into RDB$BACKUP_HISTORY, or
• add command-line switch to the nbackup to delete obsolete records (by date or by number) in RDB$BACKUP_HISTORY.

Another ideas ?

[hvlad]

* make engine to allow to DELETE FROM RDB$BACKUP_HISTORY, probably by SYSDBA\Owner only, or

* add command-line switch to the nbackup to avoid inserting record into RDB$BACKUP_HISTORY, or

Sorry, these two options is no go - nbackup should be able to find SCN of prior backup.

*add command-line switch to the nbackup to delete obsolete records (by date or by number) in RDB$BACKUP_HISTORY.

...and this one also looks suspicious.

[Hugolarson]

Imagine a backup every minute of hundreds of databases over 5 years or more.

[hvlad]

Number of databases doesn't matters, as RDB$BACKUP_HISTORY is per-database.
Even if one run nbackup every minute (which I highly doubt is possible on practice), then one will have 60 * 24 * 365 = 525 600 records per year. It is not huge and even not big, imho. It is database, not a notepad, finally ;)

Anyway, I already offered some solution and asked for another (better) ideas.

[aafemt]

Isn't it enough to keep exactly one record per each backup level? AFAIU since you have made backup of level X, subsequent backups of level X+1 can be based only on this snapshot so other backups of level X become irrelevant (or unusable).

[hvlad]

@aafemt you are wrong

[hvlad]

Let me clarify a bit

Isn't it enough to keep exactly one record per each backup level?

Only in case of GUID-based backup

AFAIU since you have made backup of level X, subsequent backups of level X+1 can be based only on this snapshot so other backups of level X become irrelevant (or unusable).

This statement is completely wrong

[Hugolarson]

Number of databases doesn't matters, as RDB$BACKUP_HISTORY is per-database. Even if one run nbackup every minute (which I highly doubt is possible on practice), then one will have 60_24_365 = 525 600 records per year. It is not huge and even not big, imho. It is database, not a notepad, finally ;)

Anyway, I already offered some solution and asked for another (better) ideas.

Sorry for being unclear. I understand RDB$BACKUP_HISTORY is per database.
We have hundreds of databases on same server so all these records will waste a lot of space and bandwidth. Over the years RDB$BACKUP_HISTORY can occupy several hundreds of MB in each database file (of hundreds files).
It feel that this is unnecessary burden of the databases in my use case of nbackup "replica" which I think is very useful. Zero configuration.

[Hugolarson]

I learned that the initial nbackup of initial database require switch -B 0 (level) and after that GUID is supplied instead of level.
How about another exclusive switch for GUID based nbackup which tells the engine only to store latest GUID?

NBACKUP -BG 0 -> initial backup. First GUID is created
NBACKUP -BG GUID -> next backup and so on

[Hugolarson]

Apologies, maybe I'm out of line but I need to ask if is there hope that this issue might be addressed?
It would be very useful if NBACKUP can be used as replica solution without filling the database files with records in RDB$BACKUP_HISTORY.
We have hundreds of databases on same server and we ideally want nbackup every 2 minutes.

[hvlad]

I still have no better ideas than

• allow to DELETE FROM RDB$BACKUP_HISTORY if system privilege USE_NBACKUP_UTILITY is granted (it is how it works for INSERT INTO RDB$BACKUP_HISTORY already), and, probably
• add command-line switch to the nbackup to delete obsolete records (by date or by number) in RDB$BACKUP_HISTORY.

Opinions ?

[hvlad]

How about another exclusive switch for GUID based nbackup which tells the engine only to store latest GUID?

This will break non-GUID based backup's of this database.

Also, with backup history you may produce backup based on any known\existing prior backup.
Without history you will be forced to start from level 0 if last backup file is lost (by any reason).

[Hugolarson]

I still have no better ideas than

• allow to DELETE FROM RDB$BACKUP_HISTORY if system privilege USE_NBACKUP_UTILITY is granted (it is how it works for INSERT INTO RDB$BACKUP_HISTORY already), and, probably
• add command-line switch to the nbackup to delete obsolete records (by date or by number) in RDB$BACKUP_HISTORY.

Opinions ?

Both suggestions are great!
If the command-line switch can be executed from services api would be awesome.