Subject Re: [firebird-support] automate compressing firebird
Author Helen Borrie
At 08:02 PM 8/12/2008, you wrote:

>can I automate the compressing of a firebird database?

Firebird doesn't have the concept of "compressing the database". That concept belongs to databases with file-based objects, which Firebird is not. Firebird tables have ROWS. Rows live on blocks of disk called PAGES. Depending on the size of your record structures, there may be one or many rows on a page.

Firebird is also a multi-versioning engine. That means that new and old versions of rows - known as "record versions" can co-exist. Old versions remain on disk until they are "garbage-collected". Once GC is complete, the space is released and a new record version can be written there.

>With gbak I can backup automatically, but restore is not good where firebird is running?

gBak creates a compressed text version of all of the metadata of your database and then writes the data separately (in the same XDR compressed text format).

When you restore a database, the database is recreated all fresh and brand-new and the data is restored into clean objects on clean pages. So - a freshly restored database has very little "spare" space, since only the current data exists, no garbage. That is why a restored database is smaller than the version that was backed up.

>Is there a tool available to take this?

If you need to "compress" a database then gbak backup and restore is the tool. You need to maintain your database on one or more HDDs that have enough space to accommodate multiple record versions. If you write your applications properly, your database file growth will settle into a predictable pattern of re-use and allocation of new space. Don't try to operate read/write database on minimal disk space - it won't work out.

If you need to restore a database, then use the -c[reate_database] switch and restore to a database of a different name from the live database. Test that you can connect to the restored database. If all is well, then
1) rename the live database to some other name;
2) rename the new database to the production name.

This is the STRONGLY recommended way to do backup and restore. Don't be tempted to use the -[r]eplace_database switch.