Chapter 3. PostgreSQL Configuration

The following section lists details for the postgres and cluster configuration.

3.1. Database Server Setup

For the reference environment, PostgreSQL 9.4 is used for the shared external database for the Red Hat CloudForms appliances, running Red Hat Enterprise Linux 7.3, with one regional database servers.

Create two virtual machines, each with eight virtual CPUs, sixteen GBs of RAM and two disks.

The first disk is assigned for the operating system and is 40 GB in size.

The second disk holds the database files, is backed by NFS, and has the following size:

Region

Database Disk Size

Production, Global and Region

500 GB

Table 3.1-1: Red Hat CloudForms Database Configuration

Note

The database size relates to the number of managed VMs and hosts.[3]

3.1.1. Write Ahead Logs Archive

To allow the standby database to replay transactions that the primary has no longer available in its pg_xlog directory, the database is configured to archive the Write Ahead Logs.

The required sizes for the NFS volumes used for this depend on the desired retention time for Write Ahead Logs and the data change rate for each database. For the reference environment the following sizes are used:

Region

WAL Archive Size

Production, Global and Region

500 GB

Table 3.1.1-1: WAL Storage Configuration

3.1.1.1. Production Database for Global and Region

For the production database, a NFS volume that is writable by both database systems is used. The top-level directory needs to be owned by the postgres user and group and the permissions should be set so that only the owner has any access.

Create a directory in the NFS volume that is owned by the postgres user: 

#mount <FILER_HOSTNAME:FILER_PATH> /mnt

#mkdir /mnt/wal-archive

#chown postgres:postgres /mnt/wal-archive

#umount /mnt

On the database systems, add the following line in /etc/fstab : 

<FILER_HOSTNAME:FILER_PATH>/wal-archive / /var/opt/rh/rh-postgresql94/lib/pgsql/wal-archive nfs defaults 0 0

On the database systems, run the following commands to mount the NFS volumes: 

#mkdir /var/opt/rh/rh-postgresql94/lib/pgsql/wal-archive

#mount -a

#systemctl enable nfs-utils

3.2. Database Replication Setup

Once this basic setup is done, apply the following configuration steps only on the first database system.

3.2.1. Creating a New Database

For a new region, create an empty primary database with the following steps.

  • appliance_console
  • Create Internal Database
  • Create v2_key
  • Create New Partition
  • Create New Region
  • Initialize

Edit /var/opt/rh/rh-postgresql94/lib/pgsql/data/postgresql. conf __ and change the following parameters: 

* shared_buffers = 1GB (¼ of total allocated physical memory)

Disable the evmserverd because we do not want postgres to be managed by CloudForms, but by pacemaker. That is configured later. 

#systemctl stop evmserverd

#systemctl disable evmserverd

On the secondary database, also create a new database with the same region number, but know that the database will be wiped later on.

  • appliance_console
  • Create Internal Database
  • Fetch v2_key
  • Create New Partition
  • Create "New" Region
  • Initialize

Edit /var/opt/rh/rh-postgresql94/lib/pgsql/data/postgresql.conf and change the following parameters: 

* shared_buffers = 1GB (¼ of total allocated physical memory)

Disable the evmserverd because we do not want postgres to be managed by CloudForms, but by pacemaker . That is configured later. 

#systemctl stop evmserverd

#systemctl disable evmserverd

Also shutdown the secondary because we will be replacing its data directory with a basebackup. 

#systemctl stop $APPLIANCE_PG_SERVICE

3.2.2. Common Database Configuration

Add the following lines to /var/opt/rh/rh-postgresql94/lib/pgsql/data/pg_hba.conf to allow the CloudForms appliances to connect to the external database, to enable the pg_basebackup command locally, and to allow replication connections from the other cluster node.

Change the IP address range for the cloudforms line to only include the CloudForms appliances from the region (UI and workers).

Change the IP address range for the replicator line to only include the two database cluster nodes.

Instead of specifying a range, add multiple lines and put IP_ADDRESS/MASK in the second-to-last column. 

local replication postgres peer

host replication replicator 10.19.11.0/21 md5

host vmdb_production cloudforms 10.19.11.0/21 md5

Edit postgresql.conf and make the following changes that are required for replication 

archive_mode = on
archive_command = 'cp %p /var/opt/rh/rh-postgresql94/lib/pgsql_ /wal-archive/%f'

Create the replication user on the first database. 

#su - postgres

#createuser -P --replication replicator

Again, write down the password given to createuser. This is needed to configure the cluster database resource.

From the second database, take a database backup using the pg_basebackup command. This will do three things:

  1. Test the tcp stream connectivity from secondary to primary
  2. Copy over a full primary database snapshot
  3. Create a wal-archive marker from which the synchronization can start 
#su - postgres

#rm -rf /var/opt/rh/rh-postgresql94/lib/pgsql/data/*

#pg_basebackup -h primarydb.example.com -D \ /var/opt/rh/rh-postgresql94/lib/pgsql/data/ -X stream -P -U \ replicator

<prompt for password>

At this point, you can test the configuration by starting the primary and then the secondary. 

#systemctl start $APPLIANCE_PG_SERVICE

The primary should be doing the following:

  • Writing files to: /var/opt/rh/rh-postgresql94/lib/pgsql/wal-archive
  • Running a process with a description like the following: 
postgres: wal sender process postgres 127.0.0.1(44663) streaming 0/2000000

The secondary should be doing the following:

  • Running a process with a description like the following: 
postgres: wal receiver process   streaming 0/2000000

Once this is confirmed, shutdown the secondary, then shutdown the primary. 

#systemctl stop $APPLIANCE_PG_SERVICE