Database server

From SciNet Users Documentation
Revision as of 15:22, 10 January 2022 by Ymeiron (talk | contribs) (Created page)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

A PostgreSQL server (version 14.1) is available for users whose research requires database access. To gain access, please contact support with a brief description of your needs. Users should specify whether they need a database of their own created, or just a user (role) on the database server (e.g. in the case where multiple users jointly access one database). By default, a database created for a user is named username_db0, with the last digit incrementing in case more than one database is needed. The user who requests the database owns it, and can grant privileges to other users as needed.

The PostgreSQL server is running on idb1.scinet.local port 5432. SSH access to the node is not possible, only SQL connections from compatible clients are accepted. The node is only accessible internally on the SciNet network (i.e. from Niagara nodes); to access the server from outside, SSH tunnelling has to be established through a Niagara login node.

Usage

The psql client program is available in the postgresql module installed in NiaEnv/2019b. Users can install the psycopg2 package in their Python virtual environment. Querying the database (even complex queries) can be done from a Niagara login node, but compute-intensive post-processing of query results has to be done on a compute node and in parallel.


Storage and backup

The database server node storage is local (i.e. disks), separate from the other SciNet systems (GPFS). The database server data are backed up nightly as a whole, with only a few snapshots kept (depending on storage availability): the implications are that if a database object is unintentionally altered or dropped, the unintended change will eventually propagate into the backups even if no further changes are made. Therefore, time is of the essence and support should be contacted immediately to extract data from a snapshot.

Quota

Database storage does not count toward a user's quota on SciNet filesystems, and the database server has no quota imposed at this time. We ask that users inform us if their database storage requirement is expected to exceed 1 TB.