Media Server 24.4
IDOL Media Server Help

# Set Up a PostgreSQL Database on Windows

This section describes how to set up a training database using PostgreSQL on Windows. The database can be on a different machine to your Media Server.

To set up a PostgreSQL database on Windows

1. Install the PostgreSQL database server. On Windows you can download an installer that installs the database server as a Windows service.
2. Configure the database server for use with Media Server. The default configuration for a new PostgreSQL installation allows only local connections, so if your Media Server is on a different machine you might need to edit the configuration file `pg_hba.conf`, to allow your Media Server to connect to the database.
3. Add the PostgreSQL `bin` directory to your `PATH` environment variable. This step enables you to use the command `psql` to start the PostgreSQL command-line tool from the Windows command prompt. If you do not add the directory to the `PATH` environment variable, you must specify the full path of the tool.

4. Create a new database to store Media Server training data and set up the database schema that Media Server requires. To set up the schema, run the `postgres.sql` script provided in the `sql` folder of the Media Server installation directory.

   1. Open a command prompt and change directory to the `sql` folder of the Media Server installation directory. This is important because the `postgres.sql` script refers to other files in the same directory.

          cd C:\MediaServer\sql

   2. Start the `psql` command-line tool.

          psql -U postgres

   3. Enter your password when prompted. This is the password you chose for the `postgres` superuser during the installation process.

   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 mediatraining WITH ENCODING 'UTF8' LC_COLLATE='English_United Kingdom' LC_CTYPE='English_United Kingdom';`

      where `mediatraining` is the name for the new database.

   5. Connect to the new database:

          \c mediatraining

   6. (Optional) Run the following command to ensure a script stops running if it encounters an error.

          \set ON_ERROR_STOP on

   7. Run the `postgres.sql` script provided in the `sql` folder of the Media Server installation directory.

      `\i postgres.sql`

   8. Close the `psql` tool.

      `\q`

5. Create a user for Media Server to use and grant the following privileges to that user:

   Grant... | On...
   --- | ---
   Create Temporary Tables | Database
   Select, Insert, Update, Delete | All tables
   Execute | All functions and stored procedures
   Usage | All sequences

   1. Start the `psql` command-line tool.

          psql -U postgres -d mediatraining

      where `mediatraining` is the name of the database you created.

   2. Create a new user:

          CREATE USER mediaserver WITH LOGIN PASSWORD 'password';

      where `mediaserver` is the name of the new user, and `password` is the password for the new user.

   3. Grant the necessary privileges to the new user:

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

      where `mediatraining` is the name of the database you created and `mediaserver` is the name of the user you created for Media Server.

   4. Close the `psql` tool.

      `\q`
6. You can now set up a data source name (DSN) so that Media Server can connect to the database. If you are running Media Server on Windows, see Create a PostgreSQL DSN on Windows. If you are running Media Server on Linux, see Create a PostgreSQL DSN on Linux.

Copyright 2024 Open Text
Version 24.4 | Last updated September 2024

Send documentation feedback

Close

Send documentation feedback

To send feedback using your default email client, open an email window.

Otherwise, send your feedback and the information below to MFI-swpdl.idoldocsfeedback@opentext.com.

We appreciate your feedback!

Product: IDOL Media Server 24.4

Topic Title: Set Up a PostgreSQL Database on Windows