3.14. Migrating the Reports Database to a Remote Server Database

⁠3.14. Migrating the Reports Database to a Remote Server Database

You can migrate the ovirt_engine_reports database to a remote database server after the Red Hat Enterprise Virtualization Manager has been initially configured.

This task is split into two procedures. The first procedure, preparing the remote PostgreSQL database, is a necessary prerequisite for the migration itself and presumes that the server has Red Hat Enterprise Linux installed and has been configured with the appropriate subscriptions.

The second procedure, migrating the database, uses PostgreSQL pg_dump and pg_restore commands to handle the database backup and restore. As such, it is necessary to edit the /var/lib/ovirt-engine-reports/build-conf/master.properties file with the updated information. At a minimum, you must update the location of the new database server. If the database name, role name, or password are modified for the new database server, these values must also be updated in both master.properties files. This procedure uses the default ovirt_engine_reports database settings to minimize modification of this file.

⁠

Procedure 3.19. Preparing the Remote PostgreSQL Database for use with the Red Hat Enterprise Virtualization Manager

Log in to the remote database server and install the PostgreSQL server package:
```
# yum install postgresql-server
```
Initialize the PostgreSQL database, start the postgresql service, and ensure that this service starts on boot:
```
# service postgresql initdb
# service postgresql start
# chkconfig postgresql on
```
Connect to the psql command line interface as the postgres user:
```
# su - postgres
$ psql
```
Create a user for the Manager to use when it writes to and reads from the database. The default user name for the ovirt_engine_reports database is ovirt_engine_reports:
```
postgres=# create role user_name with login encrypted password 'password';
```
Note
The password for the ovirt_engine_reports user is located in plain text in /var/lib/ovirt-engine-reports/build-conf/master.properties. Any password can be used when creating the role on the new server, however if a different password is used then this file must be updated with the new password.
Create a database in which to store the history of the Red Hat Enterprise Virtualization environment. The default database name is ovirt_engine_reports, and the default user name is ovirt_engine_reports:
```
postgres=# create database database_name owner user_name template template0 encoding 'UTF8' lc_collate 'en_US.UTF-8' lc_ctype 'en_US.UTF-8';
```
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:
```
host    database_name    user_name    X.X.X.X/32   md5
```
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.
Open the default port used for PostgreSQL database connections, and save the updated firewall rules:
```
# iptables -I INPUT 5 -p tcp --dport 5432 -j ACCEPT
# service iptables save
```
Restart the postgresql service:
```
# service postgresql restart
```

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

⁠

Procedure 3.20. Migrating the Database

Log in to one of the hosted-engine hosts and place the environment into global maintenance mode so that the High Availability agents do not interfere with the Manager virtual machine during the database migration:
```
# hosted-engine --set-maintenance --mode=global
```
Log in to the Red Hat Enterprise Virtualization Manager machine and stop the ovirt-engine-reportsd service so that it does not interfere with the engine backup:
```
# service ovirt-engine-reportsd stop
```

Create the ovirt_engine_reports database backup using the PostgreSQL pg_dump command:

# su - postgres -c 'pg_dump -F c ovirt_engine_reports -f /tmp/ovirt_engine_reports.dump'

Copy the backup file to the new database server. The target directory must allow write access for the postgres user:
```
# scp /tmp/ovirt_engine_reports.dump root@new.database.server.com:/tmp/ovirt_engine_reports.dump
```
Log in to the new database server and restore the database using the PostgreSQL pg_restore command:
```
# su - postgres -c 'pg_restore -d ovirt_engine_reports /tmp/ovirt_engine_reports.dump'
```
Log in to the Manager server and update /var/lib/ovirt-engine-reports/build-conf/master.properties, replacing the localhost value of dbHost with the IP address of the new database server. If the ovirt_engine_reports js.dbName, dbUsername, or dbPassword differ on the new database server, update those values in this file.
Now that the database has been migrated, you must run engine-setup to rebuild reports with the new credentials:
```
# engine-setup
```
Log in to a hosted-engine host and turn off maintenance mode, enabling the High Availability agents:
```
# hosted-engine --set-maintenance --mode=none
```