Managing Multiple PostgreSQL Instances on FreeBSD
FreeBSD allows the management of multiple instances of PostgreSQL by means ofrc.conf(5).
The trick is to use profiles, that are available for the PostgreSQL rc script (
/usr/local/etc/rc.d/postgresql) even if not well documented, at least in my opinion.
In order to understand how to deal with multiple PostgreSQL instances, consider a system with two cluster: test and prod.
In
/etc/rc.conf you need to define the postgresql_profiles variable, where you list the clusters separated by spaces. Then, for each profile, you define the well know postgresql_xxx variables, specifying the profile name before the variable suffix. For example, to define a PGDATA, that will be usually defined into postgresql_data variable, you need to specify a postgresql_<profile-name>_data variable.
Therefore, in
/etc/rc.conf you need to specify the following:
postgresql_profiles="test prod"
postgresql_test_data="/postgres/12/test"
postgresql_test_enable="YES"
postgresql_prod_data="/postgres/12/prod"
postgresql_prod_enable="YES"
Now you need to manage all instances by specifying the profile name on every
service(8) call:
% sudo service postgresql start test
% sudo service postgresql status test
pg_ctl: server is running (PID: 35979)
/usr/local/bin/postgres "-D" "/postgres/12/test"
You need to specify the profile name as last argument to
service(8) invocation.
But there is more: if you don’t specify any profile on the command line,
service(8) will iterate on all available profiles. As an example, the following two sequences are equivalent:
% sudo service postgresql stop
===> postgresql profile: test
===> postgresql profile: prod
# equivalent to
% sudo service postgresql stop test
% sudo service postgresql stop prod
With this simple profile-based management, it is easy to handle and manage multiple PostgreSQL instances on the same FreeBSD host.