Re: Postgres architecture for multiple instances

Samuel Smith schrieb am 23.02.2015 um 05:21:
> I am stuck with redhat as the OS so I'll only have initdb. But this is a good point.
> I assume there is nothing wrong with having multiple postgres instances (clusters) all running under a
> single postgres user on different ports on the same machine?

No, there isn't. One important advantage of that is that you can do a PITR for each customer individually.
If you keep all your databases in a single instance/cluster you can only do PITR for all of them.
For a multi-tenant system I don't think that will be acceptable.

Another advantage is, that you can have control superuser access on a per-cluster basis

> But then what is the best way to handle connecting to an individual cluster (local only for admin purposes) with psql?

psql uses several environment variables for the default connection information:

http://www.postgresql.org/docs/current/static/libpq-envars.html

You can either create one shell script for each instance/cluster or use different OS users that have different default environment variables - that would be closer to what you have now.

Thomas