Improvements of database backup/restore

[Alexander Obuhovich]

In-Portal very basic backup/restore capabilities of it's data. Right now it only allows to do an SQL dump of whole database into a single file and later replace current database with any of the dumps that were made before that.

I'm proposing to make it more intellectual to be able to backup/restore:

1. all/specific records from selected table(-s) only
2. associated data (based on data that is exported) from connected tables
3. associated data  (based on data that is exported) from related tables

And of course you can combine any of these into a single import/export procedure.

Connected tables

These are tables, that are used to display data on a separate tab (in Admin Console), while adding/editing an item. For example images, custom fields, etc.

Right now we define all connected tables via SubItems/ForeignKey/ParentTableKey options in each unit config.

Related tables

These are tables, where you reference this data. For example in paid listings table you specify link id (or link resource id) where this particular enhancement is applied to. Also when added a link/article to a category you reference particular category in context of that link/article.

Right now we don't specify these table relations anywhere and thus can't use this info during backup/restore process. To fill that gap I'm proposing to specify all back-references to a field in field definition.

For example:

• in ItemResourceId field definition (in unit config) of Listings table we specify that it uses data from ResourceId field of Link table
• in CategoryId field definition (in unit config) of CategoryItems table we specify that it uses data from  CategoryId field of Categories table

In unit config this might look like this:

'Fields' => Array (

'ItemResourceId' => Array ('type' => 'int', 'default' => null, ...., 'foreign_key' => 'l:ResourceId'),

),

Thanks to that described above improvements backup/restore code won't be just considering a database as a bunch of unconnected tables, but will know every piece about the data.

Storage format

Right now all backup is stored as large sql file with all table definitions and data. This is extremely inconvenient to use, because we need to threat data in file as one piece to be able to extract separate database queries from it.

When there are a lots of data this becomes very memory consuming process. Storing same data in XML format doesn't solve a problem too, because we yet again need to load ALL file into memory to be able to parse it.

That's why I'm proposing to use SQLLite engine, that is always bundled with PHP) and store a backup as a SQLLite database. Each SQLLite database is 1 file, so no problems with that.

Restoring backups

Since we now would allow a partial backup, then user can restore it back at any time without changing unrelated parts of database. When restoring we can't rely on fact that record ID=A in backup and ID=A in target database are same record.

To solve this I'm proposing to record last record id of every database table we backup (if we even backup 1 record from it).

This way during restore we will know, that if we have matching ID in target database and this ID is smaller then maximal id existed at time of backup, then it's same record.

This check can be extremely useful when LIVE/UAT databases are used.

Usage case

Some database exists on live website and we need to create close-to-live environment on UAT (user accepted testing) server.
At first we just copy paste all the data from live. Over time data is filled in on both live and uat databases and same ID of record in both databases no longer reefer to same record.

Luckily for use we recorded last ID in each table from UAT database creation. Now on restore we exactly know which records should be updated and which ones should create and their current ID in live database should be incremented (in all connected/related tables too) to prevent ID conflict in UAT database.

P.S.

I know this isn't easy subject to understand, but if we do implement what I was talking about here, then we can do export/import at least following items from dev/sandbox to live without difficulties:

• pages
• page content blocks
• custom field definitions
• data entered in custom fields

--
Best Regards,

http://www.in-portal.com
http://www.alex-time.com

[Dmitry A.]

Hi Alex,

Thanks for sharing your ideas - I do like it, but I actually not fully envisioning the Interfaces for this Backup/Restore just yet.

Do you have a clear vision on it?

DA

[Phil]

Hi guys,

I'm happy to read about that subject. I've started this thread 2 years ago.  I think it could be a nice addition to Alex proposal, and let us embed a full backup system.

Alex, I miss something in your idea: a feature to save/modify/delete a backup configuration, which would hold all parameters you talk about here. Of course, this would be also very helpful to use the automatic backup I told about, but with or without it, it seems a must have, to avoid the need to setup again many parameters to be able to perform the very same backup.

Phil.

[Alexander Obuhovich]

Thanks for sharing your ideas - I do like it, but I actually not fully envisioning the Interfaces for this Backup/Restore just yet.

I don't have complete interface idea, but I know that it would:

• have add form on top and list of added under it
• inside add form there would be several radio buttons:
• selected tables - would present list of all tables in a multiselect control (or 2 lists when user can move needed columns, like in link from csv import dialog)
• selected records - would allow user to select 1 table and then manually enter IDs of records, that needs to be backed up (showing all table records here might be bad idea)
• after selection was made user have to select one of these radio buttons:
• selected tables/records only
• all but selected tables/records
• finally add button will add all entered info to a list below

I'm happy to read about that subject. I've started this thread 2 years ago.  I think it could be a nice addition to Alex proposal, and let us embed a full backup system.

In that discussion I've found "Invert Selection" feature. When you want ALL tables/records except selected ones to be backed up.

Alex, I miss something in your idea: a feature to save/modify/delete a backup configuration, which would hold all parameters you talk about here. Of course, this would be also very helpful to use the automatic backup I told about, but with or without it, it seems a must have, to avoid the need to setup again many parameters to be able to perform the very same backup.

 

Yes, I haven't thought about it, but since there are a lots of parameters to be configured then saving all them as "backup profile" will be great addition.

[Dmitry A.]

I think we can come back to this discussion and see where it takes us.

DA

[Phil]

Well, I remember all discussion, and IMO we are ready for a rock-solid feature creation :)

Envoyé avec Sparrow

[Dmitry A.]

Hi guys,

I think we should finalize the interface for this and work out the new task. Also I want to know that this is more Export/Import feature with relationship to Backup/Restore while we should/can't have something like scheduled backups. Recently I was working with one of PHP script for VOIP and liked what they have done for backup/restore. There is a lot that they have and we probably don't need all of it, but we can do some good improvements to ours.

We can combine both together where full backup will run based on defined scheduled to create DB (in LiteSQL) and Files (ie. dynamic images) backup and run from Cron while Export/Import will be a manual part to help export/import part/all of the content by hand.

DA