Open topic with navigation

# 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. HPE 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) HPE 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).

© 2017 Hewlett Packard Enterprise Development LP | Send documentation feedback to HPE Close Send documentation feedback To open the configured email client on this computer, open an email window. Otherwise, copy the information below to a web mail client, and send this email to AutonomyTPFeedback@hpe.com. We appreciate your feedback! Product: Topic Title: Help Topic: Feedback:

_HP_HTML5_bannerTitle.htm