How to setup a PostgreSQL database for ManageIQ and CloudForms

Posted by Christian Jung on Wed, Nov 23, 2016
In CloudForms, IT, ManageIQ
Tags

When playing with several CloudForms and ManageIQ appliances in my lab environment, I face the problem that I quickly run out of RAM on my virtual infrastructure. I’m using a number of Intel NUC’s and although the newest models can go up to 32 GB of RAM, memory has always been my bottleneck. So I thought instead of running a PostgreSQL server in each virtual CloudForms or ManageIQ appliance, it might be more efficient to have a dedicated PostgreSQL Server for all of them. There are other use cases where one might want to use a external database:

  • High Availability: built a PostgreSQL cluster to increase fault tolerance

  • Scalability: a dedicated database server can make it easier to scale up

  • Maintenance: if the database is clustered upgrades and other maintenance tasks can be performed on one node, while all other nodes continue to work

These are just some examples and there are many more use cases and examples.

Prepare the PostgreSQL Server

Obviously the first step is to setup the PostgreSQL Server on a physical or virtual machine (or multiple, if one wants to setup a clustered databse). This post will not go into any details on how to do that. It’s covered in many other posts and documents. When tuning the database server, it is recommended to have a look at the example configuration which can be found on an CloudForms or ManageIQ virtual appliance.

# example configuration on CloudForms Appliances
/opt/rh/cfme-appliance/TEMPLATE/var/opt/rh/rh-postgresql94/lib/pgsql/data/
# example configuration on ManageIQ Appliances
/opt/manageiq/manageiq-appliance/TEMPLATE/var/opt/rh/rh-postgresql95/lib/pgsql/data

Make sure the applicable settings are transferred to your PostgreSQL Server to get best possible performance. Since the external database will be used a lot, its performance is crucial for the entire environment.

Tools like pg_bench can help to verify database performance.

Create the user, database and privileges

After the database server has been installed, tuned and tested, it’s time to create the user and database. I’m using one database server for many CloudForms and ManageIQ appliances. To be able to isolate database access, I create a user for every appliance and restrict this user to its dedicated database.

For the following example, let’s assume there is a new ManageIQ Appliance running the Euwe release.

# create a new user and grant super user privilegescreateuser -s -P miq-euwe# create a dedicate database and set ownercreatedb -O miq-euwe miq-euwe

We just created a user call miq-euwe and gave him ownership to the new database with the same name. Why this user was created as a super user, will be covered in the next paragraph. Before we move on, we need to grant this user remote login privileges. Modfy pg_hba.conf on the PostgreSQL server and add a line similar to this:

host all miq-euwe 192.168.1.1/32 md5

This will allow the user miq-euwe to login into all databases from the system with the IP 192.168.1.1 after authenticating with the correct password. Test the connection from the appliance with the psql command:

psql -h <hostname> -U miq-euwe -W miq-euwe

Configure database with appliance console

To configure the appliance to use the external database, the appliance_console can be used (on current version, you can just use the alias “ap”).

$ appliance_console

Chose Option 8 “Configure Database” and go through the guided procedure.

What happens

The appliance performs a number of steps while configuring the database:

  1. connect to the database and try to login

  2. verify no process is currently using the database

  3. if no other process is using the database, delete it

  4. create a new database with the specified name and credentials

  5. populate the database with the initial tables and data

On the first start of the evmserverd service, additional data is written into the database. During that phase the database can grow to around 30 to 40 MB.

Now it might be clearer, why we needed a user with superuser privilege and why the pg_hba.conf has to allow access to all databases. But not all is lost, this can be fixed after the initial setup is done.

Fix database configuration

After setting up the appliance and populating the initial data, I restrict database access to what is absolutely necessary. This is done by performing two simple steps.

Fix pg_hba.conf:

# only grant access to miq-euwe database
host miq-euwe miq-euwe 192.168.1.1/32 md5

To make this change active, send a Hangup signal to the master PostgreSQL process:

kill -HUP <PID>

Remove superuser privilege from user:

pgsql=# alter role miq-euwe nosuperuser;

Verify the appliance still works correctly after applying those changes.

Clean up the appliance

When logging into the appliance, the active database configuration can be found in the Configuration, Database menu.

Verify external database configuration

If everything works fine, the internal database role can be disabled, which will also free up some memory in the virtual machine.

How to disable database role

Wrap up

I’m using this setup since CloudForms 5.6, aka ManageIQ Darga on a daily basis. I also tested this with previous versions of CloudForms back to 5.3 and the basic steps have always been the same.

To make ManageIQ and CloudForms work without superuser privileges during setup, a Bugzilla Ticket has been created.