Re: Postgres architecture for multiple instances

On 2/21/15 6:08 PM, Adrian Klaver wrote:
> On 02/21/2015 02:01 PM, Samuel Smith 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.

Certainly more than one customer may be affected by an outage, but there
are also fewer instances to be managed.  This can be particularly
important for upgrades - fewer systems to upgrade can be a good thing.

> The issue that has come up in previous discussions on this subject is
> that there is global meta data available to all users in a Postgres
> cluster(instance), in particular the database users. So while it is
> possible to restrict access to the database itself, it is more difficult
> if not impossible to restrict access to information about the other
> databases in the cluster.

This is only a problem if the customer has direct access to the
database.  If they are connecting through a software layer then
multi-tenant solutions are practical.

>> For the most part, I am completely on the fence about deciding either
>> way. What does the postgres community recommend in this situation? All
>> docs and books that I read seem to suggest that a postgres instance
>> manages databases (plural!) and normally there would be only one
>> postgres instance running per host.
>>
>> I am also interested in the best way forward with failover and high
>> availability.
>
> Currently the built in replication solutions work at the cluster level,
> not at the database level. There are third party tools, Slony and
> Bucardo come to mind, that can work at a more focused level.

Again, it depends in what kind of access your customers have to the
database.  Logical backups can be done on a per-database basis (and are
very practical for small datasets), but physical backups (which are more
efficient) must backup the entire cluster (except for the third-party
solutions noted above that have their own challenges).

You said you don't want to give a lot of information, but the optimal
solution depends on how your customers connect to their database:

1) Entirely through an application or API
2) Directly to the db via psql, PgAdmin, JDBC, etc.

Without that information it's hard to give really solid advice.

--
- David Steele
david@pgmasters.net