You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Maison Mo <mo...@yahoo.fr> on 2013/06/28 09:07:33 UTC

Question on database, schemas and backups

Hello,

For a multi-tenant application, we evaluate the multiple databases pattern and single database / multiple schemas pattern.
Regarding the backup process, it seems that the whole database is backed up by the SYSCS_UTIL.SYSCS_BACKUP_DATABASE(), thus all schemas in this DB : is that correct ?
Is it then possible to restore a single schema ?

Given a jdbc client, it is possible to change schema once connection is established. Is it also possible to change the database ?

Thank you for your answers

M.

Re: Question on database, schemas and backups

Posted by Rick Hillegas <ri...@oracle.com>.
On 6/30/13 11:56 PM, Maison Mo wrote:
>
> --- En date de : Ven 28.6.13, Rick Hillegas<ri...@oracle.com>  a écrit :
>>> For a multi-tenant application, we evaluate the
>> multiple databases pattern and single database / multiple
>> schemas pattern.
>>> Regarding the backup process, it seems that the whole
>> database is backed up by the
>> SYSCS_UTIL.SYSCS_BACKUP_DATABASE(), thus all schemas in this
>> DB : is that correct ?
>>> Is it then possible to restore a single schema ?
>> I don't believe so. There is no schema-isolating switch
>> associated with
>> the restoreFrom connection attribute. It would be an
>> interesting problem
>> to work on. It would involve subsetting the data in the
>> system catalogs
>> and computing the referential closure of all tables, views,
>> constraints,
>> and routines in the target schema. Those objects could
>> reference objects
>> in other schemas.
> Thank you for this information.
> In mycase all schemas would be separated (no shared table, procedure or constraint...), if it can simplify things.
> So in order to restore a backup for schema X only, I would need to do things manually :
> - restore the backup in a temp DB
> - expurge any schema X related object from main DB
> - manually "merge" the temp DB schema X objects into the main DB
>
> This does not look very exciting.
> Can you imagine another way of proceeding ?
If the backup is complete (i.e., you don't need to perform rollforward 
recovery on the database after restoring the backup), then you may be 
able to use the foreignViews optional tool in order to siphon data out 
of the backup. For more information on this tool, please see 
http://db.apache.org/derby/docs/10.10/tools/rtoolsoptforeignviews.html

Hope this helps,
-Rick
>    M.
>
>

Re: Question on database, schemas and backups

Posted by Maison Mo <mo...@yahoo.fr>.

--- En date de : Ven 28.6.13, Rick Hillegas <ri...@oracle.com> a écrit :
> > For a multi-tenant application, we evaluate the
> multiple databases pattern and single database / multiple
> schemas pattern.
> > Regarding the backup process, it seems that the whole
> database is backed up by the
> SYSCS_UTIL.SYSCS_BACKUP_DATABASE(), thus all schemas in this
> DB : is that correct ?
> > Is it then possible to restore a single schema ?
> I don't believe so. There is no schema-isolating switch
> associated with 
> the restoreFrom connection attribute. It would be an
> interesting problem 
> to work on. It would involve subsetting the data in the
> system catalogs 
> and computing the referential closure of all tables, views,
> constraints, 
> and routines in the target schema. Those objects could
> reference objects 
> in other schemas.

Thank you for this information.
In mycase all schemas would be separated (no shared table, procedure or constraint...), if it can simplify things.
So in order to restore a backup for schema X only, I would need to do things manually :
- restore the backup in a temp DB
- expurge any schema X related object from main DB
- manually "merge" the temp DB schema X objects into the main DB

This does not look very exciting.
Can you imagine another way of proceeding ?

  M.

Re: Question on database, schemas and backups

Posted by Rick Hillegas <ri...@oracle.com>.
On 6/28/13 12:07 AM, Maison Mo wrote:
> Hello,
>
> For a multi-tenant application, we evaluate the multiple databases pattern and single database / multiple schemas pattern.
> Regarding the backup process, it seems that the whole database is backed up by the SYSCS_UTIL.SYSCS_BACKUP_DATABASE(), thus all schemas in this DB : is that correct ?
> Is it then possible to restore a single schema ?
I don't believe so. There is no schema-isolating switch associated with 
the restoreFrom connection attribute. It would be an interesting problem 
to work on. It would involve subsetting the data in the system catalogs 
and computing the referential closure of all tables, views, constraints, 
and routines in the target schema. Those objects could reference objects 
in other schemas.

Hope this helps,
-Rick
> Given a jdbc client, it is possible to change schema once connection is established. Is it also possible to change the database ?
>
> Thank you for your answers
>
> M.
>
>

Re: Question on database, schemas and backups

Posted by Dyre Tjeldvoll <Dy...@oracle.com>.
On 06/28/13 09:07 AM, Maison Mo wrote:
> Hello,
>
> For a multi-tenant application, we evaluate the multiple databases pattern and single database / multiple schemas pattern.
> Regarding the backup process, it seems that the whole database is backed up by the SYSCS_UTIL.SYSCS_BACKUP_DATABASE(), thus all schemas in this DB : is that correct ?
> Is it then possible to restore a single schema ?

I believe so, yes.

> Given a jdbc client, it is possible to change schema once connection is established.

SET SCHEMA,

See
http://db.apache.org/derby/docs/10.10/ref/rrefsqlj32268.html

 > Is it also possible to change the database ?

I think you need to create a new connection to the new database.


Regards,
Dyre