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
pg_hba.conf
, to allow your Media Server to connect to the database.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.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.
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
Start the psql
command-line tool.
psql -U postgres
postgres
superuser during the installation process.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.
Connect to the new database:
\c mediatraining
(Optional) Run the following command to ensure a script stops running if it encounters an error.
\set ON_ERROR_STOP on
Run the postgres.sql
script provided in the sql
folder of the Media Server installation directory.
\i postgres.sql
Close the psql
tool.
\q
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 |
Start the psql
command-line tool.
psql -U postgres
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.
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.
Close the psql
tool.
\q
|