PostgreSQL - Running Multiple Instances
Running PostgreSQL 11 and PostgreSQL 10, or any combination of major versions, on a single system is easy when using RPM packages such as the ones provided at PostgreSQL YUM reposiotry. PGDG rpms make it easy to run multiple version of PostgreSQL on a single system. Install one more PGDG versions, setup, enable services using steps shared in previous blog.
This guide will help you get started with installing and configuring multiple PostgreSQL 11 clusters on a CentOS / RHEL 7 based system, which will also work for Fedora 27, 28, 29.
Installation
You call follow my previous blog to install PGDG packages.
Configuration
Multiple PostgreSQL Instances
Let us create two instances of PostgreSQL, one for myblog and second for gnucash.
First cluster for myblog
sudo touch /etc/systemd/system/postgresql-11-myblog.service
sudo systemctl edit --full postgresql-11-myblogPaste following content
.include /lib/systemd/system/postgresql-11.service
[Service]
Environment="PGDATA=/var/lib/pgsql/11/myblog/"Second cluster for gnucash
sudo touch /etc/systemd/system/postgresql-11-gnucash.service
sudo systemctl edit --full postgresql-11-gnucashPaste following content
.include /lib/systemd/system/postgresql-11.service
[Service]
Environment="PGDATA=/var/lib/pgsql/11/gnucash/"Initializing clusters
Cluster for myblog
PGSETUP_INITDB_OPTIONS='-k' sudo -E /usr/pgsql-11/bin/postgresql-11-setup initdb postgresql-11-myblogAbove command will create PostgreSQL cluster in /var/lib/pgsql/11/myblog/.
PGSETUP_INITDB_OPTIONS env is used to set initdb parameters.1
- -k Enable checksums on data pages
- -E preserve env with sudo
- initdb initialize cluster
- Newly created service filename ( do not include .service at the end )
Cluster for gnucash
PGSETUP_INITDB_OPTIONS='-k' sudo -E /usr/pgsql-11/bin/postgresql-11-setup initdb postgresql-11-gnucashAbove command will create PostgreSQL cluster in /var/lib/pgsql/11/gnucash/.
PGSETUP_INITDB_OPTIONS env is used to set initdb parameters.1
- -k Enable checksums on data pages
- -E preserve env with sudo
- initdb initialize cluster
- Newly created service filename ( do not include .service at the end )
Changing PostgreSQL Ports
Since we trying to run two clusters on same system, it is required to modify postgresql.conf to change default port, we will run myblog on port 15432 and gnucash on port 15433.
Cluster for myblog
sudo vi /var/lib/pgsql/11/myblog/postgresql.confAdd following at the very end of file.
port=15432Cluster for gnucash
sudo vi /var/lib/pgsql/11/gnucash/postgresql.confAdd following at the very end of file.
port=15433Enabling service
Enable PostgreSQL to start automatically on server reboot.
Cluster for myblog
sudo systemctl enable postgresql-11-myblogCluster for gnucash
sudo systemctl enable postgresql-11-gnucashStarting service
Cluster for myblog
For now, start PostgreSQL service manually.
sudo systemctl start postgresql-11-myblogCluster for gnucash
For now, start PostgreSQL service manually.
sudo systemctl start postgresql-11-gnucashConnect to PostgreSQL database
Cluster for myblog
sudo -u postgres psql -p 15432psql (11.2)
Type "help" for help.
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20181215 (Red Hat 8.2.1-6), 64-bit
(1 row)
postgres=# show data_directory;
data_directory
--------------------------
/var/lib/pgsql/11/myblog
(1 row)
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \qYou are connected to PostgreSQL, database postgres as postgres user (superuser).2
Cluster for gnucash
sudo -u postgres psql -p 15433psql (11.2)
Type "help" for help.
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20181215 (Red Hat 8.2.1-6), 64-bit
(1 row)
postgres=# show data_directory;
data_directory
---------------------------
/var/lib/pgsql/11/gnucash
(1 row)
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \qStopping service
Cluster for myblog
sudo systemctl stop postgresql-11-myblogCluster for gnucash
sudo systemctl stop postgresql-11-gnucashThis guide will get you started with running multiple PostgreSQL instances using an RPM-based installation. But what if you want to build your own customized RPM from a source RPM ? Stay tuned for Part 3 “Making your own binary RPMs”.