Open topic with navigation

# Set Up a PostgreSQL Database on Windows

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

The procedure describes setting up the database server using the psql command-line tool. If you prefer, you can use the pgAdmin graphical user interface. For more information, refer to the pgAdmin documentation on www.pgadmin.org.

To set up a PostgreSQL Media Server database on Windows

1. Download and install a PostgreSQL server. For instructions, refer to the PostgreSQL documentation on www.postgresql.org.

   - Ensure that the installation includes the PostgreSQL Unicode ODBC driver.
   - During installation, set up a user account with superuser privileges.
   NOTE:

   Once installed, the PostgreSQL server appears in the Services tab in Windows Task Manager.

2. Add the PostgreSQL bin directory path to the `PATH` environmental variable.

   NOTE:

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

3. Open the psql command-line tool:

   1. In the Windows Command Prompt, run the command:

          psql -U userName

   2. Enter your password when prompted.

4. 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='English_United Kingdom' LC_CTYPE='English_United Kingdom';

5. Connect to the new database using the command:

       \c databaseName

6. 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.

      NOTE:

      Replace backslashes in the file path with forward slashes. The psql command-line tool does not recognize backslashes in file paths.

7. 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.

8. Open the Data Sources (ODBC) program:

   1. In the Windows Control Panel, click System and Security.

      The System and Security window opens.

   2. Click Administrative Tools.

      The Administrative Tools window opens.

   3. Double-click Data Sources (ODBC).

      The ODBC Data Source Administrator dialog box opens.

9. In the User DSN tab, click Add... .

   The Create New Data Source dialog box opens.

10. Select the PostgreSQL Unicode driver from the list and click Finish.

    The PostgreSQL Unicode ODBC Driver (psqlODBC) Setup dialog box opens.

11. Complete the data source information fields:

    Data Source | The data source name (DSN). Media Server uses this string to connect to the database server.
    Database | The name of the database that you created in Step 2.
    Server | The IP address or hostname of the server that the database server is installed on.
    User Name | The user name to connect to the database server with.
    Description | An optional description for the data source.
    SSL Mode | Whether to use SSL to connect to the database server. NOTE: To enable SSL mode, you must also configure the database server to support SSL. For instructions, refer to the PostgreSQL documentation.
    Port | The port to use to communicate with the database server.
    Password | The password for the user account that connects to the database server.

12. Click Datasource.

    The Advanced Options (driverName) 1/2 dialog box opens.

13. (Optional) HPE recommends that you select the Use Declare/Fetch check box, to reduce memory use.

14. Click Page 2.

    The Advanced Options (driverName) 2/2 dialog box opens.

15. Select the bytea as LO check box.

16. Click Apply and then OK.

    The Advanced Options (driverName) 2/2 dialog box closes.

17. In the PostgreSQL Unicode ODBC Driver (psqlODBC) Setup dialog box, click Test to test the connection.

    The Connection Test box opens containing a message describing whether the connection was successful. If the connection failed, use the information in the message to resolve any issues.

18. Click OK to close the Connection Test box.

19. In the PostgreSQL Unicode ODBC Driver (psqlODBC) Setup dialog box, click Save to close the dialog box.

20. In the ODBC Data Source Administrator dialog box, click OK to close the dialog box.

21. 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