Re: Multiple PostgreSQL instances on one machine

On Fri, Jun 8, 2018 at 3:29 PM Tony Sullivan <tony(at)exquisiteimages(dot)com> wrote:
>
> I am trying to consolidate some machines in my server room particularly in
> the testing environment and I was hoping someone could point me in the
> right direction.
>
> I currently have three machines running PostgreSQL for testing purposes.
> Each week a backup is made of the production database and this is deployed
> onto these three machines. None of these machines is any where near
> capacity. I would like to host all three instances on one machine.
>
> I know that "initdb" can be used to create additional installations, but
> the part I am unsure about is the tablespace. The production database has
> a tablespace defined that resides on its SSD. When I use the production
> backup, I have to create a mount point on the test machine with the same
> name as the one on the production database. I am not certain how I would
> handle this situation if I am serving three separate instances of that
> database from one computer.

Generally speaking, this not an issue. You can consolidate all three
machines to one cluster, or to multiple clusters running on same box.
The approaches have pros and cons relative to each other. I would
personally tend to simple create different databases on the same
cluster unless you had a reason not to. The database would share:

*) database roles (in particular superuser accounts)
*) WAL (possibly presenting a i/o bottleneck)
*) replication for HS/SR (which is all or nothing at the cluster level)
*) shared memory
*) availability -- if one database crashes they will all crash
*) tcp/ip port (this is good -- you don't have to worry about what
port you're connecting to)

merlin