Re: Experience with many schemas vs many databases

On Nov 15, 3:45 pm, lovetodrinkpe(dot)(dot)(dot)(at)gmail(dot)com (undisclosed user)
wrote:
> The app is very similar to wordpress MU. Each user has the same schema but
> different data.
> <snip>

> 4. Ability to backup per user
>
> Backing up data by user is required for my solution. A lot of times, users
> screw up and they want to rollback to a previous state.
> <snip>

I would still lean toward single schema, but that is just me. To make
that work, I would need the following capabilities:
1. Ability to backup the entire database
2. Ability to restore the entire database elsewhere
3. Ability to restore a single user

1. The software exists as you know. BTW, the software also exists
from PG if you wish to make one user per schema. You have to have the
space to back it up. 80 GB might come out much larger in fact I am
certain it would.
2. This requires that you have a test database you can restore to with
enough room for the full data set plus the external file for the
restore. The software is already available from PG.
3. This one depends on how automated you need the solution. If it has
to be highly automated (read repeatable and less prone to error) that
will require you to write the code to extract the data for a single
user from the backup, delete the data for the single user, and restore
the backup for the single user.
3a. If the occurrence can have less automation, you can use a tool to
do most of the heavy lifting for you. I use phpPgAdmin. It can do
the selects you need then allow you to do the deletes. It can extract
the data for the user from the backup either in copy or distinct
insert statements. Finally, if you want to get a start on the code,
phpPgAdmin will in essence do that for you. When you do a query, it
will usually give you an option to "edit" the SQL. This gives you
copy/paste access to the code the tool has written.

Loyal