Re: Postgres architecture for multiple instances - Mailing list pgsql-general

On Sat, Feb 21, 2015 at 3:01 PM, Samuel Smith <pgsql@net153.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.