Re: One PostgreSQL server for multiple apps?

On Sun, Feb 23, 2014 at 10:37 AM, Pandu Poluan <pandu(at)poluan(dot)info> wrote:

> Thanks for the confirmation.
>
> I have been reading around, and it seems (CMIIW) that in PostgreSQL, a
> 'database' is similar to a SQL Server instance, while to create separate
> databases within an 'instance', I need to create 'schemas'.
>
> Am I reading this right?
>
> Perhaps it is easiest to talk about server processes. There is a server
process that can contain multiple databases. Each database can contain
multiple schemas. Each schema contains tables, indexes, etc. Each
database comes with at least one schema.

> There is another question, is it possible to limit the resources used by
> separate instances/databases so that different apps don't start to step on
> each others' toes?
>
Not really, although there are some tuning parameters that can be applied
on a per-database level.

In practice, I'd suggest starting with the typical single server approach,
get to know postgresql, and if there are particular reasons for a more
complicated setup (and more than one server is more complicated, as each
needs monitoring, backup, etc.), you can always copy databases from one
server to another.

> Rgds,
> --
> On Feb 23, 2014 10:28 PM, "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov> wrote:
>
>> A single postgresql server can host multiple separate databases, each
>> with its own data, authentication, etc. This single-server,
>> multiple-database approach is a typical deployment strategy.
>>
>>
>>
>>
>>
>>
>> On Sun, Feb 23, 2014 at 9:52 AM, Pandu Poluan <pandu(at)poluan(dot)info> wrote:
>>
>>> Hello!
>>>
>>> First, I apologize if this email sounds very... novice. I came from MS
>>> SQL Server background, and am recently 'plunged' into a mid-to-long term
>>> project to drop dependencies on proprietary databases, and use PostgreSQL
>>> instead.
>>>
>>> Now, in MS SQL Server, I can create a single server to service several
>>> apps together. If the app is a light load and doesn't require something
>>> more than db_owner privileges, I usually just clump them together in the
>>> Default instance, using a new 'database'. If the app has heavier
>>> requirements, then I would spin up a new instance, on the same server
>>> (provided RAM and IOPS requirements suffice, of course).
>>>
>>> How would the same principle be applied in PostgreSQL?
>>>
>>> Thanks in advance for your kind guide.
>>>
>>> Rgds,
>>> --
>>>
>>
>>