Appendix D. Preparing a Remote PostgreSQL Database

You can configure a PostgreSQL database on a remote Red Hat Enterprise Linux 7 machine for the Red Hat Virtualization Manager or for Data Warehouse.
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 for Use with the Red Hat Virtualization Manager. 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 prepare 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.

Procedure D.1. Preparing a Remote PostgreSQL Database

  1. Install the PostgreSQL server package: 
    # yum install postgresql-server
  2. Initialize the PostgreSQL database, start the postgresql service, and ensure that this service starts on boot: 
    # su -l postgres -c "/usr/bin/initdb --locale=en_US.UTF8 --auth='ident' --pgdata=/var/lib/pgsql/data/"
# systemctl start postgresql.service
# systemctl enable postgresql.service
  3. Connect to the psql command line interface as the postgres user: 
    # su - postgres
$ 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 and add the plpgsql language: 
    postgres=# \c database_name
database_name=# CREATE LANGUAGE plpgsql;
  7. 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    X.X.X.X/32   md5
  8. Allow TCP/IP connections to the database. Edit the /var/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.
  9. Open the default port used for PostgreSQL database connections, and save the updated firewall rules: 
    # yum install iptables-services
# iptables -I INPUT 5 -p tcp --dport 5432 -j ACCEPT
# service iptables save
  10. Edit the following parameters in the /var/lib/pgsql/data/postgresql.conf file: 
    autovacuum_vacuum_scale_factor='0.01'
autovacuum_analyze_scale_factor='0.075'
autovacuum_max_workers='6'
maintenance_work_mem='65536'
max_connections='150'
  11. Restart the postgresql service: 
    # systemctl restart postgresql.service
Optionally, set up SSL to secure database connections using the instructions at http://www.postgresql.org/docs/9.6/static/ssl-tcp.html#SSL-FILE-USAGE.