Create the emaj extension in a database
If an extension already exists in the database, but in an old E-Maj version, you need to upgrade it.
The standart way to install E-Maj consists in creating an EXTENSION object (in the PostgreSQL terminology). To achieve this task, the user must be logged to the database as superuser.
In environments for which this is not possible (cases of minimum installation), a psql script can be executed.
Optional preliminary operation
The technical tables of the E-Maj extension are created into the default tablespace. If the E-Maj administrator wants to store them into a dedicated tablespace, he can create it if needed and define it as the default tablespace for the session:
SET default_tablespace = <tablespace.name>;
Standart creation of the emaj EXTENSION
The E-Maj extension can now be created into the database, by executing the SQL command:
CREATE EXTENSION emaj CASCADE;
After having verified that the PostgreSQL version is at least 9.5, the script creates the emaj schema and populate it with technical tables, functions and some other objects.
Caution
The emaj schema must only contain E-Maj related objects.
If they are not already present, both emaj_adm and emaj_viewer roles are created.
Finally, the installation script looks at the instance configuration and may display a warning message regarding the max_prepared_transactions parameter.
Creating the extension by script
When creating the emaj EXTENSION is not possible, a psql script can be used instead:
\i <emaj_directory>/sql/emaj-<version>.sql
where <emaj_directory> is the directory generated by the E-Maj installation and <version> the current E-Maj version.
Caution
It is not mandatory to execute the installation script as superuser. But if it is not the case, the role used for this installation will need the rights to create triggers on the application tables of the future tables groups.
In this installation mode, all optimizations regarding E-Maj rollbacks are not available, leading to a decreased performance level of these operations.
Changes in the PostgreSQL instance configuration
Two configuration parameters may need to be changed in the postgresql.conf file:
max_locks_per_transaction to manage tables groups with a high number of tables. Main E-Maj functions set a lock on each table of a processed tables group. If an E-Maj operation fails with a message indicating that all entries of the shared lock table have been used, this parameter must be increased. Its default value equals 64.
max_prepared_transactions, to use the E-Maj parallel rollback client. Its default value is 0, blocking the use of this tool. Its value must be greater or equal the maximum number of concurrent parallel rollbacks operations.
In both cases, the PostgreSQL instance must be restarted.
E-Maj parameters
Several parameters have an influence on the E-Maj behaviour. They are presented in details here.
The parameters setting step is optional. E-Maj works well with the default parameter values.
However, if the E-Maj administrator wishes to take benefit from the rollback operations monitoring capabilities, it is necessary to set the dblink_user_password parameter into the emaj_param table and give the E-Maj administrator role the right to execute the dblink_connect_u function. More details…
Test and demonstration
It is possible to check whether the E-Maj installation works fine, and discover its main features by executing a demonstration script. Under psql, just execute the emaj_demo.sql script that is supplied with the extension:
\i <emaj_directory>/sql/demo.sql
If no error is encountered, the script displays this final message:
### This ends the E-Maj demo. Thank You for using E-Maj and have fun!
Examining the messages generated by the script execution, allows to discover most E-Maj features. Once the script execution is completed, the demonstration environment is left as is, so that it remains possible to examine it or to play with it. To suppress it, execute the cleaning function that the script has created:
SELECT emaj.emaj_demo_cleanup();
This drops the emaj_demo_app_schema schema and both emaj demo group 1 and emaj demo group 2 tables groups.