Appendix D. Preparing a Remote PostgreSQL Database

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