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
Edit the .repo file to exclude PostgreSQL:
/etc/yum.repos.d
. Add the following line to the [base]
and [updates]
sections:
exclude=postgresql*
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
Install the PostgreSQL RPM file by running the command:
sudo rpm -i RPM
where RPM
is the name of the downloaded RPM file.
Install the required packages from the RPM file. Ensure that these include the ODBC driver. For example:
sudo yum install postgresql93 postgresql93-odbc
Add the PostgreSQL bin directory path to the PATH
environmental variable by running the command:
export PATH=$PATH:binDirectoryPath
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.
Initialize and start PostgreSQL.
Initialize the server by running the command:
sudo service postgresql-9.3 initdb
Start the server by running the command:
sudo service postgresql-9.3 start
Log on to the psql command-line tool by running the command:
sudo -u postgres psql
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';
Connect to the new database using the command:
\c databaseName
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.
Micro Focus recommends running the following command to ensure that the script stops running if it encounters an error:
\set ON_ERROR_STOP on
Run the script using the command:
\i 'path/postgres.sql'
where path
is the script file path.
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. |
Install unixODBC driver manager version 2.2.14 or later. If using the Yum package manager, run the command:
sudo yum install unixODBC
odbc.ini
file with a text editor. This file is usually stored in the /etc
directory.Add the data source name in square brackets. The name can be any string. For example:
[PostgreSQL_1]
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 CAUTION:
If this value is not set to |
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
You can set other parameters in this file, but these have not been tested with Media Server.
Configure the ODBC driver.
/etc
directory.If not already present, add the database server name in square brackets. For example:
[PostgreSQL]
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
You can set other parameters in this file, but these have not been tested with Media Server.
|