Skip to content

Latest commit

 

History

History
368 lines (297 loc) · 15.4 KB

CONFIGURE_POSTGRESQL_JBOSS_EAP.adoc

File metadata and controls

368 lines (297 loc) · 15.4 KB
Raw

Configure the PostgreSQL Database for Use with the Quickstarts

Summary

Some of the quickstarts require the PostgreSQL database. This guide describes how to install and configure the database for use with these quickstarts. The instructions here are the minimum required to install PostgreSQL version 9.6. If you install a later version, be sure to modify the version when you issue the commands below. More detailed instructions on how to install, configure, and start PostgreSQL can be found on the Internet.

Note
 Although the database only needs to be installed once, to help partition each quickstart, Red Hat recommends using a separate database for each quickstart. Where you see QUICKSTART_DATABASE_NAME in the instructions, you should replace that with the name provided in the specific quickstart’s README file.

The following sections describe the steps necessary to install and configure PostgreSQL for use with the quickstarts:

  1. Download and Install PostgreSQL

    Follow the instructions for your operating system to download and install PostgreSQL.

  2. Create a Database User

    Create a user for the PostgreSQL database.

  3. Add the PostgreSQL Module to the JBoss EAP

    Create a JBoss Module for the JBoss EAP server.

  4. Configure the PostgreSQL Driver in the JBoss EAP Server

    Add PostgreSQL to the JBoss EAP server configuration.

  5. Remove the PostgreSQL Configuration

    When you are done testing, remove PostgreSQL from the server configuration.

Note
 See Use of EAP_HOME for information about the JBoss EAP installation path.

Download and Install PostgreSQL

Follow the instructions below for your operating system.

Note
 The installation of PostgreSQL is a one time procedure. However, unless you have set up the database to automatically start as a service, you must repeat the instructions for your operating system to start the database server every time you reboot your machine.

Download and Install PostgreSQL on Linux

Follow these steps to install and configure PostgreSQL on Linux. You can download the PDF installation guide here: http://yum.postgresql.org/files/PostgreSQL-RPM-Installation-PGDG.pdf

  1. Install PostgreSQL

    • The yum install instructions for PostgreSQL can be found here: http://yum.postgresql.org/howtoyum.php/

    • Download the repository RPM from here: http://yum.postgresql.org/repopackages.php/

    • To install PostgreSQL, open a terminal and type sudo rpm -ivh RPM_FILE_NAME, where RPM_FILE_NAME is the name of the downloaded repository RPM file, for example:

      $ sudo rpm -ivh pgdg-fedora96-9.6-3.noarch.rpm

    • Edit your distributions package manager definitions to exclude PostgreSQL. See the "important note" on http://yum.postgresql.org/howtoyum.php for details on how to exclude install-and-configure-the-postgresql-database packages from the repository of the distribution.

    • Install postgresql96 and postgres96-server by typing the following in a terminal:

      $ sudo yum install postgresql96 postgresql96-server

  2. Set a password for the postgres user.

    • In a terminal, login as root and set the postgres password by typing the following commands:

      $ su
passwd postgres

    • Choose a password

  3. Configure the test database.

    • In a terminal, login as the postgres user, navigate to the postgres directory, and initialize the database by typing:

      $ su postgres
# cd /usr/pgsql-9.6/bin/
# ./initdb -D /var/lib/pgsql/9.6/data

    • Modify the /var/lib/pgsql/9.6/data/pg_hba.conf file to set the authentication scheme to password for TCP connections. Modify the line following the IPv4 local connections, changing trust to to password. The line should look like this:

      host    all    all    127.0.0.1/32    password

    • Modify the /var/lib/pgsql/9.6/data/postgresql.conf file to allow prepared transactions and reduce the maximum number of connections

      max_prepared_transactions = 10
max_connections = 10

  4. Start the database server.

    • In the same terminal, type the following command. This command does not release the terminal. In the next step you need to open a new terminal.

      $ ./postgres -D /var/lib/pgsql/9.6/data
      Note
      		 If you do not configure the the database to automatically start as a service, you must repeat this instruction to start the database every time you reboot your machine.

  5. Create a database for the quickstart. As noted above, replace QUICKSTART_DATABASE_NAME with the database name provided in the quickstart README file.

    Open a new terminal and log in again as the postgres user. Navigate to the postgres/ directory, and create the database by typing the following:

    $ su postgres
