Re: New database or New Schema?

"David Fetter" <david(at)fetter(dot)org> wrote in message
news:20090821170259(dot)GA6792(at)fetter(dot)org(dot)(dot)(dot)
> On Fri, Aug 21, 2009 at 12:16:45PM -0400, Eric Comeau wrote:
>> In the next release of our software the developers are moving to
>> JBoss and have introduced the use of JBoss Messaging. They want to
>> change from using the built-in hsqldb to using our PostgreSQL
>> database.
>>
>> What is the best approach, create a new database or new schema
>> with-in our current PostgreSQL database?
>>
>> I'm leaning toward creating a new schema as we already have built-in
>> procedures to backup and restore the application database.
>
> It's difficult to tell from what you've described. If you were even
> vaguely contemplating queries that touched both that database and
> others you already have, it becomes much easier: use a schema.

So to be so vague I was trying to keep the description short.

I don't see that we will need to query data between the systems for any
business type of operations. Potentially for troubleshooting.

I was hoping to get more input on what the best-practices
(benefits/disadvantages) of going with a new schema versus a new database.

I don't really see any downside with going with a new schema in the
application database, and since we already have built-in procedures (at the
application layer) to back-up and restore the application database (using
pg_dump) I don't believe they will need to change. Adding a new database, I
need to update the backup process to either dump a second db (jboss_jms) or
start using pg_dumpall.

Eric