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

_FT_HTML5_bannerTitle.htm