Using PostgreSQL to host an ADR database

By default, Ansys Dynamic Reporting uses an SQLite database to store item metadata and large data files are store in the 'media' directory. In some installations, it can be desirable to use PostgreSQL to store the metadata. This configuration is supported, but is more complex. The various steps are outlined here.

Note

ADR supports PostgreSQL 12 and higher.

Note

The example will use the ADR CLI tools, but the same operations can be performed using the GUI launcher tool which has the added benefit of being able to test a connection independently.

This operation supports database hosting. It is not needed if one wants to pull data from an external PostgreSQL database as part of report generation. That operation may be handled by the SQL Query Generator template.

Have an PostgreSQL instance running

The PostgreSQL server instance needs to be up and running first. The hostname, port, username and password to the server are needed. ADR will place its tables inside of a specific 'database' inside of the PostgreSQL server. The name of the database is a parameter to ADR. The database should be created before going any further.

Note

ADR will overwrite the contents of the named database. One may choose a username/password that only has access to the specific database in the PostgreSQL server to avoid any issues.

The psql CLI tool is one way of creating the database. The following will create the database 'example_database' using the user 'postgres' and the default server info.

psql -U postgres -c 'create database example_database;'

Tools like pgAdmin4 may also be used to perform this operation.

Create an ADR database

ADR needs a location to store the 'media' content, status and other runtime files. The next step is to create a new, empty ADR database that will at this point be used the SQLite database backend. In our example, the directory 'D:/adr_test_dir' exists and is empty.

bin\adr_launcher create --db_directory D:\adr_test_dir

At this point, the directory will include a 'media' subdirectory and file like 'view_report.nexdb', 'nexus.conf' and 'db.sqlite3'.

Switch the ADR database to PostgreSQL

To switch the SQL metadata store from SQLite to PostgreSQL the current configuration file needs to be updated. This can be done from the command line with:

bin\adr_launcher save_config --db_directory D:\adr_test_dir  --postgresql_url postgresql://postgres:{password}@127.0.0.1:5432/example_database

The 'nexus.conf' file will be updated with the location of the new database. The --postgresql_url has the form:

postgresql://{username}:{password}@{host}:{port}}/{datbasename}

In our example, PostgreSQL is running on the local machine (127.0.0.1) and the default port (5432). The database name in the PostgreSQL server is 'example_database'. The username is 'postgres' and the password is hidden in these docs.

Note

No data migration from SQLite to PostgreSQL is performed. Any ADR data items and templates that might have been in the database will be lost in the operation. Thus, it is strong suggested one only make this change with a new, empty database.

Start the ADR server

This step will actually exercise the PostgreSQL connection and will create all of the necessary tables in the database. This command will not return until the server is stopped.

bin\adr_launcher start --db_directory D:\adr_test_dir --server_port 8888

The ADR server will be started and can be accessed on port 8888. To stop the server, use the command:

bin\adr_launcher stop --db_directory D:\adr_test_dir

Run in another command line shell. The PostgreSQL connection details were stored in the 'nexus.conf' file when the 'save_config' command was run, so they do not need to be provided again. If they change, rerun the 'save_config' command with the updated information.