Обсуждение: One PostgreSQL server for multiple apps?

Поиск
Список
Период
Сортировка

One PostgreSQL server for multiple apps?

От
Pandu Poluan
Дата:

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,
--

Re: One PostgreSQL server for multiple apps?

От
Sean Davis
Дата:
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@poluan.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,
--


Re: One PostgreSQL server for multiple apps?

От
Pandu Poluan
Дата:

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?

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?

Rgds,
--

On Feb 23, 2014 10:28 PM, "Sean Davis" <sdavis2@mail.nih.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@poluan.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,
--


Re: One PostgreSQL server for multiple apps?

От
Sean Davis
Дата:



On Sun, Feb 23, 2014 at 10:37 AM, Pandu Poluan <pandu@poluan.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@mail.nih.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@poluan.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,
--



Re: One PostgreSQL server for multiple apps?

От
Thomas Kellerer
Дата:
Pandu Poluan wrote on 23.02.2014 16:37:
> 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?

I don't think so. "Databases" in SQL Server and Postgres are very similar.

The main difference is that in Postgres you can not do cross-database queries.

The schema concept inside a single database is more or less the same thing if we are talking about SQL Server 2008 and
later.

I'm not familiar with SQL Server's instance concept but I would consider a Postgres "instance" a separate data
directory("cluster") with it's own set of processes listening on it's own port. 

> 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?

No, I don't think this is currently possible with Postgres