On 21/12/2011 4:08 AM, Bill Moran wrote:
In response to "James B. Byrne"<byrnejb@xxxxxxxxxxxxx>:
We run a small in-house data centre for our various
operations. Currently, we are moving applications from
dedicated boxes to kvm based CentOS-6.1 virtual machines
on a single CentOS-6.1 host. At issue is the question on
how to handle the PostgreSQL instances that we currently
have running on different machines.
As we see it presently we have the option of having one VM
host a centralized PostgreSQL server with multiple
databases or continue with each application specific VM
running its own copy of PostgreSQL with just the dedicated
application database.
Since whatever we chose we are likely to be using five
years from now I am soliciting informed option over which
option is considered a better choice for the long term.
In my experience, you'll be better off using a single DB for all the
databases. With proper roles, database permissions, and pg_hba.conf,
you'll have acceptable security. The advantage to doing this is
better utilization of hardware, since you don't have all the overhead
of multiple VMs using up memory, CPU, and IO load. You also have
less instances to monitor.
While I tend to agree with this, there are some important downsides too.
Perhaps the most important is that you can't currently use streaming or
WAL-shipping replication to replicate only *one* database out of a
cluster. You have to replicate all databases in the cluster. If you have
some DBs that are small or low traffic but very important, and other DBs
that're big or high traffic but less important, this can be a problem.
As you noted, it's also harder to isolate performance between DBs and
protect more important DBs from response time drops caused by less
important but heavily loaded DBs, big reporting queries on other DBs, etc.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
