Database servers

This article is a draft

This is not a complete article: This is a draft, a work in progress that is intended to be published into an article, which may or may not be ready for inclusion in the main wiki. It should not necessarily be considered factual or authoritative.

Database Servers Available for Researchers

Compute Canada offers access to MySQL and Postgres database servers for researchers.

Cedar MySQL server

Server version: MariaDB MySQL version 10.2 Community Edition

Documentation: http://www.mariadb.com

The Cedar MySQL server runs as a VM called "cedar-mysql-vm" on a database machine. Users who have accounts on the MySQL server are able to connect only through the Cedar headnode (cedar.computecanada.ca), compute nodes and joffre machine.

For security, users cannot make an SSH connection to cedar-mysql-vm.

To get an account on the Cedar MySQL server, send a request to support@computecanada.ca with full particulars.

MySQL account and connection

If you need a MySQL account on the Cedar MySQL server please send your request to support@computecanada.ca. Once the account is created all required information for connection to the MySQL server (cedar-mysql-vm) will be stored in a file called .my.cnf located at your home directory on a Cedar node .

The information includes the MySQL username which is the same as Compute Canada username, password which is a 16 digit random ASCII characters (it is not your Compute Canada password) and name of the machine which runs MySQL server (cedar-mysql-vm). Please do not delete this file as it contains your mysql password. The file is confidential and readable only by the user. To connect to the mysql server run the following command line type on Cedar:

1. mysql

Reminder: users are not allowed to run compute-intensive production jobs on the Cedar head node. Therefore the above command should be run as an interactive job submission where the database server is doing most of the work.

Please DO NOT use the -p option as an argument in running mysql. The required password will be automatically taken from your .my.cnf file if you don't use -p option. To connect to a mysql server from a Perl module, for example, from one of the BioPerl modules which is usually used to upload data in to the database the command line of a BioPerl command should contain the -d option as following:

-d [database name]:mysql_read_default_file=.my.cnf ....

where [database name] is the name of the database which should be already created by the user.

Rules to create database

You can create multiple databases. To create a database, the name of the database is arbitrary but it must start with

[username]_ .

E.g. for username "david" the name of the database MUST start with "david_" and the MySQL command to create a database called "david_db1" would be:

CREATE DATABASE david_db1;

The created database will automatically be accessible from the Cedar headnode, computenodes and joffre, so you do not need to do any other grant. However, if you want another user with a MySQL account on the server to view tables in your database you can issue this MySQL command:

GRANT SELECT ON [database name].* TO '[username 2]'@'172.%';

[username 2] is the user who takes that grant. In order to connect to MySQL from gbrowse, the corresponding line to co nnect to MySQL should contains:

db_args = -adaptor DBI::mysql

                          -dsn [database name];mysql_read_default_file=/home/[username]/.my.cnf
                         -user [username]

where [username] is the corresponding user name and [database name] is the name of the database.

PostgreSQL server

Server version: PostgreSQL version 10.1

Available extensions include: PostGIS version 2.4

Documentation: https://www.postgresql.org

The Cedar PostgreSQL server runs as a VM called "cedar-pgsql-vm" on a database machine. Users who have accounts on the

PostgreSQL server are able to connect only through the Cedar headnode (cedar.computecanada.ca), compute nodes and jof

fre machine. For security users cannot make an SSH connection to cedar-pgsql-vm.

To get an account on the Cedar PostgreSQL server, send a request to support@computecanada.ca with full particulars.

PostgreSQL account and connection

PostgreSQL uses "ident" authentication for connection from compute nodes which means a password does not need to be su pplied to your PostgreSQL account. PostgreSQL uses PAM authentication from the head node which means that you will be prompted for your Compute Canada pa ssword during an interactive session. For example, to connect to the PostgreSQL server from the head node, issue the c ommand where you will be prompted for your password:

1. psql -d db_[username]

where db_[username] is the name of the database that was set up for you with your PostgreSQL account. (If you require more databases to be set up for your PostgreSQL account to use, please send a request to support@computecanada.ca).

To connect to postgres from a perl modules for example from one of BioPerl modules the command line should contains "- a" and "-d" options as following:

-a DBI::Pg  -d dbi:Pg:dbname=[database name] ....

where [database name] is the name of your database.

In order to connect to postres from gbrowse, the corresponding line in configure file should contain:

db_args = -dsn dbi:Pg:dbname=[database name ]

                        -user [username]