# cd /usr/pgsql-9.6/bin/
# ./createdb QUICKSTART_DATABASE_NAME

Download and Install PostgreSQL on Macintosh

Follow these steps to install and start PostgreSQL on Mac OS X. Note that this guide covers only 'One click installer' option.

  1. Install PostgreSQL using the Mac OS X One click installer: http://www.postgresql.org/download/macosx

  2. Allow prepared transactions.

    • Log in as the postgres user.

      sudo su - postgres

    • Edit the /Library/PostgreSQL/9.6/data/postgresql.conf file to allow prepared transactions

      max_prepared_transactions = 10

  3. Start the database server

    cd /Library/PostgreSQL/9.6/bin
./pg_ctl -D ../data restart

  4. Create a database for the quickstart. As noted above, replace QUICKSTART_DATABASE_NAME with the name provided in the quickstart README file.

    ./createdb QUICKSTART_DATABASE_NAME

  5. Verify that everything works. As the postgres user using the password you specified in Step 1, type the following:

    cd /Library/PostgreSQL/9.6/bin
./psql -U postgres

    At the prompt, type the following commands:

    start transaction;
select 1;
prepare transaction 'foobar';
commit prepared 'foobar';

Download and Install PostgreSQL on Windows

Follow these steps to install and configure PostgreSQL on Windows.

  1. Install PostgreSQL using the Windows installer: http://www.postgresql.org/download/windows

  2. Enable password authentication and configure PostgreSQL to allow prepared transactions.

    • Modify the C:\Program Files\PostgreSQL\9.6\data\pg_hba.conf file to set the authentication scheme to password for TCP connections. Modify the line following the IPv4 local connections to change trust to password. The line should look like this:

      host    all    all    127.0.0.1/32    password

    • Modify the C:\Program Files\PostgreSQL\9.6\data\postgresql.conf file to allow prepared transactions and reduce the maximum number of connections:

      max_prepared_transactions = 10
max_connections = 10

  3. Start the database server.

    • Choose Start → All Programs → PostgreSQL 9.6\pgAdmin III

    • Server Groups → Servers (1) → PostgreSQL 9.6 (localhost:5432)

    • Right click → Stop Service

    • Right click → Start Service

  4. Create a database for the quickstart. As noted above, replace QUICKSTART_DATABASE_NAME with the name provided in the quickstart README file.

    Open a terminal and type the following:

    > cd C:\Program Files\PostgreSQL\9.6\bin\
> createdb.exe -U postgres QUICKSTART_DATABASE_NAME

