Alliance Doc

Database servers

Revision as of 14:39, 2 December 2021 by Cgeroux (talk | contribs)
Other languages:


Database servers available for researchers

Compute Canada offers access to MySQL and Postgres database servers for researchers on both Cedar and Graham:

  • Cedar
    • MySQL
      • Description: General purpose server for the researcher wanting to set up SQL tables and issue SQL commands against them.
      • Server name: cedar-mysql-vm.int.cedar.computecanada.ca
      • Short server name: cedar-mysql-vm (can be used instead of long name on most compute nodes)
      • Version: MariaDB version 10.4 Community Edition
      • Documentation: http://www.mariadb.com
    • Postgres
      • Description: General purpose server for the researcher wanting to set up SQL tables and issue SQL commands against them. Includes a PostGIS extension available for those needing to do geocoding.
      • Server name: cedar-pgsql-vm.int.cedar.computecanada.ca
      • Short server name: cedar-pgsql-vm (can be used instead of long name on most compute nodes)
      • Version: PostgreSQL version 10.1, PostGIS version 2.4 extension available
      • Documentation: https://www.postgresql.org and https://postgis.net/documentation
  • Graham
    • MySQL
      • Description: General purpose server for the researcher wanting to set up SQL tables and issue SQL commands against them.
      • Server IP Address: 199.241.163.99
      • Server name: cc-gra-dbaas1.sharcnet.ca
      • Version: MariaDB version 10.2
      • Documentation: http://www.mariadb.com

Cedar MySQL server

The Cedar MySQL server offers MariaDB version 10.4 which is compatible with other flavours of MySQL. For information on compatibility, see https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/ . For information on the latest changes and improvements in MariaDB version 10.4, see https://mariadb.com/kb/en/changes-improvements-in-mariadb-104/ .

