Skip to content

PostgreSQL Database

Database Requirements

The supported versions are PostgreSQL 13.3 (or newer) - 64 bit

Note that libxml is needed, you might need to rebuild PostgreSQL with it.

Database Setup

Login to an existing database as a database superuser or a user who has CREATEROLE and CREATEDB privileges, then execute the following command: psql.exe -h <HOST-NAME> -W -U <USER_NAME> -p <PORT> -d <DATABASE_NAME>      -- Delete previous user if needed    -- DROP USER "MM";      -- If the user cannot be dropped due to any ownership issues, you'll need to reassign those objects to another user    -- REASSIGN OWNED BY "MM" TO <OTHER-USER-NAME>;    -- Or drop those objects    -- DROP OWNED BY "MM"      -- Create a user MM with LOGIN privilege    CREATE USER "MM" LOGIN PASSWORD 'MM123!';      -- Create a database MM with UTF8 encoding.    CREATE DATABASE "MM" WITH OWNER "MM" ENCODING 'UTF8';

Note: For maintenance reasons, PostgreSQL database indexes can be rebuilt as follows:

  • Stop the MM Tomcat server

  • run the following command under the MS-DOS command prompt assuming postgresql/bin is in the system path. This may take some time if your database is large. reindexdb --username=MM --dbname=MM -v

  • Restart the MM Tomcat server

For more information on reindexdb, refer to the PostgreSQL documentation.

Database Administration

In general, you should refer to your database administrator to perform administrative tasks with your PostgreSQL DB. However, there is a set of tools packaged for PostgreSQL databases, named pgadmin, which is available at https://www.pgadmin.org/download/.

The two most common activities seen are:

  • PostgreSQL database compression

  • PostgreSQL database reindexing.

PostgreSQL database compression

Steps

  1. Stop the Application Server and then the Database Server.

  2. Make a copy of the DB data folder.

  3. Start pgadmin and go to VACUUM FULL ANALYZE Maintenance:

  1. Review the log of the process:

PostgreSQL database reindexing

To address any issues when the indexes are corrupted.

Steps

  1. Start pgadmin and Indexes.:

  2. Select the index for which you see log errors and select Re-index.

  3. You may do this while the database is still running.

  4. If the issue is not resolved with the log messages, you will need to drop and recreate the index.

  5. For this you must top the Application Server and then the Database Server.

  6. Make a copy of the DB data folder.