Installing Stored Procedures on PostgreSQL

Follow these steps to install Team Studio stored procedures within a PostgreSQL database on Linux.

1. In the PostgreSQL data directory, find the file postgresql.conf (for example, /data/pgsql/9.3/data/postgresql.conf).
2. Change date style as follows.

   datestyle = 'iso, mdy'

3. Reload the configuration change (without being forced to restart Postgres).

   # su - postgres
   # /usr/bin/pg_ctl reload

4. Log in to Postgres using the psql command, and then run the following query.

   SELECT pg_reload_conf();

5. Verify that the master node can communicate with the Team Studio server.
   1. Modify the file /etc/hosts on the Team Studio server to include the Postgres master node IP address and hostname.
   2. Modify the file /etc/hosts on the Postgres master node to include the Team Studio IP address and hostname.
6. On the Postgres master host, create a folder called /home/postgres/database_setup.
7. Use secure copy (SCP) to transfer $CHORUS_HOME/alpine-current/database_setup.zip from the Team Studio server to the Postgres master node.

   Place it in the folder /home/gpadmin/database_setup.

8. Unzip the database_setup.zip file.

   This generates several folders.

   unzip /home/postgres/database_setup/database_setup.zip

   1. If the Postgres database administrator does not possess the ownership of this directory, then issue the chown command to reassign the ownership.

      # chown -R postgres:postgres /home/postgres/database_setup/

9. Log in to the system as the Postgres administrator (for example, postgres) on the Postgres master host.

   # su - postgres

10. Set the search path to include the public schema.
11. Navigate to the database_setup/Postgres directory.

    $ cd /home/postgres/database_setup/Postgres

12. Run the Team Studio installer (the .bin file).

    $ sh alpine_miner_installer_Postgres.bin

    1. Read and accept the license agreement.
    2. Specify the Postgres installation path. The setup places the required shared library in the directory $PGHOME/lib/postgres/.
    3. Specify if the installer should copy the shared library to the segment hosts. Enter y for multi-node clusters.
    4. If you entered y, then enter the full path to the file containing the segment host names.

       You can create your own host file. For example, create a file /tmp/hostfile and add all the segment host names or IP addresses one after the other in the file, similar to the following.

       segmenthost1
       segmenthost2
       segmenthost3
       /path/to/hostfile_pginitsystem 
       (or /tmp/hostfile)

    5. Specify the default database to install the Team Studio functions.
    6. Specify the port on which the Postgres database is running.
    7. Specify if you would like to create the Team Studio demo database.
    8. Verify the shared library exists on each segment node.
    The installer can fail with the following error.

    ......
    ./install.sh: line 162: lsb_release: command not found
    *****************************************************************
        Path /usr/pgsql-9.3/lib is the Postgres lib path to copy alpine_miner.so to? (y/n)
        Provide the path of Postgres lib to copy alpine_miner.so to
        or press ENTER to accept the default
    *****************************************************************
    Postgres lib path:
    Copying sharedLib/9.3/alpine_miner..so to /usr/pgsql-9.3/lib/alpine_miner.so
    Shared library file sharedLib/9.3/alpine_miner..so does not exist.

    If you see this error, then you must install the following packages and rerun the installer.

    yum install redhat-lsb redhat-lsb-core

13. Locate and open for editing the file /var/lib/pgsql/data/pg_hba.conf.
14. Modify the file to allow users access to the appropriate databases. For example, using the miner_demo database, add the following lines to the end of pg_hba.conf.

    local miner_demo miner_demo trust
    host miner_demo miner_demo 192.168.1.0/24 password

15. Reload the Postgres database to activate the changes made in the configuration file.

    # su - postgres
    # /usr/bin/pg_ctl reload