Running Multiple PostgreSQL Instances

There are many reasons to run multiple database servers on a single FreeBSD machine. You might want to test out a new version of Postgres, or replicate a database in addition to running an application. Whatever the reason, it's pretty easy to get a second instance of PostgreSQL running, thanks to the easy configurability of FreeBSD's init system.

Copy and Edit the Init Files

You can copy and edit the configuration files you need in a few lines using sed:

# cd /usr/local/etc/rc.d/
# sed 's/postgresql_/postgresql0_/' <postgresql >postgresql0
# sed -i '' 's/name=postgresql/name=postgresql0/' postgresql0

Then edit the file and change the postgresql0_data directory to /usr/local/pgsql/data0. This is also an opportune time to review the file, and make sure you don't see anything strange. Small typing errors with sed can have big consequences.

Verify that the permissions are set correctly on postgresql0. If not:

# chmod 555 postgresql0

Initialize the New Database

You have to initialize a new database before starting the server:

# su pgsql
$ initdb -D /usr/local/pgsql/data0

Configuration

Change the port for the server in /usr/local/pgsql/data0/postgresql.conf to a free port. Check /etc/services for a list of ports to avoid. If you pick a number with five random digits, you're likely to be fine. grep can help you double-check the port quickly:

$ grep 12345 /etc/services

Then configure /usr/local/pgsql/data0/pg_hba.conf according to your requirements, and add this to /etc/rc.conf:

postgresql0_enable="YES"

Firing It Up

If everything has gone according to plan, your server should be ready to go:

# service postgresql0 start

If you get a starting server message, you can check to make sure it continued to run after that message with:

# service postgresql0 status

If the server crashed soon after starting, you can check the log at /usr/local/pgsql/data0/postmaster.log.