PostgreSQL - Zero to Hero (part 2)

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-myblog

Paste 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-gnucash

Paste 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-myblog

Above 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-gnucash

Above 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.conf

Add following at the very end of file.

port=15432

Cluster for gnucash

sudo vi /var/lib/pgsql/11/gnucash/postgresql.conf

Add following at the very end of file.

port=15433

Enabling service

Enable PostgreSQL to start automatically on server reboot.

Cluster for myblog

sudo systemctl enable postgresql-11-myblog

Cluster for gnucash

sudo systemctl enable postgresql-11-gnucash

Starting service

Cluster for myblog

For now, start PostgreSQL service manually.

sudo systemctl start postgresql-11-myblog

Cluster for gnucash

For now, start PostgreSQL service manually.

sudo systemctl start postgresql-11-gnucash

Connect to PostgreSQL database

Cluster for myblog

sudo -u postgres psql -p 15432
psql (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=# \q

You are connected to PostgreSQL, database postgres as postgres user (superuser).2

Cluster for gnucash

sudo -u postgres psql -p 15433
psql (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=# \q

Stopping service

Cluster for myblog

sudo systemctl stop postgresql-11-myblog

Cluster for gnucash

sudo systemctl stop postgresql-11-gnucash

This 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”.