Chapter 2. Setting up Databases
2.1. Configuring PostgreSQL
- Adequate PostgreSQL settings for memory, timeouts, connections, and related settings
- A database
- A user with adequate permissions
2.1.1. Installing PostgreSQL
YUMto install PostgreSQL:
sudo yum install postgresql postgresql-serverTo install a specific version of PostgreSQL, go to: https://yum.postgresql.org/rpmchart.php and download the
postgresql, postgresql-server and postgresql-libsRPM packages and install via
yumfrom the download directory. For example:
sudo yum install postgresql91-9.1.24-2PGDG.rhel6.x86_64.rpm postgresql91-libs-9.1.24-2PGDG.rhel6.x86_64.rpm postgresql91-server-9.1.24-2PGDG.rhel6.x86_64.rpm
2.1.2. Configuring PostgreSQL
- Optional. Change the password for the Unix user for PostgreSQL:
sudo passwd postgres
- Initialize the database. The database must be initialized before starting the server.
service postgresql initdb
- Start Postgres. For example, on Red Hat Enterprise Linux:
service postgresql startOn Windows:
net start pgsql-8.3
- Set up a password for the
postgresuser on the database:
# su - postgres $ psql postgres=# ALTER USER postgres PASSWORD 'password'; ALTER ROLE postgres=#
- Create a PostgreSQL role named
rhqadmin, where 'password' should be replaced with a strong password.
postgres=# CREATE USER rhqadmin PASSWORD 'password'; CREATE ROLE
ImportantAlthough the default postgresql credentials are user
rhqadmin, these credentials should not be used as they present a security risk. Use these credentials are needed for Section 3.3, “Configuring the Server with the Web Installer” or Section 3.4, “Silently Installing the JBoss ON Server”.
- Create a PostgreSQL database named
rhq, specifying the
rhqadminrole as the owner.
postgres=# CREATE DATABASE rhq OWNER rhqadmin; CREATE DATABASE
- Give users on the computer access to the database. To allow all users, add the appropriate connection settings for each connection type (local, IPv4, and IPv6) to the
data/pg_hba.confconfiguration file, for both local and external connections:
# "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 172.31.7.0/24 md5 # IPv6 local connections: host all all ::1/128 md5Using
all allsets these settings for every user to every PostgreSQL database. This settings can be applied to only the JBoss ON database by using
rhq allor even to specific users for JBoss ON, such as
rhq rhqadmin.Then, restart the database service.
service postgresql restart
- Make the configuration changes in Section 2.1.3, “Setting PostgreSQL Parameters”.
2.1.3. Setting PostgreSQL Parameters
18.104.22.168. Editing the postgresql.conf File
- Make sure that an adequate amount of memory and system resources are assigned to accommodate the JBoss ON database.
## not necessary if the database is started with the -i flag listen_addresses = '*' ## performance changes for JBoss ON shared_buffers = 80MB # default is 32MB work_mem = 2048 # default is 1MB checkpoint_segments = 10 # default is 3For PostgreSQL 8.2.4 and 8.3, also set the
max_fsm_pagesparameter. (This parameter should not be used on PostgreSQL 8.4 and later databases.)
max_fsm_pages = 100000 # default is 204800
- Optional. Set the statement timeout period so a size that is adequate to handle data compression in large environments. By default, the default is zero (0) seconds, which means there is no statement timeout set; not having a timeout period is the preferred setting for smaller deployments.
statement_timeout = 0s # default is 0s
NoteIf there is already a global statement timeout period for that database, but you need to use a larger setting for JBoss ON, set a user-level statement timeout value that only applies to the JBoss ON user.
ALTER USER rhqadmin SET statement_timeout=600000;
- JBoss ON can use up to 55 database connections for the server. PostgreSQL also allows for connections reserved for administrators. These connections are counted in the pool of
max_connectionsand therefore need to be added to the total number of
max_connections. For example, if there are five connections reserved for the administrator, edit the
postgresql.conffile as follows:
max_connections = 60 # default is 100 superuser_reserved_connections = 5 # default is 3 max_prepared_transactions = 60 # default is 5 (in v8.3) or 0 (in v8.4)
max_prepared_transactionsis set to the same value as
max_connections, as explained in the "max_prepared_transactions (integer)" in the PostgreSQL documentation.If JBoss ON is also monitoring this database instance, add one more connection per (logical) database that is set up in PostgreSQL. For further information about this plug-in, see the Postgres server section of the Resource Monitoring Reference.
22.214.171.124. Setting Kernel Parameters
126.96.36.199. Editing pg_hba.conf
pg_hba.conffile to allow the newly-created role to connect from the machine the JBoss ON server is installed on, such as localhost. Adding client connections is covered in the PostgreSQL documentation in the Client Authentication section.
pg_hba.conffile, restart PostgreSQL for the changes to take effect. If no errors are displayed, the database is now ready to support a JBoss ON installation.
188.8.131.52. Fixes for "Relation RHQ_Principal does not exist" Error
non-Clocale through PostgreSQL instances.
- Using a database explorer, create an empty table called
RHQ_PRINCIPALin the database used for JBoss ON.
- Click Install server.The installer displays a warning about an existing schema. Overwrite the existing schema as it only consists of one empty table.
SQL-ASCIIat creation time. For example:
initdb -D /my/test/data -E SQL_ASCII --locale en_US.UTF-8