On Nov 15, 3:45 pm, lovetodrinkpe...@xxxxxxxxx (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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
