Re: PostgreSQL server architecture

In response to "James B. Byrne" <byrnejb(at)harte-lyne(dot)ca>:
> 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.

The disadvantage of doing so is a) that it doesn't scale as far, and
b) if you have one badly behaved application it can negatively affect
other databases.

In the case of both A and B, the answer when you hit that problem is
to just add another VM or physical machine and move databases off the
main server instance an onto their own instance on an as-needed basis.

In my experience, what you end up with as time goes on and you learn
how things operate are a few database servers housing many database.
With things spread out across the multiple instances as seems most
logical based on your observation of how they behave.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/