The MariaDB server runs as a VM called "cedar-mysql-vm" (full name: cedar-mysql-vm.int.cedar.computecanada.ca). Users who have accounts on the MySQL server are able to connect only through the Cedar head node (cedar.computecanada.ca), Cedar compute nodes and cedar portal (https://gateway.cedar.computecanada.ca).

For security, users cannot make an SSH connection to the database server directly.


MySQL account and connection

If you need the privileges to create your own database, you will need a MySQL account. To get a MySQL account on the Cedar MySQL server, please send a request to support@computecanada.ca with the following information:

  • Your Compute Canada username
  • Amount of database space needed for your project

We will then create an MySQL account with the same username as your Compute Canada username and a 16 digit random string password. The username, password, database server name, and other information required to make a MySQL connection will be stored in a file called .my.cnf located in your home directory. This file is confidential. You cannot change its contents but you can read it or delete it. If the file is deleted however, you will lose access to your database.

Run the "mysql" client to connect to the MySQL server. An older version of the client may be available without loading a module, but it will not give you access the latest features on the MySQL server. We recommend issuing the following commands to load a more recent version of the client: 

[name@server ~]$ module load mariadb
[name@server ~]$ mysql --version
mysql  Ver 15.1 Distrib 10.4.11-MariaDB, for Linux (x86_64) using  EditLine wrapper

You can use the following commands to test that your MySQL database account is set up correctly:

  
[name@server ~]$ mysql
MariaDB [(none)]> show databases;
MariaDB [(none)]> quit

Do not use the -p or -h options as arguments when running mysql. The required password and server name are taken automatically from your .my.cnf file.

It is acceptable to submit a long-running SQL command from the head node, as most of the CPU usage is taken from the database server side. However, if you run a script which issues SQL commands and uses lots of CPU, then it needs to be submitted as a job to the scheduler. See Running jobs for details.

How to set up your MySQL database

In order to be able to set up MySQL tables and query them, you need to create your own database. To create a database, the name of the database is arbitrary but it must start with "username_" where "username" is your MySQL username. For example, if your username were "david" the name of the database must start with "david_" and the commands to create a database called "david_db1", for example, would be:

  
[name@server ~]$ mysql
 MariaDB [(none)]> CREATE DATABASE david_db1;
 MariaDB [(none)]> quit

You may create multiple MySQL databases as long as they all start with "username_". The created databases will automatically be accessible only to you from the cedar head node, cedar compute nodes, and cedar portal (gateway.cedar.computecanada.ca) and you will have full privileges to create SQL objects such as tables, views, etc..

How to work with your MySQL database

Suppose you have account "david" and have created a database called "david_db1" and want to use it. Here's how:

  
[name@server ~]$ mysql
 MariaDB [(none)]> -- List available databases. Confirm david_db1 is in the list
 MariaDB [(none)]> SHOW DATABASES;
 MariaDB [(none)]> -- Get into the database
 MariaDB [(none)]> USE david_db1;
 MariaDB [(none)]> ... Issue SQL commands. See below for information.
 MariaDB [(none)]> quit

Useful websites for information on using MariaDB:

How to share your MySQL data

All MySQL account holders on cedar are able to share their own database with others. If you would like to share any table of your database with other users on the cluster please follow these steps. First you need to run mysql command and get into MySQL environment. Choose your database that you would like to share its table and run the command USE database; where "database" is the name of the database. Then run the command GRANT priv_type ON mytable TO ′user′@'172.%'; where "priv_type" and "mytable" are the type of grant and name of the table and "user" is the username of the person whom you would like to share your table with. Example: username "david" would like to share his table, "mytable" from database, "david_db" with another user whose username is "john" for reading only. Here are commands he needs to run.

  
[name@server ~]$ mysql
 MariaDB [(none)]> USE david_db;
 MariaDB [(none)]> GRANT SELECT on mytable to 'john'@'172.%';
 MariaDB [(none)]> quit;

Cedar PostgreSQL server

The Cedar Postgres server offers Postgres version 10.1 along with the PostGIS extension version 2.4.

The Cedar PostgreSQL server runs as a VM called "cedar-pgsql-vm" (full name: cedar-pgsql-vm.int.cedar.computecanada.ca). Users who have accounts on the PostgreSQL server are able to connect through the main Cedar head node (cedar.computecanada.ca), Cedar compute nodes, and Cedar portal machine (https://gateway.cedar.computecanada.ca).

For security, users cannot make an SSH connection to the database server directly.

To get an account and database on the Cedar PostgreSQL server, send a request to support@computecanada.ca with the following information:

  • Your Compute Canada username
  • Amount of database space needed for your project
  • Do you need the PostGIS 2.4 extension for the database?

PostgreSQL account and connection

The PostgreSQL account we create for you will have the same username as your Compute Canada username. You will be given a database. The name of the database will typically be "<username>_db" where <username> is your Compute Canada username. You cannot create a database yourself. If you need more than one database, please send us a request at support@computecanada.ca.

PostgreSQL uses IDENT authentication for connection from Cedar nodes which means you do not need to supply a password to access your PostgreSQL account. For security reasons your Compute Canada password must NEVER be required or used in a script. This also means that one user cannot access another user's database directly.

Run the "psql" client to connect to the PostgreSQL server. An older version of the client may be available without loading a module, but it will not give you access to the latest features of PostgreSQL version 10. We recommend loading the following module to use a more recent version of the client: 

[name@server ~]$ module load postgresql
[name@server ~]$ psql --version
psql (PostgreSQL) 10.2

How to work with your PostgreSQL database

Suppose you have account "david" and have been assigned a database called "david_db". Here is an example of how to use it from the Cedar head node:

  
[name@server ~]$ psql -h cedar-pgsql-vm -d david_db
 david_db=> -- List names of tables in your database
 david_db=> \dt
 david_db=> ... Issue SQL commands. See below for more information.
 david_db=> -- Quit
 david_db=> \q

Useful websites for information on using PostgreSQL:

How to share your PostgreSQL data

You can share your data in your PostgreSQL database with others. This is how:

  • The person with whom you want to share access must have a Postgres account on the system. They should apply for one as described above.
  • You will have to give the person connect access to your database.
  • For each table or view to be shared, you give the person one or more of select, update, insert, and delete access to it.
  • You can also revoke access to a table, view, or database.

Here is an example of user 'david' sharing a table with user 'kim':

  
[name@server ~]$ psql -h cedar-pgsql-vm -d david_db
 david_db=> -- Give kim connect access to the database
 david_db=> grant connect on database david_db to kim;
 david_db=> -- Give kim select-only access to a table called mytable
 david_db=> grant select on mytable to kim;
 david_db=> -- Quit
 david_db=> \q

Here is an example of user 'kim' accessing the shared table:

  
[name@server ~]$ psql -h cedar-pgsql-vm -d kim_db
 kim_db=> -- Connect to the database containing the table to be accessed
 kim_db=> \c david_db
 david_db=> -- Display the rows in the shared table
 david_db=> select * from mytable;
 david_db=> -- Quit
 david_db=> \q

Here is an example of user 'david' revoking access to 'kim':

  
[name@server ~]$ psql -h cedar-pgsql-vm -d david_db
 david_db=> -- Revoke kim's select-only access to a table called mytable
 david_db=> revoke select on mytable from kim;
 david_db=> -- Revoke kim's connect access to the database
 david_db=> revoke connect on database david_db from kim;
 david_db=> -- Quit
 david_db=> \q

Graham MySQL server

The steps for obtaining and using an account on the Graham MySQL server are similar to those described above for Cedar.


Cloud-based database servers

Database as a Service (DBaaS)

If a VM is not sufficient to run a database load, a managed database can be used instead, the current offering includes MySQL/MariaDB and Postgres on a physical system. The database systems as well as all databases are being backed up once a day. The backups are archived for 3 months. To request access, please contact Technical support.

Please provide in your request the client network or IP address you will access the database from.

Type Hostname TCP port
mysql dbaas101.arbutus.cloud.computecanada.ca 3306
pgsql dbaas101.arbutus.cloud.computecanada.ca 5432


The CA certificate which is used to sign the host certificate for the service, is available for download here.

PostgreSQL Database

Your instance will use a ssl connection to connect to the DBaaS host. The example below connects to the DBaaS host, as user01 and uses the database dbinstance via a ssl connection. 

psql --set "sslmode=require" -h dbaas101.arbutus.cloud.computecanada.ca -U user01 -d dbinstance
Password for user user01: 
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
dbinstance=> \l dbinstance
                               List of databases
    Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 
------------+--------+----------+-------------+-------------+-------------------
 dbinstance | user01 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | user01=CTc/user01
(1 row)

The ssl connection is enforced and plain text connections fail.

MariaDB/MySQL Database

Your instance will use a ssl connection to connect to the DBaaS host. The example below connects to the DBaaS host, as user01 and uses the database dbinstance via a ssl connection. 

mysql --ssl -h dbaas101.arbutus.cloud.computecanada.ca -u user01 -p dbinstance
Enter password: 
MariaDB [dbinstance]> show databases;
+--------------------+
| Database           |
+--------------------+
| dbinstance         |
| information_schema |
+--------------------+
2 rows in set (0.001 sec)

If you try to use a plain text connection, your authentication will fail. 

mysql -h dbaas101.arbutus.cloud.computecanada.ca -u user01 -p dbinstance
Enter password: 
ERROR 1045 (28000): Access denied for user 'user01'@'client.arbutus' (using password: YES)
Retrieved from "https://docs.alliancecan.ca/mediawiki/index.php?title=Database_servers&oldid=107199"