>Shared Database and Shared Schema initially sounds great, until you realize >what a pain it will be to do backups/restores of individual tenants. > Eventually, a tenant will mess up it's data and will ask you to restore back > to date/time. Now you are in a real pickle, unless each tenant has it's own > DB and you have a solid point-in-time DB recovery process.
Mike is correct here. Shared DB is not going to work with Multi-tenant architecture. Here is an excellent article from an Enterprise DB guy regarding Multi-tenancy: http://rhaas.blogspot.com/2010/07/multi-tenancy-and-virtualization.html >Mysql allows PITR at the DB level, where postgresql is an all/nothing (i.e.ALL >databases) PITR recovery. So, if you want to do multi-tenant on a >big >gigantic DB server, the way to go is mysql. Mike, you have probably missed an important feature of Postgres (Write-ahead logging) which is years ahead of Oracle and MySql in PITR. Following articles are explain WAL feature in detail: http://www.postgresql.org/docs/9.3/static/continuous-archiving.html http://blog.ganneff.de/blog/2008/02/15/postgresql-continuous-archivin.html > If you choose to use postgres, you will have to create a mini postgres server (possibly running on a different port/VM) for each tenant. Please go through Multi-tenancy vs virtualization http://rhaas.blogspot.com/2010/07/multi-tenancy-and-virtualization.html Many organizations such as enterprize-db and hub.org are serving thousands of customers each having different databases and schemas with a single postgres instance. In my opinion, choosing postgres is not a bad idea for multi-tenant architecture. Regards: Ejaz Ahmed
