Databases for AiiDA¶
AiiDA needs a database backend to store the nodes, node attributes and other information, allowing the end user to perform very fast queries of the results.
Before installing AiiDA, you have to choose (and possibly configure) a suitable supported backend.
Supported databases¶
Note
For those who do not want to read all this section, the short answer if you want to choose a database is SQLite if you just want to try out AiiDA without spending too much time in configuration (but SQLite is not suitable for production runs), while PostgreSQL for regular production use of AiiDA.
For those who are interested in the details, there are three supported database backends:
SQLite The SQLite backend is the fastest to configure: in fact, it does not really use a “real” database, but stores everything in a file. This is great if you never configured a database before and you just want to give AiiDA a try. However, keep in mind that it has many big shortcomings for a real AiiDA usage!
In fact, since everything is stored on a single file, each access (especially when writing or doing a transaction) to the database locks it: this means that a second thread wanting to access the database has to wait that the lock is released. We set up a timeout of about 60 seconds for each thread to retry to connect to the database, but after that time you will get an exception, with the risk of storing corrupted data in the AiiDA repository.
Therefore, it is OK to use SQLite for testing, but as soon as you want to use AiiDA in production, with more than one calculation submitted at each given time, please switch to a real database backend, like PostgreSQL.
Note
note, however, that typically SQLite is pretty fast for queries, once the database is loaded into memory, so it could be an interesting solution if you do not want to launch new calculations, but only to import the results and then query them (in a single-user approach).
PostgreSQL This is the database backend that the we, the AiiDA developers, suggest to use, because it is the one with most features.
MySQL This is another possible backend that you could use. However, we suggest that you use PostgreSQL instead of MySQL, due to some MySQL limitations (unless you have very strong reasons to prefer MySQL over PostgreSQL). In particular, some of the limitations of MySQL are:
- Only a precision of 1 second is possible for time objects, while PostgreSQL supports microsecond precision. This can be relevant for a proper sorting of calculations launched almost simultaneously.
- Indexed text columns can have an hardcoded maximum length. This can give issues with attributes, if you have very long key names or nested dictionaries/lists. These cannot be natively stored and therefore you either end up storing a JSON (therefore partially losing query capability) or you can even incur in problems.
Setup instructions¶
For any database, you may need to install a specific python package using
pip
; this typically also requires to have the development libraries
installed (the .h
C header files). Refer to the
installation documentation for more details.
SQLite¶
To use SQLite as backend, please install:
sudo apt-get install libsqlite3-dev
SQLite requires almost no configuration. In the verdi install
phase,
just type sqlite
when the Database engine
is required,
and then provide an absolute path
for the AiiDA Database location
field, that will be the file that
will store the full database (if
no file exists yet in that position, a fresh AiiDA database will be created).
Note
Do not forget to backup your database (instructions here).
PostgreSQL¶
Note
We assume here that you already installed PostgreSQL on your computer and that
you know the password for the postgres
user
(there are many tutorials online that explain how to do it,
depending on your operating system and distribution).
To install PostgreSQL under Ubuntu, you can do:
sudo apt-get install postgresql-9.1
sudo apt-get install postgresql-server-dev-9.1
sudo apt-get install postgresql-client-9.1
On Mac OS X, you can download binary packages to install PostgreSQL from the official website.
To properly configure a new database for AiiDA with PostgreSQL, you need to
create a new aiida
user and a new aiidadb
table.
To create the new aiida
user and the aiidadb
database, first
become the UNIX postgres
user, typing as root:
su - postgres
(or equivalently type sudo su - postgres
, depending on your distribution).
Then type the following command to enter in the PostgreSQL shell in the modality to create users:
psql template1
To create a new user for postgres (you can call it simply aiida
, as in the
example below), type in the psql
shell:
CREATE USER aiida WITH PASSWORD 'the_aiida_password';
where of course you have to change the_aiida_password
with a valid password.
Note
Remember, however, that since AiiDA needs to connect to this database, you will need to store this password in clear text in your home folder for each user that wants to have direct access to the database, therefore choose a strong password, but different from any that you already use!
Note
Did you just copy and paste the line above, therefore setting the
password to the_aiida_password
? Then, let’s change it! Choose a good
password this time, and then type the following command (this time replacing
the string new_aiida_password
with the password you chose!):
ALTER USER aiida PASSWORD 'new_aiida_password';
Then create a new aiidadb
database for AiiDA, and give ownership to user aiida
created above:
CREATE DATABASE aiidadb OWNER aiida;
and grant all privileges on this DB to the previously-created aiida
user:
GRANT ALL PRIVILEGES ON DATABASE aiidadb to aiida;
Finally, type \q
to quit the template1
shell, and exit
to exit the PostgreSQL shell.
To test if this worked, type this on a bash terminal (as a normal user):
psql -h localhost -d aiidadb -U aiida -W
and type the password you inserted before, when prompted.
If everything worked, you should get no error and the psql
shell.
Type \q
to exit.
If you use the names suggested above, in the verdi install
phase
you should use the following parameters:
Database engine: postgresql
PostgreSQL host: localhost
PostgreSQL port: 5432
AiiDA Database name: aiidadb
AiiDA Database user: aiida
AiiDA Database password: the_aiida_password
Note
Do not forget to backup your database (instructions here).
Note
If you want to move the physical location of the data files on your hard drive AFTER it has been created and filled, look at the instructions here.
Note
Due to the presence of a bug, PostgreSQL could refuse to restart after a crash. If this happens you should follow the instructions written here.
MySQL¶
To use properly configure a new database for AiiDA with MySQL, you need to
create a new aiida
user and a new aiidadb
table.
We assume here that you already installed MySQL on your computer and that you know your MySQL root password (there are many tutorials online that explain how to do it, depending on your operating system and distribution). To install mysql-client, you can do:
sudo apt-get install libmysqlclient-dev
After MySQL is installed, connect to it as the MySQL root account to create a new account. This can be done typing in the shell:
mysql -h localhost mysql -u root -p
(we are assuming that you installed the database on localhost
, even if this
is not strictly required - if this is not the case, change localhost
with the proper database host, but note that also some of the commands
reported below need to be adapted) and then type the MySQL root password when
prompted.
In the MySQL shell, type the following command to create a new user:
CREATE USER 'aiida'@'localhost' IDENTIFIED BY 'the_aiida_password';
where of course you have to change the_aiida_password
with a valid password.
Note
Remember, however, that since AiiDA needs to connect to this database, you will need to store this password in clear text in your home folder for each user that wants to have direct access to the database, therefore choose a strong password, but different from any that you already use!
Then, still in the MySQL shell, create a new database named aiida
using the
command:
CREATE DATABASE aiidadb;
and give all privileges to the aiida
user on this database:
GRANT ALL PRIVILEGES on aiidadb.* to aiida@localhost;
Note
‘’(only for developers)’’ If you are a developer and want to run
the tests using the MySQL database (to do so, you also have to set the
tests.use_sqlite
AiiDA property to False using the
verdi devel setproperty tests.use_sqlite False
command), you also have
to create a test_aiidadb
database. In this case, run also the two
following commands:
CREATE DATABASE test_aiidadb;
GRANT ALL PRIVILEGES on test_aiidadb.* to aiida@localhost;
If you use the names suggested above, in the verdi install
phase
you should use the following parameters:
Database engine: mysql
mySQL host: localhost
mySQL port: 3306
AiiDA Database name: aiidadb
AiiDA Database user: aiida
AiiDA Database password: the_aiida_passwd
Note
Do not forget to backup your database (instructions here).