Appendix D. Preparing a Remote PostgreSQL Database

Procedure

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

   # subscription-manager register

   Copy to Clipboard Toggle word wrap

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

   # subscription-manager list --available

   Copy to Clipboard Toggle word wrap

3. Use the pool IDs to attach the subscriptions to the system:

   # subscription-manager attach --pool=pool_id

   Copy to Clipboard Toggle word wrap
   Note

   To view currently attached subscriptions:

   # subscription-manager list --consumed

   Copy to Clipboard Toggle word wrap

   To list all enabled repositories:

   # yum repolist

   Copy to Clipboard Toggle word wrap

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.9-rpms \
       --enable=jb-eap-7-for-rhel-7-server-rpms

   Copy to Clipboard Toggle word wrap

1. Install the PostgreSQL server package:

   # yum install rh-postgresql95 rh-postgresql95-postgresql-contrib

   Copy to Clipboard Toggle word wrap

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

   Copy to Clipboard Toggle word wrap

3. Connect to the psql command line interface as the postgres user:

   su - postgres -c 'scl enable rh-postgresql95 -- psql'

   Copy to Clipboard Toggle word wrap

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';

   Copy to Clipboard Toggle word wrap

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';

   Copy to Clipboard Toggle word wrap

6. Connect to the new database:

   postgres=# \c database_name

   Copy to Clipboard Toggle word wrap

7. Add the uuid-ossp extension:

   database_name=# CREATE EXTENSION "uuid-ossp";

   Copy to Clipboard Toggle word wrap

8. Add the plpgsql language if it does not exist:

   database_name=# CREATE LANGUAGE plpgsql;

   Copy to Clipboard Toggle word wrap

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

   Copy to Clipboard Toggle word wrap

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='*'

    Copy to Clipboard Toggle word wrap

    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'

    Copy to Clipboard Toggle word wrap

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

    Copy to Clipboard Toggle word wrap

13. Restart the postgresql service:

    # systemctl rh-postgresql95-postgresql restart

    Copy to Clipboard Toggle word wrap