Database servers

Revision as of 18:12, 14 June 2018 by Wolfgang (talk | contribs) (Revamped MySQL section to load latest client and use it to access MySQL server)
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.2 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 - to be filled in
    • Postgres - to be filled in

Cedar MySQL server

The Cedar MySQL server offers MariaDB version 10.2 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 features in MariaDB version 10.2, see https://mariadb.com/resources/blog/whats-new-mariadb-server-102 .

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, and database server name and other required information to make a MySQL connection will be stored in a file called .my.cnf located in your home directory on Cedar. 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.

You can run the "mysql" client to connect to the MySQL server. However, the version of the client installed on Cedar is an older version and will not give you access the latest features on the MySQL server.

Issue the following commands to load up the latest version of the client:

 
[name@server ~]$ mysql --version
mysql  Ver 14.14 Distrib 5.5.54, for Linux (x86_64) using readline 5.1
 
[name@server ~]$ module load mariadb
 
[name@server ~]$ mysql --version
mysql  Ver 15.1 Distrib 10.1.21-MariaDB, for Linux (x86_64) using readline 5.1

To avoid needing to issue a module load mariadb command each time you log in to Cedar, simply add the command to your .bashrc file.

Issue the following commands to connect to the MySQL server, show a list of databases that you are allowed to access, then quit:

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

Please do not use the -p or -h option as an argument in running mysql. The required password and server name is taken automatically from your .my.cnf file.


It is acceptable to submit a long-running SQL command from the Cedar head node, as most of the CPU usage is taken from the database server side. However, if you are running a script which is issuing 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

Users can 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:

Cedar PostgreSQL server

The Cedar Postgres server offers Postgres version 10.1 along with the PostGIS extension version 2.4 . Postgres 10.1 mainly is an upgrade to correct bugs in version 10. For information on the latest features in Postgres version 10, see https://wiki.postgresql.org/wiki/New_in_postgres_10 .

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

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

Your created PostgreSQL account will have the same username as your Compute Canada username. You will be given a database. The name of the database will typically be "db_<username>" where <username> is your Compute Canada username. Users are not able to create databases. If you need more databases please send us a request at support@computecanada.ca . PostgreSQL uses IDENT authentication for connection from cedar compute nodes which means no password is needed to be supplied to your PostgreSQL account. For security reasons your Compute Canada password is to NEVER be required or used in a script. This also means that user A from a cedar compute node cannot make a PostgreSQL connection to user B.

When access is from the head node, however, PostgreSQL uses PAM authentication which means that you will be prompted for a password. Your password for PostgreSQL connection is the same as your Compute Canada password for this case. Example:

 
[name@server ~]$ psql -h cedar-pgsql-vm -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).

The example above runs an older version of the psql Postgres interactive client that by default is installed on all the nodes:

 
[name@server ~]$ psql --version
psql (PostgreSQL) 9.6.2

You should load the most recent version of the psql Postgres client which will stay loaded until you log off from your session. Example:

 
[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 "db_david" and want to use it from the Cedar head node. Here's how:

 
[name@server ~]$ psql -h cedar-pgsql-vm -d db_david
 Password:<-- Enter Compute Canada password
 db_david=> -- List names of tables in your database
 db_david=> \l
 db_david=> ... Issue SQL commands. See below for information.
 db_david=> -- Quit
 db_david=> \q

Useful websites for information on using PostgreSQL: