Installing Multiple Instances (Clusters) of PostgreSQL on CentOS 7

Today I installed multiple instances of PostgreSQL 9.4 on one CentOS 7 machine. I learned a little bit about systemd and how it can adjust the Out-Of-Memory killer with OOMScoreAdjust.

I also learned about data page checksums that were added in PostgreSQL 9.3 but not enabled by default. (Coming from SQL Server this corresponds to PAGE_VERIFY CHECKSUM which is a per-database setting. CHECKSUM was introduced in SQL Server 2005 and is the default for new databases and can be changed at any time.)

I happened to find a similar post about installing multiple PostgreSQL instances but I already had the postgres binaries, I just needed to configure additional services to run the different clusters.

# Installing multiple instances of Postgresql 9.4 on CentOS 7 64-bit using systemd
# This disables the Out-Of-Memory killer for the main process

# Change these three variables to pick an instance name, port 
# and data directory. I suggest using a directory name that is the
# same as the instance name but you can make that different.
MYPGINSTANCE=cluster1
MYPGPORT=5432
MYPGDATA=/data/pgsql/9.4/${MYPGINSTANCE}

# Create a data directory and change its ownership
mkdir –p ${MYPGDATA}
chown postgres:postgres ${MYPGDATA}

# initialize the database using user postgres and enable page checksums (introduced in 9.3)
su postgres -c "/usr/pgsql-9.4/bin/initdb -k -D ${MYPGDATA} -U postgres"

# Use heredoc to create a new .service file for this instance:
cat >/etc/systemd/system/postgresql-9.4-${MYPGINSTANCE}.service <<EOF
.include /lib/systemd/system/postgresql-9.4.service

[Service]
Environment=PGDATA=${MYPGDATA}
Environment=PGPORT=${MYPGPORT}
EOF

# Lets see if this looks right...
cat /etc/systemd/system/postgresql-9.4-${MYPGINSTANCE}.service

# Enable the service at startup, start it, and check its status
systemctl enable postgresql-9.4-${MYPGINSTANCE}
systemctl start postgresql-9.4-${MYPGINSTANCE}
systemctl status postgresql-9.4-${MYPGINSTANCE}

# Lets see the logs for this new service before the postgresql logging takes over
journalctl -u postgresql-9.4-${MYPGINSTANCE}