It’s always something that comes up, even in today’s virtualised and containerised environments… “How can I run multiple PostgreSQL clusters on a single server?”
If you’re used to doing RPM installs on RedHat/CentOS, then you’ll be aware that the default location for all database files is:
/var/lib/pgsql/<version>/data
And the related service name is:
postgresql-<version>
That works well when you’re only running a single cluster of each major version on a server, but things become complicated when you need more than one.
What network port should it use?
This is the easiest to solve, as the default is “5432”. By convention subsequent clusters running on a host simply increment this number by one, so “5433”, then “5434”, etc.
Modify $PGDATA/postgresql.conf, and uncomment this line with the new value, and restart the cluster:
#port = 5432 # (change requires restart)
Where will $PGDATA be?
The simplest way is to extend the existing structure: /var/lib/pgsql/<version>/<cluster_name>/data
What will the services be called?
Assuming you’re using CentOS v7 and systemd then there are two options: a service file template postgresql-<version>@.service or a service file per cluster postgresql-<version>-<cluster_name>.service.
Service File Template
/etc/systemd/system/postgresql-<version>@.service
.include /lib/systemd/system/postgresql-<version>.service [Unit] Description=PostgreSQL <version> database server - cluster_name:%i [Service] Environment=PGDATA=/var/lib/pgsql/<version>/%i/data
To start: sudo systemctl start postgresql-9.6@main
Video
I presented my ideas to the London PostgreSQL Users Group: https://skillsmatter.com/skillscasts/11736-multiple-postgre-clusters-on-redhat-centos#video