E-Maj extension setup

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 postgresql.conf configuration file

Main E-Maj functions set a lock on each table of a processed tables group. If some groups contains a large number of tables, it may be necessary to increase the value of the max_locks_per_transaction parameter in the postgresql.conf configuration file. This parameter is used by PostgreSQL to compute the size of the shared lock table that tracks locks for the whole instance. Its default value equals 64. It can be increased if an E-Maj operation fails with a message indicating that all entries of the shared lock table have been used.

Furthermore, if the parallel rollback client may be used, it will be probably necessary to adjust the max_prepared_transactions parameter.

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.