Create a Database User

  1. Make sure the PostgreSQL bin/ directory is in your PATH.

    • Open a terminal, change to the root directory, and type the following command.

      $ psql
      Note
      		 If you see an error that 'psql' is not a recognized command, you need to add the PostgreSQL bin/` directory to your PATH environment variable.

  2. As the postgres user, start the PostgreSQL interactive terminal by typing the following command.

    $ psql -U postgres

  3. Create the user sa with password sa and all privileges on the database by typing the following commands. As noted above, replace QUICKSTART_DATABASE_NAME with the name provided in the quickstart README file.

    create user sa with password 'sa';
grant all privileges on database QUICKSTART_DATABASE_NAME to sa;
\q

  4. Test the connection to the database using the TCP connection as user sa. This validates that the change to pg_hba.conf file was made correctly:

    $ psql -h 127.0.0.1 -U sa QUICKSTART_DATABASE_NAME

Add the PostgreSQL Module to the JBoss EAP

  1. Create the following directory structure: EAP_HOME/modules/org/postgresql/main

  2. Download the JDBC41 version of JBDC driver from http://jdbc.postgresql.org/download.html and save it into the directory you created in the previous step. It should be named something like postgresql-42.1.4.jre7.jar.

  3. In the same directory, create a file named module.xml. Copy the following contents into the file, replacing the JAR name with the revision that matches the downloaded JAR name.

    <?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="org.postgresql">
    <resources>
        <resource-root path="postgresql-42.1.4.jre7.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

Configure the PostgreSQL Driver in the JBoss EAP Server

You configure the PostgreSQL driver in the JBoss EAP server by running management CLI commands. For your convenience, the quickstarts batch the commands into a configure-postgresql.cli script provided in the root directory of the quickstarts.

Run the Management CLI Script to Configure PostgreSQL

  1. Before you begin, back up your server configuration file.

    • If it is running, stop the JBoss EAP server.

    • Back up the configuration file: EAP_HOME/standalone/configuration/standalone-full.xml

    • After you have completed testing the quickstarts, you can replace this file to restore the server to its original configuration.

  2. Start the JBoss EAP server.

    • If the quickstart does not provide additional instructions, type the following command.

      • For Linux: $ EAP_HOME/bin/standalone.sh -c standalone-full.xml

      • For Windows: > EAP_HOME\bin\standalone.bat -c standalone-full.xml

    • If the quickstart instructs you to pass the node ID, add the -Djboss.tx.node.id=UNIQUE_NODE_ID argument using the appropriate node ID when you start the server.

      • For Linux: $ EAP_HOME/bin/standalone.sh -c standalone-full.xml -Djboss.tx.node.id=UNIQUE_NODE_ID

      • For Windows: > EAP_HOME\bin\standalone.bat -c standalone-full.xml -Djboss.tx.node.id=UNIQUE_NODE_ID

  3. Review the configure-postgres.cli file in the root of the quickstarts directory. This script adds the PostgreSQL driver to the datasources subsystem in the server configuration.

  4. Open a new terminal, navigate to the root directory of the quickstarts, and run the following command, replacing EAP_HOME with the path to your server:

    • For Linux: $ EAP_HOME/bin/jboss-cli.sh --connect --file=configure-postgresql.cli

    • For Windows: > EAP_HOME\bin\jboss-cli.bat --connect --file=configure-postgresql.cli

  5. You should see the following result when you run the script:

    #1 /subsystem=datasources/jdbc-driver=postgresql:add(driver-name=postgresql,driver-module-name=org.postgresql,driver-xa-datasource-class-name=org.postgresql.xa.PGXADataSource)
The batch executed successfully.

Review the PostgreSQL Changes to the Server Configuration

If you want to review and understand newly added XML configuration, stop the JBoss EAP server and open the EAP_HOME/standalone/configuration/standalone-full.xml file.

The postgresql driver was added to the <drivers> section in the datasources subsystem of the server configuration file.

<driver name="postgresql" module="org.postgresql">
    <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
</driver>

Remove the PostgreSQL Configuration

When you are done testing the quickstarts, you can remove the PostgreSQL configuration by running the remove-postgresql.cli script provided in the root directory of the quickstarts or by manually restoring the back-up copy the configuration file.

Remove the PostgreSQL Configuration by Running the Management CLI Script

  1. Start the JBoss EAP server.

    • If the quickstart does not provide additional instructions, type the following command.

      • For Linux: $ EAP_HOME/bin/standalone.sh -c standalone-full.xml

      • For Windows: > EAP_HOME\bin\standalone.bat -c standalone-full.xml

    • If the quickstart instructs you to pass the node ID, add the -Djboss.tx.node.id=UNIQUE_NODE_ID argument using the appropriate node ID when you start the server.

      • For Linux: $ EAP_HOME/bin/standalone.sh -c standalone-full.xml -Djboss.tx.node.id=UNIQUE_NODE_ID

      • For Windows: > EAP_HOME\bin\standalone.bat -c standalone-full.xml -Djboss.tx.node.id=UNIQUE_NODE_ID

  2. Open a new terminal, navigate to the root directory of this quickstart, and run the following command, replacing EAP_HOME with the path to your server:

    $ EAP_HOME/bin/jboss-cli.sh --connect --file=remove-postgresql.cli

    This script removes PostgreSQL from the datasources subsystem in the server configuration. You should see the following result when you run the script:

    #1 /subsystem=datasources/jdbc-driver=postgresql:remove
The batch executed successfully.
{"outcome" => "success"}

Remove the PostgreSQL Configuration Manually

  1. If it is running, stop the JBoss EAP server.

  2. Replace the EAP_HOME/standalone/configuration/standalone-full.xml file with the back-up copy of the file.