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