Set Up a PostgreSQL Database on Linux

To use Media Server with a PostgreSQL database, you must install a PostgreSQL server and ODBC driver, and configure Media Server to connect to the database through the driver.

The procedure describes how to set up a PostgreSQL database on a CentOS 6 distribution.

To set up a PostgreSQL Media Server database on Linux

  1. Edit the .repo file to exclude PostgreSQL:

    1. Open the CentOS-Base.repo file with a text editor. The file is usually located in /etc/yum.repos.d.
    2. Add the following line to the [base] and [updates] sections:

      exclude=postgresql*
  2. Download the PostgreSQL 9.x RPM file for your Linux distribution from the PostgreSQL Yum repository on www.postgresql.org. For example:

    curl -O http://yum.postgresql.org/9.3/redhat/rhel-5-x86_64/pgdg-centos93-9.3-1.noarch.rpm
  3. Install the PostgreSQL RPM file by running the command:

    sudo rpm -i RPM

    where RPM is the name of the downloaded RPM file.

  4. Install the required packages from the RPM file. Ensure that these include the ODBC driver. For example:

    sudo yum install postgresql93 postgresql93-odbc
  5. Add the PostgreSQL bin directory path to the PATH environmental variable by running the command:

    export PATH=$PATH:binDirectoryPath
    NOTE:

    This step enables you to use the command psql to start the PostgreSQL command-line tool (psql) from the terminal. If the directory path is not added to the PATH variable, you must specify the psql.exe file path in the terminal to start psql.

  6. Initialize and start PostgreSQL.

    1. Initialize the server by running the command:

      sudo service postgresql-9.3 initdb
    2. Start the server by running the command:

      sudo service postgresql-9.3 start
  7. Log on to the psql command-line tool by running the command:

    sudo -u postgres psql
  8. Run a CREATE DATABASE command to create a new database. Specify the following database settings.

    Database name Any name.
    Encoding Must be Unicode–either UTF8 or UCS2.
    Collation Any that is compatible with the encoding.
    Locale Any that is compatible with the encoding.

    For example:

    CREATE DATABASE myDatabase WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8';
  9. Connect to the new database using the command:

    \c databaseName
  10. Run the postgres.sql script provided in the Media Server installation directory. This script sets up the database schema that Media Server requires. The schema is inserted inside the public schema.

    1. Micro Focus recommends running the following command to ensure that the script stops running if it encounters an error:

      \set ON_ERROR_STOP on
    2. Run the script using the command:

      \i 'path/postgres.sql'

      where path is the script file path.

  11. Grant privileges to the user that Media Server will connect as. If security is not a consideration you could grant all privileges, but the required privileges are:

    Database Create Temporary Tables
    All tables Select, Insert, Update, Delete
    All functions and stored procedures Execute
    All sequences Usage

    For example:

    GRANT TEMP ON DATABASE databaseName TO userName;
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO userName;
    GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO userName;
    GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO userName;

    where,

    databaseName is the name of the database that you created.
    userName is the user name that Media Server will connect as.
  12. Install unixODBC driver manager version 2.2.14 or later. If using the Yum package manager, run the command:

    sudo yum install unixODBC
  13. Configure the data source.

    1. Open the odbc.ini file with a text editor. This file is usually stored in the /etc directory.
    2. Add the data source name in square brackets. The name can be any string. For example:

      [PostgreSQL_1]
    3. Under the data source name, set the following parameters.

      Parameter Description
      Driver The driver to use.
      ServerName The IP address or hostname of the server that the database server is installed on.
      Port The port to use to communicate with the database server.
      UserName The user name to connect to the database server with.
      Password The password for the user account that connects to the database server.
      Database The name of the database that you created in Step 2.
      ByteAsLongVarBinary

      You must set this parameter to 1.

      CAUTION:

      If this value is not set to 1, Media Server fails to start.

      UseDeclareFetch (Optional) Micro Focus recommends setting this parameter to 1, to reduce memory use.

      For example:

      [PostgreSQL_1]
      Driver=PostgreSQL
      ServerName=localhost
      Port=5432
      UserName=postgres
      Password=password
      Database=myDatabase
      ByteAsLongVarBinary=1
      UseDeclareFetch=1
      NOTE:

      You can set other parameters in this file, but these have not been tested with Media Server.

    4. Save and close the file.
  14. Configure the ODBC driver.

    1. Open the odbcinst.ini file with a text editor. This file is usually stored in the /etc directory.
    2. If not already present, add the database server name in square brackets. For example:

      [PostgreSQL]
    3. Under the database server name, set the following parameters.

      Parameter Description
      Description A description of the driver instance.
      Driver64 The location of the PostgreSQL driver library file.
      Setup64 The location of the driver installer file.
      FileUsage Set this parameter to 1.

      For example:

      [PostgreSQL]
      Description=ODBC for PostgreSQL
      Driver64=/usr/pgsql-9.3/lib/psqlodbc.so
      Setup64=/usr/lib64/libodbcpsqlS.so
      FileUsage=1
      NOTE:

      You can set other parameters in this file, but these have not been tested with Media Server.

    4. Save and close the file.
  15. You can now configure Media Server to connect to the database (see Configure Media Server).

_FT_HTML5_bannerTitle.htm