Appendix D. Preparing a Remote PostgreSQL Database

By default, the Manager’s configuration script, engine-setup, creates and configures the Manager database locally on the Manager machine. For automatic database configuration, see Section 2.3, “Configuring the Red Hat Virtualization Manager”.

To set up the Manager database with custom values on the Manager machine, see Appendix E, Preparing a Local Manually-Configured PostgreSQL Database. You should set up a Manager database before you configure the Manager. You must supply the database credentials during engine-setup.

The Data Warehouse’s configuration script offers the choice of creating a local or remote database. However, situations may arise where you might want to configure a remote database for Data Warehouse manually.

Use this procedure to configure the database on a machine that is separate from the machine where the Manager is installed.

Note

The engine-setup and engine-backup --mode=restore commands only support system error messages in the en_US.UTF8 locale, even if the system locale is different.

The locale settings in the postgresql.conf file must be set to en_US.UTF8.

Important

The database name must contain only numbers, underscores, and lowercase letters.

Enabling the Red Hat Virtualization Manager Repositories

Register the system with Red Hat Subscription Manager, attach the Red Hat Enterprise Linux Server and Red Hat Virtualization subscriptions, and enable the Manager repositories.

Procedure

  1. Register your system with the Content Delivery Network, entering your Customer Portal user name and password when prompted:

    # subscription-manager register
  2. Find the Red Hat Enterprise Linux Server and Red Hat Virtualization subscription pools and record the pool IDs:

    # subscription-manager list --available
  3. Use the pool IDs to attach the subscriptions to the system:

    # subscription-manager attach --pool=pool_id
    Note

    To view currently attached subscriptions:

    # subscription-manager list --consumed

    To list all enabled repositories:

    # yum repolist
  4. Configure the repositories:

    # subscription-manager repos \
        --disable='*' \
        --enable=rhel-7-server-rpms \
        --enable=rhel-7-server-supplementary-rpms \
        --enable=rhel-7-server-rhv-4.2-manager-rpms \
        --enable=rhel-7-server-rhv-4-manager-tools-rpms \
        --enable=rhel-7-server-ansible-2-rpms \
        --enable=jb-eap-7-for-rhel-7-server-rpms

Initializing the PostgreSQL Database

  1. Install the PostgreSQL server package:

    # yum install rh-postgresql95 rh-postgresql95-postgresql-contrib
  2. Initialize the PostgreSQL database, start the postgresql service, and ensure that this service starts on boot:

    # scl enable rh-postgresql95 -- postgresql-setup --initdb
    # systemctl enable rh-postgresql95-postgresql
    # systemctl start rh-postgresql95-postgresql
  3. Connect to the psql command line interface as the postgres user:

    su - postgres -c 'scl enable rh-postgresql95 -- psql'
  4. Create a default user. The Manager’s default user is engine and the Data Warehouse’s default user is ovirt_engine_history:

    postgres=# create role user_name with login encrypted password 'password';
  5. Create a database. The Manager’s default database name is engine and Data Warehouse’s default database name is ovirt_engine_history:

    postgres=# create database database_name owner user_name template template0 encoding 'UTF8' lc_collate 'en_US.UTF-8' lc_ctype 'en_US.UTF-8';
  6. Connect to the new database:

    postgres=# \c database_name
  7. Add the uuid-ossp extension:

    database_name=# CREATE EXTENSION "uuid-ossp";
  8. Add the plpgsql language if it does not exist:

    database_name=# CREATE LANGUAGE plpgsql;
  9. Ensure the database can be accessed remotely by enabling md5 client authentication. Edit the /var/lib/pgsql/data/pg_hba.conf file, and add the following line immediately underneath the line starting with local at the bottom of the file, replacing X.X.X.X with the IP address of the Manager or the Data Warehouse machine:

    host    database_name    user_name    ::0/32    md5
    host    database_name    user_name    ::0/128   md5
  10. Allow TCP/IP connections to the database. Edit the /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf file and add the following line:

    listen_addresses='*'

    This example configures the postgresql service to listen for connections on all interfaces. You can specify an interface by giving its IP address.

  11. Update the PostgreSQL server’s configuration. Edit the /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf file and add the following lines:

    autovacuum_vacuum_scale_factor='0.01'
    autovacuum_analyze_scale_factor='0.075'
    autovacuum_max_workers='6'
    maintenance_work_mem='65536'
    max_connections='150'
    work_mem='8192'
  12. Open the default port used for PostgreSQL database connections, and save the updated firewall rules:

    # firewall-cmd --zone=public --add-service=postgresql
    # firewall-cmd --permanent --zone=public --add-service=postgresql
  13. Restart the postgresql service:

    # systemctl rh-postgresql95-postgresql restart

Optionally, set up SSL to secure database connections using the instructions at http://www.postgresql.org/docs/9.5/static/ssl-tcp.html#SSL-FILE-USAGE.