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.

[hvlad]

The proposition is following:

1. Allow to DELETE FROM RDB$BACKUP_HISTORY, require system privilege USE_NBACKUP_UTILITY
2. Add following command-line switches to the nbackup:

• CLEAN_HISTORY - tell nbackup to delete old records from RDB$HISTORY
• KEEP_DAYS <value> - specify how many days back from today should be kept in the history
• KEEP_ROWS <value> - specify how many most recent rows in the history should be kept
• CLEAN_HISTORY requires one of the KEEP_DAYS or KEEP_ROWS
• KEEP_DAYS or KEEP_ROWS requires CLEAN_HISTORY
• only one of KEEP_DAYS or KEEP_ROWS switch could be used
• value of KEEP_DAYS/KEEP_ROWS should be integer greater than one
• CLEAN_HISTORY could be used as additional switch of BACKUP switch (action) only

3. Append Services API with two new tags isc_spb_nbk_history_keep_days and isc_spb_nbk_history_keep_rows
4. nbackup will delete old records from RDB$HISTORY after INSERTing of new record and in the same transaction. Note, new record will be accounted by DELETE query.

Items to discuss:

• two combo's of switches -CLEAN_HISTORY -KEEP_DAYS <N> or -CLEAN_HISTORY -KEEP_ROWS <N> could be repalced by two single switches but I can't offer clear names for it with reasonable not too long names. I.e. I don't like to use something like CLEAN_HISTORY_KEEP_DAYS (yes, the same number of chars to type)
• use some reasonable default value for single usage of CLEAN_HISTORY, say keep last 100 or 1000 rows - it allows to make KEEP_XXX switches optional
• anything else you consider to discuss

[Hugolarson]

CLEAN_HISTORY. Is it not the case that all rows beyond last N0 irrelevant? Maybe default can be delete all beyond last N0.

If GUID only last nbackup is relevant?

[hvlad]

What is good for you is not necessary good for everyone. If you not use nbackup for backup purpose alongside with cold "standby" - it doesn't mean we should by default destroy ability to do it. With proposition above you can keep one row in RDB$BACKUP_HISTORY if you strongly need that. But don't force dangerous behaviour for every one, please.

[hvlad]

Implementation is committed, please test next snapshot build.
Documentation will follow soon.

[Hugolarson]

Excellent!
Are there snapshots for FB 4?

[hvlad]

Old link for snapshot builds works now:
https://web.firebirdsql.org/downloads/snapshot_builds/

If it will not work tomorrow, follow instructions here:
https://firebirdsql.org/en/snapshot-builds/

note, you need build of branch v4.0-release

[Hugolarson]

The build works and I'm testing it :)

What about cleaning records without creating an actual nbackup? I think it might be useful.

nbackup -CLEAN_HIST -KEEP_R 2 -U SYSDBA -P PASSKEY TESTDB.FDB

[hvlad]

Thanks for testing.

As for "cleaning records without creating an actual nbackup" - now you may run isql and delete anything you want.
But make sure it will not break backup sequence!
Of course, you need to have USE_NBACKUP_UTILITY privilege granted to do it.

[mrotteveel]

Has this code landed in master as well? If not, when will it be added there?

[hvlad]

I'm preparing a change for command-line syntax, it will be committed into v4 and then into master, if there will be no objections.
Services API will not be changed.

[hvlad]

I've committed some changes, now the command line syntax is as follows:

• CLEAN_HISTORY - tell nbackup to delete old records from RDB$HISTORY
• KEEP <value> ROWS | DAYS - specify how many most recent rows or days back from today should be kept in the history

Usage notes:

• CLEAN_HISTORY requires the KEEP switch
• KEEP requires the CLEAN_HISTORY switch
• switches CLEAN_HISTORY and KEEP allowed to be used one time only
• value of the KEEP should be integer greater than one
• CLEAN_HISTORY could be used as additional switch of BACKUP switch (action) only
• nbackup delete old records from RDB$BACKUP_HISTORY after INSERTing of new record and in the same transaction.
  Note, new record is accounted by DELETE query.
• switches names could be shortened up to CLEAN_HIST for CLEAN_HISTORY, R for ROWS and D for DAYS

Services API appended with with new parameter tags for isc_spb_nbackup_action:

• isc_spb_nbk_clean_history : tell nbackup to clean RDB$BACKUP_HISTORY table,
  corresponds to the CLEAN_HISTORY switch
• isc_spb_nbk_keep_days <int> : specify how many recent rows should be kept in the history
  corresponds to the KEEP <N> DAYS switch
• isc_spb_nbk_keep_rows <int> : specify how many days back from today should be kept in the history
  corresponds to the KEEP <N> ROWS switch

Examples

1. make backup of level 1, clean RDB$HISTORY table and keep 1 recent row in it

nbackup -B 1 db.fdb db.nbk -clean_hist -keep 1 row

fbsvcmgr action_nbak dbfile db.fdb nbk_file db.nbk nbk_level 1 nbk_clean_history nbk_keep_rows 1

2. make backup of level 2, clean RDB$HISTORY table and keep rows for the last 7 days in it:

nbackup -clean_hist -keep 7 d -B 2 db.fdb db.nbk

fbsvcmgr action_nbak dbfile db.fdb nbk_file db.nbk nbk_level 2 nbk_clean_history nbk_keep_days 7