Set Up a PostgreSQL Database on Linux

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

To set up a PostgreSQL database on Linux

  1. Install the PostgreSQL database server using your package manager.

    TIP: PostgreSQL is installed by default on some Linux distributions, but it might be an older version. Your package manager might also offer an older version, unless you use the PostgreSQL yum or apt repositories (see https://www.postgresql.org).

  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 make the following changes:

    • Edit the configuration file postgresql.conf and configure PostgreSQL to listen on an IP address and port.
    • Edit the configuration file pg_hba.conf, to allow your Media Server to connect to the database.
  3. 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 terminal 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. For example:

      cd /opt/MediaServer/sql
    2. Start the psql command-line tool as the postgres user.

      sudo -u postgres psql
    3. 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='en_US.UTF-8' LC_CTYPE='en_US.UTF-8';

      where mediatraining is the name for the new database.

    4. Connect to the new database:

      \c mediatraining
    5. (Optional) Run the following command to ensure a script stops running if it encounters an error:

      \set ON_ERROR_STOP on
    6. Run the postgres.sql script provided in the sql folder of the Media Server installation directory.

      \i postgres.sql
    7. Close the psql tool.

      \q
  4. 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.

      sudo -u postgres psql -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
  5. 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.