Re: Postgres architecture for multiple instances

On Sat, Feb 21, 2015 at 3:01 PM, Samuel Smith <pgsql(at)net153(dot)net> wrote:
> Howdy,
> I am looking for advice on migrating to postgres from another database
> system.
>
> Without going into too much detail, my company offers a software solution
> which we self host ourselves in our data center. We have gotten a green
> light from management to start using postgres as a free and cheaper
> database. Normally the application connects to a DB2 database.
>
> The current architecture has multiple DB2 databases installed on a single
> linux host (on top of vmware with 4 vcpus). Each DB2 instance runs as a
> different local linux user and only manages one database. Normally there are
> less than 5 DB2 instances per host.
>
> My question is, if I am going to replace DB2 as the database with Postgres,
> should I keep to this architecture of each customer application gets their
> own database instance? What I was thinking would be much more simpler would
> be to have multiple databases under one postgres instance (which is how most
> linux distros install postgres anyway). Having multiple databases under one
> instance seems to be a much easier way. Of course then the issue becomes if
> there is an issue with this single instance, multiple customers will be
> affected.

I'd run a debian based distro (Ubuntu or Debian work well) and use the
pg_* commands to create the clusters the same way. Gives you the
maximum separation for clients.

pg_createcluster

Usage: /usr/bin/pg_createcluster [options] <version> <cluster name>
[-- <initdb options>]

Options:
-u <uid> cluster owner and superuser (default: 'postgres')
-g <gid> group for data files (default: primary group of owner)
-d <dir> data directory (default:
/var/lib/postgresql/<version>/<cluster name>)
-s <dir> socket directory (default: /var/run/postgresql for clusters
owned by 'postgres', /tmp for other clusters)
-l <dir> path to desired log file (default:
/var/log/postgresql/postgresql-<version>-<cluster>.log)
--locale <encoding>
set cluster locale (default: inherit from environment)
--lc-collate/ctype/messages/monetary/numeric/time <locale>
like --locale, but only set for a particular category
-e <encoding> Default encoding (default: derived from locale)
-p <port> port number (default: next free port starting from 5432)
--start start the cluster after creating it
--start-conf auto|manual|disabled
Set automatic startup behaviour in start.conf (default: 'auto')
--createclusterconf=file alternative createcluster.conf to use
--environment=file alternative environment file to use
<initdb options> other options to pass to initdb

Just use -u and -d to put it where you want, -l to setup logging and
-p to set the port you want.

--
To understand recursion, one must first understand recursion.